декомпозиция отношений базы данных

Нормализация отношений. Шесть нормальных форм

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

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

Используемые термины

Атрибут — свойство некоторой сущности. Часто называется полем таблицы.

Домен атрибута — множество допустимых значений, которые может принимать атрибут.

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

Отношение — конечное множество кортежей (таблица).

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

Проекция — отношение, полученное из заданного путём удаления и (или) перестановки некоторых атрибутов.

Функциональная зависимость между атрибутами (множествами атрибутов) X и Y означает, что для любого допустимого набора кортежей в данном отношении: если два кортежа совпадают по значению X, то они совпадают по значению Y. Например, если значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom. Обозначение: -> .

Нормальная форма — требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц).

Метод нормальных форм (НФ) состоит в сборе информации о объектах решения задачи в рамках одного отношения и последующей декомпозиции этого отношения на несколько взаимосвязанных отношений на основе процедур нормализации отношений.

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

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

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

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

Аномалии-добавления возникают, когда информацию в таблицу нельзя поместить, пока она не полная, либо вставка записи требует дополнительного просмотра таблицы.

Первая нормальная форма

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

Например, есть таблица «Автомобили»:

Вторая нормальная форма

Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут неприводимо зависит от Первичного Ключа(ПК).

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

Например, дана таблица:

Таблица находится в первой нормальной форме, но не во второй. Цена машины зависит от модели и фирмы. Скидка зависят от фирмы, то есть зависимость от первичного ключа неполная. Исправляется это путем декомпозиции на два отношения, в которых не ключевые атрибуты зависят от ПК.

Третья нормальная форма

Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа. Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых может относиться к нескольким записям таблицы в отдельные таблицы.

Таблица находится во 2НФ, но не в 3НФ.
В отношении атрибут «Модель» является первичным ключом. Личных телефонов у автомобилей нет, и телефон зависит исключительно от магазина.
Таким образом, в отношении существуют следующие функциональные зависимости: Модель → Магазин, Магазин → Телефон, Модель → Телефон.
Зависимость Модель → Телефон является транзитивной, следовательно, отношение не находится в 3НФ.
В результате разделения исходного отношения получаются два отношения, находящиеся в 3НФ:

Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы)

Определение 3НФ не совсем подходит для следующих отношений:
1) отношение имеет два или более потенциальных ключа;
2) два и более потенциальных ключа являются составными;
3) они пересекаются, т.е. имеют хотя бы один общий атрибут.

Для отношений, имеющих один потенциальный ключ (первичный), НФБК является 3НФ.

Отношение находится в НФБК, когда каждая нетривиальная и неприводимая слева функциональная зависимость обладает потенциальным ключом в качестве детерминанта.

Предположим, рассматривается отношение, представляющее данные о бронировании стоянки на день:

Отношение находится в 3НФ. Требования второй нормальной формы выполняются, так как все атрибуты входят в какой-то из потенциальных ключей, а неключевых атрибутов в отношении нет. Также нет и транзитивных зависимостей, что соответствует требованиям третьей нормальной формы. Тем не менее, существует функциональная зависимость Тариф → Номер стоянки, в которой левая часть (детерминант) не является потенциальным ключом отношения, то есть отношение не находится в нормальной форме Бойса — Кодда.

Недостатком данной структуры является то, что, например, по ошибке можно приписать тариф «Бережливый» к бронированию второй стоянки, хотя он может относиться только к первой стоянки.

Можно улучшить структуру с помощью декомпозиции отношения на два и добавления атрибута Имеет льготы, получив отношения, удовлетворяющие НФБК (подчёркнуты атрибуты, входящие в первичный ключ.):

Четвертая нормальная форма

Отношение находится в 4НФ, если оно находится в НФБК и все нетривиальные многозначные зависимости фактически являются функциональными зависимостями от ее потенциальных ключей.

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

Такая переменная отношения не соответствует 4НФ, так как существует следующая многозначная зависимость:
<Ресторан>→ <Вид пиццы>
<Ресторан>→

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

Для предотвращения аномалии нужно декомпозировать отношение, разместив независимые факты в разных отношениях. В данном примере следует выполнить декомпозицию на <Ресторан, Вид пиццы>и <Ресторан, Район доставки>.

Однако, если к исходной переменной отношения добавить атрибут, функционально зависящий от потенциального ключа, например цену с учётом стоимости доставки ( <Ресторан, Вид пиццы, Район доставки>→ Цена), то полученное отношение будет находиться в 4НФ и его уже нельзя подвергнуть декомпозиции без потерь.

Пятая нормальная форма

Отношения находятся в 5НФ, если оно находится в 4НФ и отсутствуют сложные зависимые соединения между атрибутами.
Если «Атрибут_1» зависит от «Атрибута_2», а «Атрибут_2» в свою очередь зависит от «Атрибута_3», а «Атрибут_3» зависит от «Атрибута_1», то все три атрибута обязательно входят в один кортеж.

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

Например, некоторая таблица содержит три атрибута «Поставщик», «Товар» и «Покупатель». Покупатель_1 приобретает несколько Товаров у Поставщика_1. Покупатель_1 приобрел новый Товар у Поставщика_2. Тогда в силу изложенного выше требования Поставщик_1 обязан поставлять Покупателю_1 тот же самый новый Товар, а Поставщик_2 должен поставлять Покупателю_1, кроме нового Товара, всю номенклатуру Товаров Поставщика_1. Этого на практике не бывает. Покупатель свободен в своем выборе товаров. Поэтому для устранения отмеченного затруднения все три атрибута разносят по разным отношениям (таблицам). После выделения трех новых отношений (Поставщик, Товар и Покупатель) необходимо помнить, что при извлечении информации (например, о покупателях и товарах) необходимо в запросе соединить все три отношения. Любая комбинация соединения двух отношений из трех неминуемо приведет к извлечению неверной (некорректной) информации. Некоторые СУБД снабжены специальными механизмами, устраняющими извлечение недостоверной информации. Тем не менее, следует придерживаться общей рекомендации: структуру базы данных строить таким образом, чтобы избежать применения 4НФ и 5НФ.

Пятая нормальная форма ориентирована на работу с зависимыми соединениями. Указанные зависимые соединения между тремя атрибутами встречаются очень редко. Зависимые соединения между четырьмя, пятью и более атрибутами указать практически невозможно.

Доменно-ключевая нормальная форма

Переменная отношения находится в ДКНФ тогда и только тогда, когда каждое наложенное на неё ограничение является логическим следствием ограничений доменов и ограничений ключей, наложенных на данную переменную отношения.
Ограничение домена – ограничение, предписывающее использовать для определённого атрибута значения только из некоторого заданного домена. Ограничение по своей сути является заданием перечня (или логического эквивалента перечня) допустимых значений типа и объявлением о том, что указанный атрибут имеет данный тип.

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

Любая переменная отношения, находящаяся в ДКНФ, обязательно находится в 5НФ. Однако не любую переменную отношения можно привести к ДКНФ.

Шестая нормальная форма

Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ.

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

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

Переменная отношения «Работники» не находится в 6НФ и может быть подвергнута декомпозиции на переменные отношения «Должности работников» и «Домашние адреса работников».

Источник

Методы проектирования логических моделей реляционных баз данных. Декомпозиция и синтез отношений

Методы проектирования на основе декомпозиции отношений

Понятие о методах декомпозиции отношений

Предположим, что схема базы данных содержит F-зависимости. Из положений теории о F-зависимостях следует, что если отношения базы данных находятся в нормальной форме Бойса-Кодда (НФБК), то проектирование логической модели базы данных можно считать завершенным в этом классе зависимостей. Как видно, теория дает полезный (!) критерий останова проектирования.

Сформулируем наглядный критерий, позволяющий определить, находится ли отношение в НФБК. Для этого введем следующее вспомогательное понятие.

Определение 3. Пусть дана ФЗ: и В функционально не зависит от любого подмножества А, тогда А называется детерминантом В.

Детерминантами в отношениях являются атрибуты левых частей ФЗ. Возможные ключи (см. учебный элемент » Реляционная модель данных «) идентифицируются путем нахождения минимального множества значений атрибутов, которые определяют значения всех остальных атрибутов в отношении. Напомним понятие возможного ключа отношения как атрибута или атрибутов данного отношения, который (-ые) могут быть в данном отношении выбраны в качестве первичного.

Тогда критерий Кодда, позволяющий определить, находится ли отношение в НФБК, может быть сформулирован следующим образом:

Таким образом, при декомпозиции отношений необходимо строить списки возможных ключей и детерминантов и сравнивать их на совпадение. Устранив таким образом большинство потенциальных аномалий, проектирование можно завершить.

Алгоритм метода декомпозиции отношений

Алгоритм метода декомпозиции отношений

Уточним некоторые аспекты метода декомпозиции.

Если то в качестве ФЗ для осуществления проекции используется крайняя правая зависимость или «конец цепочки»

Методы проектирования на основе синтеза отношений

Некоторые проблемы метода декомпозиции

Первая ситуация: в процессе декомпозиции потеряна ФЗ и если отношения R₁ и R₂ будут использованы для создания базы данных, то нельзя гарантировать, что связи между этими атрибутами будут внесены в БД корректно. Отсюда вытекает второе эмпирическое правило выбора ФЗ для выполнения проекции: не следует использовать ФЗ в качестве ФЗ для осуществления проекции, если ее левая часть является детерминантом другой ФЗ. Эта проблема разрешается путем применения правила цепочки.

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

Понятие о методах синтеза отношений

Исключить избыточность в исходном наборе ФЗ можно путем применения правил вывода ФЗ (См. учебный элемент » Функциональные зависимости «). Как известно, для класса F-зависимостей достаточно использовать шесть таких правил. При этом критерием останова процедуры исключения может служить получение минимального покрытия исходного набора ФЗ.

Неединственность минимальных покрытий указывает на то, что порядок исключения избыточных ФЗ может оказать влияние на полученные результаты. Отсюда следует эмпирическое правило:

Избыточные ФЗ следует удалять по одной, каждый раз проводя анализ полученного набора ФЗ на избыточность.

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

Кроме того, задача приведения исходных отношений к НФБК может оказаться невыполнимой. Такой факт имел место в практике проектирования. Поскольку НФБК может не существовать на заданном наборе ФЗ, то логичным было бы отказаться от требования приведения отношений базы данных к этой форме. Эта ситуация подкрепляется и теоретически: при любом заданном множестве F-зависимостей над схемой r БД можно построить схему БД в 3НФ.

Таким образом, ограничимся поиском 3НФ в ходе применения метода синтеза, при этом остаются проблемы, связанные с выполнением операций над данными.

Источник

Фамилия

Видно, что данные содержат множественные поля, т.е. атрибуты неатомарны. Дублирование данных в атрибутах позволяет представить данные о студенте в форме отношения (таблица 7.2).

Таблица 7.2. Отношение СТУДЕНТ

1. распознать отношения, подлежащие разбиению?

2. Как осуществлять разбиение?

3. Когда окончить процесс разбиения?

Определив все ФЗ, присущие предметной области базы данных, можно приступать к процессу разбиения отношений, именуемому декомпозицией схем отношений. Декомпозиция схем отношений является одним из основных методов построения логических моделей реляционных баз данных. Использование универсального отношения позволяет иметь отправную точку декомпозиции отношений базы данных. Результатом декомпозиции является нормализованная модель данных.

Декомпозиция схем отношений, свойства соединения без потерь и сохранения ФЗ

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

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

Определение 1. Декомпозицией схемы отношений называется замена ее совокупностью подмножества R .

Рассмотрим алгоритм проверки свойства соединения без потерь.

Алгоритм. Проверка декомпозиции на свойство соединения без потерь

Приведенный алгоритм позволяет корректно определить, обладает ли декомпозиция свойством соединения без потерь.

Введем следующее определение.

Рассмотрим пример нарушения ФЗ при декомпозиции.

Пример. Нарушение ФЗ при декомпозиции

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

· исключать избыточное дублирование;

· исключать потенциальную противоречивость данных;

· обладать свойством соединения без потерь;

· обладать свойством сохранения ФЗ.

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

Методы проектирования на основе декомпозиции отношений

Понятие о методах декомпозиции отношений

Предположим, что схема базы данных содержит F-зависимости. Из положений теории о F-зависимостях следует, что если отношения базы данных находятся в нормальной форме Бойса-Кодда (НФБК), то проектирование логической модели базы данных можно считать завершенным в этом классе зависимостей. Как видно, теория дает полезный (!) критерий останова проектирования.

Сформулируем наглядный критерий, позволяющий определить, находится ли отношение в НФБК. Для этого введем следующее вспомогательное понятие.

Детерминантами в отношениях являются атрибуты левых частей ФЗ. Возможные ключи (см. учебный элемент «Реляционная модель данных») идентифицируются путем нахождения минимального множества значений атрибутов, которые определяют значения всех остальных атрибутов в отношении. Напомним понятие возможного ключа отношения как атрибута или атрибутов данного отношения, который (-ые) могут быть в данном отношении выбраны в качестве первичного.

Тогда критерий Кодда, позволяющий определить, находится ли отношение в НФБК, может быть сформулирован следующим образом:

Отношение находится в НФБК тогда и только тогда, когда каждый детерминант отношения является возможным ключом.

Таким образом, при декомпозиции отношений необходимо строить списки возможных ключей и детерминантов и сравнивать их на совпадение. Устранив таким образом большинство потенциальных аномалий, проектирование можно завершить.

Алгоритм метода декомпозиции отношений

Алгоритм метода декомпозиции отношений

1. Разработка универсального отношения для базы данных.

2. Определение всех ФЗ между атрибутами отношения.

3. Определение, находится ли отношение в НФБК. Если да, то завершить проектирование; в противном случае отношение должно быть разбито на два других отношения.

4. Повторение пунктов 2 и 3 для каждого нового отношения, полученного в результате декомпозиции.

Уточним некоторые аспекты метода декомпозиции.

Методы проектирования на основе синтеза отношений

Некоторые проблемы метода декомпозиции

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

Понятие о методах синтеза отношений

Исключить избыточность в исходном наборе ФЗ можно путем применения правил вывода ФЗ (См. учебный элемент «Функциональные зависимости»). Как известно, для класса F-зависимостей достаточно использовать шесть таких правил. При этом критерием останова процедуры исключения может служить получение минимального покрытия исходного набора ФЗ.

Неединственность минимальных покрытий указывает на то, что порядок исключения избыточных ФЗ может оказать влияние на полученные результаты. Отсюда следует эмпирическое правило:

Избыточные ФЗ следует удалять по одной, каждый раз проводя анализ полученного набора ФЗ на избыточность.

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

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

Кроме того, задача приведения исходных отношений к НФБК может оказаться невыполнимой. Такой факт имел место в практике проектирования. Поскольку НФБК может не существовать на заданном наборе ФЗ, то логичным было бы отказаться от требования приведения отношений базы данных к этой форме. Эта ситуация подкрепляется и теоретически: при любом заданном множестве F-зависимостей над схемой r БД можно построить схему БД в 3НФ.

Таким образом, ограничимся поиском 3НФ в ходе применения метода синтеза, при этом остаются проблемы, связанные с выполнением операций над данными.

Алгоритм метода синтеза отношений

В данном разделе приводится лишь некоторый обзор алгоритма синтеза отношений.

На пути решения этой проблемы было бы неплохо усилить все ФЗ, связав их с уникальными ключами, скажем, описывая для них уникальные индексы. Тогда можно контролировать целостность базы данных. Для этого нужно усилить минимальное покрытие. Грубо говоря, усиливаемость минимального покрытия означает, что выделено множество первичных ключей и все ФЗ из минимального покрытия пересмотрены в призме этого множества с точки зрения выводимости ФЗ рассматриваемой базы данных.

Определение 4. Реляционная база данных называется полной, если:

· все ФЗ усилены ключами;

· все отношения находятся в 3НФ;

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

Почти всегда в предметной области базы данных можно выделить набор отношений, обладающих свойством полноты. Доказана теорема [Мейер], что существует алгоритм, который выводит полную базу данных из множества заданных ФЗ.

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

Пример. Универсальный ключ и ложные соединения

Пусть отношение R имеет кортежи:

Случай 1. Отсутствие ложных соединений

Разбиение на отношения

не дает ложных соединений.

Случай 2. Наличие ложных соединений

Разбиение на отношения

дает ложные соединения

и выполнить соединение отношений AB, BCD и AC , которое восстановит исходное отношение ABCD .

Заметим, что атрибут А выступает в качестве ключа практически во всех ФЗ. Выделенный или добавленный атрибут, обладающий подобным свойством, называется универсальным ключом. Таким образом, решение проблемы ложных соединений заключается в добавлении подсхемы, которая содержит универсальный ключ, и выполнении соединения с ее использованием.

Теперь можно перейти к обзору алгоритма синтеза реляционной базы данных.

Теоретически показано, что для того, чтобы синтезировать полную базу данных, необходимо построить кольцевое минимальное покрытие для исходного набора ФЗ.

В принятых обозначениях основные этапы алгоритма синтеза отношений приведены ниже.

Алгоритм поэтапного синтеза отношений

Выход: схема полной базы данных

Этап 1. Нахождение неизбыточного покрытия F₁ для F

Этап 2. Сокращение слева элементов F₁

Этап 3. Сокращение справа элементов F₂

На этом сокращение ФЗ закончено. Избыточность отсутствует. Необходимо приступать к построению минимального покрытия.

Этап 4. Разделение F₃ на классы эквивалентности по правым частям

Этап 5. Удаление в классах эквивалентности избыточных ФЗ

Этап 6. Получение классов эквивалентности по левым частям F₅ и составных ФЗ C₁

Этап 7. Удаление избыточных зависимостей из С₁

Этап 8. Формирование кольцевого минимального покрытия

Этап 9. Формирования схемы полной базы данных

Алгоритм поэтапного синтеза отношений обладает хорошей сходимостью, его целесообразно использовать в запрограммированном виде. Для этого можно воспользоваться уже готовой программой, или написать свою программу с учетом специфики своих задач, обратившись к монографии Д. Мейера [3].

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

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

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

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

4. каждый преподаватель читает не более одного курса, а каждый курс читается только одним преподавателем.

Возможны варианты с иной степенью связи, например когда каждый преподаватель может читать несколько курсов.

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

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

Сформулируем первое правило.

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

Исходное отношение является одновременно и конечным отношением.

Это правило может быть применено во втором варианте, когда исходное отношение уже требует декомпозиции. Исходное отношение ПРЕПОДАВАТЕЛЬ_1 содержит проблему нуль-значений: данные о предметах, которые не читаются в данный момент, не могут быть внесены в базу данных.

Результирующее отношение ПРЕПОДАВАТЕЛЬ_2 не имеет проблемы нуль-значений. В результирующем отношении ПРЕДМЕТЫ эта проблема исключается: для предмета, который в данный момент не читается, определяется специальное непустое значение по умолчанию. Миграция ключа необходима для восстановления исходного отношения. Таким образом, миграция ключа в методе декомпозиции представляет собой перенос первичного ключа одного отношения в другое отношение для предотвращения потери данных при соединении.

Сформулируем третье правило.

Сформулируем четвертое правило.

Сформулируем пятое правило.

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

Аналогично для отношения с n-сторонней связью требуется построение n+1 отношений.

Следует помнить, что после предварительного разбиения исходного отношения необходимо показать, используя ФЗ и ключи отношений, что полученная схема реляционной базы данных находится в НФ Бойса-Кодда (НФБК) (или 3НФ). Только тогда полученная схема отношений может считаться окончательной. Обычно проектировщики баз данных при использовании метода декомпозиции не выполняют таких действий, так как в большинстве случаев (но не всегда!) применение правил преобразования дает НФБК.

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

Заметим, что связь, которая соединяет две роли одной исходной сущности, называется рекурсивной (служащие руководят служащими). Связь, которая соединяет роли двух различных сущностей, не является рекурсивной.

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

Сформулируем восьмое правило.

Правило 8. Исходная сущность служит источником генерации одного отношения. Ключ сущности есть ключ отношения. Подчиненные сущности (ролевые элементы) и связи, соединяющие их, порождают такое количество отношений, которое определяется набором правил 1-7, причем каждый ролевой элемент трактуется как обычная сущность.

Пример преобразования ER-диаграмм в отношения базы данных

Продемонстрируем применение метода декомпозиции (без потерь при естественном соединении) для создания логической модели и базы данных для поддержки деятельности небольшой консультационной фирмы. Фирма производит консалтинг и обучение своих постоянных клиентов; данные о клиентах, сохраняемые в базе данных, будут использоваться консультантами фирмы для проведения семинаров, практических занятий и консультаций.

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

5. Для каждого клиента определяется рейтинг (активность, частота обращений и т.д.) за период по конкретной теме. Рейтинг клиента однозначно определяется по его номеру.

6. Консультации носят тематический характер. Каждая тема имеет свой идентификатор.

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

Рассмотрим детерминанты и возможные ключи отношения КОНСУЛЬТАНТ.

Источник