Друга і третя НФ. Алгоритм декомпозиції

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

DEF. БД знаходиться в Другій Нормальній Формі (2НФ), якщо всі її відношення знаходяться в 2НФ.

DEF. Відношення знаходиться в Третій Нормальній Формі (3НФ), якщо воно знаходиться в 2НФ і з нього виключені всі транзитивні функціональні залежності.

DEF. БД знаходиться в Третій Нормальній Формі (3НФ), якщо всі її відношення знаходяться в 3НФ.

Основним прийомом нормалізації відношення є декомпозиція без втрат. Опишемо коротко загальний алгоритм декомпозиції. Скорочено аномальну ФЗ називатимемо неповну або транзитивну залежність.

1. Для кожного детермінанта аномальної функціональної залежності виписується своє відношення з цим детермінантом як ПК відношення.

2. Виписується відношення зв'язку, в яке включаються всі атрибути, що не увійшли до відношень з пункту 1, плюс до них всі ПК відношень з пункту 1 (поля зв'язку).

Слід пам'ятати, що нормалізація 1НФ проводиться послідовно 1НФ —» 2НФ —» 3НФ, тобто спочатку проводиться декомпозиція 1НФ по всіх функціонально неповних залежностях, а потім, отримані відношення розбиваються з метою позбавлення від транзитивних залежностей.

Застосуємо цей алгоритм до відношенню ВИКЛАДАЧ_ДИСЦИПЛІНА.

· Приведемо відношення до 2НФ, тобто позбавимося від функціонально неповних залежностей:

Назва предмета → К-сть годин

Особистий номер → <Прізвище, Посада, Кафедра, Оклад, Телефон>

В результаті отримаємо три відношення:

ВИКЛАДАЧ = < Особистий номер, Прізвище, Посада, Оклад, Кафедра, Телефон>;

ПРЕДМЕТ = <Назва предмету, К-сть годин>;

НАВАНТАЖЕННЯ = < Особистий номер (FK), Назва предмету (FK)>

Знаком FK позначені поля зв’язку, що називаються зовнішніми ключами (foreign key).

· Два останніх відношення не містять транзитивних залежностей, тому знаходяться в 3НФ. Відношення ВИКЛАДАЧ містить три транзитивних залежності:

Посада → Оклад;

Кафедра → Телефон;

Телефон → Кафедра.

Примінимо алгоритм декомпозиції для цих ФЗ. Строге слідування алгоритму потребує розбиття відношення ВИКЛАДАЧ на чотири

ТАРИФНА_СІТКА = < Посада, Оклад>

ТЕЛ_ДОВ1 = < Кафедра, Телефон (FK)>

ТЕЛ_ДОВ2 = < Телефон, Кафедра (FK)>

ВИКЛАДАЧ = < Особистий номер, Прізвище, Посада, Кафедра (FK)>.

В останнє відношення замість атрибуту Кафедра можна включити атрибут Телефон. Включення обох атрибутів у відношення ВИКЛАДАЧ надлишково. Більш того, можна в результаті аналізу наочної області і переговорів із замовником БД відмовитися від однієї сторони взаємно-однозначної залежності. Тоді відповідне відношення ТЕЛ_ДОВ1 або ТЕЛ_ДОВ2 пропаде. Проте якщо необхідно зберегти в схемі РБД обидві залежності, то доведеться залишити обидва відношення.

В результаті 3НФ РБД ВИКЛАДАЧ_ПРЕДМЕТ складається з шести відношень

ПРЕДМЕТ = < Назва предмету, К-сть годин>;

НАВАНТАЖЕННЯ = < Особистий номер (FK), Назва предмету (FK)>

ТАРИФНА_СІТКА = < Посада, Оклад>

ТЕЛ_ДОВ1 = < Кафедра, Телефон (FK)>

ТЕЛ_ДОВ2 = < Телефон, Кафедра (FK)>

ВИКЛАДАЧ = < Особистий номер, Прізвище, Посада (FK), Кафедра (FK)>.

 

Нормальна форма Бойса-Кодда

В області реляційних БД Коддом доведено твердження про те, що більшість аномалій в БД будуть усунені в разі належної декомпозиції кожного відношення в нормальну форму Бойса - Кодда (НФБК).

DEF. Відношення знаходиться в НФБК тоді і лише тоді, коли воно знаходиться в 3НФ, і кожен його детермінант є так само і можливим первинним ключем.

Відношення ВИКЛАДАЧ_ПРЕДМЕТ не знаходиться в НФБК. Для доказу порівняємо можливі ключі і детермінанти (таблиця. 3).

Проте РБД ВИКЛАДАЧ_ПРЕДМЕТ, що складається з шести відношень, знаходиться в НФБК, оскільки кожне її відношення знаходиться в НФБК (див. таблиці. 4).

Таблиця 3

Можливі ключі	Детермінанти
<Назва предмету, Особистий номер>	<Назва предмету>
	<Посада>
	<Кафедра>
	<Особистий номер>
	<Телефон>

 

Таблиця 4

Відношення	Можливі ключі	Детермінанти
ПРЕДМЕТ	<Назва предмету>	<Назва предмету>
ТАРИФНА_СІТКА	<Посада>	<Посада>
ТЕЛ_ДОВ1	<Кафедра>	<Кафедра>
ВИКЛАДАЧ	<Особистий номер>	<Особистий номер>
ТЕЛ_ДОВ2	<Телефон>	<Телефон>
НАВАНТАЖЕННЯ	<Назва предмету, Особистий номер>	<Назва предмету, Особистий номер>

 

Може здатися, що будь-яке відношення, що знаходиться в 3НФ знаходиться і в НФБК. Проте це не так. Приклади слід шукати у відношеннях з декількома можливими первинними ключами.

Розглянемо процес здачі студентом сесії. Структура відношення, що відображує це явище, може мати вигляд:

УСПІШНІСТЬ = <№Зал.Кн., ID_студента, Дисципліна, Дата, Оцінка>

Під ID_студента розуміється деякий номер, який привласнений студентові під час вступу, наприклад, в деканаті (залікова книжка може бути загублена, видана нова з іншим номером, а ID не міняється протягом всього періоду навчання).

Структура ФЗ в цьому випадку зображена на рис. 8.

Множина ВПК: {<№Зал.Кн.>, < ID_студента >}

У відношенні успішність немає ні функціонально неповних, ні транзитивних залежностей. Дійсно, взаємно-однозначна ФЗ №Зал.Кн. ID _Студента не може бути ідентифікована як неповна, оскільки не є залежністю між неключовими атрибутами.

Рис. 8

Таким чином, відношення УСПІШНІСТЬ знаходиться в 3НФ. В той же час воно не знаходиться в НФБК, оскільки безліч ВПК і Детермінантів не збігається (таблиця. 5).

Таблиця 5

Можливі ключі	Детермінанти
<№Зал.Кн., Дисципліна, Дата>	<№Зал.Кн., Дисципліна, Дата>
<ID_студента, Дисципліна, Дата>	<ID_студента, Дисципліна, Дата>
	<ID_студента>
	<№Зал.Кн.>

 

Щоб привести відношення УСПІШНІСТЬ до НФБК слід розділити його два одним з наступних способів:

СТУДЕНТ1 = < №Зал.Кн., ID_студента >

УСПІШНІСТЬ = < №Зал.Кн., Дисципліна, Дата, Оцінка>

або

СТУДЕНТ2 = < ID_студента, №Зал.Кн.>

УСПІШНІСТЬ = < ID_студента, Дисципліна, Дата, Оцінка>

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

Відзначимо, що у будь-якому випадку ми відмовилися від одного з напрямів взаємно-однозначної відповідності, інакше нам було б потрібно обидва відношення СТУДЕНТ1 і СТУДЕНТ2.