Получение реляционной схемы из ER-модели

Итак, ER-диаграммы построены. Следующий этап проектирования – перенести диаграммы на язык таблиц конкретной СУБД. Можно сказать, что ER-диаграммы порождают реляционную базу данных. Процесс порождения можно легко формализовать, довести до автоматизма. Прежде всего, заметим, что почти всегда есть взаимнооднозначное соответствие между сущностью ER-модели и таблицей. При этом атрибуты сущности переходят в атрибуты (колонки, столбцы) таблицы, а первичный ключ сущности переходит в первичный ключ таблицы. В таблице 6.2 представлены правила соответствия бинарных связей сущностей и соответствующих элементов реляционной базы данных.

Таблица 6.2 - Правила соответствия

Тип бинарной связи	Элементы реляционной базы данных
Связь «один к одному», характеристики (0,1) - (1,1) (1,1) - (0,1)	Для каждой сущности строится своя таблица. Связь между таблицами «один к одному».
Связь «один к одному», характеристики (1,1) – (1,1)	Строится одна таблица, структура которой состоит из атрибутов обеих сущностей. В качестве первичного ключа берется ключ одной из сущностей.
Связь «один к одному», характеристики (0,1) – (0,1)	При построении связи на основе двух таблиц мы вынуждены допустить, что значение внешнего ключа может быть равно NULL. Если исключить эту возможность, то такую связь следует строить на основе трех таблиц. Одна таблица является таблицей – посредником. Она содержит первичные ключи двух других таблиц.
Связь «один ко многим», характеристики (0,1) – (1,N) (1,1) – (1,N)	Каждой сущности ставится в соответствие таблица. Связи между таблицами имеет тип «один ко многим» и строится на основе первичного ключа первой таблицы.
Связь «один ко многим», характеристики (0,1) – (0,N) (1,1) – (0,N)	Обычно такой тип связи строится на основе трех таблиц (пар. 2, Один ко многим). Таблица посредник содержит внешний ключ, соответствующий первичному ключу первой таблицы и внешний ключ, соответствующий первичному ключу второй таблицы.
Связь «многие ко многим», характеристики (0,N) – (0,N) (1,N) – (1,N) (1,N) – (0,N) (0,N) – (1,N)	Любая связь такого типа строится на основе трех таблиц (см. пар. 2, Многие ко многим).

Правила порождения позволят легко перейти от ER-модели данных к логической и физической реляционным моделям.

Алгоритм перехода приведен ниже.

Шаг 1. Каждая простая сущность превращается в таблицу. Простая сущность - сущность, не являющаяся подтипом и не имеющая подтипов. Имя сущности становится именем таблицы.

Шаг 2. Каждый атрибут становится возможным столбцом с тем же именем; может выбираться более точный формат. Столбцы, соответствующие необязательным атрибутам, могут содержать неопределенные значения; столбцы, соответствующие обязательным атрибутам, - не могут.

Шаг 3. Компоненты уникального идентификатора сущности превращаются в первичный ключ таблицы. Если имеется несколько возможных уникальных идентификатора, выбирается наиболее используемый. Если в состав уникального идентификатора входят связи, к числу столбцов первичного ключа добавляется копия уникального идентификатора сущности, находящейся на дальнем конце связи (этот процесс может продолжаться рекурсивно). Для именования этих столбцов используются имена концов связей и/или имена сущностей.

Шаг 4. Связи многие-к-одному (и один-к-одному) становятся внешними ключами. Т.е. делается копия уникального идентификатора с конца связи "один", и соответствующие столбцы составляют внешний ключ. Необязательные связи соответствуют столбцам, допускающим неопределенные значения; обязательные связи - столбцам, не допускающим неопределенные значения.

Шаг 5. Индексы создаются для первичного ключа (уникальный индекс), внешних ключей и тех атрибутов, на которых предполагается в основном базировать запросы.

Шаг 6. Если в концептуальной схеме присутствовали подтипы, то возможны два способа:

а) все подтипы в одной таблице;

б) для каждого подтипа - отдельная таблица.

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

При использовании метода (б) для каждого подтипа первого уровня (для более нижних - представления) супертип воссоздается с помощью представления UNION (из всех таблиц подтипов выбираются общие столбцы - столбцы супертипа).

Таблица 6.3 – Способы хранения подтипов

Все в одной таблице	Отдельная таблица - на подтип
Преимущества
Все хранится вместе Легкий доступ к супертипу и подтипам Требуется меньше таблиц	Более ясны правила подтипов Программы работают только с нужными таблицами
Недостатки
Слишком общее решение Требуется дополнительная логика работы с разными наборами столбцов и разными ограничениями Потенциальное узкое место (в связи с блокировками) Столбцы подтипов должны быть необязательными В некоторых СУБД для хранения неопределенных значений требуется дополнительная память	Слишком много таблиц Смущающие столбцы в представлении UNION Потенциальная потеря производительности при работе через UNION Над супертипом невозможны модификации

Шаг 7. Имеется два способа работы при наличии исключающих связей:

а) общий домен;

б) явные внешние ключи.

Если остающиеся внешние ключи все в одном домене, т.е. имеют общий формат (способ (а)), то создаются два столбца: идентификатор связи и идентификатор сущности. Столбец идентификатора связи используется для различения связей, покрываемых дугой исключения. Столбец идентификатора сущности используется для хранения значений уникального идентификатора сущности на дальнем конце соответствующей связи.

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

Таблица 6.4 – Способы реализации исключающих связей

Общий домен	Явные внешние ключи
Преимущества
Нужно только два столбца	Условия соединения - явные
Недостатки
Оба дополнительных атрибута должны использоваться в соединениях	Слишком много столбцов

Альтернативные модели сущностей показаны на рисунках 6.19-6.21.

Рис. 6.19 - Вариант 1 (плохой)

Рис. 6.9 - Вариант 2 (существенно лучше, если подтипы действительно существуют)

Рис. 6.10 - Вариант 3 (применим при наличии осмысленного

супертипа D).

Заканчивая рассматривать ER-модель, заметим, что при тщательном анализе предметной области на предмет выявления сущностей, при переходе к реляционной базе данных дополнительная нормализация таблиц, скорее всего, не понадобится. Наличие зависимостей внутри таблицы часто означает, что мы в одной таблице пытаемся вместить несколько сущностей.