4.3.5. Нормальные формы

Всего определены шесть нормальных форм.

• первая нормальная форма (1НФ);

• вторая нормальная форма (2НФ);

• третья нормальная форма (3НФ);

• нормальная форма Бойса-Кода (усиленная 3НФ);

• четвертая нормальная форма (4НФ);

• пятая нормальная форма (5НФ).

Задачи получения 4НФ и 5НФ на практике возникают редко. Далее будут рассматриваться 1НФ, 2НФ и 3НФ.

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

Отношение, у которого все атрибуты простые, называется приведенным к 1НФ.

Рассмотрим ранее встречавшееся отношение ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ (см.табл.4.10). Данное отношение находится в 1НФ. В этом отношении существуют следующие частичные функциональные зависимости:

1. Атрибуты Фамилия, Должность, Оклад, Кафедра, Телефон зависят от части составного ключа ТабНом;

2. Атрибут КолЧасов зависит от части составного ключа Предмет.

Такие частичные зависимости приводят к следующим аномалиям:

1. Избыточность. Дублируются данные о преподавателе (преподаватель может вести несколько предметов) и о количестве часов предмета (один предмет могут вести разные преподаватели).

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

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

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

Таким образом, первая нормальная форма требует дальнейших преобразований.

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

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

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

2. Построить проекцию на часть составного ключа и атрибуты, зависящие от этой части.

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

1. НАГРУЗКА_ПРЕДМЕТ=ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ[ТабНом, Предмет,КолЧасов]

2. ПРЕПОДАВАТЕЛЬ=ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ[ТабНом, Фамилия,Должность, Оклад,Кафедра,Телефон]

После выполнения данных проекций отношение ПРЕПОДАВАТЕЛЬ находится в 2НФ (табл.4.11).

В отношении НАГРУЗКА_ПРЕДМЕТ (см. табл.4.11) осталась частичная зависимость атрибута КолЧасов от части составного ключа Предмет. Устранение такой зависимости осуществляется на втором шаге приведения отношения ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ ко 2НФ. На данном шаге необходимо выполнить следующие проекции:

1. НАГРУЗКА=НАГРУЗКА_ПРЕДМЕТ[ТабНом,Предмет]

2. ПРЕДМЕТ=НАГРУЗКА_ПРЕДМЕТ[Предмет,КолЧасов]

В итоге получим БД, состоящую из трех отношений: ПРЕДМЕТ, НАГРУЗКА и ПРЕПОДАВАТЕЛЬ, находящуюся в 2НФ (табл.4.12).

Таблица 4.11

Результат первого шага приведения к 2НФ отношения ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ

НАГРУЗКА_ПРЕДМЕТ ТабНом Предмет КолЧасов 1 ТЭИС 51 1 БД 68 2 ЭВМ 48 3 ЭВМ 48 4 МатАн 68 4 ТеорВер 102

ПРЕПОДАВАТЕЛЬ ТабНом Фамилия Должность Оклад Кафедра Телефон 1 Иванов Доцент 450 ИС 1-15 2 Петров Доцент 450 ИС 1-15 3 Сидоров профессор 550 ЭВМ 2-43 4 Федоров ст.преп. 350 ВМ 2-75

Таблица 4.12

Результат приведения к 2НФ отношения ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ

ПРЕДМЕТ НАГРУЗКА Предмет КолЧасов ТабНом Предмет ТЭИС 51 1 ТЭИС БД 68 1 БД ЭВМ 48 2 ЭВМ МатАн 68 3 ЭВМ ТеорВер 102 4 МатАн 4 ТеорВер

ПРЕПОДАВАТЕЛЬ ТабНом Фамилия Должность Оклад Кафедра Телефон 1 Иванов Доцент 450 ИС 1-15 2 Петров Доцент 450 ИС 1-15 3 Сидоров профессор 550 ЭВМ 2-43 4 Федоров ст.преп. 350 ВМ 2-75

Изображение отношений в виде таблиц, как это делалось до сих пор, не удобно при проектировании больших БД. Это связано с тем, что такое изображение достаточно громоздко и при его использовании не показываются связи между отношениями. Для изображения моделей данных существует много международно-признанных систем обозначений (нотаций). Рассмотрим кратко основные элементы нотации IDEF1X и далее будем изображать модели данных как с помощью таблиц, так и в данной нотации. Более подробно стандарт IDEF1X будет рассмотрен в курсе «Проектирование информационных систем».

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

В IDEF1X различают зависимые и независимые сущности. Тип сущности определяется ее связями с другими сущностями. Различают идентифицирующие и неидентифицирующие связи.

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

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

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

Пример неидентифицирующей связи между независимыми сущностями показан на рис.4.3.

Неидентифицирующие связи разделяются на необязательные и обязательные.

В случае необязательной неидентифицирующей связи атрибут дочерней сущности, являющийся внешним ключом, может иметь пустое значение. Например, на рис.4.3 неидентифицирующая связь между сущностями «Отдел» и «Сотрудник» не является обязательной. Это означает, что значение внешнего ключа «Номер отдела» в некотором экземпляре сущности «Сотрудник» может быть пустым. То есть может существовать некоторый сотрудник, который не относится ни к какому отделу.

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

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

Если для создания IDEF1X диаграмм используется программное средство AllFusion Erwin Data Modeler, то при установлении связи дочерняя сущность дополняется атрибутами родительской сущности. Такая операция называется миграцией атрибутов. Миграция атрибутов выполняется по следующим правилам:

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

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

С учетом операции миграции атрибутов можно сформулировать правила приведения ко 2НФ в среде AllFusion Erwin Data Modeler:

1. Создать новую сущность A, включив в нее часть составного ключа исходной сущности и атрибуты, зависящие от этой части.

2. Создать новую сущность B путем удаления из исходной сущности всех атрибутов, вошедших в сущность A.

3. Установить идентифицирующую связь между сущностью A и B. При этом сущность A является родительской, а сущность B - дочерней.

Вернемся к примеру приведения отношения ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ (табл.4.10) ко 2НФ. Как было отмечено выше, в данном отношении существуют две частичные функциональные зависимости (атрибуты Фамилия, Должность, Оклад, Кафедра, Телефон зависят от части составного ключа ТабНом, и атрибут КолЧасов зависит от части составного ключа Предмет). Поэтому процесс приведения данного отношения ко 2НФ состоял из двух шагов.

На первом шаге устранялась зависимость атрибутов Фамилия, Должность, Оклад, Кафедра, Телефон от части составного ключа ТабНом. Выполнение этого же процесса в среде AllFusion Erwin Data Modeler проиллюстрировано на рис. 4.4.

На втором шаге устранялась зависимость атрибута КолЧасов от части составного ключа Предмет. Выполнение этого же процесса в среде средства AllFusion Erwin Data Modeler проиллюстрировано на рис. 4.5.

В результате получилась модель БД в 2НФ, представленная IDEF1X диаграммой на рис.4.6.

Продолжим дальнейший анализ отношений, полученных в результате приведения отношения ПРЕПОДАВАТЕЛЬ_ПРЕДМЕТ ко 2НФ. В отношении ПРЕПОДАВАТЕЛЬ есть транзитивные зависимости:

ТабНом  Кафедра  Телефон

ТабНом  Должность  Оклад

Их наличие порождает неудобства и аномалии следующего характера (на примере «Телефон»):

1. Дублирование информации о телефоне для всех преподавателей.

2. Проблема контроля избыточности при изменении номера телефона.

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

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

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

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

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

БД находится в 3НФ, если все ее отношения имеют 3НФ.

Для рассматриваемого примера можно получить три отношения в 3НФ: ПРЕПОДАВАТЕЛЬ, ДОЛЖНОСТЬ и КАФЕДРА, представленные в табл.4.13.

С учетом операции миграции атрибутов в AllFusion Erwin Data Modeler правила приведения отношения во 2НФ к отношению в 3НФ будут следующие:

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

2. Создать новую сущность B путем удаления из исходной сущности всех атрибутов, вошедших в сущность A.

3. Установить неидентифицирующую связь от сущности A к сущности B.

Таблица 4.13

Примеры отношений в третьей нормальной форме

ПРЕПОДАВАТЕЛЬ ДОЛЖНОСТЬ КАФЕДРА ТабНом Фамилия Должность Кафедра Должность Оклад Кафедра Телефон 1 Иванов доцент ИС доцент 450 ИС 1-15 2 Петров доцент ИС профессор 550 ЭВМ 2-43 3 Сидоров профессор ЭВМ ст.преп. 350 ВМ 2-75 4 Федоров ст.преп. ВМ

В нашем примере в отношении ПРЕПОДАВАТЕЛЬ, которое находится во 2НФ, есть две зависимости между неключевыми атрибутами: атрибут Телефон зависит от атрибута Кафедра и атрибут Оклад зависит от атрибута Должность. Поэтому процесс приведения данного отношения к 3НФ будет состоять из двух шагов.

На первом шаге устраняется зависимость атрибута Телефон от атрибута Кафедра. Выполнение этого процесса в среде средства AllFusion Erwin Data Modeler проиллюстрировано на рис. 4.7.

На втором шаге устраняется зависимость атрибута Оклад от атрибута Должность. Выполнение этого процесса в среде средства AllFusion Erwin Data Modeler проиллюстрировано на рис. 4.8.

Полностью модель БД в 3НФ для хранения отношения ПРЕПОДПВАТЕЛЬ_ПРЕДМЕТ, представленная IDEF1X диаграммой на рис.4.9.