Проектирование реляционной базы данных

ER-модель может быть представлена в виде таблиц реляционной базы данных. Алгоритм такого проектирование приводится ниже. Здесь предполагается знание языка SQL.

Шаг 1. Преобразование сущностей в таблицы

Каждая простая сущность преобразуется в таблицу. Простой является сущность, которая не является подтипом или сама не имеет подтипов. В качестве имени отношения используется множественное число имени сущности.

Шаг 2. Преобразование атрибутов в столбцы

Каждый атрибут преобразуется в столбец с тем же именем. В этот момент может быть выбран более точный формат столбца.

Факультативные атрибуты становятся null-столбцами. Обязательные атрибуты становятся not-null-столбцами.

Шаг 3. Представление уникальных идентификаторов ключами таблиц

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

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

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

Шаг 4. Пребразование связей многие-к-одному и один-к-одному во внешние ключи.

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

Факультативным связям соответствуют null-столбцы. Обязательным связям соответствуют not-null-столбцы.

Шаги 1 - 4 предпринимаются в большинстве случаев логического проектирования.

АЭРОПОРТЫ
Код	char		not null		Первичный ключ
Название	char		not null
АВИАКОМПАНИИ
Код	char		not null		Первичный ключ
Название	char		not null		Внешний ключ из рекурсивной связи,
Код вышестоящей компании	char		null		квалифицируемый именами сущности и связи
РЕЙСЫ
Номер	integer		not null		Первичный ключ
Код авиакомпании	char		not null		Внешний ключ из авиакомпании
Код аэропорта из	char		not null
Код аэропорта в	char		not null		Внешние ключи из аэропорта
Время вылета	date		null

Рис. 7.37. Пример проектирования сущностей, атрибутов и связей.

Шаг 5. Проектирование при наличии подтипов

Подтип сущности - это просто сущность с ее собственными атрибутами и связями, однако она наследует любые атрибуты и/или связи из родительской сущности (супертипа) и т.д. вверх по иерархии супертипов.

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

Все в одной таблице

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

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

Эта же процедура применяется для каждого подтипа, всех его подтипов и т.д. Различие заключается только в том, что все столбцы, создаваемые для подтипов, делаются not null (факультативными). Обязательность атрибута (или связи) для одного подтипа не должна применяться для другого - поэтому все они должны стать факультативными, с возможным поддержанием этого ограничения целостности либо в прикладном программном обеспечении, либо посредством виртуальной таблицы с соответствующей опцией проверки.

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

Для каждого из подтипов может создаваться своя реляционная виртуальная таблица, для предоставления возмож­но­сти доступа только к необходимым данным в соответствии с требованиями прикладных задач, определяющих манипулирование сущностями, являющимися подтипами. Такие виртуальные таблицы должны включать столбцы подтипа, всех его подтипов и его супертипа(ов). Имеется возможность добавить к виртуальной таблице фразу WHERE для проверки правильности внешних ключей и опции факультативности для столбца ТИП.

Рассмотрим пример на рис. 6.37. Здесь в состав пер­вич­­но­го ключа введено поле Тип, значения которого указывают, описывает ли строка таблицы супертип (ЗАК) или один из его подтипов (СЗБ или ДСЗ).

	СТРОКИ_ЗАКАЗА           Код_строки integer   not null     Код_заказа integer   not null   первичный Тип char   not null   ключ Описание char   null     Комментарий char   null     Количество integer   null     Номер_рейса integer   null
Рис. 6.37. Представление подтипов в одной таблице

Возможными описаниями виртуальных таблиц в языке SQL могут быть следующие:

CREATE VIEW ДРУГИЕ_СТРОКИ_ЗАКАЗА AS

SELECT КОД_СТРОКИ, КОД_ЗАКАЗА, ОПИСАНИЕ, КОММЕНТАРИЙ, ТИП

FROM СТРОКИ_ЗАКАЗА

WHERE ТИП="ДСЗ"

CREATE VIEW СТРОКИ_ЗАКАЗА_ТОВАРА AS

SELECT КОД_СТРОКИ, КОД_ЗАКАЗА, ОПИСАНИЕ, КОЛИЧЕСТВО, НОМЕР_РЕЙСА, ТИП

FROM СТРОКИ_ЗАКАЗА

WHERE ТИП="СЗБ" AND

КОЛИЧЕСТВО NOT NULL AND

EXISTS (SELECT *

FROM РЕЙСОВЫЕ_ПОЛЕТЫ

WHERE РЕЙСОВЫЕ_ПОЛЕТЫ.НОМЕР = СТРОКИ_ЗАКАЗА.НОМЕР_РЕЙСА)

Заметим, что обе виртуальные таблицы проверяют столбец "тип", а вторая из них требует обязательного присутствия значения столбца количества и, кроме того, гарантирует существования номера рейса в таблице РЕЙСОВЫЕ_ПОЛЕТЫ, совпадающего с существующим номером.

По таблице на каждый подтип

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

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

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

Может быть также создана виртуальная таблица UNION для предоставления возможности обработки супертипа.

На рис. 6.38 дается пример представления подтипа В одной таблице. B       b1 not null Из подтипа В b2 null   Из подтипа В a1 not null   G_g not null   Первичный ключ из супертипа А тип not null     a2 null     d null   Из подтипа D, факультативный F_f null   Из подтипа D, факультативный e null Из подтипа E, факультативный материнский_a1 null   Из связи между E и B, повторе­ние материнский_G_g null первичного ключа для "материн- материнский_тип null   ский" с факультативной опцией. Рис. 6.38. Пример представления одного подтипа в таблице.

Давайте сейчас посмотрим, что собой представляет виртуальная таблица для подтипа E:

CREATE VIEW E AS

SELECT e, b1, b2, a1, G_g, тип, a2, материнский_a1, материнский_G_g, материнский_тип

FROM B

WHERE тип = "E" AND

e not null AND

EXISTS (SELECT *

FROM B

WHERE B.а1 = E.материнский_а1 AND

B.G_g = E.материнский_G_g AND

B.тип = E.материнский_тип)

И для полноты давайте приведем виртуальную таблицу UNION для A.

CREATE VIEW A AS

SELECT * FROM B

UNION

SELECT * FROM C

Шаг 6. Взаимоисключающие связи

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

Общий домен

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

Для таблицы КУПОН столбцами для реализации связей с РЕЙСОВЫЙ ПОЛЕТ или РЕЙС могут быть:

Связан_с char 4 not null например, значения "РПОЛ" и "РЕЙС" для идентификации связи

Номер_рейса/полета integer 4 not null поле для запоминания идентификатора связываемой сущности

В связи с тем, что эти обе связи являются обязательными, то и столбцы имеют not null опции.

Явные внешние ключи

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

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

Номер_рейса integer 4 null

Дата_полета date null

Время_вылета time null

Шаг 7. Производные атрибуты

В процессе проектирования следует принять решение о представлении производных атрибутов.

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

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

· производное значение изменяется редко;

· стоимость вычислений слишком велика. Обычно это происходит только если вычисления производятся более чем на одной строке базы данных.

Литература

1) Джексон Г. Проектирование реляционных баз данных для использования с микроЭВМ.– М: Мир, 1991ю – 252 с.

2) Тиори Т., Фрай Дж. Проектирование структур баз данных: В 2-х кн. Пер. с англ. – М.: Мир, 1985. – 287 с.

3) Хаббард Дж. Автоматизированное проектирование баз данных. – М: Мир, 1984. – 294 с.

4) Barker R. Case method. Entity Relationship Modelling. Addison-Wesley Publishing Company.

5) Griethuysen J., J. Concepts and Terminology for the Conceptual Schema and Information Base. ISO TC97, 1982, 110 p.