Формирование ДЛМ реляционной БД

Рассмотрим последовательно алгоритм формирования логической структуры реляционной базы данных на основе ER —диаграммы предметной области.

1 Преобразование простых классов объектов. Это классы объектов, информация о которых первой появляется в предметной области, они не имеют рекурсивных связей и не входят в супертипы, арки. Связи на стороне этих классов объектов имеют тип «один». Такие классы объектов называют родительскими или главными.

Алгоритм преобразования следующий: именем реляционного отношения становится имя класса объектов. Каждое свойство класса объектов становится атрибутом отношения, первичный ключ выделяется, уникальные (потенциальные) ключи помечаются. Все свойства, входящие в состав первичного ключа, должны быть обязательными. Большой составной первичный ключ может быть заменен суррогатным (техническим) первичным ключом. Атрибуты, входящие в состав уникальных ключей могут быть необязательными. Из состава уникальных ключей могут быть выбраны (помечены) ключи, альтернативные первичному ключу, для возможной реализации связи полученного реляционного отношения с другими отношениями.

На рисунке 25 приведен фрагмент ER —диаграммы с простым классом объектов, в таблице 13 – формализованное описание реляционного отношения.

Рисунок 25 – Простой класс объектов

Таблица 13 – Реляционное отношение «Физическое лицо»

В таблице использованы сокращения: ПК – первичный ключ, УК – уникальный ключ, Д.б. – обязательное значение поля, М.б. – необязательное значение поля.

2 Преобразование связи 1:М. Связь реализуется копированием первичного ключа из реляционного отношения на стороне «один» в реляционное отношение на стороне "много", из главного отношения в подчиненное. Новому появившемуся атрибуту присваивается уникальное в пределах отношения имя. В имени хорошо использовать имя таблицы, откуда осуществляется копия. Этот вновь появившийся атрибут помечается как внешний ключ. Если на ER —диаграмме опциональность связи со стороны «много» была обязательной, то опциональность внешнего ключа также обязательная. В противном случае опциональность внешнего ключа будет иметь значение "м.б.". Если уникальность класса объектов со стороны "много" определялась из связи, то внешний ключ должен входить в состав первичного ключа, эта ситуация соответственно помечается.

На рисунке 26 приведен фрагмент ER —диаграммы со связью 1:М. В таблицах 14 и 15 – реализация фрагмента в схеме реляционной БД.

Рисунок 26 – Пример связи 1:М

Таблица 14 – Реляционное отношение «Категория должности»

Таблица 15 – Реляционное отношение «Должность»

На рисунке 27 приведен пример фрагмента ER —диаграммы с отображением уникальности класса объектов из связи, в таблице 16 реализация части фрагмента в схеме реляционной БД. Каждый объект класса объектов «ЗАПИСЬ ТРУДОВОЙ КНИГИ» уникально определяется совокупностью двух свойств: свойства «дата начала» из класса объектов «ЗАПИСЬ ТРУДОВОЙ КНИГИ» и свойства «таб.номер» из класса объектов «ФИЗИЧЕСКОЕ ЛИЦО». Необходимо отметить, что свойство «дата» само по себе не может являться уникальным идентификатором.

Рисунок 27 – Определение уникальности класса объектов из связи

Таблица 16 – Реляционное отношение «Запись трудовой книги»

3 Преобразование связи 1:1. В ER —диаграмме связь 1:1 может иметь разную опциональность. От этого зависит её отображение в схеме БД. Если связь "один к одному" обязательна с одной стороны, то поле с внешним ключом добавляется в отношение на обязательной стороне и это отношение становится подчиненным, опциональность внешнего ключа будет обязательной. Если связь 1:1 необязательная или обязательная (что очень редко) в обоих направлениях, необходимо выбрать в какую таблицу будет помещен внешний ключ. Решение принимается в зависимости от времени появления и объема данных:

— если строка в одном отношении создается обычно раньше, чем в другом (это определяется предметной областью), то это отношение назначается главным, а внешний ключ создается в подчиненном отношении;

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

На рисунке 28 приведен пример фрагмента ER —диаграммы со связью 1:1, необязательной с одной стороны, в таблице 17 реализация части фрагмента в схеме реляционной БД. Главный класс объектов – ФИЗИЧЕСКОЕ ЛИЦО.

Рисунок 28 — Пример связи 1:1

Таблица 17 – Реляционное отношение «Адрес»

На рисунке 29 приведен пример фрагмента ER —диаграммы со связью 1:1, не обязательной с обеих сторон, в таблице 18 реализация части фрагмента в схеме реляционной БД. В качестве главного класса объектов выбран класс объектов ЧЛЕН КЛУБА. Это, наверное, более логично, хотя, если велосипедов гораздо меньше, чем членов клуба, то в виде главного может быть выбран класс объектов ВЕЛОСИПЕД.

Рисунок 29 – Пример необязательной связи 1:1

Таблица 18 – Реляционное отношение «Велосипед»

4 Преобразование рекурсивной связи. Поскольку рекурсивная связь – это связь между объектами одного класса объектов, то внешний ключ создается путем копирования первичного ключа в эту же схему отношения. Дополнительные ограничения рекурсивной связи, такие как, «объект не должен ссылаться сам на себя (нельзя быть женатым не себе самом, нельзя подчиняться себе самому)» реализуются либо в логике приложений, либо с помощью хранимых процедур, что более предпочтительно. Замечание: для рекурсивной связи 1:1 комбинация значений первичного и внешнего ключа не должна повторяться в кортежах отношения.

На рисунке 30 приведен пример фрагмента ER —диаграммы с рекурсивной связью, в таблице 19 реализация части фрагмента в схеме реляционной БД.

Рисунок 30 – Пример рекурсивной связи

Таблица 19 – Реляционное отношение «Узел конструкции»

5 Реализация взаимоисключающих связей. Реализация арка в логической структуре может иметь несколько альтернатив. Имеется два способа преобразования арков во внешние ключи: явное проектирование арка и неявное проектирование арка.

При явном проектировании арка создается столбец внешних ключей для каждой связи, входящей в арк. Этот метод используется в том случае, если внешние ключи имеют разные форматы (тип, длину). При этом если связи на ER —диаграмме обозначены как обязательные, то столбцы внешних ключей не могут определяться как обязательные, потому что для каждой строки реляционной таблицы имеет значение только один из внешних ключей — должна быть реализована ситуация взаимоисключаемости связей — «или—или». Проверка исключительности каждого внешнего ключа реализуется с помощью хранимых процедур или триггеров.

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

На рисунке 31 приведен пример фрагмента ER —диаграммы с арком, в таблице 20 реализация арка в схеме БД явно, в таблице 21 – реализация арка в схеме БД неявно.

Рисунок 31. Фрагмент ER —диаграммы с арком

Таблица 20 – Явная реализация арка. Реляционное отношение «Членский билет»

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

Таблица 21 – Неявная реализация арка. Реляционное отношение «Членский билет»

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

6 Реализация взаимоисключающих классов объектов. Реляционные отношения, отображающие супертипы и подтипы могут быть смоделированы по—разному. Два наиболее используемых способа реализации: в виде одного отношения и в виде нескольких отношений (сколько подтипов – столько отношений).

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

На рисунке 32 приведен пример фрагмента ER —диаграммы с супертипами, в таблице 22 приведена реализация супертипа в схеме БД в виде одного отношения.

Рисунок 32 – Пример фрагмента ER —диаграммы с супертипом

Таблица 22 — Реализация супертипа в виде одного отношения. Реляционное отношение «Назначение»

В таблице данные одного подтипа выделены курсивом, другого – жирным шрифтом. Опциональность всех полей таблицы, принадлежащих подтипам – «может быть».

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

В таблицах 23 и 24– представлена реализация супертипа в схеме БД в виде двух отношений (два подтипа).

Таблица 23 – Реализация супертипа в виде нескольких отношений. Реляционное отношение «Заявление»

Таблица 24 – Реализация супертипа в виде нескольких отношений. Реляционное отношение «Договор»

7 Реализация связей М:М. Наличие в ER —диаграмме связей М:М – это не дообследование предметной области и в этом случае модель предметной области не совсем адекватна.

Если связь М:М все—таки осталась, то от неё в схеме реляционной БД необходимо избавиться. Реляционные СУБД такую связь не поддерживают.

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