Нормалізація: третя форма, BCNF і денормалізація

Після 1НФ значення в клітинках є атомарними, а після 2НФ кожен неключовий атрибут повністю залежить від усього складеного ключа. Проте цього ще недостатньо. У таблиці можуть залишитися факти, які залежать від ключа через інший неключовий атрибут. Так виникають повторення й аномалії, які усуває третя нормальна форма.

У цій лекції розглянемо 3НФ, сильнішу нормальну форму Бойса-Кодда, дві важливі властивості декомпозиції та умови, за яких від нормалізованої структури свідомо відступають.

Цілі лекції

Після опрацювання матеріалу ви зможете:

знаходити транзитивні функціональні залежності;
приводити відношення з 2НФ до 3НФ;
визначати детермінанти й перевіряти умову BCNF;
пояснювати відмінність між 3НФ і BCNF;
оцінювати декомпозицію щодо відсутності втрат і збереження залежностей;
відрізняти свідому денормалізацію від випадкового поганого проєктування.

Передумови

Потрібно знати поняття відношення, атрибута, кортежу, кандидатного ключа, суперключа та функціональної залежності. Вважаємо, що початкова таблиця вже перебуває у 1НФ і 2НФ.

Нагадаємо позначення: запис X → Y означає, що однаковому значенню X завжди відповідає одне значення Y. Набір атрибутів ліворуч, тобто X, називають детермінантом залежності.

1. Транзитивна залежність

Розглянемо відношення:

STUDENT(student_id, student_name, group_id, group_curator)

Його функціональні залежності:

student_id → student_name, group_id
group_id → group_curator

student_id є ключем і визначає групу студента. Група, своєю чергою, визначає куратора. Тому маємо ланцюжок:

student_id → group_id → group_curator

Отже, group_curator транзитивно залежить від student_id через group_id.

Транзитивна функціональна залежність неключового атрибута від ключа існує, коли ключ визначає деякий неключовий атрибут, а той визначає інший неключовий атрибут.

Це створює надлишковість: ім'я куратора повторюється в кожному рядку студента тієї самої групи. Наслідки вже знайомі:

аномалія оновлення: після зміни куратора треба змінити багато рядків;
аномалія вставки: не можна записати групу та її куратора, доки немає студента;
аномалія видалення: видалення останнього студента може знищити єдині відомості про куратора групи.

Важливо аналізувати не випадкові збіги в поточних даних, а правила предметної області. Якщо сьогодні всі групи випадково мають різних кураторів, це ще не доводить залежність group_curator → group_id. Потрібно з'ясувати, чи правило гарантує її для всіх допустимих станів бази.

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

Доступне практичне формулювання:

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

Для точнішої перевірки використовують правило для кожної нетривіальної функціональної залежності X → A. Залежність є нетривіальною, якщо A не входить до X. Відношення перебуває у 3НФ, якщо для кожної такої залежності виконується хоча б одна умова:

X є суперключем; або
A є ключовим атрибутом, тобто входить хоча б до одного кандидатного ключа.

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

Приведення прикладу до 3НФ

Винесемо факт про групу до окремого відношення:

STUDENT(student_id, student_name, group_id)
GROUP(group_id, group_curator)

Тепер у STUDENT ключ student_id визначає ім'я та групу студента. У GROUP ключ group_id визначає куратора. Кожен факт зберігається в одному логічному місці.

У майбутній SQL-схемі STUDENT.group_id буде зовнішнім ключем, який посилається на GROUP.group_id. Саме зовнішній ключ пов'язує розділені факти, але нормалізація починається з аналізу залежностей, а не з механічного створення таблиць.

Алгоритм пошуку 3НФ

Запишіть змістовні функціональні залежності.
Знайдіть кандидатні ключі.
Переконайтеся, що відношення вже у 2НФ.
Знайдіть залежності між неключовими атрибутами.
Винесіть атрибути, що описують окремий факт, разом з їхнім детермінантом.
Залиште детермінант у початковій таблиці як атрибут зв'язку.
Перевірте, чи можна відновити початкові дані з'єднанням і чи можна контролювати початкові залежності.

Нормалізація не означає «розділити таблицю на якомога більше частин». Кожне розділення має випливати з функціональної залежності та зберігати зміст даних.

3. Детермінанти та BCNF

Нормальна форма Бойса-Кодда (BCNF) посилює вимогу 3НФ:

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

Інакше кажучи, право однозначно визначати інші атрибути повинні мати лише набори атрибутів, здатні однозначно визначити весь кортеж.

Приклад STUDENT після декомпозиції відповідає BCNF:

у STUDENT детермінант student_id є ключем;
у GROUP детермінант group_id є ключем.

BCNF особливо важлива, коли відношення має кілька перекривних кандидатних ключів. Саме тоді простого правила «неключові атрибути залежать тільки від ключа» недостатньо.

4. Чим 3НФ відрізняється від BCNF

Кожне відношення у BCNF перебуває у 3НФ, але не кожне відношення у 3НФ перебуває у BCNF.

Розглянемо навчальний розклад:

TEACHING(student, subject, teacher)

Нехай діють правила:

для кожної пари «студент, дисципліна» визначено одного викладача;
кожен викладач веде лише одну дисципліну, але дисципліну можуть вести різні викладачі.

Функціональні залежності:

(student, subject) → teacher
teacher → subject

Кандидатні ключі:

(student, subject)
(student, teacher)

У залежності teacher → subject детермінант teacher не є суперключем: один викладач може навчати багатьох студентів. Отже, BCNF порушено.

Водночас subject є ключовим атрибутом, бо входить до кандидатного ключа (student, subject). Тому залежність задовольняє точне правило 3НФ. Це і є типовий випадок 3НФ, але не BCNF.

Декомпозиція до BCNF:

TEACHER_SUBJECT(teacher, subject)
STUDENT_TEACHER(student, teacher)

Перше відношення зберігає дисципліну викладача, друге - факт навчання студента в цього викладача. Повторення пари «викладач - дисципліна» зникає.

Ознака	3НФ	BCNF
Перевіряє всі нетривіальні функціональні залежності	Так	Так
Вимагає, щоб детермінант завжди був суперключем	Ні, є виняток для ключового атрибута праворуч	Так
Може залишити окрему надлишковість	Іноді	Менше шансів
Завжди зберігає всі залежності після стандартного синтезу	Можна побудувати таку декомпозицію до 3НФ	Для BCNF це не завжди можливо

Практичний висновок: BCNF є бажаною, але не треба механічно вимагати її ціною неможливості просто контролювати важливі правила. Потрібно оцінити властивості конкретної декомпозиції.

5. Декомпозиція без втрат

Після поділу відношення дані мають зберегти свій зміст.

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

Для декомпозиції STUDENT і GROUP спільним атрибутом є group_id. У GROUP він є ключем. Тому кожен рядок студента з'єднується не з випадковою множиною рядків, а з єдиною відповідною групою.

Поганий поділ можна побачити на прикладі:

ENROLLMENT(student, subject, semester)

Якщо створити лише STUDENT_SUBJECT(student, subject) і STUDENT_SEMESTER(student, semester), то після з'єднання для студента з кількома дисциплінами й семестрами можуть утворитися комбінації, яких ніколи не було. Така декомпозиція створює хибні кортежі й не є безвтратною.

Доступна перевірка для поділу відношення R на R1 і R2: їхні спільні атрибути мають функціонально визначати всі атрибути хоча б одного з двох отриманих відношень. Це не замінює повного формального аналізу складних випадків, але добре працює для наших прикладів.

6. Збереження залежностей

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

У декомпозиції:

STUDENT(student_id, student_name, group_id)
GROUP(group_id, group_curator)

залежність student_id → student_name, group_id перевіряється в STUDENT, а group_id → group_curator - у GROUP. Залежності збережено.

Безвтратність і збереження залежностей відповідають на різні питання:

без втрат: чи можемо правильно відновити дані;
збереження залежностей: чи можемо локально контролювати правила.

Декомпозиція до BCNF завжди може бути виконана без втрат, але вона іноді не зберігає всі залежності. Тоді перевірка окремого правила може вимагати з'єднання таблиць, додаткового обмеження або прикладної логіки. Через це на практиці інколи обирають коректну 3НФ замість подальшого переходу до BCNF.

7. Свідома денормалізація

Денормалізація - це навмисне внесення контрольованої надлишковості до вже зрозумілої нормалізованої моделі заради конкретної вимірюваної мети.

Вона не означає «не встигли нормалізувати» або «JOIN здається складним». Спочатку будують коректну нормалізовану схему, визначають правила й вимірюють роботу системи. Лише після цього розглядають компроміс.

Обґрунтовані причини можуть бути такими:

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

Наприклад, у замовленні можна зберегти product_name_at_purchase. Це не просто копія поточної назви товару, а історичний факт: як товар називався в момент купівлі. Такий атрибут має окрему семантику.

Інший приклад - збережений order_total, хоча його можна обчислити з позицій замовлення. Це рішення потребує механізму узгодження: транзакції, тригера, контрольованої функції або іншого єдиного шляху оновлення. Інакше сума стане суперечливою.

Перед денормалізацією дайте відповіді на запитання:

Який конкретний запит або операція є проблемою?
Якими вимірюваннями це підтверджено?
Чому індекс, переписаний запит, кеш або матеріалізоване представлення не розв'язує проблему краще?
Який факт дублюватиметься?
Яке джерело є головним?
Як і коли копії синхронізуються?
Як система виявить і виправить розбіжність?

Ціна денормалізації - складніші записи, додаткові перевірки, ризик аномалій та більше місця. Вигода має переважати цю ціну і бути підтверджена вимірюваннями.

8. Межа цієї лекції

Існують вищі нормальні форми, зокрема 4НФ і 5НФ. Вони працюють з іншими видами залежностей і складнішими випадками декомпозиції. Тут достатньо знати, що 3НФ і BCNF не завершують усю теорію нормалізації.

Багатозначні залежності, 4НФ, 5НФ та інтегрований вибір структури для повної предметної області розглядатимуться в лекції 9. Не слід називати будь-який повтор «порушенням 4НФ» без аналізу відповідного виду залежності.

Практичні завдання

Завдання 1. Від 2НФ до 3НФ

Дано відношення:

ENROLLMENT(student_id, course_id, student_name, course_title, department_id, department_name, grade)

Діють залежності:

student_id → student_name
course_id → course_title, department_id
department_id → department_name
(student_id, course_id) → grade

Вважайте, що часткові залежності вже усунуто створенням окремих відношень STUDENT, COURSE та ENROLLMENT. Знайдіть транзитивну залежність, приведіть результат до 3НФ і запишіть ключі.

Завдання 2. 3НФ чи BCNF

Для відношення TEACHING(student, subject, teacher) використайте залежності:

(student, subject) → teacher
teacher → subject

Знайдіть усі кандидатні ключі.
Доведіть, що відношення перебуває у 3НФ.
Поясніть порушення BCNF.
Виконайте безвтратну декомпозицію до BCNF.

Завдання 3. Рішення про денормалізацію

Інтернет-магазин обчислює суму замовлення з його позицій. Команда пропонує додати order_total до таблиці ORDER, бо сторінка історії замовлень відкривається повільно.

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

Підсумок

Транзитивна залежність проходить від ключа до неключового атрибута через інший неключовий атрибут.
3НФ усуває такі залежності; у точному правилі допускається залежність від не-суперключа, якщо праворуч стоїть ключовий атрибут.
Детермінант - ліва частина функціональної залежності.
BCNF вимагає, щоб кожен детермінант нетривіальної залежності був суперключем.
BCNF сильніша за 3НФ: кожна BCNF є 3НФ, але не навпаки.
Декомпозиція без втрат правильно відновлює початкове відношення; збереження залежностей дає змогу локально контролювати початкові правила.
Денормалізація є свідомим, виміряним і контрольованим компромісом, а не заміною нормалізації.
4НФ, 5НФ та інтегровані проєктні рішення належать до наступної лекції.