Что такое третья нормальная форма базы данных?

Что такое третья нормальная форма базы данных (3NF)

Третья нормальная форма (3NF) — это уровень нормализации схемы базы данных, при котором устраняются аномалии обновления, удаления и вставки, вызванные функциональными зависимостями от неключевых атрибутов. Это один из стандартов для проектирования хорошей схемы БД.

История нормализации

Нормализация — это процесс организации данных в базе данных для:

• Минимизации избыточности (дублирования) данных
• Улучшения целостности данных
• Облегчения обслуживания
• Повышения производительности

Уровни нормализации от худшего к лучшему:

1. Ненормализованная форма (UNF) — полное дублирование
2. Первая нормальная форма (1NF) — атомарные значения
3. Вторая нормальная форма (2NF) — нет частичных зависимостей
4. Третья нормальная форма (3NF) — нет транзитивных зависимостей
5. НФБК (BCNF) — более строгий вариант 3NF

Принципы третьей нормальной формы

Для соответствия 3NF таблица должна:

1. Находиться во 2NF (первое требование)
2. Не иметь транзитивных зависимостей — неключевой атрибут не должен зависеть от другого неключевого атрибута

Пример проблемы: БЕЗ 3NF

-- ПЛОХО: Не соответствует 3NF
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100),  -- Зависит от department_id, не от employee_id!
    department_location VARCHAR(100) -- Также зависит от department_id
);

Проблемы:

1. Аномалия обновления: если изменить имя отдела, нужно обновить все строки сотрудников

   -- Нужно обновить 50 строк!
   UPDATE Employees SET department_name = 'Engineering' 
   WHERE department_id = 1;
   

2. Аномалия удаления: удаление последнего сотрудника отдела теряет информацию об отделе

   -- Удаляем сотрудника — и теряем информацию об отделе!
   DELETE FROM Employees WHERE employee_id = 999;
   

3. Аномалия вставки: нельзя добавить новый отдел без сотрудника

   -- Нельзя вставить новый отдел!
   -- employee_id PRIMARY KEY не может быть NULL
   INSERT INTO Employees (name, department_id, department_name) 
   VALUES (NULL, 5, 'Marketing');
   

Решение: приведение к 3NF

-- ХОРОШО: Соответствует 3NF
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

Преимущества:

1. Одна таблица для данных отдела
2. Обновление отдела происходит в одном месте
3. Можно добавить отдел без сотрудников
4. Удаление сотрудника не теряет информацию об отделе

Более сложный пример

-- ПЛОХО: Нарушает 3NF
CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    professor_id INT,
    professor_name VARCHAR(100),  -- Зависит от professor_id, не от ключа!
    professor_email VARCHAR(100), -- Зависит от professor_id
    course_name VARCHAR(100),
    credits INT,
    PRIMARY KEY (student_id, course_id)
);

Повторяющиеся данные профессора и курса для каждого студента!

-- ХОРОШО: Соответствует 3NF
CREATE TABLE Professors (
    professor_id INT PRIMARY KEY,
    professor_name VARCHAR(100),
    professor_email VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    credits INT,
    professor_id INT,
    FOREIGN KEY (professor_id) REFERENCES Professors(professor_id)
);

CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Практический пример: E-commerce

-- ПЛОХО: Без нормализации
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    customer_city VARCHAR(100),
    customer_country VARCHAR(100),
    product_id INT,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    product_category VARCHAR(50),
    supplier_name VARCHAR(100),
    supplier_phone VARCHAR(20),
    order_date DATE,
    quantity INT,
    total_amount DECIMAL(10,2)
);

Проблемы:

• Данные клиента повторяются для каждого заказа
• Данные продукта повторяются для каждого заказа
• Данные поставщика повторяются для каждого продукта

-- ХОРОШО: Нормализовано до 3NF
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    city VARCHAR(100),
    country VARCHAR(100)
);

CREATE TABLE Suppliers (
    supplier_id INT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    country VARCHAR(100)
);

CREATE TABLE ProductCategories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category_id INT,
    supplier_id INT,
    FOREIGN KEY (category_id) REFERENCES ProductCategories(category_id),
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE OrderItems (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Проверка 3NF

У каждой неключевой колонки должна быть зависимость только от полного ключа и ничего больше:

# Проверка 3NF
def check_3nf(table):
    for column in table.non_key_columns:
        # Колонка должна зависеть ОТ всего ключа
        depends_on = get_dependencies(column)
        
        # И НЕ должна зависеть от других неключевых колонок
        for other in table.non_key_columns:
            if other != column and depends_on(other):
                return False  # Нарушение 3NF!
    
    return True

3NF vs BCNF

3NF позволяет:

• Некоторые аномалии, связанные с кандидатными ключами
• Зависимость неключевого атрибута от кандидатного ключа

BCNF (Boyce-Codd Normal Form):

• Более строгий стандарт
• Не допускает никаких аномалий
• Требует, чтобы каждый детерминант был кандидатным ключом

-- Пример, где 3NF но не BCNF
CREATE TABLE StudentLecturers (
    student_id INT,
    subject VARCHAR(50),
    lecturer_id INT,
    -- Уникальный индекс: только один лектор на предмет и студента
    UNIQUE(student_id, subject),
    UNIQUE(subject, lecturer_id)
);

-- Проблема BCNF: lecturer_id → subject
-- Но lecturer_id не является ключом таблицы
-- Это допускается в 3NF, но не в BCNF

Практические рекомендации

# ✓ ХОРОШО: Большинство таблиц должны быть в 3NF
class Database:
    # Разделить данные по смыслу
    users = {
        'id': 'PK',
        'name': 'depends on id only',
        'email': 'depends on id only'
    }
    
    departments = {
        'id': 'PK',
        'name': 'depends on id only'
    }
    
    employees = {
        'id': 'PK',
        'name': 'depends on id only',
        'department_id': 'FK'
    }

# ✗ ПЛОХО: Смешивание данных
class BadDatabase:
    everything = {
        'employee_id': 'PK',
        'employee_name': 'depends on employee_id',
        'department_name': 'depends on department_id (транзитивная зависимость!)',
        'location': 'depends on department_name'
    }

Производительность

-- Нормализованная БД требует JOIN'ов
SELECT e.name, d.name, d.location
FROM Employees e
JOIN Departments d ON e.department_id = d.department_id;

-- Денормализованная БД быстрее для чтения, но хуже для обновления
SELECT name, department_name, department_location
FROM Employees;

Баланс между нормализацией и производительностью

-- В большинстве случаев 3NF оптимален
-- Если нужна производительность, можно использовать денормализацию
-- с индексами, кэшированием или материализованными представлениями

CREATE MATERIALIZED VIEW EmployeeDepartmentView AS
SELECT e.id, e.name, d.name as dept_name, d.location
FROM Employees e
JOIN Departments d ON e.department_id = d.department_id;

Заключение

Третья нормальная форма — это практический стандарт для проектирования реляционных базы данных. Она:

✓ Устраняет большинство аномалий ✓ Минимизирует дублирование ✓ Облегчает обслуживание ✓ Оптимален для большинства приложений ✓ Баланс между нормализацией и производительностью

Получение наборе данных в 3NF — ключевой навык для разработчика баз данных.