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

Нормализация базы данных: теория и практика

Что такое нормализация

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

1. Минимизации дублирования данных
2. Улучшения целостности данных
3. Упрощения обслуживания БД
4. Повышения производительности запросов

Без нормализации (денормализованная БД)

-- ❌ ПЛОХО: все в одной таблице
CREATE TABLE students_courses (
    id INT PRIMARY KEY,
    student_name VARCHAR(100),
    student_email VARCHAR(100),
    student_phone VARCHAR(20),
    student_address VARCHAR(200),
    student_age INT,
    course_name VARCHAR(100),
    course_instructor VARCHAR(100),
    course_schedule VARCHAR(100),
    course_room VARCHAR(20),
    enrollment_date DATE,
    grade CHAR(1)
);

-- Проблемы:
-- 1. Дублирование: студент повторяется для каждого курса
-- 2. Аномалии обновления: если изменить инструктора, нужно обновить все строки
-- 3. Аномалии удаления: удалить последний курс = потерять студента

Нормальные формы (Normal Forms)

1NF (First Normal Form) — атомарные значения

Правило: каждая ячейка содержит только одно значение (не массив или набор)

-- ❌ НЕ 1NF: курсы в одной ячейке
CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    courses VARCHAR(500)  -- "Python, SQL, JavaScript"
);

-- ✅ 1NF: разные курсы в разных строках
CREATE TABLE students (
    student_id INT,
    name VARCHAR(100)
);

CREATE TABLE courses (
    student_id INT,
    course_name VARCHAR(100),
    PRIMARY KEY (student_id, course_name),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

2NF (Second Normal Form) — удаление частичных зависимостей

Правило: все не-ключевые атрибуты должны зависеть от ВСЕГО первичного ключа, не от части

-- ❌ НЕ 2NF: course_instructor зависит от course_id, но не от student_id
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade CHAR(1),
    course_instructor VARCHAR(100),  -- зависит только от course_id!
    PRIMARY KEY (student_id, course_id)
);

-- ✅ 2NF: информация о курсе отделена
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    course_instructor VARCHAR(100)
);

3NF (Third Normal Form) — удаление транзитивных зависимостей

Правило: не-ключевые атрибуты не должны зависеть от других не-ключевых атрибутов

-- ❌ НЕ 3NF: instructor_department зависит от instructor_name
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor_name VARCHAR(100),
    instructor_department VARCHAR(50)  -- зависит от instructor_name!
);

-- ✅ 3NF: информация об инструкторе отделена
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor_id INT,
    FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
);

CREATE TABLE instructors (
    instructor_id INT PRIMARY KEY,
    instructor_name VARCHAR(100),
    instructor_department VARCHAR(50)
);

Визуальный пример 3NF:

Давайте по порядку:

STUDENT → ENROLLMENT → COURSE → INSTRUCTOR
         ↑             ↑
      зависит       зависит
    от STUDENT     от COURSE
    
❌ НЕПРАВИЛЬНО: instructor_name в enrollments
   (в enrollments есть информация о курсе)

✅ ПРАВИЛЬНО: каждая сущность в своей таблице

BCNF (Boyce-Codd Normal Form) — более строгая 3NF

Правило: каждый детерминант должен быть кандидатным ключом

-- ❌ НЕ BCNF
CREATE TABLE professor_courses (
    professor_id INT,
    course_id INT,
    semester INT,
    time_slot VARCHAR(20)  -- time_slot зависит только от course_id
);
-- Детерминант: course_id → time_slot
-- Но course_id не является кандидатным ключом

-- ✅ BCNF
CREATE TABLE professor_courses (
    professor_id INT,
    course_id INT,
    semester INT,
    PRIMARY KEY (professor_id, course_id, semester),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    time_slot VARCHAR(20)
);

Практический пример: интернет-магазин

-- Таблица 1: Пользователи
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES countries(country_id)
);

-- Таблица 2: Страны (отделяем, чтобы не дублировать названия)
CREATE TABLE countries (
    country_id INT PRIMARY KEY,
    country_name VARCHAR(100) UNIQUE,
    currency_code VARCHAR(3)
);

-- Таблица 3: Товары
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    category_id INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Таблица 4: Категории товаров
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100) UNIQUE
);

-- Таблица 5: Заказы
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(12, 2),
    status VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Таблица 6: Позиции заказа (Many-to-Many)
CREATE TABLE order_items (
    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:

1. Есть ли дублирование данных?
   → Если да, нужна нормализация

2. Требуется ли обновлять одно значение в нескольких местах?
   → Если да, денормализовано

3. Могут ли быть аномалии удаления?
   → Удаление записи потеряет важные данные

4. Есть ли зависимости между не-ключевыми атрибутами?
   → Если да, нужна 3NF

Денормализация: когда это нужно

Нормализация улучшает целостность, но может замедлить запросы:

-- Нормализованный запрос (3 JOIN'а)
SELECT 
    o.order_id,
    u.first_name,
    u.last_name,
    c.country_name,
    p.product_name,
    oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN countries c ON u.country_id = c.country_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date = CURRENT_DATE;

-- Денормализованная версия (без JOIN'ов)
CREATE TABLE order_details_denormalized AS
SELECT 
    o.order_id,
    u.first_name,
    u.last_name,
    c.country_name,
    p.product_name,
    oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN countries c ON u.country_id = c.country_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- Запрос на денормализованную таблицу (быстро)
SELECT * FROM order_details_denormalized
WHERE order_date = CURRENT_DATE;

Когда денормализовать:

• ✅ Если запрос очень часто используется
• ✅ Если много JOIN'ов замедляют систему
• ✅ Если можно контролировать консистентность (triggers)
• ❌ Если требуется real-time обновление

Уровни нормализации на практике

Практический совет для аналитика

# При анализе схемы БД спроси себя:

questions = {
    '1NF': 'Есть ли массивы или кортежи в ячейках? (нужно разложить)',
    '2NF': 'Есть ли таблицы с составным PK, где часть атрибутов зависит от части PK?',
    '3NF': 'Есть ли таблицы, где не-ключевые атрибуты зависят друг от друга?',
    'BCNF': 'Есть ли детерминанты, которые не являются ключами?'
}

for form, question in questions.items():
    if check_schema(question):
        print(f"⚠️ Нарушение {form}: {question}")

Пример: анализ реальной БД

-- Найти возможные нарушения нормализации

-- 1. Дублирование значений
SELECT 
    column_name,
    COUNT(DISTINCT column_name) as unique_values,
    COUNT(*) as total_rows,
    ROUND(COUNT(DISTINCT column_name) * 100.0 / COUNT(*), 2) as uniqueness_percent
FROM your_table
GROUP BY column_name
ORDER BY uniqueness_percent ASC;

-- 2. Зависимости между столбцами (возможное нарушение 3NF)
SELECT 
    col1, col2, COUNT(*)
FROM your_table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

-- Если COUNT(*) > 1, значит col2 зависит только от col1
-- (нарушение 3NF)

Памятка для собеседования

Стандарт для OLTP БД: 3NF или BCNF Стандарт для OLAP БД: Star/Snowflake schema (денормализованно)

Ключевая идея: Нормализация = баланс между:

• Целостностью данных (хочу нормализованную)
• Производительностью (хочу денормализованную)

Выбирай в зависимости от use case'а.