Нормалізація баз даних
Нормалізація - це процес упорядкування даних у базі даних таким чином, щоб вона була вільна від надлишковості та залежності. Це допомагає усунути невідповідності та аномалії даних, тим самим покращуючи цілісність даних. Нормалізація - це набір правил або вказівки щодо розробки схеми бази даних таким чином, щоб уникнути дублювання даних, надмірності даних і неузгодженості даних.
Нормалізація зменшує надлишковість даних і залежність, роблячи базу даних більш ефективною, гнучкою та масштабованою. Це також допомагає підтримувати узгодженість і точність даних, а також гарантує належну обробку оновлень і видалень.
Існує 6 + 3 нормальних форми, але у цій главі ми розглянемо лише перші 3, оскільки найпоширенішою нормальною формою, яку ви побачите, є 3НФ (третя нормальна форма).
Нормалізовані таблиці:
Легше зрозуміти
Легше вдосконалювати та розширювати
Захищені від:
аномалій вставки
аномалій оновлення
аномалій видалення
Аномалії
Аномалії вставки
Існують обставини, за яких певні факти взагалі не можуть бути записані. Наприклад, кожен запис у відношенні «Викладачі та їхні курси» може містити ідентифікатор викладача, ім’я викладача та код курсу. Таким чином, можна записати інформацію про будь-якого викладача, який викладає хоча б один курс, але не можна записати інформацію про новоприйнятого викладача, якому ще не було призначено викладання жодного курсу, окрім як встановивши код курсу на нуль.
Faculty ID |
Faculty Name |
Course Code |
|---|---|---|
389 |
Dr. Headstrong |
ENG-206 |
407 |
Dr. Mugwort |
CMP-101 |
407 |
Dr. Mugwort |
CMP-201 |
Faculty ID |
Faculty Name |
Course Code |
|---|---|---|
389 |
Dr. Newone |
??? |
Анамолії оновлення
Одна й та сама інформація може бути виражена в декількох рядках, тому оновлення відношення може призвести до логічної неузгодженості. Наприклад, кожен запис у відношенні «Навички працівників» може містити ах Ідентифікатор працівника, Адреса працівника та Навичка; таким чином, може виникнути потреба в оновленні адреси для певного працівника в декількох записах (по одному для кожної навички). Якщо оновлення відбувається лише частково - адреса працівника оновлюється в одних записах, але не в інших - то відношення залишається в неузгодженому стані. Зокрема, відношення надає суперечливі відповіді на питання про те, якою є адреса конкретного працівника.
Employee ID |
Employee Address |
Skill |
|---|---|---|
426 |
230 Parkland Crescent |
typing |
426 |
230 Parkland Crescent |
shorthand |
529 |
158 Watkins Place |
public speaking |
529 |
108 Church Street |
carpentry |
Аномалії видалення
За певних обставин видалення даних, що представляють певні факти, призводить до видалення даних, що представляють зовсім інші факти. Відношення «Викладачі та їхні курси», описане в попередньому прикладі, страждає від цього типу аномалії, тому що якщо викладач тимчасово перестає бути призначеним на будь-який курс, останній запис, в якому він з’являється, має бути видалений, фактично видаляючи і самого викладача, якщо тільки поле Код курсу не встановлено в нуль.
Перша нормальна форма (1NF)
Приклад даних
У таблиці нижче представлені гравці та інвентар їхніх персонажів, впорядковані за рівнями персонажів.
username |
inventory |
|---|---|
prombery87 |
2 амулети, 3 каблучки |
wheed1997 |
18 мідних монет, 1 лук, 5 стріл |
acen1999 |
1 меч, 1 щит, 4 персні |
Вимоги
Порядок рядків не повинен використовуватися для передачі інформації.
Типи даних не повинні змішуватися в одному стовпчику (і СУБД не дозволить вам цього зробити в будь-якому випадку).
Таблиці повинні мати первинні ключі.
Групи, що повторюються, мають бути виключені.
Застосування 1NF
Перш за все, використання порядку рядків для представлення інформації порушує 1NF. Це можна легко виправити, додавши додатковий стовпець level для зберігання інформації про рівень персонажа. Звідси впорядковані послідовності можуть бути отримані за допомогою запиту:
SELECT username FROM player ORDER BY level;
Друге правило - не змішувати типи даних в одному стовпчику. Наприклад, рівень символів можна описати наступним чином:
username |
inventory |
level |
|---|---|---|
prombery87 |
2 амулети, 3 каблучки |
9 |
wheed1997 |
18 мідних монет, 1 лук, 5 стріл |
almost 8 |
acen1999 |
1 меч, 1 щит, 4 персні |
5.5 |
У стовпчику level змішано типи даних, що порушує 1NF; і зазвичай СУБД не дозволяє цього робити.
Таблиці без первинного ключа також порушують 1NF. поле username можна використовувати як PK для прикладу відношення гравців. Це запобігатиме дублюванню даних, наприклад, наявності обох гравців wheed1997 на 7 і 8 рівнях одночасно, що не має сенсу.
username |
inventory |
level |
|---|---|---|
prombery87 |
2 амулети, 3 каблучки |
9 |
wheed1997 |
18 мідних монет, 1 лук, 5 стріл |
8 |
wheed1997 |
18 мідних монет, 1 лук, 5 стріл |
7 |
acen1999 |
1 меч, 1 щит, 4 персні |
5 |
Нарешті, стовпчик інвентар містить групи даних, що повторюються (також звані неатомними даними), що порушує 1НФ. Щоб нормалізувати таблицю, її слід змінити таким чином, щоб у кожному рядку було представлено лише одну інвентарну одиницю.
player_id |
item_type |
item_quantity |
|---|---|---|
prombery87 |
амулет |
2 |
prombery87 |
каблучка |
3 |
wheed1997 |
мідна монета |
18 |
wheed1997 |
лук |
1 |
wheed1997 |
стріла |
5 |
acen1999 |
меч |
1 |
acen1999 |
щит |
1 |
acen1999 |
каблучка |
4 |
Первинним ключем для цієї таблиці є комбінація player_id та item_type - складений первинний ключ.
Друга нормальна форма (2NF)
Тут починається 2NF. Наприклад, давайте повернемо до таблиці інформацію про рівень, що представляє гравців та їхній інвентар.
player_id |
item_type |
item_quantity |
player_level |
|---|---|---|---|
prombery87 |
амулет |
2 |
9 |
prombery87 |
каблучка |
3 |
9 |
wheed1997 |
мідна монета |
18 |
6 |
wheed1997 |
лук |
1 |
6 |
wheed1997 |
стріла |
5 |
6 |
acen1999 |
меч |
1 |
3 |
acen1999 |
щит |
1 |
3 |
acen1999 |
каблучка |
4 |
3 |
Рівень гравця повинен дублюватися в кожному рядку. Такий дизайн таблиці залишає двері відкритими для вставки, оновлення та видалення аномалій.
Вимоги
Кожен неключовий атрибут повинен залежати від усього первинного ключа
Застосування 2NF
2NF - це все про те, «як неключові стовпці пов’язані з первинним ключем». У наведеному вище прикладі неключові атрибути (що є іншим терміном для «неключових стовпців») є: item_quantity та player_level.
Відношення між первинним ключем та неключовими атрибутами можна описати так:
{ player_id, item_type } -> { item_quantity }
{ player_id } -> { player_level }
Стовпець item_quantity залежить від стовпців player_id і item_type, що створює залежність 1 до 1 для цього стовпця від усього первинного ключа. Це означає, що не існує способу розмістити різні значення у стовпці item_quantity для одного первинного ключа.
Але стовпець player_level залежить тільки від player_id. Це проблема, тому що player_id не є первинним ключем, але є частиною первинного ключа. Це порушує 2НФ, і саме тому таблиця вразлива до проблем.
Важливим поняттям для представлення даних є гравець. Тому найкращим рішенням буде зберігати дані, пов’язані з гравцями, в окремій таблиці.
player_id |
player_level |
|---|---|
prombery87 |
9 |
wheed1997 |
6 |
acen1999 |
3 |
player_id |
item_type |
item_quantity |
|---|---|---|
prombery87 |
амулет |
2 |
prombery87 |
каблучка |
3 |
wheed1997 |
мідна монета |
18 |
wheed1997 |
лук |
1 |
wheed1997 |
стріла |
5 |
acen1999 |
меч |
1 |
acen1999 |
щит |
1 |
acen1999 |
каблучка |
4 |
3-тя нормальна форма (3NF)
Припустимо, таблиця гравців доповнена стовпчиком рейтинг гравця, який описує навички гравців як «початківець», «середній» або «просунутий». І припустимо, що навички впорядковані за типом «like»:
Skill Level |
Beginner |
Intermediate |
Advanced |
||||||
Player Level |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
player_id |
player_level |
player_rating |
|---|---|---|
prombery87 |
9 |
advanced |
wheed1997 |
6 |
intermediate |
acen1999 |
3 |
beginner |
Залежності неключових атрибутів можна описати наступним чином:
{ player_id } -> { player_level }
{ player_id } -> { player_level } -> { player_skill }
Атрибут рейтинг_гравця не залежить від первинного ключа, але залежить від атрибута рівень_гравця, який залежить від первинного ключа. Цей тип залежностей називається транзитивною залежністю. Наявність таких залежностей у таблиці відкриває шлях до отримання даних у непослідовній формі. Наприклад, припустимо, що гравець «acen1999» підвищив свій рівень до 4, але щось пішло не так, і рейтинг_гравця не було оновлено.
acen1999 |
4 |
beginner |
Вимоги
Кожен неключовий атрибут у таблиці повинен залежати від ключа, всього ключа і ні від чого, крім ключа (без транзитивної залежності).
Примітка
Нормальна форма Бойса-Кодда
BCNF - це сильніша версія 3NF. Її також називають 3.5NF (третя з половиною нормальна форма).
Кожен атрибут у таблиці повинен залежати від ключа, всього ключа і ні від чого, крім ключа.
Застосування 3NF
Використання таблиці «player_rating_levels» вирішить цю проблему.
player_id |
player_level |
|---|---|
prombery87 |
9 |
wheed1997 |
6 |
acen1999 |
3 |
player_level |
player_rating |
|---|---|
9 |
advanced |
8 |
advanced |
7 |
advanced |
6 |
intermediate |
5 |
intermediate |
4 |
intermediate |
3 |
beginner |
2 |
beginner |
1 |
beginner |
Висновок
Кожна наступна нормальна форма включає всі вимоги до попередньої.
Дотримуючись наведених вище правил, ви отримаєте повністю нормалізовані таблиці у 99% випадків.