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

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

Опишем типовую многошаговую процедуру преобразования ER-диаграммы в реляционную (более точно, в SQL-ориентированную) схему базы данных.

Базовые приемы

Каждый простой тип сущности превращается в таблицу. (Простым типом сущности называется тип сущности, не являющийся подтипом и не имеющий подтипов.) Имя сущности становится именем таблицы. Экземплярам типа сущности соответствуют строки соответствующей таблицы.

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

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

Связи "многие к одному" (и "один к одному") становятся внешними ключами, т. е. образуется копия уникального идентификатора сущности на конце связи "один", и соответствующие столбцы составляют внешний ключ таблицы, соответствующей типу сущности на конце связи "многие". Необязательные связи соответствуют столбцам внешнего ключа, допускающим наличие неопределенных значений; обязательные связи – столбцам, не допускающим неопределенных значений. Если между двумя типами сущности   A и B имеется связь "один к одному", то соответствующий внешний ключ по желанию проектировщика может быть объявлен как в таблице A, так и в таблице B. Чтобы отразить в определении таблицы ограничение, которое заключается в том, что степень конца связи должна равняться единице, соответствующий (возможно, составной) столбец должен быть дополнительно специфицирован как возможный ключ таблицы (в случае использования языка SQL для этого служит спецификация UNIQUE ).

Для поддержки связи "многие ко многим" между типами сущности   A и B создается дополнительная таблица AB с двумя столбцами, один из которых содержит уникальные идентификаторы   экземпляров сущности   A, а другой – уникальные идентификаторы   экземпляров сущности   B. Обозначим через УИД(с)   уникальный идентификатор   экземпляра с некоторого типа сущности   C. Тогда, если в экземпляре связи "многие ко многим" участвуют экземпляры   a₁, a₂, ..., a_n   типа сущности   A и экземпляры   b₁, b₂, ..., b_m   типа сущности   B, то в таблице AB должны присутствовать все строки вида < УИД(ai), УИД(bj)> для i = 1, 2, ..., n, j = 1, 2, ..., m. Понятно, что, используя таблицы A, B и AB, с помощью стандартных реляционных операций можно найти все пары экземпляров типов сущности, участвующих в данной связи.

Индексы создаются для первичного ключа (уникальный индекс), внешних ключей и тех атрибутов, на которых предполагается в основном базировать запросы. ^{8Как отмечалось в начале лекции 6, вопросы определения индексов и других вспомогательных структур данных относятся к этапу физического, а не логического проектирования данных. Конечно, на практике эти этапы часто перекрываются во времени. Заметим, кстати, что в SQL-ориентированных СУБД индексы для всех возможных и внешних ключей, как правило, создаются системой автоматически.}

Представление в реляционной схеме супертипов и подтипов сущности

В этом подразделе мы предполагаем, что реляционная схема базы данных проектируется в расчете на использование обычной SQL-ориентированной СУБД, не поддерживающей объектно-реляционные расширения. Кстати, заметим, что поддержка таких расширений не слишком помогает при переходе от концептуальной схемы базы данных в модели "Сущность-Связь" к объектно-реляционной схеме, соответствующей последним стандартам языка SQL.

Если в концептуальной схеме (ER-диаграмме) присутствуют подтипы, то возможны два способа их представления в реляционной схеме:

• (a) собрать все подтипы в одной таблице;
• (b) для каждого подтипа образовать отдельную таблицу.

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

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

У каждого способа есть свои достоинства и недостатки. К достоинствам первого способа (одна таблица для супертипа и всех его подтипов ) можно отнести следующее:

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

Недостатки метода (a):

• прикладная программа, имеющая дело с одной таблицей супертипа, должна включать дополнительную логику работы с разными наборами столбцов (в зависимости от значения столбца ТИП) и разными ограничениями целостности (в зависимости от особенностей связей, определенных для подтипа );
• общая для всех подтипов таблица потенциально может стать узким местом при многопользовательском доступе по причине возможности блокировки таблицы целиком ^{9Этот аспект тоже относится к этапу физического проектирования, поскольку связан с особенностями реализации конкретной СУБД.} ;
• для индивидуальных столбцов подтипов должна допускаться возможность содержать неопределенные значения; таким образом, потенциально в общей таблице будет содержаться много неопределенных значений, что при использовании некоторых РСУБД может потребовать значительного объема внешней памяти ^{10Хотя в большинстве SQL-ориентированных СУБД хранение неопределенных значений вызывает минимальные накладные расходы; это снова аспект физического проектирования.}.

Достоинства метода (b) состоят в следующем:

• действуют более понятные правила работы с подтипами (каждому подтипу соответствует одноименная таблица);
• упрощается логика приложений; каждая программа работает только с нужной таблицей.

Недостатки метода (b):

• в общем случае требуется слишком много отдельных таблиц;
• работа с экземплярами   супертипа на основе представления, объединяющего таблицы супертипов, может оказаться недостаточно эффективной;
• поскольку множество экземпляров   супертипа является объединением множеств экземпляров   подтипов, не все РСУБД могут обеспечить выполнение операций модификации экземпляров   супертипа.