Пошук уроків, статей та іншого контенту
Виявляйте та усувайте транзитивні залежності для зменшення дублювання в таблицях.
Третя нормальна форма (3NF) — це правило проєктування реляційних таблиць, яке усуває транзитивні залежності між стовпцями.
Таблиця перебуває в 3NF, якщо:
вона вже відповідає першій нормальній формі;
вона вже відповідає другій нормальній формі;
неключові атрибути не залежать від інших неключових атрибутів.
Іншими словами, кожен неключовий стовпець має залежати:
від ключа;
від усього ключа;
і ні від чого, крім ключа.
Це правило часто коротко формулюють так:
Кожен факт має зберігатися в таблиці, ключ якої безпосередньо визначає цей факт.
Розглянемо таблицю працівників:
employees
----------------------------------------------------------------
employee_id | employee_name | department_id | department_name
----------------------------------------------------------------
1 | Олена | 10 | Розробка
2 | Андрій | 10 | Розробка
3 | Марія | 20 | МаркетингПервинний ключ таблиці — employee_id.
Маємо такі залежності:
employee_id -> department_id
department_id -> department_nameОтже, існує непряма залежність:
employee_id -> department_namedepartment_name залежить не безпосередньо від працівника, а від department_id.
Це і є транзитивна залежність:
employee_id -> department_id -> department_namedepartment_name є неключовим атрибутом, який залежить від іншого неключового атрибута department_id. Тому таблиця не відповідає 3NF.
Назва відділу повторюється для кожного працівника цього відділу.
Якщо у відділі працює 100 людей, назву відділу буде збережено 100 разів.
Якщо відділ Розробка перейменували на Інженерія, потрібно оновити всі рядки працівників цього відділу.
Якщо один рядок залишиться без змін, у базі з'являться суперечливі дані.
Неможливо додати новий відділ, якщо в ньому ще немає працівників, оскільки таблиця призначена для зберігання працівників.
Якщо видалити останнього працівника певного відділу, можна випадково втратити й інформацію про сам відділ.
Потрібно розділити незалежні сутності на окремі таблиці:
departments зберігатиме відділи;
employees зберігатиме працівників;
employees.department_id буде зовнішнім ключем на departments.department_id.
Після цього структура матиме такий вигляд:
departments
----------------------------
department_id | department_name
----------------------------
10 | Розробка
20 | Маркетинг
employees
-----------------------------------------
employee_id | employee_name | department_id
-----------------------------------------
1 | Олена | 10
2 | Андрій | 10
3 | Марія | 20Тепер:
employee_id -> department_id
department_id -> department_nameКожна таблиця зберігає факти лише про власну сутність:
працівник безпосередньо визначає свої дані;
відділ безпосередньо визначає свою назву.
Нижче наведено повний приклад, який можна виконати в PostgreSQL.
-- Видаляємо таблиці, якщо вони вже існують.
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
-- Створюємо таблицю відділів.
CREATE TABLE departments (
department_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
department_name varchar(100) NOT NULL UNIQUE
);
-- Створюємо таблицю працівників.
CREATE TABLE employees (
employee_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_name varchar(100) NOT NULL,
department_id integer NOT NULL,
CONSTRAINT employees_department_fk
FOREIGN KEY (department_id)
REFERENCES departments (department_id)
);
-- Додаємо відділи.
INSERT INTO departments (department_name)
VALUES
('Розробка'),
('Маркетинг');
-- Додаємо працівників і пов'язуємо їх із відділами.
INSERT INTO employees (employee_name, department_id)
VALUES
('Олена', 1),
('Андрій', 1),
('Марія', 2);
-- Отримуємо працівників разом із назвами їхніх відділів.
SELECT
e.employee_id,
e.employee_name,
d.department_name
FROM employees AS e
JOIN departments AS d
ON d.department_id = e.department_id
ORDER BY e.employee_id;Результат запиту:
employee_id | employee_name | department_name
-------------+---------------+-----------------
1 | Олена | Розробка
2 | Андрій | Розробка
3 | Марія | МаркетингНазва відділу більше не дублюється в кожному рядку працівника. Вона зберігається один раз у departments, а потрібне представлення формується за допомогою JOIN.
Зовнішній ключ гарантує, що працівник посилається на існуючий відділ:
INSERT INTO employees (employee_name, department_id)
VALUES ('Ігор', 999);Такий запит завершиться помилкою, оскільки відділу з ідентифікатором 999 немає.
Це захищає цілісність даних. У таблиці employees не з'явиться посилання на неіснуючий відділ.
Також зовнішній ключ не дозволить видалити відділ, на який посилаються працівники:
DELETE FROM departments
WHERE department_id = 1;За замовчуванням PostgreSQL відхилить таке видалення, доки існують працівники з department_id = 1.
Під час аналізу таблиці поставте такі запитання:
Який стовпець є первинним ключем?
Які стовпці описують сутність, визначену цим ключем?
Чи є неключовий стовпець, який визначає інший неключовий стовпець?
Чи можна винести частину атрибутів в окрему таблицю?
Наприклад:
order_id -> customer_id
customer_id -> customer_emailЯкщо в таблиці замовлень зберігаються і customer_id, і customer_email, то електронна адреса клієнта залежить від клієнта, а не безпосередньо від замовлення.
У такому випадку клієнтів варто винести в окрему таблицю:
customers
-----------------------------
customer_id | customer_email
orders
-----------------------------
order_id | customer_idОднак важливо перевірити бізнес-правила. Якщо електронна адреса на момент замовлення повинна зберігатися як історичне значення, її збереження в замовленні може бути навмисним. Нормалізація має враховувати призначення даних, а не виконуватися механічно.
Для перегляду структури таблиць у psql можна використати:
\d departments
\d employeesДля перегляду обмежень конкретної таблиці:
SELECT
constraint_name,
constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';У таблиці employees мають бути:
первинний ключ employee_id;
зовнішній ключ employees_department_fk;
обов'язкові поля з NOT NULL.
Якщо таблиця має такі залежності:
A -> B
B -> Cі A є ключем, то потрібно перевірити, чи не зберігаються B і C в одній таблиці без необхідності.
Зазвичай декомпозиція виглядає так:
Таблиця 1:
A -> B
Таблиця 2:
B -> CУ базі даних зв'язок між таблицями реалізується зовнішнім ключем:
CREATE TABLE first_table (
a integer PRIMARY KEY,
b integer NOT NULL
);
CREATE TABLE second_table (
b integer PRIMARY KEY,
c varchar(100) NOT NULL
);
ALTER TABLE first_table
ADD CONSTRAINT first_table_b_fk
FOREIGN KEY (b)
REFERENCES second_table (b);На практиці назви таблиць і стовпців мають описувати предметну область. У прикладі з працівниками краще використовувати departments, а не абстрактні first_table і second_table.
3NF усуває транзитивні залежності, але не вирішує всі можливі проблеми структури даних.
Наприклад, окрема таблиця може формально відповідати 3NF, але містити надмірну кількість зв'язків або складні залежності. Для деяких схем застосовують нормальну форму Бойса — Кодда або інші підходи.
У межах 3NF важливо зосередитися на основному правилі:
Якщо атрибут описує іншу сутність, його слід зберігати в таблиці цієї сутності.
Погано:
employees
------------------------------------------------
employee_id | employee_name | department_id | department_nameКраще:
departments
----------------------------
department_id | department_name
employees
-----------------------------------------
employee_id | employee_name | department_idСамого стовпця department_id недостатньо. Без зовнішнього ключа PostgreSQL не перевірятиме, чи існує відповідний відділ.
Якщо назва відділу є в departments, не потрібно додатково зберігати її в employees, якщо це не має спеціального історичного або бізнесового призначення.
2NF усуває залежність від частини складеного ключа.
3NF усуває залежність неключового атрибута від іншого неключового атрибута.
Якщо таблиця має простий первинний ключ, проблема транзитивної залежності все одно може існувати.
Після нормалізації дані пов'язані зовнішніми ключами. Перед видаленням батьківського запису потрібно врахувати записи, які на нього посилаються.
3NF усуває транзитивні залежності.
Транзитивна залежність має вигляд A -> B -> C.
Неключовий атрибут не повинен визначати інший неключовий атрибут у тій самій таблиці.
Дані про різні сутності потрібно зберігати в окремих таблицях.
Для зв'язку таблиць у PostgreSQL використовують зовнішні ключі.
Нормалізація зменшує дублювання та запобігає аномаліям вставки, оновлення й видалення.
Дані з нормалізованих таблиць об'єднують за допомогою JOIN.