Пошук уроків, статей та іншого контенту
Усуньте часткові залежності атрибутів від складеного первинного ключа.
Друга нормальна форма (2NF) — це вимога до структури таблиці, яка усуває часткові залежності неключових атрибутів від складеного первинного ключа.
Таблиця перебуває у 2NF, якщо:
вона вже відповідає першій нормальній формі;
кожен неключовий атрибут залежить від усього складеного первинного ключа, а не лише від його частини.
Друга нормальна форма має значення лише для таблиць, у яких ключ складається з кількох атрибутів. Якщо первинний ключ простий, часткової залежності від його частини бути не може.
Складений первинний ключ містить два або більше стовпців.
Наприклад, у таблиці записів студентів на курси унікальний запис можна визначити парою:
student_id;
course_id.
Один студент може записатися на багато курсів, і один курс може мати багато студентів. Тому окремо student_id або course_id не є унікальними, але їхня комбінація — унікальна.
PRIMARY KEY (student_id, course_id)Це і є складений первинний ключ.
Розглянемо таблицю enrollments:
enrollments
-----------
student_id
course_id
student_name
course_name
gradeПервинний ключ:
(student_id, course_id)Залежності між атрибутами:
student_id → student_name
course_id → course_name
(student_id, course_id) → gradestudent_name залежить лише від student_id, а не від усієї пари (student_id, course_id).
Так само course_name залежить лише від course_id.
Це і є часткові залежності:
частина ключа визначає student_name;
інша частина ключа визначає course_name.
Таблиця не відповідає 2NF.
Водночас grade залежить від усієї пари (student_id, course_id), адже оцінка належить конкретному студенту на конкретному курсі.
CREATE TABLE enrollments_bad (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
student_name TEXT NOT NULL,
course_name TEXT NOT NULL,
grade NUMERIC(5, 2),
PRIMARY KEY (student_id, course_id)
);
INSERT INTO enrollments_bad (
student_id,
course_id,
student_name,
course_name,
grade
)
VALUES
(1, 101, 'Олена Коваль', 'PostgreSQL', 95),
(1, 102, 'Олена Коваль', 'JavaScript', 88),
(2, 101, 'Андрій Мельник', 'PostgreSQL', 91);У цій таблиці значення повторюються:
ім’я Олени зберігається в кожному її записі;
назва курсу PostgreSQL зберігається в записі кожного студента цього курсу.
Не можна додати новий курс, якщо на нього ще не записаний жоден студент. Для цього немає окремого рядка, у якому можна зберегти курс.
Якщо курс PostgreSQL перейменують, потрібно змінити course_name у всіх відповідних рядках. Якщо пропустити хоча б один рядок, дані стануть суперечливими.
Якщо видалити останній запис студента на курс JavaScript, разом із ним буде втрачено інформацію про сам курс.
Щоб усунути часткові залежності, потрібно винести атрибути в таблиці, яким вони належать:
дані студента — у students;
дані курсу — у courses;
факт запису на курс та оцінку — у enrollments.
DROP TABLE IF EXISTS enrollments_bad;
CREATE TABLE students (
student_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_name TEXT NOT NULL
);
CREATE TABLE courses (
course_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
course_name TEXT NOT NULL UNIQUE
);
CREATE TABLE enrollments (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
grade NUMERIC(5, 2),
PRIMARY KEY (student_id, course_id),
CONSTRAINT enrollments_student_fk
FOREIGN KEY (student_id)
REFERENCES students (student_id),
CONSTRAINT enrollments_course_fk
FOREIGN KEY (course_id)
REFERENCES courses (course_id),
CONSTRAINT enrollments_grade_check
CHECK (grade IS NULL OR grade BETWEEN 0 AND 100)
);
INSERT INTO students (student_name)
VALUES
('Олена Коваль'),
('Андрій Мельник');
INSERT INTO courses (course_name)
VALUES
('PostgreSQL'),
('JavaScript');
INSERT INTO enrollments (student_id, course_id, grade)
VALUES
(1, 1, 95),
(1, 2, 88),
(2, 1, 91);Тепер залежності розподілені правильно:
students:
student_id → student_name
courses:
course_id → course_name
enrollments:
(student_id, course_id) → gradeУ таблиці enrollments жоден неключовий атрибут не залежить лише від student_id або лише від course_id. Оцінка залежить від усієї пари.
Нормалізація не означає втрату можливості отримати попереднє представлення даних. Для цього використовують JOIN.
SELECT
s.student_id,
s.student_name,
c.course_id,
c.course_name,
e.grade
FROM enrollments AS e
JOIN students AS s
ON s.student_id = e.student_id
JOIN courses AS c
ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;Результат міститиме:
student_id | student_name | course_id | course_name | grade
-----------+-----------------+-----------+-------------+------
1 | Олена Коваль | 1 | PostgreSQL | 95.00
1 | Олена Коваль | 2 | JavaScript | 88.00
2 | Андрій Мельник | 1 | PostgreSQL | 91.00Під час аналізу таблиці виконайте такі кроки:
Визначте первинний ключ.
З’ясуйте, чи є він складеним.
Випишіть функціональні залежності між атрибутами.
Для кожного неключового атрибута перевірте, від чого він залежить.
Якщо атрибут залежить лише від частини складеного ключа, винесіть його в окрему таблицю.
Залиште в таблиці зв’язку лише атрибути, що описують повну комбінацію ключів.
Наприклад, для таблиці order_items:
(order_id, product_id) → quantity
product_id → product_name
order_id → order_dateproduct_name залежить лише від product_id, а order_date — лише від order_id. Отже, вони не повинні зберігатися в таблиці позицій замовлення.
Коректний поділ:
orders(order_id, order_date);
products(product_id, product_name);
order_items(order_id, product_id, quantity).
Розглянемо таблицю з простим ключем:
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);У таблиці немає частини первинного ключа: ключ складається лише з product_id.
Тому часткова залежність неможлива. Якщо таблиця відповідає 1NF, то з погляду часткових залежностей вона автоматично відповідає 2NF.
Однак це не означає, що таблиця обов’язково відповідає всім наступним нормальним формам. 2NF перевіряє лише залежності від частини складеного ключа.
Повторення значень саме по собі не є формальним визначенням порушення 2NF. Потрібно перевірити залежність атрибута від частини складеного ключа.
Проте дублювання часто є ознакою такої залежності та створює аномалії вставки, оновлення й видалення.
Неможливо оцінити часткову залежність, не знаючи первинного або кандидатного ключа.
Спочатку потрібно визначити, які атрибути разом однозначно ідентифікують рядок.
У таблиці зв’язку мають зберігатися дані про сам зв’язок. Наприклад, grade належить зв’язку між студентом і курсом.
А student_name належить студенту, а course_name — курсу. Їх слід зберігати у відповідних таблицях.
Наприклад:
enrollment_id — первинний ключ
student_id
course_id
student_name
course_name
gradeФормально всі інші атрибути можуть залежати від enrollment_id, але це не усуває реальних залежностей:
student_id → student_name
course_id → course_nameЯкщо student_id і course_id разом мають бути унікальними, це потрібно явно гарантувати обмеженням:
UNIQUE (student_id, course_id)Вибір сурогатного ключа не скасовує аналізу предметної області та функціональних залежностей.
2NF усуває залежності від частини складеного ключа.
3NF розглядає інший тип проблем — транзитивні залежності між неключовими атрибутами.
Наприклад, якщо:
student_id → department_id
department_id → department_nameто це вже питання транзитивної залежності, а не часткової залежності від складеного ключа.
2NF застосовується до таблиць, які вже відповідають 1NF.
Основний об’єкт перевірки — складений первинний або кандидатний ключ.
Часткова залежність виникає, коли неключовий атрибут залежить лише від частини складеного ключа.
Атрибути, що описують окремі сутності, потрібно винести в окремі таблиці.
Таблиця зв’язку має зберігати атрибути, які залежать від усієї комбінації ключів.
Для об’єднання нормалізованих даних використовують JOIN.
Таблиця з простим первинним ключем не має часткових залежностей від ключа, тому з цього аспекту автоматично відповідає 2NF.