Лекція 6. Нормалізація
1. Постановка задачі
Рис. 1 ‒ Приклад ненормалізованої БД
Надлишковість даних в БД є небажаним явищем, оскільки призводить до збільшення об'єму пам'яті, уповільнює роботу БД. Надлишковість даних є результатом в першу чергу дублювання даних. Розрізняють незбиткове та збиткове дублювання даних. Повністю усувати надлишковість не потрібно, оскільки при цьому неможливо буде підтримувати БД як єдине ціле. Слід тільки мінімізувати надлишковість, залишивши необхідне дублювання даних.
Відсутність надлишковості. У системі є присутня надлишковість, якщо одні і ті ж дані знаходяться у декількох місцях. При цьому не тільки нераціонально використовується місце у пам'яті, але й можуть виникати неузгодженості та неоднозначності даних. Наприклад, у наведеній вище таблиці повторюються назви кафедр з місцями їх розташування.
Дублювання даних створює проблеми при виконанні операцій з БД. Ці проблеми виникають при спробі зробити операції: редагування, додавання або вилучення даних.
Аномаліями називається така ситуація в БД, яка призводить до протиріч у БД, або суттєво ускладнює обробку даних. Розрізняють аномалії модифікації, додавання і вилучення.
Приклад. Розглянемо відношення Студент (табл. 7.1).
Таблиця 7.1
|
Номер залікової книжки |
Прізвище |
Група |
Факультет |
Декан |
|
1010 |
Бойко |
ІТП-31 |
АІТ |
Барков |
|
2020 |
Лемешко |
ІУСТ-22 |
АІТ |
Барков |
|
1030 |
Шевченко |
ІТП-31 |
АІТ |
Барков |
|
1121 |
Петренко |
БМО-32 |
АІТ |
Барков |
|
2231 |
Грицюк |
ТБ-21 |
БТ |
Тимчук |
Аномалія модифікації виникає при спробі змінити прізвище декана. В цій ситуації необхідно переглянути всі кортежі. При великих розмірах БД це потребує значного часу, при цьому можливі помилки (у разі невірного введення прізвища), які порушать цілісність БД.
Аномалія додавання виникає при додаванні інформації про нового студента, при цьому необхідно вводити інформацію, яка вже є в БД: назва факультету, прізвище декана. Крім того неможливо створити нову групу поки не існує студентів, які в ній займаються (можна присвоїти NULL-знaчeння усім стовпцям опису студентів нашої таблиці, включаючи і номер заліковки. Це теж є аномалією вставки, оскільки номер заліковки (первинний ключ) не може містити NULL-знaчeння.
Аномалія видалення виникає при спробі видалити дані про студента, який в групі поки ще один, наприклад Лемешко. В цьому випадку зникне інформація про групу ІУСТ-22.
Виконання декомпозиції наведеного відношення дозволяє позбутися вищеозначених аномалій (табл. 7.2...7.4).
Таблиця 7.2. Студент
|
Номер залікової книжки |
Прізвище |
Група |
|
1010 |
Бойко |
ІТП-31 |
|
2020 |
Лемешко |
ІУСТ-22 |
|
1030 |
Шевченко |
ІТП-31 |
|
1121 |
Петренко |
БМО-32 |
|
2231 |
Грицюк |
ТБ-21 |
Таблиця 7.3. Група
|
Група |
Факультет |
|
ІТП-31 |
АІТ |
|
ІУСТ-22 |
АІТ |
|
БМО-32 |
АІТ |
|
ТБ-21 |
БТ |
Таблиця 7.4. Факультет
|
Факультет |
Декан |
|
АІТ |
Барков |
|
БТ |
Тимчук |
Як бачимо, проста декомпозиція вихідної таблиці на таблиці Студент, Група, Факультет усуває зайву надлишковість та проблеми, пов'язані з аномаліями обновлення.
Нормалізація ‒ це формальний метод аналізу таблиць на основі їх первинних чи потенційних ключів та існуючих функціональних залежностей. Він включає ряд правил, які можуть використовуватися для перевірення окремих таблиць таким чином, щоб уся БД могла бути нормалізованою до необхідного степеню. Якщо деяка вимога не виконується, тоді таблиця, що суперечить цій вимозі, повинна бути розбита на таблиці, кожна з яких окремо задовольняє усім вимогам нормалізації. Результатом нормалізаії є логічна модель БД.
Процес проектування БД з використанням декомпозиції являє собою процес послідовної нормалізації схем відношень, при цьому кожна наступна ітерація відповідає нормальній формі більш високого рівня і має кращі властивості у порівнянні з попередньою. Кожній нормальній формі (НФ) відповідає деякий набір обмежень. Визначають такі нормальні форми: 1НФ, 2НФ, 3НФ, НФБК (нормальна форма Бойса-Кодда), 4НФ, 5НФ, 6НФ.
При виконанні декомпозиції зберігається множина вихідних функціональних залежностей між атрибутами і виконується зворотність. Із декомпозицією великої таблиці на більш дрібні пов’язані важливі властивості:
1) Властивість «з'єднання без втрат» гарантує, що будь-яка стрічка вихідної таблиці може бути визначена за допомогою відповідних стрічок більш дрібних таблиць.
2) Властивість «збереження функціональних залежностей» гарантує, що обмеження вихідної таблиці можна забезпечити шляхом накладання таких ж обмежень на усі дрібні таблиці.
3) Властивість «зворотності» ‒ означає можливість відновлення вихідної схеми.
Атрибут В функціонально залежить від А, якщо кожному значенню А відповідає в точності одне значення В. Математичний запис функціональної залежності (ФЗ): А→В.
Приклад. Функціональні залежності:
Студент → Група; Група → Факультет;
Викладач, Студент, Дисципліна → Оцінка.
Якщо існує ФЗ А→В, то це означає, що у всіх кортежах з однаковим значенням атрибуту А атрибут В буде мати також одне й те ж значення. А і В можуть складатися з декількох атрибутів.
2. Нормальні форми
Рис. 7.2 ‒ Рівні нормалізації
Перші три нормальні форми були запропоновані у 1972 році Едгаром Коддом. При цьому мінімально допустимою формою нормалізації таблиці є 1NF. Мотивацією Кодда для введення додаткових визначень було те, що 2NF є «більш бажаною», аніж 1NF, a 3NF, у свою чергу, «більш бажана», аніж 2NF. Таким чином, доцільніше проектувати БД у 3NF, аніж у 1NF чи лише 2NF. Варто зазначити, що оригінальне визначення Кодда для 3NF має певні неадекватності, і тому згодом ця форма була перероблена Бойсом та Коддом, і, відповідно, отримала назву нормальної форми Бойса-Кодда (BCNF).
Через деякий час Фейгіном (Fagin) була визначена нова, четверта нормальна форма (4NF), яка стала четвертою за рахунком, оскільки на момент її створення нормальна форма Бойса-Кодда вважалася третьою. Згодом Фейгін дав визначення ще одної нормальної форми, яку назвав проекційно-з'єднувальною нормальною формою; її також називають п'ятою нормальною формою (5NF). Шоста нормальна форма (6NF) ‒ одна з можливих нормальних форм таблиці реляційної бази даних. Введена К. Дейтом як узагальнення п'ятої нормальної форми для хронологічної бази даних.
Перша нормальна форма. Відношення знаходиться в 1НФ тоді і тільки тоді, коли всі його атрибути є атомарними.
Значення атрибуту вважається атомарним, якщо воно є неподільним у всіх застосуваннях.
Приклад. Представлення даних у таблицях може вважатися як атомарним, так і неатомарним залежно від використання. Засіб представлення визначається необхідним ступенем деталізації і повинен підтримуватися у всіх застосуваннях (табл. 7.5).
Таблиця 7.5. Дата народження
|
Прізвище |
Дата народження |
|
Бойко |
15 лютого 1991 |
|
Прізвище |
Дата і місяць |
Рік |
|
|
Бойко |
15 лютого |
1991 |
|
|
|
|||
|
Прізвище |
День |
Місяць |
Рік |
|
Бойко |
15 |
лютий |
1991 |
Друга нормальна форма. Відношення знаходиться в 2НФ, якщо воно знаходиться в 1НФ і кожен його непервинний атрибут функціонально повно залежить від первинного ключа.
Неповною функціональною залежністю називається залежність неключового атрибуту від частини ключа, що складається з декількох атрибутів. Повна функціональна залежність передбачає залежність неключового атрибуту від всіх атрибутів одночасно, що входять до складу ключа.
Приклад. Розглянемо відношення Студент (табл. 7.6).
Таблиця 7.6. Студент
|
Номер залікової книжки |
Прізвище |
Група |
Дисципліна |
Оцінка |
|
1010 |
Бойко |
ІТП-31 |
Бази даних |
5 |
Функціональні залежності:
№ залік. кн., Дисципліна → Прізвище, Група, Оцінка
№ залік. кн. → Прізвище, Група
Для приведення даного відношення до 2НФ необхідно розбити його на проекції, при цьому повинна бути виконана умова відновлення вихідного відношення без втрат. Проекції мають такий вигляд (табл. 7.7...7.8).
Таблиця 7.7. Студент
|
Номер залікової книжки |
Дисципліна |
Оцінка |
|
1010 |
Бази даних |
5 |
Таблиця 7.8. Група
|
Номер залікової книжки |
Прізвище |
Група |
|
|
1010 |
Бойко |
ІТП-31 |
Відсутність втрат при декомпозиції відношення R(x,y,z) на відношення Rl(x,y) і R2(x,z) виконується, якщо від спільного атрибуту двох отриманих відношень (х) залежить хоча б один атрибут з двох, що залишилися (у або z), тобто якщо виконується (х -> у) або (х -> z).
Третя нормальна форма. Відношення знаходиться в 3НФ, якщо воно знаходиться в 2НФ і жоден з непервинних атрибутів у відношенні не є транзитивно залежним від первинного ключа.
Атрибут С транзитивно залежить від атрибуту А, якщо для атрибутів А, В, С виконуються такі умови А → В і В → С, але зворотня залежність відсутня.
Приклад. Розглянемо відношення Студент (табл. 7.9).
Таблиця 7.9. Студент
|
Номер залікової книжки |
Прізвище |
Група |
Факультет |
|
1010 |
Бойко |
ІТП-31 |
АІТ |
Функціональні залежності:
№ залік. кн. → Прізвище, Група, Факультет
Група → Факультет
Між атрибутами існує транзитивна залежність. Транзитивна залежність ‒ це залежність між неключовимн атрибутами. Для того щоби запобігти цьому необхідно виконати декомпозицію відношення (табл. 7.10, 7.11):
Таблиця 7.10. Студент
|
Номер залікової книжки |
Прізвище |
Група |
|
|
1010 |
Бойко |
ІТП-31 |
Таблиця 7.11. Група
|
Група |
Факультет |
|
ІТП-31 |
АІТ |
Нормальна форма Бойса-Кодда. Відношення знаходиться в НФБК, якщо воно знаходиться в 3НФ і у ньому відсутні залежності атрибутів первинного ключа від неключових атрибутів.
Приклад. Розглянемо відношення Спеціальність (табл. 7.12).
Таблиця 7.12. Спеціальність
|
Спеціальність |
Дисципліна |
Викладач |
|
ІТП |
Бази даних |
Барко |
|
ІУСТ |
Бази даних |
Шевченко |
Припустимо, що на кожній спеціальності певну дисципліну може викладати тільки один викладач і кожен викладач викладає тільки одну дисципліну. У цьому випадку мають місце такі залежності:
Спеціальність, Дисципліна → Викладач
Викладач → Дисципліна
Відношення знаходиться в 3НФ, але неключовий атрибут Викладач визначає атрибут Дисципліна, що входить у ключ.
Для того щоби позбутися аномалій необхідно виконати декомпозицію відношення (табл. 7.13, 7.14).
Таблиця 7.13.Спеціальність
|
Спеціальність |
Дисципліна |
|
ІТП |
Бази даних |
|
ІУСТ |
Бази даних |
Таблиця 7.14. Дисципліна
|
Викладач |
Дисципліна |
|
Барко |
Бази даних |
|
Шевченко |
Бази даних |
Четверта нормальна форма. Відношення знаходиться в 4НФ тоді і тільки тоді, коли у випадку існування багатозначної залежності А→→В всі інші атрибути відношення функціонально залежать від А.
У відношенні R(A,B,C) існує багатозначна залежність в тому і тільки в тому випадку, коли множина значень В, що відповідає парі значень А і С залежить тільки від А і не залежить від С.
Відношення R(A,B,C можна розбити без втрат інформації на відношення R1(A,B) і R2(A,C) в тому і тільки в тому випадку, якщо існують багатозначні залежності А→→В і А→→С.
Приклад. Розглянемо відношення Кафедра (табл.7.15).
Таблиця 7.15.Кафедра
|
Кафедра |
Викладач |
Група |
|
ІТ |
Барко |
ІТП-31 |
|
ІТ |
Барко |
ІТП-32 |
|
ІТ |
Шевченко |
ІТП-31 |
|
ІТ |
Шевченко |
ІТП-32 |
У даному відношенні існують дві багатозначні залежності:
Кафедра →→ Викладач
Кафедра →→ Група
Це означає, що кожній кафедрі відповідає перелік викладачів, які на ній працюють і кожній кафедрі відповідає перелік груп, яким ця кафедра викладає дисципліни.
Для того, щоби звести відношення до 4НФ, необхідно виконати його декомпозицію (табл. 7.16, 7.17).
Таблиця 7.16. Кафедра
|
Кафедра |
Викладач |
|
ІТ |
Барко |
|
ІТ |
Шевченко |
Таблиця 7.17. Група
|
Кафедра |
Група |
|
ІТ |
ІТП-31 |
|
ІТ |
ІТП-32 |
П'ята нормальна форма. Відношення знаходиться в 5НФ тоді і тільки тоді, коли будь-яка залежність з'єднання у відношенні виходить з існування деякого можливого ключа у відношенні.
Відношення R(X,Y,...,Z) задовольняє залежності з'єднання (Х,У,...,2) тоді і тільки тоді, коли R відновлюється без втрат інформації шляхом з'єднання своїх проекцій на X, У,., Z. Залежність з’єднання є узагальненням функціональної і багатозначної залежностей.
Приклад. Розглянемо відношення Заняття:
Заняття (Студент, Викладач, Дисципліна)
Кожен студент слухає лекції багатьох викладачів, кожен викладач викладає для багатьох студентів, кожен студент вивчає багато дисциплін, кожен викладач викладає багато дисциплін. У відношенні відсутні багатозначні і функціональні залежності й воно знаходиться в 4НФ. У відношенні можливі аномалії, які пов'язані з повтором значень атрибутів в декількох кортежах. Наприклад, якщо студент навчається у багатьох викладачів, то при його відрахуванні з університету необхідно знайти і вилучити декілька записів з відношення.
Утворимо такі складені атрибути відношення:
СВ (Студент, Викладач)
СД (Студент, Дисципліна)
ВД (Витадач, Дисципліна).
Якщо відношення R спроектувати на складені атрибути СВ, СД, ВД, то з'єднання цих проекцій дасть вихідне відношення. Це означає, що у відношенні Заняття існувала залежність з'єднання. Результатом декомпозиції відношення Заняття буде отримання таких відношень: R1(Студент, Викладач), R2(Cmyдeнm, Дисципліна), R3(Викладач, Дисципліна).
Для зведення вихідного відношення до 5НФ виконують його декомпозицію на відношення, кількість яких перевищує два.
Шоста нормальна форма (6NF) ‒ одна з можливих нормальних форм таблиці реляційної бази даних. Введена К. Дейтом як узагальнення п'ятої нормальної форми для хронологічної бази даних.
Змінна відношення знаходиться в шостій нормальній формі тоді і тільки тоді, коли вона задовольняє всім нетривіальним залежностям з’єднання. З визначення випливає, що змінна знаходиться в 6НФ тоді і тільки тоді, коли вона неприводима, тобто не може бути піддана подальшій декомпозиції без втрат. Кожна змінна відношення, яка знаходиться в 6НФ, також знаходиться і в 5НФ.
|
Таб. № |
Час |
Посада |
Домашня адреса |
|
6575 |
[01-01-2000:10-02-2003] |
Слюсар |
вул. Бандери, 10 |
|
6575 |
[11-02-2003:15-06-2006] |
Слюсар |
вул. Грушевського, 22 |
|
6575 |
[16-06-2006:05-03-2009] |
Бригадир |
вул. Грушевського, 22 |
Змінна відношення «Працівники» не знаходиться в 6НФ і може бути піддана декомпозиції на змінні відносини «Посади працівників» та «Домашні адреси працівників».
|
Таб. № |
Час |
Посада |
|
6575 |
[11-02-2003:15-06-2006] |
Слюсар |
|
6575 |
[16-06-2006:05-03-2009] |
Бригадир |
|
Таб. № |
Час |
Домашня адреса |
|
6575 |
[01-01-2000:10-02-2003] |
вул. Бандери, 10 |
|
6575 |
[16-06-2006:05-03-2009] |
вул. Грушевського, 22 |
Результати зведення до нормальних форм наведені в табл. 7.18.
Таблиця 7.18. Приклади формування нормальних форм
|
Нормальні форми |
Приклади |
|
1НФ |
R(ABCD) ‒ відношення A,B,C,D ‒ атомарні атрибути |
|
2НФ |
R(ABCD) ‒ відношення, АВ ‒ ключ, АВ → CD, неможливі залежності: А → CD, А → C, А → D, B → CD, B → C, B → D |
|
3НФ |
R(ABCD) ‒ відношення, АВ ‒ ключ, АВ → CD, неможливі залежності: С → D, D → C |
|
НФБК |
R(ABCD) ‒ відношення, АВ ‒ ключ, АВ → СD, неможливі залежності: C → A, C → B, D → A, D → B, C → AB, D → AB |
|
4НФ |
R(ABC) ‒ відношення, А →→ В, А → С, неможливі залежності: А →→ С |
|
5НФ |
R(ABC) ‒ вихідне відношення; результат декомпозиції: R1(AB), R2(AC), R3(BC)
|
Денормалізація ‒ модифікація реляційної моделі, при якій ступінь нормалізації модифікованого відношення стає нижче, ніж ступінь нормалізації щонайменше одного з вихідних відношень.
Денормалізація застосовується у тих випадках, коли нормалізована БД не задовольняє вимогам, що висуваються до продуктивності системи. Денормалізація може застосовуватися у таких випадках:
- об'єднання таблиць зі зв'язками «один до одного»;
- дублювання неключових атрибутів у зв'язках «один до багатьох» для зменшення кількості з'єднань;
- дублювання атрибутів зовнішнього ключа у зв'язках «один до багатьох» для зменшення кількості з'єднань;
- дублювання атрибутів «багато до багатьох» для зменшення кількості з'єднань;
- створення таблиць з даних, що містяться в інших таблицях;
- введення груп полів, що повторюються.
Застосовуючи денормалізацію слід враховувати, що цей процес має такі негативні наслідки:
- призводить до появи аномалій БД;
- знижує гнучкість системи;
- може зменшити час на відповіді до БД, але при цьому уповільнює операції оновлення даних;
- може ускладнити фізичну реалізацію системи.
Контрольні запитання
1. Дати визначення терміну функціональна залежність. Навести приклади функціональних залежностей.
2. У чому полягає збиткове і незбиткове дублювання даних?
3. Що таке аномалії додавання, оновлення, вилучення?
4. Дати визначення 2НФ. Навести приклад відношення, яке знаходиться в 1НФ, але не знаходиться у 2НФ. Звести його до 2НФ.
5. Дати визначення 3НФ. Навести приклад відношення, яке знаходиться в 2НФ, але не знаходиться у 3НФ. Звести його до 3НФ.
6. Дати визначення НФБК. Навести приклад відношення, яке знаходиться в 3НФ, але не знаходиться у НФБК. Звести його до НФБК.
7. Дати визначення терміну багатозначна залежність. Навести приклади багатозначних залежностей.
8. Дати визначення 4НФ. Навести приклад відношення, яке знаходиться в НФБК, але не знаходиться у 4НФ. Звести його до 4НФ.
9. Дати визначення 5НФ. Навести приклад 5НФ.
10. Яке місце займає нормалізація в процесі проектування бази даних?
11. Що таке денормалізація бази даних і які її переваги і недоліки?
12. Навести приклади проекту бази даних, коли доцільно виконати денормалізацію.