Нормализация

5.6 Определение второй нормальной формы. Правило приведения

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

Оказывается могут существовать зависимости не от всего ключа, а от его части. Посмотрите какое, может быть необычное для вас, отношение "Доходы_совместителей" со столбцами ИНН, ФИО, Организация, Зарплата изображено на рисунке 5.3. Оно описывает доходы совместителей, то есть людей, работающих в двух или более организациях. В первичный ключ входят поля ИНН и Организация, иначе непонятно было бы, где человек с этим ИНН получает зарплату. Имеется одна особенность: поле ФИО, зависящее от всего ключа, кроме того, функционально зависит от части ключа - ИНН - , так как фамилия, имя и отчество однозначно определяются по ИНН.

Рис. 5.3. Доходы совместителей

Теперь можно переходить к определению второй нормальной формы (2НФ, 2NF).

Определение (Полнота функциональной зависимости). Если набор атрибутов зависит от всего набора атрибутов , но не зависит от части этого набора, то говорят, что функциональная зависимость полная.

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

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

Эти определения эквивалентны, в отличие от определений 1НФ.

Замечание. Если единственный ключ отношения в ШФ является простым (не конкатенированным), то отношение находится в 2НФ по той простой причине, что невозможно выделить часть ключа.

Правила приведения, как и в ШФ, сначала рассмотрим на примере. Имеется (рисунок 5.4 вверху) ненормализованная сущность под названием "Проект". Ее ключ образуют два поля "Наименование_проекта" и "Таб_номер_руководителя". Неключевые поля "Дата_начала" (проекта), "Дата завершения" (проекта), "Фамилия", "Имя", "Отчество" и "Должность" (руководителя проекта). Поскольку фамилия, имя, отчество и должность руководителя проекта зависят функционально от табельного номера, предполагаем, что внутри спрятана сущность "Руководитель" и выделяем ее (рисунок 5.7 справа вверху). Что останется в сущности "Проект"? Атрибуты "Наименование_проекта", "Дата начала" и "Дата завершения".

Остается создать связь. Заметим, что экземплярами изолированной сущности "Руководитель_проекта" могут быть любые сотрудники. Но нас интересуют только выбранные из них руководители проектов. Отсюда делаем вывод о том, что сущность "Руководитель_проекта" слабая и потому связь идентифицирующая (рисунок 5.7 слева внизу). В последнем варианте диаграммы (рисунок 5.7 справа внизу) атрибут "Табельный номер" сущности "Проект" заменен на "Табельный_номер_рук" Зачем? В исходной сущности непонятно, чей это табельный номер. А вдруг проекты тоже имеют табельные номера. В сущности "Руководитель" понятно, что "Табельный номер" принадлежит руководителю. Такого рода уточнения не обязательны, но могут быть полезными.

Правила приведения ко второй нормальной форме.

Рис. 5.4. Пример приведения к 2НФ

• Выделить неключевые атрибуты, зависящие от части первичного ключа. Иначе говоря, найти функциональную зависимость группы неключевых атрибутов от части атрибутов ключа. (В нашем примере мы обнаружили, что некоторые из неключевых атрибутов отношения зависят функционально от табельного номера руководителя).
• Создать новую сущность. В соответствии с теоремой Хиса все ее атрибуты входят в найденную функциональную зависимость.
• Вычеркнуть атрибуты-значения найденной функции в исходной сущности. (Обратите внимание на то, что работая в ERwin, мы выделили сущность "Руководитель_проекта" и все ее атрибуты, включая атрибут-аргумент функции "Табельный номер", удалили из исходного отношения. На следующем этапе этот атрибут вернется в сущность "Проект").
• Установить идентифицирующую связь от преобразованной исходной сущности к созданной сущности.

5.7 Третья нормальная форма. Связь третьей и второй нормальных форм

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

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

Рис. 5.5. Пример приведения к 3НФ

Предварительно определим две разновидности функциональных зависимостей (ФЗ): транзитивную и прямую.

Определение (транзитивная и прямая ФЗ). Функциональная зависимость называется транзитивной, если найдется атрибут или набор атрибутов , отличный от и , такой что , - функциональные зависимости. Если не существует транзитивной зависимости, то функциональная зависимость называется прямой.

Теперь можно дать два эквивалентных определения третьей нормальной

формы. (3НФ, 3NF).

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

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

Внимательный читатель должен сразу же спросить: нет ли ошибки в определениях? Не следовало бы начинать определения словами "отношение в 2НФ ..."? Ответ дает следующая теорема.

Теорема 5.1. Если отношение находится в 3НФ, то оно находится во 2НФ.

Доказательство. Предварительно сформулируем два отрицательных высказывания, с которыми будем работать:

1. Нарушение условия 2НФ: Во 2НФ каждый непервичный атрибут не может частично зависеть от ключа. Обозначим его (2НФ) ("отрицание второй нормальной формы").
2. Нарушение условия 3НФ: В 3НФ ни один из непервичных атрибутов не может быть транзитивно зависимым от ключа. Обозначим его (3НФ) ("отрицание 3НФ").

Выбор схемы доказательства: Оказывается, достаточно показать, что из частичной зависимости следует транзитивная зависимость. Это будет означать, что из нарушения условия 2НФ следует нарушение условие 3НФ. В самом деле, по определению импликации . Докажем, что ((3НФ) (2НФ)) (2НФ) (3НФ) . В самом деле, в обозначениях и :.

Доказательство: Пусть 2НФ, то есть найдется непервичный атрибут , который частично зависит от ключа . Это означает, что . Конечно, не существует зависимости

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

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

Рис. 5.6. Транзитивные зависимости для 2НФ и ЗНФ

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

Правило приведения к третьей нормальной форме.

• Найти функциональную зависимость неключевых атрибутов от других неключевых атрибутов.
• Создать новую сущность. В соответствии с теоремой Хиса все ее атрибуты входят в найденную функциональную зависимость.
• Вычеркнуть атрибуты-значения найденной функции в исходной сущности.
• Установить неидентифицируюшую связь от созданной сущности к исходной сущности

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