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

Третья нормальная форма (3НФ) ужесточает требования к разрешенным в отношении функциональным зависимостям между атрибутами АR.

Рассмотрим приведение отношения к 3НФ на примере следующего отношения R (рис. 16):

Факультет Группа ДисциплинаВид_работы ПреподавательДолжность
ФИСУГ1Д1В1П1А1
ФИСУГ1Д1В2П2А2
ФИСУГ1Д2В1П2А2

Рис. 16. Пример состояния схемы отношения

Пусть для этого отношения задана следующая структура зависимостей (рис. 17):

ФЗ = {Дисциплина, Вид_работы Преподаватель,
Дисциплина, Вид_работы Факультет,
Дисциплина, Вид_работы Группа,
Дисциплина, Вид_работы Должность,
Преподаватель Должность
Группа Факультет,
Преподаватель Факультет}

Рис. 17. Список ФЗ, заданных на отношении, приведенном на рис. 16

В представленном на рис. 16 отношении с заданным множеством зависимостей ФЗ (рис. 17) первичный ключ состоит из атрибутов Дисциплина, Вид_работы .

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

Определение 3НФ

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

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

Более точно правило принадлежности отношения к 3НФ можно сформулировать следующим образом.

Выделим из множества имен атрибутов АR отношения R три подмножества X, Y, Z, не являющихся атрибутами ключа, так что X ≠ Y и Y ≠ Z.

Если XY, YZ и YX, то Z транзитивно зависит от X, т.е. XZ и ZX

Заметим, что запрещается только ФЗ YX, так как в этом случае они бы функционально определяли друг друга (что фактически соответствовало бы их эквивалентности). Наличие или отсутствие ФЗ ZY в данном случае игнорируется: она может как существовать, так и не существовать.

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

Рассмотрим переход к третьей нормальной форме (рис. 18 - 21) от отношения, состояние которого представлено на рис. 16:

Факультет ДисциплинаВид_работы Должность
ФИСУД1В1А1
ФИСУД1В2А2
ФИСУД2В1А2

Рис. 18. Отношение R18


Группа Дисциплина Вид_работыПреподаватель
Г1Д1В1П1
Г1Д1В2П2
Г1Д2В1П2

Рис. 19. Отношение R8


Факультет ПреподавательДолжность
ФИСУП1А1
ФИСУП2А2
ФакультетГруппа
ФИСУГ1

Рис. 20. Отношение R9 Рис. 21. Отношение R10

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

Заметим, что исходное отношение, представленное на рисунке 16, восстановимо из полученных проекций показанных на рисунках 18 - 21. На языке SQL эту операцию можно записать как

SELECT Факультет, Группа, Дисциплина, Вид_работы, Преподаватель, Должность
FROM R7, R8, R9, R10
WHERE (R7.Дисциплина, R7.Вид_Работы) = (R8.Дисциплина, R8.Вид_Работы) AND R10.Группа=R8.Группа AND R9.Преподаватель=R8.Преподаватель;

Рис. 22. Предложение языка SQL для восстановления отношения на рис. 16

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

Однако доказано [1, 13], что не любое отношение можно привести к БКНФ.

Усиленная 3НФ (БКНФ)

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

Пример 1. Пусть в отношении R(x1,x2,x3) определено множество F={x1x3, x2x3} [2]. Если исходное отношение представить в виде совокупности проекций R1=R[x1,x2] и R2=R[x1, x3], то состояние отношения R (рис. 23) восстановимо из проекций R1 (рис. 24) и R2 (рис.25) по ключу отношения R1 и R2.

x1x2 x3
123
456
x1x2
12
45
x1x2
13
46
Рис.23. RРис. 24. R1Рис. 25. R2

Однако в базовые таблицы R1 (рис. 24) и R2 (рис. 25) нельзя добавить строки: <7, 2> и <7, 8>, так как эти строки противоречат зависимости x2x3. Проверить (узнать), что в этом случае в базе происходит нарушение ограничений целостности, при наличии только описания таблиц R1 и R2 невозможно. Фактически подобное разложение привело к "потере" функциональной зависимости x2x3, что, в конечном итоге, может привести к противоречивости хранимых данных.

С практической точки зрения данный пример показывает, что структура функциональных зависимостей отношения (являясь основой для нормализации отношения) не восстанавливается по структурам функциональных зависимостей выделенных проекций и требуется хранить систему образующих структуры функциональных зависимостей [2, 13].

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

Пример 2. Пусть имеется отношение R (x1, x2, x3), на котором определены функциональные зависимости F={x1, x2x3, x3x2}. Ключом отношения R является совокупность атрибутов {x1, x3}, при этом атрибут x2 транзитивно зависит от ключа.

Хотя атрибут x2 является первичным, его желательно исключить [13] из схемы отношения, поскольку в R имеются кортежи с повторяющимися значениями атрибутов {x2, x3}
Итак, несмотря на то, что отношение R находится в 3НФ, его следует разбить на две проекции: R1(x1, x3) и R2 (x3, x2), для устранения избыточности. Ключи в указанных отношениях подчеркнуты.
Отношения R1 и R2 находятся в усиленной третьей нормальной форме.

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

Для приведения отношения к БКНФ используется общее правило декомпозиции, которое применялось и для приведения отношения из 2НФ в 3НФ.

Из множества функциональных зависимостей F выбирается функциональная зависимость XY, где X AR, Y AR\X и X не является ключом R. Выполняется декомпозиция R на R1(AR\Y) и R2(X,Y).

Пример 3. Воспользуемся отношением R (Город, Адрес, Индекс), из параграфа 2.4, где было доказано, что в этом отношении существуют два множества атрибутов {Адрес, Индекс} и {Город, Индекс}, которые являются возможными ключами, если для отношения определена структура функциональных зависимостей: F={Город, Адрес Индекс, Индекс Город}

Тогда, используя функциональную зависимость, Индекс Город выделим из отношения R две проекции:

R1 (Адрес, Индекс) и R2 (Город, Индекс)

Ключи приведенных отношений подчеркнуты.

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

Существуют и другие определения усиленной 3НФ.

Определение. Нормализованное отношение находится в У3НФ, если каждая детерминанта является возможным ключом.

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

Рассмотрим использование введенного определения на примере отношения R(Студент, Предмет, Преподаватель), на котором задано множество функциональных зависимостей F={F1, F2}, где

F1 = Студент, Предмет Преподаватель,
F2 = Преподаватель Предмет.

Семантический смысл F1 заключается в том, что каждый студент обучается определенному предмету только одним преподавателем, а смысл F2 - в том что, преподаватель ведет только один предмет.

Возможными ключами являются атрибуты {Студент, Предмет} и {Студент, Преподаватель}.

Пусть отношение R имеет следующее состояние (рис. 26):

СтудентПредметПреподаватель
ИвановМатематикаСидоров
ИвановФизикаПетров
СмирновМатематикаСидоров
СмирновФизикаБрук

Рис. 26. Состояние отношения R

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

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

Однако, если это отношение привести к У3НФ, учитывая, что атрибут Преподаватель является детерминантой, то от данного недостатка можно избавиться. Для этого отношение R надо представить в виде эквисоединения двух проекций (рис. 27, 28), в которых каждая детерминанта будет возможным ключом.

R = R1 x° R2 = R1(Студент, Преподаватель) x° R2(Предмет, Преподаватель).

СтудентПреподаватель
ИвановСидоров
ИвановПетров
СмирновСидоров
СмирновБрук
ПредметПреподаватель
МатематикаСидоров
ФизикаПетров
ФизикаБрук

Рис. 27. Отношение R1 Рис. 28. Отношение R2


[ Назад  Начало раздела  Далее  Содержание]