Преобразование концептуальной модели в реляционную
Концептуальные модели обеспечивают более точное представление сложных аспектов прикладной проблемы, чем реляционные и другие более ранние модели. Для практической реализации более удобной является реляционная модель. Поэтому необходимы методы перехода от концептуальной модели к реляционной.
Концептуальная модель данных состоит из объектов, отношений, атрибутов, конкретизаций, составных объектов и т.д. Рассмотрим методы преобразования каждой из этих конструкций в реляционные таблицы. Важным при этом является тот факт, что все полученные в результате реляционные таблицы будут иметь четвертую нормальную форму и дальнейшая нормализация модели не требуется.
Преобразование моделей без ключей. Суть процесса преобразования заключается в следующем. Объектное множество с атрибутами может быть преобразовано в реляционную таблицу. При этом реляционная таблица получает имя объектного множества, ее атрибутами становятся атрибуты объектного множества. Если некоторый набор этих атрибутов может быть использован в качестве ключа таблицы, то он выбирается ключом таблицы. В противном случае к таблице добавляется атрибут, значения которого будут однозначно определять строки этой таблицы. Рассмотрим пример модели, приведенный на рисунке 2.13.
Рисунок 2.13 - Концептуальная модель продажи.
Полученная из этой модели таблица ПРОДАЖИ (КОД ТОВАРА, КОЛИЧЕСТВО, ЦЕНА) не имеет ключа, так как может быть несколько продаж с одними и теми же значениями кода товара, количества и цены. Следовательно, необходимо добавить ключевой атрибут № ПРОДАЖИ. В результате получим таблицу ПРОДАЖИ (№ ПРОДАЖИ, КОД ТОВАРА, КОЛИЧЕСТВО, ЦЕНА).
Преобразование конкретизаций и обобщений объектных множеств. Рассмотрим концептуальную модель (рисунок 2.9). Объектное множество ЧЕЛОВЕК легко преобразуется в таблицу:
ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ, ДАТА РОЖДЕНИЯ)
Объект ЖЕНАТЫЙ ЧЕЛОВЕК является подобъектом объекта ЧЕЛОВЕК и наследует атрибуты объекта ЧЕЛОВЕК. Кроме того, у него есть собственные атрибуты. Следовательно, получим реляционную таблицу
ЖЕНАТЫЙ ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, ИМЯ, ДАТА РОЖДЕНИЯ, СУПРУГ)
Внешний ключ № СТРАХОВОГО СИДЕТЕЛЬСТВА ссылается на таблицу обобщения ЧЕЛОВЕК. Атрибуты ИМЯ и ДАТА РОЖДЕНИЯ содержатся в обеих таблицах. Эти атрибуты можно исключить из таблицы конкретизации ЖЕНАТЫЙ ЧЕЛОВЕК. В результате эта таблица будет иметь вид:
ЖЕНАТЫЙ ЧЕЛОВЕК (№ СТРАХОВОГО СВИДЕТЕЛЬСТВА, СУПРУГ)
Преобразование отношений. Отношение преобразуется одним из трех способов в зависимости от его мощности. Рассмотрим отдельно отношения один-к-одному, один-ко-многим и много-ко-многим.
Отношение один-к-одному. Рассмотрим объектные множества КЛИЕНТ и ТЕКУЩИЙ СЧЕТ. Предположим, что клиент банка может иметь только один текущий счет, то есть отношение ИМЕЕТ ТЕКУЩИЙ СЧЕТ имеет мощность один-к-одному. Пусть ключами являются НОМЕР КЛИЕНТА и НОМЕР СЧЕТА. В этом случае будем иметь две таблицы, каждая из которых состоит из одного столбца:
КЛИЕНТ (НОМЕР КЛИЕНТА),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА).
Чтобы показать связь между этими таблицами, в таблицу КЛИЕНТ необходимо добавить атрибут НОМЕР СЧЕТА, а в таблицу ТЕКУЩИЙ СЧЕТ – НОМЕР КЛИЕНТА. Получим таблицы:
КЛИЕНТ (НОМЕР КЛИЕНТА, НОМЕР СЧЕТА),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА).
Очевидно, что при таком решении данные дублируются, так как необходимой является только одна комбинация НОМЕР КЛИЕНТА, НОМЕР СЧЕТА. Из таблицы КЛИЕНТ можно исключить атрибут НОМЕР СЧЕТА. В результате получим таблицы:
КЛИЕНТ (НОМЕР КЛИЕНТА),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА).
Внешним ключом здесь является НОМЕР КЛИЕНТА.
Другим решением исключения избыточности является удаление из таблицы ТЕКУЩИЙ СЧЕТ атрибута НОМЕР КЛИЕНТА. В этом случае получим следующие таблицы:
КЛИЕНТ (НОМЕР КЛИЕНТА, НОМЕР СЧЕТА),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА).
Очевидно, что в полной схеме базы данных эти таблицы будут иметь множество других атрибутов. В рассматриваемых примерах показываются только те атрибуты, которые нужны для преобразования простой концептуальной модели. Если бы рассмотренные объектные множества имели другие атрибуты, то они были бы помещены в соответствующие таблицы. Например,
КЛИЕНТ (НОМЕР КЛИЕНТА, ИМЯ, АДРЕС, ТЕЛЕФОН),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА, БАЛАНС).
Вывод: отношение один-к-одному преобразуется путем помещения одного из объектных множеств в качестве атрибута в таблицу второго объектного множества.
Отношение один-ко-многим. Предположим, что отношение ИМЕЕТ ТЕКУЩИЙ СЧЕТ имеет мощность «много» со стороны ТЕКУЩИЙ СЧЕТ. Другими словами, у клиента может быть несколько текущих счетов. В этом случае получаем следующую структуру реляционных таблиц:
КЛИЕНТ (НОМЕР КЛИЕНТА),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА),
где внешним ключом является НОМЕР КЛИЕНТА.
При преобразовании отношения один-ко-многим ключевой атрибут таблицы, описывающей объектное множество, мощность со стороны которого равна «одному», включается в качестве внешнего ключа в таблицу, описывающую объектное множество, мощность со стороны которого равна «многим».
Отношение много-ко-многим. Предположим, что мощность отношения ИМЕЕТ ТЕКУЩИЙ СЧЕТ много-ко-многим. Другими словами, не только клиент может иметь несколько счетов, но и одним счетом могут пользоваться несколько клиентов. Чтобы преобразовать отношение много-ко-многим, создадим таблицу пересечения. В рассматриваемом примере потребуются три таблицы: по одной на каждое объектное множество и одна для отношения ИМЕЕТ ТЕКУЩИЙ СЧЕТ:
КЛИЕНТ (НОМЕР КЛИЕНТА, ИМЯ, АДРЕС, ТЕЛЕФОН),
ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, БАЛАНС),
ИМЕЕТ ТЕКУЩИЙ СЧЕТ (НОМЕР СЧЕТА, НОМЕР КЛИЕНТА).
Ключом таблицы ИМЕЕТ ТЕКУЩИЙ СЧЕТ служат два атрибута: НОМЕР СЧЕТА и НОМЕР КЛИЕНТА. Внешние ключи этой таблицы: НОМЕР КЛИЕНТА ссылается на таблицу КЛИЕНТ, НОМЕР СЧЕТА ссылается на таблицу ТЕКУЩИЙ СЧЕТ. Таблица ИМЕЕТ ТЕКУЩИЙ СЧЕТ называется таблицей пересечения, так как она показывает, как связаны элементы таблиц КЛИЕНТ и ТЕКУЩИЙ СЧЕТ.
Преобразование составных объектных множеств. Рассмотрим рисунок 2.10, на котором приведена концептуальная модель отслеживания продаж по регионам. Отношение ПРОДАН рассматривается как составное объектное множество, имеет атрибут КОЛИЧЕСТВО. Преобразуем эту модель в соответствии с рассмотренными выше правилами. Так как отношение имеет мощность много-ко-многим, создадим три таблицы:
ТОВАР (НОМЕНКЛАТУРНЫЙ №),
РЕГИОН (НАЗВАНИЕ РЕГИОНА),
ПРОДАН (НОМЕНКЛАТУРНЫЙ №, НАЗВАНИЕ РЕГИОНА, КОЛИЧЕСТВО).
Внешние ключи: НОМЕНКЛАТУРНЫЙ № ссылается на таблицу ТОВАР, НАЗВАНИЕ РЕГИОНА ссылается на таблицу РЕГИОН. Атрибут КОЛИЧЕСТВО помещен в таблицу ПРОДАН, так как он относится к составному объекту. Если бы отношение ПРОДАН и объектные множества ТОВАР и РЕГИОН имели другие относящиеся к ним атрибуты, то их так же включили бы в соответствующие таблицы.
На рисунке 2.11 представлена модель отслеживания продаж с использованием трехстороннего отношения. В этом случае атрибут КОЛИЧЕСТВО относится к товару, проданному в конкретном регионе в конкретный день. Эту концептуальную модель можно преобразовать в реляционную следующим образом:
ТОВАР (НОМЕНКЛАТУРНЫЙ №),
РЕГИОН (НАЗВАНИЕ РЕГИОНА),
ДАТА (ДАТА),
ПРОДАН В (НОМЕНКЛАТУРНЫЙ №, НАЗВАНИЕ РЕГИОНА, ДАТА, КОЛИЧЕСТВО).
Преобразование рекурсивных отношений. Рекурсивное отношение – отношение, связывающее объектное множество с самим собой. Пример такого отношения показан на рисунке 2.14. Объектное множество РАБОТНИК дважды встречается на диаграмме. Это одно и то же объектное множество. Обе копии объектного множества РАБОТНИК имеют одни и те же атрибуты. С целью упрощения эти атрибуты показаны только для правой копии. В этом примере отношение мощности один-ко-многим означает, что одному работнику подчиняются несколько других работников.
Используя ранее рассмотренные подходы, можно получить следующий результат:
Рисунок 2.14 - Рекурсивное отношение.
РАБОТНИК (ТАБЕЛЬНЫЙ НОМЕР, ИМЯ, ТАБЕЛЬНЫЙ НОМЕР)
Это решение некорректно, поскольку реляционная таблица РАБОТНИК имеет два атрибута с именем ТАБЕЛЬНЫЙ НОМЕР. Решение состоит в том, чтобы изменить имя второго атрибута ТАБЕЛЬНЫЙ НОМЕР на имя, соответствующее отношению КОНТРОЛИРУЕТ. Заменим его на КОНТРОЛ_ТАБ_НОМ.
РАБОТНИК (ТАБЕЛЬНЫЙ НОМЕР, ИМЯ, КОНТРОЛ_ТАБ_НОМ)
КОНТРОЛ_ТАБ_НОМ – это рекурсивный внешний ключ, поскольку он ссылается на ТАБЕЛЬНЫЙ НОМЕР, то есть ключ своей собственной таблицы.