Нормалізація: перша та друга нормальні форми

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

Усю лекцію будемо послідовно вдосконалювати один реєстр запису студентів на курси.

Цілі лекції

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

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

Передумови

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

1. Початковий реєстр і проблема повторення

Навчальний центр спочатку веде один рядок на студента, а курси записує групами колонок:

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

Інший невдалий варіант - записати в одну клітинку DB; WEB, а в сусідню 91; 88. Такі списки важко надійно зіставляти, перевіряти й змінювати. Одна клітинка починає приховувати кілька значень.

2. Навіщо нормалізувати дані

Нормалізація - це систематичне впорядкування структури відношень на основі залежностей між атрибутами. Її практична мета:

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

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

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

3. Аномалії зміни даних

Щоб побачити проблему, спочатку подамо початковий реєстр у зручнішій формі: один курс студента в одному рядку.

Ключем рядка є пара (student_id, course_id): студент може проходити багато курсів, курс має багато студентів, але одна пара трапляється один раз.

Аномалія оновлення

Якщо курс DB перейменували на «Реляційні бази даних», треба знайти й змінити всі рядки цього курсу. Пропуск одного рядка створить дві різні назви одного курсу.

Аномалія вставки

Центр хоче додати курс TEST із назвою «Тестування ПЗ», але на нього ще ніхто не записався. У спільному реєстрі курс не можна зберегти без вигаданого студента або порожніх частин складеного ключа.

Аномалія видалення

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

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

4. Функціональні залежності й детермінанти

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

Ліва частина залежності називається детермінантом (визначником), бо вона визначає значення правої частини.

Для реєстру діють такі правила предметної області:

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

У першій залежності детермінант student_id: одному ідентифікатору студента відповідає одне актуальне ім'я. У другій детермінант course_id. У третій оцінку визначає вся пара: ані студент без курсу, ані курс без студента не визначає оцінку.

Залежність є твердженням про правило предметної області, а не випадковим спостереженням за кількома рядками. У показаних даних усі оцінки різні, але з цього не випливає grade → student_id: інший студент цілком може отримати 91.

Повна функціональна залежність

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

(student_id, course_id) → grade

Для визначення grade потрібні обидві частини. Це повна залежність.

Часткова функціональна залежність

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

(student_id, course_id) → student_name
student_id → student_name

Пара формально визначає ім'я, однак course_id для цього зайвий. Так само course_title залежить лише від course_id. Саме часткові залежності пояснюють повторення імен і назв у реєстрі.

5. Перша нормальна форма

Відношення перебуває у першій нормальній формі (1НФ), якщо:

• кожна клітинка містить одне значення з відповідного домену;
• немає повторюваних груп колонок;
• кожен рядок можна однозначно ідентифікувати ключем.

Атомарність залежить від задачі. Повне ім'я Олена Коваль може бути одним значенням, якщо система лише показує його. Якщо потрібно окремо сортувати за прізвищем і звертатися на ім'я, доцільні окремі атрибути. Натомість список ідентифікаторів DB; WEB не є одним атомарним значенням для реєстру курсів, бо система працює з кожним записом окремо.

Перетворимо повторювані course_1, course_2 на окремі рядки:

Enrollment_1NF(student_id, student_name, course_id, course_title, grade)
PRIMARY KEY (student_id, course_id)

Отримана раніше таблиця з трьома рядками відповідає 1НФ. Тепер кількість курсів не потребує нових колонок, кожна оцінка належить одній парі студент-курс, а всі значення атомарні.

Проте 1НФ не усунула дублювання імен і назв та пов'язані аномалії. Нормальні форми утворюють послідовність вимог: відповідність 1НФ є лише першим кроком.

6. Друга нормальна форма

Відношення перебуває у другій нормальній формі (2НФ), якщо:

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

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

У Enrollment_1NF ключ (student_id, course_id) складений. Перевіримо неключові атрибути:

Щоб усунути часткові залежності, винесемо факти про студента й курс у власні відношення:

Student(student_id, student_name)
PRIMARY KEY (student_id)

Course(course_id, course_title)
PRIMARY KEY (course_id)

Enrollment(student_id, course_id, grade)
PRIMARY KEY (student_id, course_id)
FOREIGN KEY student_id REFERENCES Student
FOREIGN KEY course_id REFERENCES Course

Дані після розкладання:

Student

Course

Enrollment

У Student ім'я повністю залежить від простого ключа student_id. У Course назва залежить від course_id. У Enrollment оцінка повністю залежить від складеного ключа. Усі три відношення відповідають 2НФ за вказаними правилами предметної області.

Тепер назву курсу змінюють в одному рядку Course; новий курс можна додати без реєстрації студента; видалення останньої реєстрації не видаляє сам курс. Зовнішні ключі дають змогу відновити зв'язки між фактами.

Runnable-приклад для PostgreSQL

Наступний код показує остаточну структуру. Синтаксис DDL докладно вивчатимемо пізніше; зараз зверніть увагу на ключі та розподіл атрибутів.

CREATE TABLE student (
    student_id integer PRIMARY KEY,
    student_name text NOT NULL
);

CREATE TABLE course (
    course_id text PRIMARY KEY,
    course_title text NOT NULL
);

CREATE TABLE enrollment (
    student_id integer REFERENCES student(student_id),
    course_id text REFERENCES course(course_id),
    grade integer,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO student VALUES
    (101, 'Олена Коваль'),
    (102, 'Максим Бойко');

INSERT INTO course VALUES
    ('DB', 'Бази даних'),
    ('WEB', 'Веброзробка');

INSERT INTO enrollment VALUES
    (101, 'DB', 91),
    (101, 'WEB', 88),
    (102, 'DB', 76);

SELECT s.student_name, c.course_title, e.grade
FROM enrollment AS e
JOIN student AS s ON s.student_id = e.student_id
JOIN course AS c ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;

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

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

Завдання 1. Знайдіть аномалії

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

Завдання 2. Побудуйте карту залежностей

Для ключа (student_id, course_id) класифікуйте залежності атрибутів student_name, course_title, grade як повні або часткові. Запишіть мінімальний детермінант кожного атрибута стрілковою нотацією та поясніть, чому зайва частина ключа не потрібна або потрібна.

Завдання 3. Нормалізуйте нові рядки

До початкового реєстру надійшли дані:

1. Додайте ці факти до таблиць Student, Course, Enrollment у 2НФ, не створюючи дублів.
2. Запишіть отримані нові рядки кожної таблиці.
3. Поясніть, як ключі не дозволяють повторно додати того самого студента, курс або ту саму пару студент-курс.

Підсумок

• Нормалізація впорядковує відношення відповідно до залежностей між фактами.
• Надлишкове зберігання одного факту породжує аномалії вставки, оновлення й видалення.
• У залежності X → Y значення X визначає Y, а X є детермінантом.
• Повна залежність потребує всього складеного детермінанта; частковій достатньо його частини.
• 1НФ вимагає атомарних значень, відсутності повторюваних груп та ідентифікованих рядків.
• 2НФ вимагає 1НФ і відсутності часткових залежностей неключових атрибутів від складеного кандидатного ключа.
• У прикладі розклад на Student, Course, Enrollment зберіг факти та усунув часткові залежності.
• У наступній лекції розглянемо інші види залежностей і наступні нормальні форми; тут їхні правила не застосовувалися.