Текст книги "Как научиться проектировать базы данных и остаться в живых"

Как научиться проектировать базы данных и остаться в живых
Елена Литвак

© Елена Литвак, 2021

ISBN 978-5-0053-1339-3

Создано в интеллектуальной издательской системе Ridero

Введение

Вопросы проектирования реляционных баз данных поднимаются во множестве книг, написанных корифеями компьютерных наук. Я бы разделила эти книги на два типа: взрывающая мозг математика и интуитивное описание для чайников.

Почему математика? Да потому, что базы данных – это чисто математический объект, который называется «реляционная алгебра». И тут, поверьте, есть от чего взорваться мозгу. В книге известного исследователя в области информационных технологий Джеффри Ульмана «Системы баз данных: полный курс» целых 1088 страниц. Проектированию на основе математического аппарата и описания реляционной алгебры посвящена примерно половина книги. Надо ли говорить, что студент второго курса обычно засыпает уже на десятой странице? А взрослый работающий человек, который хочет повысить свой скил по проектированию, просто отчаивается: «Когда я все это освою, если мне нужно уже через неделю спроектировать базу данных и показать результаты?!».

«Интуитивное» описание процесса проектирования намного проще, но оно не дает универсального подхода. Друзья, вот таблица, вот еще таблица. Эта штука называется первичный ключ. А в другой таблице есть столбец с таким же названием. Так вот их нужно соединить! Понятно? Понятно. Только вот, что делать, если у меня другая задача и другие таблицы? Кто вообще определяет, сколько у меня будет таблиц и что с чем соединять? Откуда в другой таблице взялся этот волшебный столбец? Что делать, если его там нет? На все эти вопросы «интуитивный» подход четкого ответа не дает. Он просто позволяет увидеть решение одной конкретной задачи.

Я проходила через все это сама, когда была студенткой на специальности «Прикладная математика». Я прохожу через это уже 18 лет с каждой новой группой моих студентов, которых я обучаю проектированию баз данных. На прочтение Ульмана у них просто нет ни времени, ни энтузиазма – им пять дисциплин сдавать в сессию. Да и, что тут греха таить, математика сразу вызывает стресс. А метод «я нашел в интернете» не позволяет спроектировать любую базу данных на все случаи жизни, то есть он не универсален.

За годы чтения курса баз данных на специальности «Прикладная информатика» я придумала методику проектирования, которая убивает двух зайцев сразу. С одной стороны совершенно не нужно продираться через несколько сотен страниц математики и падать в обморок от слов «кортеж», «алгебра», «нормальная форма», с другой стороны методика годится на все случаи жизни. Ну почти на все; -). Я даже рискну сказать ужасную для преподавателя вещь: если вы будете пользоваться этой методикой, вам не нужно будет думать, просто действуйте по алгоритму, и он сам приведет вас к результату. Ну что рискнем? Поехали!

Почему не нужно бояться баз данных?

Знаете почему не нужно бояться баз данных? Потому что в них нет ничего такого, чего нет в окружающем нас реальном мире. Вот смотрите, база данных проектируется для какой-то ограниченной части реального мира. Мы называем эту часть предметной областью. Например, «управление поликлиникой», «прием и отгрузка товара со склада», «управление кафе» – это все примеры предметных областей. База данных – это модель предметной области. А само слово «модель» означает, что допускаются определенные упрощения в сравнении с реальностью. В модели самолета нет таких деталей, которых нет в настоящем самолете. Наоборот, для упрощения некоторые детали в модели отсутствуют. Поэтому любая модель всегда будет проще реальности.

Модель поликлиники всегда будет проще, чем реальная поликлиника, потому что при построении этой модель мы обязательно сочтем что-то несущественным и не станем включать в модель. Модель склада тоже будет проще реального склада. И так далее.

Поэтому есть хорошая новость: база данных должна быть проще, чем предметная область, для которой мы ее строим. Ну а раз в реальной предметной области – в поликлинике, на складе, в кафе – работают живые люди, которые ничего не знают о проектировании баз данных, но все же как-то справляются со своими обязанностями, значит уж с упрощенной моделью этой реальности мы точно справимся. Нужно только понять, по каким правилам следует упрощать реальность.

Сущности предметной области

Совсем без терминов все-таки не получится. И самый важный для нас термин – это сущность предметной области. Иногда еще их называют информационными объектами, но мне больше нравится слово «сущность». Так что же это такое?

Сущностью предметной области называется абстрактное понятие, которое объединяет группу реальных объектов, имеющих одинаковые свойства.

Реальные объекты, объединенные в одну сущность, будем называть экземплярами сущности.

Как это понимать? Вот смотрите, допустим, наша предметная область – это поликлиника. Требуется разработать базу данных для ведения учета работающих врачей, пациентов и их визитов к врачам.

Иван Иванович – хирург, врач высшей категории со стажем 25 лет. А Анастасия Павловна – терапевт, врач первой категории со стажем 2 года. Оба они совершенно конкретные живые люди, с которыми можно поговорить. И чтобы рассказать о них, мы назвали их имена, специальности, категории и стаж. Таких врачей, как они, в поликлинике почти сотня и всех их мы можем назвать одним словом «врач». Врач – это и будет то абстрактное понятие, которое объединяет конкретных Ивана Ивановича, Анастасию Павловну и других их коллег. «Имя», «Специальность», «Категория» и «Стаж» – это и есть одинаковые свойства, которыми описываются разные врачи.

Свойства, которыми описывается сущность, принято называть атрибутами сущности.

Итак, мы выделили сущность «Врач» с атрибутами «Имя», «Специальность», «Категория» и «Стаж». А Иван Иванович и Анастасия Павловна являются экземплярами сущности «Врач», для которых атрибуты принимают уже конкретные значения.

Графически обычно сущность изображают в прямоугольнике, а атрибуты – в эллипсах, которые соединены с прямоугольником линиями.

Рис.1 Сущность «Врач»

Самое трудное в проектировании увидеть эти самые сущности. Давайте продолжим тренироваться на примере поликлиники. С врачами разобрались. А «Пациент» – это сущность? Как это выяснить? Очень просто. Задаем два вопроса и отвечаем на них:

– Реальных пациентов в поликлинике много или один? – Много. Следовательно, «пациенты» являются группой объектов.

– Разные пациенты описываются одинаковыми свойствами? – Да. У всех пациентов есть «Фамилия», «Дата рождения», «Адрес».

Следовательно, понятие «Пациент» полностью соответствует определению сущности.

Рис.2 Сущность «Пациент»

А является ли сущностью «Регистратура»? Снова задаем вопрос:

– Реальных регистратур в поликлинике много или одна? – Одна. Следовательно, «Регистратура» не является сущностью, потому что нет группы объектов. Второй вопрос можно уже не задавать.

А чем же тогда является «Регистратура»? Ведь такой объект в поликлинике есть, значит, он должен как-то отражаться в базе данных. Регистратура – это экземпляр сущности «Подразделение». Проверим эту гипотезу:

– Подразделений в поликлинике много? – Много. И регистратура – одно из них. А есть еще «Администрация», «Терапия», «Дневной стационар» и др. Следовательно, мы имеем группу объектов.

– Разные подразделения описываются одинаковыми свойствами? – Да. У каждого подразделения есть «Наименование», «Руководитель», «Номер телефона».

Следовательно, «Подразделение» – это сущность.

Рис.3 Сущность «Подразделение»

Не надейтесь, что вы сразу выделите все сущности предметной области. В процессе проектирования будут обнаруживаться новые сущности, которые мы упустили из виду. Это нормально. Наша задача выделить на первом этапе основные сущности, которые сразу бросаются в глаза.

Связи между сущностями

Итак, в предметной области «Поликлиника» мы выделили пока три сущности:

– Врач.

– Пациент.

– Подразделение.

Для начала нам хватит.

В реальной жизни объекты предметной области взаимодействуют между собой. При проектировании факт взаимодействия отражается построением связей между сущностями. Связи между сущностями бывают трех типов:

– Связь «один-к-одному».

– Связь «один-ко-многим».

– Связь «много-ко-многим».

И на этом этапе несчастные студенты вынуждены заучивать нудное определение: «Связью типа „один-ко-многим“ называется связь, при которой одному экземпляру одной сущности соответствует много экземпляров другой сущности». Выучил? Молодец. А теперь пример. А в ответ тишина… Потому что практический смысл этого определения неясен.

Мы подойдем к проблеме чуть-чуть иначе. Для начала вспомним, что база данных – это модель реальности. Это значит, что само понятие «связь» можно увидеть где-то в реальной жизни. Где? Все просто. Возьмите любые две сущности и попробуйте описать словами русского языка их взаимодействие в формате

Рис.4 Структура описания связи

Да, да! Вас не зря мучили русским языком в школе. Ютуберы врут, когда рассказывают, что в школе их учили ерунде, которая никогда не пригодится в жизни. Вот тут-то эти знания вам и пригодились.

Итак,

Рис.5 Примеры описания связей

Если у вас получилось построить такое предложение и оно звучит нормально и естественно, то связь есть. Только помните, что в одном предложении участвуют ровно две сущности!

Между любыми ли двумя сущностями должна быть связь? Конечно, нет. Например, есть ли связь между сущностями «Пациент» и «Подразделение»? Можем ли мы подобрать такой глагол, который позволит построить предложение? Что у нас пациент должен делать в подразделении? Посещать его? Но посещает он не подразделение, а конкретного врача. Нет, очевидно, мы не можем подобрать такой глагол, который бы естественно связал эти две сущности. Поэтому прямой связи между ними нет, и не нужно строить в базе данных то, чего нет в реальной жизни.

Теперь давайте определим типы связей между сущностями. Для этого я предлагаю простой алгоритм. Не поленитесь взять ручку или открыть текстовый редактор и еще раз описать связь. Только теперь уже двумя предложениями. Подлежащее в обоих предложениях должно обязательно начинаться со слова «Один» («Одна», «Одно»), а вот дополнение может начинаться как со слова «Один» так и со слова «Много». Первое предложение должно описывать связь с позиции первой сущности, а второе предложение – с позиции второй сущности.

Итак,

Рис. 6 Описание связей с учетом типа

Дальше просто возьмите ручку или мышку и обведите числительные в дополнениях обоих предложений. Вот так:

Рис.7 Выделяем числительные в дополнении

То, что вы обвели, и будет названием типа связи между сущностями. То есть между сущностями «Подразделение» и «Врач» имеется связь типа «один-ко-многим».

Да, вот так просто. Не нужно думать и ломать голову, нужно всего лишь на естественном человеческом языке проговорить описание связи. Только сделать это следует по определенным правилам. И тогда тип связи назовет себя сам.

Давайте теперь посмотрим, что у нас там с сущностями «Врач» и «Пациент».

Рис.8 Снова выделяем числительные в дополнении

Ведь так же и происходит в реальной жизни? И вот уже связь сама кричит нам «Да я же связь „много-ко-многим“, вы что не видите!».

Все, мы закончили со связями. И теперь можно изобразить результат наших стараний графически в виде так называемой ER-диаграммы или диаграммы «сущность-связь». Вот так.

Рис.9 ER-диаграмма в нотации П. Чена

Кроме уже знакомых нам прямоугольников-сущностей и эллипсов-атрибутов, сюда добавлены связи, которые изображаются в ромбах, а также их типы. Связь «один-ко-многим» обозначается как 1:M, а связь «много-ко-многим» как N:M.

Такой вид ER-диаграмм предложил американский ученый в области информатики Питер Чен, поэтому он так и называется «нотация П. Чена».

Вообще говоря, нотация П. Чена громоздка и поэтому не очень удобна. В ней трудно изобразить сложную предметную область с большим числом сущностей, атрибутов и связей. Но для начинающих она очень полезна. Чуть позже мы перейдем к другому более компактному виду ER-диаграмм.

Сколько разных связей можно теоретически построить между двумя сущностями?

Допустим у нас есть две любые сущности. Сколько связей теоретически можно между ними построить? Чтобы ответить на этот вопрос нужно просто перебрать все возможные варианты построения наших двух предложений.

Рис.10 Все возможное варианты связей

У нас получилось четыре возможных варианта. Разумеется, только один из них (последний) соответствует тому, что происходит в реальной жизни. Все остальные три варианта неверны. Где вы видели врача, который за всю свою карьеру принял ровно одного пациента? Или пациента, который посетил только одного врача?

Но речь сейчас не об этом. Важно понимать, что существует три типа связи и четыре разные возможности построить связь между любыми двумя сущностями. Обратите внимание, что связи типа «один-ко-многим» соответствует две разные возможности. Одна возможность описывает ситуацию, когда у одного врача много пациентов, другая описывает обратную ситуацию, когда у одного пациента много врачей. Правильной же из всех четырех возможностей в любом случае будет только одна.

Переходим к базе данных. Первичный ключ

Пока мы оперируем понятиями сущность и атрибут, нужно держать в голове простое правило:

В реляционной модели каждая сущность впоследствии превратится в тыкву таблицу, атрибуты сущности станут столбцами этой таблицы, а экземпляры сущности – строками.

Как-то вот так:

Рис.11 Переходим от сущности к таблице

При этом лучше сразу усвоить правильную терминологию. В теории баз данных не принято говорить «столбцы» и «строки». Чаще вместо этих слов используют термины «поле» и «запись».

Рис.12 Поля и записи

Ну хорошо. Сущность превращается в таблицу. А во что превращается связь? А вот тут нам никак не обойтись без нового понятия. Для каждой сущности нужно выделить так называемый первичный ключ.

Одиночным первичным ключом сущности называется такой атрибут, значение которого удовлетворят двум требованиям:

– оно никогда не бывает пустым.

– оно уникально для каждого экземпляра сущности.

Как это понимать? Сразу представляем сущность в виде таблицы и ищем такое поле, значение которого, во-первых, никогда не может быть пропущено, во-вторых, не будет двух записей с одинаковым значением этого поля.

Вот, допустим, специальность. Она есть у любого врача? Да. Значит у поля «Специальность» значение всегда непустое. Это хорошо. Но уникально ли оно? Конечно, нет! Ведь в поликлинике может быть несколько хирургов, несколько терапевтов. Значит, поле «Специальность» не может быть первичным ключом.

Очевидно, что с категорией и стажем точно такая же история. Да и имя врача, даже если мы добавим к нему фамилию, тоже атрибут крайне ненадежный в плане уникальности. Как говорил герой фильма «Здравствуйте, я ваша тетя»: «Мало ли в Бразилии Педров?». В поликлинике, как и в Бразилии, тоже могут быть полные тезки.

Поэтому в нашем случае ни один атрибут не может претендовать на роль первичного ключа. Что же делать? Скорее всего у сотрудников поликлиники есть уникальные коды сотрудников, которые присваивает отдел кадров. Их было бы правильно использовать как первичный ключ. Если подходящий атрибут не находится, то всегда можно создать искусственный атрибут с порядковой нумерацией. На ER-диаграмме первичный ключ обычно выделяется подчеркиванием.

Рис.12 Добавляем первичные ключи в ER-диаграмму

Кроме одиночных первичных ключей бывают также составные первичные ключи. Когда в качестве первичного ключа рассматривают два или более атрибутов в комбинации. При этом каждый из атрибутов по отдельности не является уникальным, но их комбинация уникальна.

Например, если мы решим хранить серию и номер паспорта в двух разных столбцах таблицы, то и серии, и номера будут повторяться. Но их комбинация всегда будет уникальной.

Что означают связи на уровне базы данных?

Как построить связь «один-ко-многим»?

Теперь, когда мы разобрались с первичным ключом, можно сформулировать правила построения связей.

Правило №1. Для того чтобы построить в базе данных связь типа «один-ко-многим» нужно перенести первичный ключ из одной таблицы в другую. Новое поле, которое появится после переноса, уже не будет уникальным. И называться оно будет внешним ключом.

Как мы помним, связь типа «один-ко-многим» имеется между сущностями «Подразделение» и «Врач». При чем одному подразделению ставится в соответствие много врачей, а не наоборот. Поэтому в данном случае первичный ключ нужно будет переносить из таблицы «Подразделения» в таблицу «Врачи». Проще говоря, кто «один», тот и отдает первичный ключ. Кого «много», тот принимает внешний ключ. В таблицах первичный ключ обычно обозначают аббревиатурой PK – Primary Key, а внешний ключ обозначают аббревиатурой FK – Foreign Key.

Теперь таблицы будут выглядеть так:

Рис.13 Построение связи типа «один-ко-многим»

Как построить связь «много-ко-многим»? Естественный ключ

Правило №2. Для того, чтобы построить в базе данных связь типа «много-ко-многим» нужно создать дополнительную ассоциирующую таблицу, в которую будут перенесены первичные ключи двух исходных таблиц в качестве внешних ключей.

То есть, чтобы отразить в базе данных связь типа «много-ко-многим» между сущностями «Врач» и «Пациент», нужно создать таблицу, у которой в одной записи рядом будут стоять «Код врача» и «Код пациента».

Но мы же помним, что сущность предметной области превращается в последствие в таблицу? Так вот обратное тоже верно. Если есть таблица, то должна быть и сущность, которую мы не увидели на первых этапах проектирования. Значит нужно искать, какой сущности соответствуют «Код врача» и «Код пациента», записанные рядом. Очевидно, что эти данные описывают факт посещения данного врача данным пациентом. Значит, должна быть сущность «Посещение». И если подумать, то в нее логично добавить такие атрибуты как «Дата посещения», «Время посещения».

А что будет в этой новой сущности первичным ключом? Очевидно, что ни один атрибут этой сущности не уникален. Повторяться будут значения всех атрибутов. Уникальной будет комбинация трех атрибутов: «Код пациента» + «Код врача»+ «Дата посещения», потому что один пациент к одному врачу вряд ли придет дважды за один и тот же рабочий день.

Таким образом, мы имеем составной первичный ключ. Такой ключ называется естественным ключом. Он уникальный, но громоздкий. Если мы дальше будем расширять задачу и связывать сущность «Посещение» с новыми сущностями, то переносить в другие таблицы придется все три поля естественного ключа. Это довольно неудобно, поэтому естественный ключ лучше заменить на одиночный искусственный ключ «Код посещения» с порядковой нумерацией и дальше пользоваться им.

Рис.14 Построение связи типа «много-ко-многим»

Пришло время прощаться с ER-диаграммами в нотации П. Чена, так как схема данных растет и становится очень громоздкой для этого способы записи. К счастью существует более компактная нотация ER-диаграмм. Называется она Crow’s foot – «Воронья лапка». Сущности в ней изображаются прямоугольниками, атрибуты записываются внутри этих прямоугольников, а связи показываются линиями, которые заканчиваются чем-то похожим на воронью лапку с той стороны, где в связи участвует много экземпляров сущности.

Рис.15 Окончательная ER-диаграмма в нотации Crow’s foot

Как построить связь «один-к-одному»? Категоризация

Мы обсудили все виды связи, кроме связи «один-к-одному». Поэтому следует восполнить этот пробел.

Правило №3. Для того чтобы построить связь типа «один-к-одному», нужно перенести первичный ключ из одной таблицы в другую (да, так же как и в случае связи «один-ко-многим»), но во второй таблице это поле должно стать уже первичным ключом.

Вообще говоря, связь типа «один-к-одному» в реальности бывает нужна очень редко. Когда у вас одно и тоже поле является первичным ключом для двух таблиц, то эти таблицы просто можно объединить.

Исключением является один случай, когда применение этого типа связи оправдано. Называется этот случай отношением категоризации.

Представьте себе, что вам нужно хранить таблицу с различными видами оборудования и его техническими характеристиками. Для той же поликлиники, например. Оборудование – это и компьютеры, и принтеры, и ксероксы и много чего другого. Безусловно, все это одна сущность «Оборудование». Только вот атрибуты у разных видов оборудования будут разными. Если мы будем хранить и компьютеры, и принтеры в одной таблице, то выглядеть она будет примерно так как показано на рис.16.

Рис.16 Сущность, состоящая из категорий с разными атрибутами

Как видите в таблице остаются пустыми целые прямоугольные массивы ячеек, так как для компьютера не заполняются атрибуты принтера, а для принтера остаются пустыми атрибуты компьютера. Поэтому есть смысл перейти от одной таблицы к трем. В таблице «Оборудование» будут храниться коды всех единиц оборудования, а в таблицах «Компьютеры» и «Принтеры» только соответствующие категории с их особыми атрибутами. При этом между сущностью «Оборудование» и сущностями «Компьютер» и «Принтер» будут две связи типа «один-к-одному» (рис.17).

Рис.17 Отношение категоризации. Связи типа «один-к-одному»