Преобразование концептуальной модели в реляционную

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

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

ПРЕОБРАЗОВАНИЕ ОБЪЕКТНЫХ МНОЖЕСТВ И АТРИБУТОВ. Рассмотрим простую концептуальную модель на рис 6., где представлено объектное множество ЧЕЛОВЕК с тремя атрибутами. ЧЕЛОВЕК – это абстрактное (нелексическое) объектное множество, а № СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ и ДАТА РОЖДЕНИЯ – лексические атрибуты.

Так как атрибуты в реляционной модели должна быть лексическими, то № СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ и ДАТА РОЖДЕНИЯ могут быть атрибутами реляционной таблицы. Таким образом, можно преобразовать диаграмму в реляционную таблицу ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ, ДАТА РОЖДЕНИЯ). Поскольку можно предполагать, что № СТРАХОВОГО СВИДЕТЕЛЬСТВА однозначно определяет человека, то ключом будет № СТРАХОВОГО СВИДЕТЕЛЬСТВА.

ПРЕОБРАЗОВАНИЕ МОДЕЛЕЙ БЕЗ КЛЮЧЕЙ. Суть процесса преобразования заключается в следующем. Объектное множество с атрибутами может быть преобразовано в реляционную таблицу. При этом реляционная таблица получает имя объектного множества, ее атрибутами становятся атрибуты объектного множества. Если некоторый набор этих атрибутов может быть использован в качестве ключа таблицы, то он выбирается ключом таблицы. В противном случае к таблице добавляется атрибут, значения которого будут однозначно определять строки этой таблицы. Рассмотрим пример модели, приведенный на рис. 19.

Рис. 19. Концептуальная модель продажи.

Полученная из этой модели таблица ПРОДАЖИ (КОД ТОВАРА, КОЛИЧЕСТВО) не имеет ключа, так как может быть несколько продаж с одними и теми же значениями кода товара и количества. Следовательно, необходимо добавить ключевой атрибут № ПРОДАЖИ. В результате получим таблицу ПРОДАЖИ (№ ПРОДАЖИ, КОД ТОВАРА, КОЛИЧЕСТВО).

ПРЕОБРАЗОВАНИЕ КОНКРЕТИЗАЦИЙ И ОБОБЩЕНИЙ ОБЪЕКТНЫХ МНОЖЕСТВ. Рассмотрим концептуальную модель (рис. 8). Объектное множество ЧЕЛОВЕК легко преобразуется в таблицу:

ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ, ДАТА РОЖДЕНИЯ)

Объект ЖЕНАТЫЙ ЧЕЛОВЕК является подобъектом объекта ЧЕЛОВЕК и наследует атрибуты объекта ЧЕЛОВЕК. Кроме того, у него есть собственные атрибуты. Следовательно, получим реляционную таблицу

ЖЕНАТЫЙ ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ, ДАТА РОЖДЕНИЯ, СУПРУГ)

Внешний ключ № СТРАХОВОГО СИДЕТЕЛЬСТВА ссылается на таблицу обобщения ЧЕЛОВЕК. Атрибуты ИМЯ и ДАТА РОЖДЕНИЯ содержатся в обеих таблицах. Эти атрибуты можно исключить из таблицы конкретизации ЖЕНАТЫЙ ЧЕЛОВЕК. В результате эта таблица будет иметь вид:

ЖЕНАТЫЙ ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, СУПРУГ)

ПРЕОБРАЗОВАНИЕ ОТНОШЕНИЙ. Отношение преобразуется одним из трех способов в зависимости от его мощности. Рассмотрим отдельно отношения один-к-одному, один-ко-многим и много-ко-многим.

Отношение один-к-одному. Рассмотрим объектные множества КЛИЕНТ и ТЕКУЩИЙ СЧЕТ. Предположим, что клиент банка может иметь только один текущий счет, то есть отношение ИМЕЕТ ТЕКУЩИЙ СЧЕТ имеет мощность один-к-одному. Пусть ключами являются НОМЕР КЛИЕНТА и НОМЕР СЧЕТА. В этом случае будем иметь две таблицы, каждая из которых состоит из одного столбца:

КЛИЕНТ (НОМЕР КЛИЕНТА),

ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА).

Чтобы показать связь между этими таблицами, в таблицу КЛИЕНТ необходимо добавить атрибут НОМЕР СЧЕТА, а в таблицу ТЕКУЩИЙ СЧЕТ – НОМЕР КЛИЕНТА. Получим таблицы:

КЛИЕНТ (НОМЕР КЛИЕНТА, НОМЕР СЧЕТА),

ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА).

Очевидно, что при таком решении данные дублируются, так как необходимой является только одна комбинация НОМЕР КЛИЕНТА, НОМЕР СЧЕТА. Из таблицы КЛИЕНТ можно исключить атрибут НОМЕР СЧЕТА. В результате получим таблицы:

КЛИЕНТ (НОМЕР КЛИЕНТА),

ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА).

Внешним ключом здесь является НОМЕР КЛИЕНТА.

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

КЛИЕНТ (НОМЕР КЛИЕНТА, ИМЯ, АДРЕС, ТЕЛЕФОН),

ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА, БАЛАНС).

Вывод: отношение один-к-одному преобразуется путем помещения одного из объектных множеств в качестве атрибута в таблицу второго объектного множества.

Отношение один-ко-многим. Предположим, что отношение ИМЕЕТ ТЕКУЩИЙ СЧЕТ имеет мощность «много» со стороны ТЕКУЩИЙ СЧЕТ. Другими словами, у клиента может быть несколько текущих счетов. В этом случае получаем следующую структуру реляционных таблиц:

КЛИЕНТ (НОМЕР КЛИЕНТА),

ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА),

Где внешним ключом является НОМЕР КЛИЕНТА.

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

Отношение много-ко-многим. Предположим, что мощность отношения ИМЕЕТ ТЕКУЩИЙ СЧЕТ много-ко-многим. Другими словами, не только клиент может иметь несколько счетов, но и одним счетом могут пользоваться несколько клиентов. Чтобы преобразовать отношение много-ко-многим, создадим таблицу пересечения. В рассматриваемом примере потребуются три таблицы: по одной на каждое объектное множество и одна для отношения ИМЕЕТ ТЕКУЩИЙ СЧЕТ:

КЛИЕНТ (НОМЕР КЛИЕНТА, ИМЯ, АДРЕС, ТЕЛЕФОН),

ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, БАЛАНС),

ИМЕЕТ ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА).

Внешние ключи: НОМЕР КЛИЕНТА ссылается на таблицу КЛИЕНТ, НОМЕР СЧЕТА ссылается на таблицу ТЕКУЩИЙ СЧЕТ. Таблица ИМЕЕТ ТЕКУЩИЙ СЧЕТ называется таблицей пересечения, так как она показывает, как связаны элементы таблиц КЛИЕНТ и ТЕКУЩИЙ СЧЕТ.

ПРЕОБРАЗОВАНИЕ СОСТАВНЫХ ОБЪЕКТНЫХ МНОЖЕСТ. Рассмотрим рис. 12, на котором приведена концептуальная модель отслеживания продаж по регионам. Отношение ПРОДАН В рассматривается как составное объектное множество, имеет атрибут КОЛИЧЕСТВО. Преобразуем эту модель в соответствии с рассмотренными выше правилами. Так как отношение имеет мощность много-ко-многим, создадим три таблицы:

ТОВАР (НОМЕНКЛАТУРНЫЙ №),

РЕГИОН (НАЗВАНИЕ РЕГИОНА),

ПРОДАН В (НОМЕНКЛАТУРНЫЙ №, НАЗВАНИЕ РЕГИОНА, КОЛИЧЕСТВО).

Внешние ключи: НОМЕНКЛАТУРНЫЙ № ссылается на таблицу ТОВАР, НАЗВАНИЕ РЕГИОНА ссылается на таблицу РЕГИОН. Атрибут КОЛИЧЕСТВО помещен в таблицу ПРОДАН В, так как он относится к составному объекту. Если бы отношение ПРОДАН В и объектные множества ТОВАР и РЕГИОН имели другие относящиеся к ним атрибуты, то их так же включили бы в соответствующие таблицы.

На рис. 13 представлена модель отслеживания продаж с использованием трехстороннего отношения. В этом случае атрибут КОЛИЧЕСТВО относится к товару, проданному в конкретном регионе в конкретный день. Эту концептуальную модель можно преобразовать в реляционную следующим образом:

ТОВАР (НОМЕНКЛАТУРНЫЙ №),

РЕГИОН (НАЗВАНИЕ РЕГИОНА),

ДАТА (ДАТА),

ПРОДАН В (НОМЕНКЛАТУРНЫЙ №, НАЗВАНИЕ РЕГИОНА, ДАТА, КОЛИЧЕСТВО).

ПРЕОБРАЗОВАНИЕ РЕКУРСИВНЫХ ОТНОШЕНИЙ. На рис. 20 показан пример рекурсивного отношения, связывающего объектное множество с самим собой. Объектное множество РАБОТНИК дважды встречается на диаграмме. Это одно и то же объектное множество. Обе копии объектного множества РАБОТНИК имеют одни и те же атрибуты. С целью упрощения эти атрибуты показаны только для правой копии. В этом примере отношение мощности один-ко-многим означает, что одному работнику подчиняются несколько других работников.

Используя ранее рассмотренные подходы, можно получить следующий результат:

Рис. 20. Рекурсивное отношение.

РАБОТНИК (ТАБЕЛЬНЫЙ НОМЕР, ИМЯ, ТАБЕЛЬНЫЙ НОМЕР)

Это решение некорректно, поскольку реляционная таблица РАБОТНИК имеет два атрибута с именем ТАБЕЛЬНЫЙ НОМЕР. Решение состоит в том, чтобы изменить имя второго атрибута ТАБЕЛЬНЫЙ НОМЕР на имя, соответствующее отношению КОНТРОЛИРУЕТ. Заменим его на КОНТРОЛ_ТАБ_НОМ.

РАБОТНИК (ТАБЕЛЬНЫЙ НОМЕР, ИМЯ, КОНТРОЛ_ТАБ_НОМ)

КОНТРОЛ_ТАБ_НОМ – это рекурсивный внешний ключ, поскольку он ссылается на ТАБЕЛЬНЫЙ НОМЕР, то есть ключ своей собственной таблицы.

ЗАКЛЮЧИТЕЛЬНЫЙ ЭТАП ПРЕОБРАЗОВАНИЯ. Выше продемонстрированы способы преобразований конструкций концептуальной модели – объектов, атрибутов, отношений, конкретизаций и составных объектов – в реляционные таблицы. После завершения преобразований всех конкретных конструкций полученную реляционную схему необходимо пересмотреть на предмет избавления от избыточности. Любые избыточные таблицы (то есть таблицы, информация которых полностью содержится в других таблицах схемы) необходимо удалить из схемы.

Следует обратить внимание на то, что все полученные таким образом таблицы имеют четвертую нормальную форму. Это объясняется следующим. Функциональные зависимости, определенные для реляционной модели, являются атрибутами отношения один-к-одному или один-ко-многим. Описанный выше процесс преобразования каждой из этих конструкций в атрибуты реляционных таблиц гарантирует, что они будут зависеть только от ключевых атрибутов. Следовательно, каждая полученная реляционная таблица будет иметь 3НФ. Многозначные атрибуты реляционной модели встречаются только в отношениях много-ко-многим. Эти отношения преобразуются в реляционные таблицы, обладающие составными ключами из ключевых атрибутов отдельных объектных множеств. Следовательно, они гарантированно имеют 4НФ.