В примерах из домашних заданий до сих пор составление схемы БД было интуитивным. Каждой сущности и сложным видам связи ставились в соответствие таблицы, в которые входили атрибуты соответствующих им сущностей и связей. Вместе с тем, существует формальный механизм «правильного» проектирования схемы БД. Нормализация – формальный метод анализа отношения на основе первичного ключа (или потенциальных ключей) и функциональных зависимостей. Другое определение: нормализация– последовательное приведение схемы БД в удовлетворительное количество отношений и связей. В настоящее время известно шесть нормальных форм: каждая следующая нормальная форма в некотором смысле лучше предыдущей; при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются.
аномалия вставки: нельзя вставить новое отделение, в котором нет сотрудников;
аномалия удаления: при удалении всех сотрудников одного отделения исчезает информация о самом отделении;
аномалия модификации: если несколько сотрудников имеют одинаковое задание, то его изменение придется выполнять в нескольких кортежах.
Определение нормальных форм и пример нормализации БД приведен в Табл. 4.
Как показывает опыт, разработчики БД интуитивным путем создают БД в третьей НФ, если каждой сущности или документу предметной области ставят в соответствие таблицу (если, конечно, бизнес-правила в организации построены логично). При этом нужно следить, чтобы атрибуты находились в нужной таблице. Если есть сомнения, к какой таблице отнести атрибут, то, возможно, это означает, что таблицы на самом деле связаны между собой промежуточной таблицей, которую не включили в схему. Нужно обращать внимание на то, чтобы одна и та же информация не дублировалась в разных таблицах (иначе возможна противоречивость информации). В отношении бизнес-правил также стоит задуматься о последствиях изменения некоторых атрибутов. Так, например, изменение значения отдела у продавца приведет к тому, что все его прошлые сделки, которые он совершил, работая в одном отделе, перейдут к другому отделу. Или изменение фамилии приведет к изменению прошлых официальных документов, в которых человек был под другой фамилией, что может быть не желательно. Такие проблемы требуют изменения структуры БД.
Табл. 4. Нормализация БД
НФ
Описание
Дополнительное определение
Пример
Последствия
1NF
Отсутствие составных атрибутов
ФИО разделить на три части
2NF
1NF + каждый неключевой атрибут полностью зависит от первичного ключа.
В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X (r) R.Y.
Функциональная зависимость R.X (r) R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X.
В результате мы не сможем вставить в отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ кортеж, описывающий сотрудника, который еще не выполняет никакого проекта. При удалении кортежа мы не только разрушаем связь данного сотрудника с данным проектом, но утрачиваем информацию о том, что он работает в некотором отделе. При переводе сотрудника в другой отдел мы будем вынуждены модифицировать все кортежи, описывающие этого сотрудника, или получим несогласованный результат.
3NF
2NF + каждый неключевой атрибут нетранзитивно зависит от первичного ключа.
Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z --> R.X.
В результате мы не сможем занести в базу данных информацию, характеризующую заработную плату отдела, до тех пор, пока в этом отделе не появится хотя бы один сотрудник (первичный ключ не может содержать неопределенное значение). При удалении кортежа, описывающего последнего сотрудника данного отдела, мы лишимся информации о заработной плате отдела. Чтобы согласованным образом изменить заработную плату отдела, мы будем вынуждены предварительно найти все кортежи, описывающие сотрудников этого отдела.
BCNF (НФ Бойса-Кодда)
Каждый детерминант является возможным ключом.
Детерминант - любой атрибут, от которого полностью функционально зависит некоторый другой атрибут.
для того, чтобы изменить имя сотрудника с данным номером согласованным образом, нам потребуется модифицировать все кортежи, включающие его номер.
4NF
В случае существования многозначной зависимости A ->-> B все остальные атрибуты R функционально зависят от A.
В отношении R (A, B, C) существует многозначная зависимость R.A ->-> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С.
некоторый сотрудник присоединяется к данному проекту, необходимо вставить в отношение ПРОЕКТЫ столько кортежей, сколько заданий в нем предусмотрено.
PJ/NF (Проекция-Соединение)
любая зависимость соединения в R следует из существования некоторого возможного ключа в R.
Отношение R (X, Y, ..., Z) удовлетворяет зависимости соединения * (X, Y, ..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y, ..., Z.
Предположим, что в отношении СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения:
* (СО, СП, ОП)
На примерах легко показать, что при вставках и удалениях кортежей могут возникнуть проблемы. Их можно устранить путем декомпозиции исходного отношения в три новых отношения:
