Логическое проектирование реляционной БД

2.1.5.1 Преобразование ER–диаграммы в схему базы данных

База данных создаётся на основании схемы базы данных. Для преобразования ER–диаграммы в схему БД приведём уточнённую ER–диаграмму, содержащая атрибуты сущностей (рисунок 8).

Рисунок 8 Уточнённая ER–диаграмма издательской компании

Примечание: многозначные атрибуты на рисунке выделены подчеркиванием.

Преобразование ER–диаграммы в схему БД выполняется путем сопоставления каждой сущности и каждой связи, имеющей атрибуты, отношения (таблицы БД). Будем использовать обозначения, представленные на рисунке 9.

Рисунок 9 Обозначения, используемые на схеме базы данных

Полученная схема реляционной базы данных (РБД) приведена на рисунке 10.

Рисунок 10 Схема РБД, полученная из ER–диаграммы издательской компании

На схеме (рисунок 6) есть связь типа 1:1 – обязательная связь между КНИГАМИ и КОНТРАКТАМИ. Такие отношения следует объединять в одно. Дополнительный эффект от объединения этих отношений – слияние связей авторы–контракты и авторы–книги: ведь в нашем случае контракт заключается именно для написания книги.

Примечание: исключение для связи типа 1:1 составляют ситуации, когда для увеличения производительности системы в отдельную таблицу выделяются редко используемые данные большого объёма.

Связь типа 1:n (один-ко-многим) между отношениями реализуется через внешний ключ. Ключ вводится для того отношения, к которому осуществляется множественная связь (КНИГИ).

Связь редактировать между отношениями КНИГИ и СОТРУДНИКИ принадлежит к типу n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение, которое является соединением первичных ключей соответствующих отношений.

Бинарная связь между отношениями не может быть обязательной для обоих отношений. После объединения сущностей КНИГИ и КОНТРАКТЫ остаётся три связи, обязательные для всех участников: между авторами и книгами и между заказами и строками заказов. Такой тип связи означает, что, например, прежде чем добавить новый заказ в отношение ЗАКАЗЫ, нужно добавить новую строку в отношение СТРОКИ ЗАКАЗА, и наоборот. Поэтому для такой связи необходимо снять с одной стороны условие обязательности. Так как все эти связи будут реализованы с помощью внешнего ключа, снимем условие обязательности связей для отношений, содержащих первичные ключи.

Уточнённая схема РБД издательской компании приведена на рисунке 11.

Рисунок 11 Уточнённая схема РБД издательской компании

Схема на рисунке 11 содержит цикл "сотрудники–книги–сотрудники". Цикл допустим только в том случае, если связи, входящие в него, независимы друг от друга. Примем для нашей ПО, что ответственный редактор книги может являться также просто редактором этой же книги или не входить в число редакторов. При этом цикл не приводит к нарушению логической целостности данных.

Примечание. Существует несколько подходов для разрешения ситуаций, в которых связи, входящие в цикл, зависят друг от друга. Рассмотрим пример цикла "отделы–проекты–сотрудники–отделы" (рисунок 8,а). Будем считать, что в выполнении проекта могут участвовать только сотрудники, работающие в том же отделе, к которому относится проект. При циклической схеме СУБД не сможет гарантировать логическую целостность данных без использования дополнительных средств.

Один из способов – разорвать цикл, исключив одну из связей (рисунок 8,б) или введя промежуточное отношение (рисунок 8,в). В нашем случае можно было бы разорвать связь "сотрудники–проекты", если бы каждый сотрудник участвовал во всех проектах своего отдела. Промежуточное отношение можно было бы использовать, если бы существовала общая связь между сущностями, входящими в цикл. Например, если бы каждый сотрудник заключал договор с отделом на выполнение работ в рамках проекта. Тогда сущность ДОГОВОРЫ отражала бы связь между отделами, сотрудниками и проектами.

Другой способ разрешения цикла заключается в том, что в промежуточное отношение СОТРУДНИКИ– ПРОЕКТЫ, которое реализует связь многие-ко-многим, добавляются (мигрируют) внешние ключи Код отдела (D_id) из отношений СОТРУДНИКИ и ПРОЕКТЫ (рисунок 8,г). Эти ключи проверяются на равенство друг другу с помощью соответствующего ограничения целостности. Использование этого способа возможно в том случае, когда соответствующие связи (отдел–проект и отдел–сотрудник) имеют тип один-ко-многим и являются обязательными.

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

Рисунок 12 Некоторые способы разрешения циклов в схеме базы данных