Нормальные формы: 1NF, 2NF, 3NF, BCNF, 4NF и 5NF полный справочник

Этот материал дает подробное объяснение всех нормальных форм в теории баз данных с примерами от 1NF до 5NF.

Вкратце

Нормальные формы это формальные правила для структурирования реляционных баз данных, которые пошагово исключают избыточность и обеспечивают целостность данных. Каждая более высокая нормальная форма строится на предыдущей.

Краткое определение

Нормализация это процесс пошагового улучшения структуры базы данных путем применения формальных правил. Каждая нормальная форма решает конкретные проблемы аномалий и зависимостей.

Обзор нормальных форм:

• 1NF: Атомарные значения, отсутствие групп повторений
• 2NF: 1NF + отсутствие частичных зависимостей
• 3NF: 2NF + отсутствие транзитивных зависимостей
• BCNF: Более строгая 3NF с жесткими правилами
• 4NF: 3NF + отсутствие многозначных зависимостей
• 5NF: 4NF + отсутствие зависимостей от соединения

Математические основы:

• Функциональные зависимости: X → Y
• Многозначные зависимости: X →→ Y
• Зависимости от соединения: (A, B, C)

Практическая значимость: Первые три нормальные формы (1NF-3NF) достаточны для большинства приложений. Более высокие нормальные формы (4NF-5NF) нужны для сложных структур данных и в академических контекстах.

Ключевые моменты для подготовки

• 1NF: Атомарные значения, отсутствие групп повторений, уникальные первичные ключи
• 2NF: Отсутствие частичных зависимостей от составного первичного ключа
• 3NF: Отсутствие транзитивных зависимостей через неключевые атрибуты
• BCNF: Каждый детерминант это кандидат в ключ
• 4NF: Отсутствие многозначных зависимостей, кроме тривиальных
• 5NF: Отсутствие зависимостей от соединения, кроме тех что через кандидаты в ключи
• Функциональные зависимости: Основа нормализации
• Для экзаменов: Особенно важны 1NF-3NF для проектирования баз данных

Основные компоненты

1. Функциональная зависимость (FD): X → Y (X однозначно определяет Y)
2. Многозначная зависимость (MVD): X →→ Y (X определяет несколько значений Y)
3. Зависимость от соединения (JD): (A, B, C) (таблица может быть восстановлена из соединений)
4. Детерминант: Атрибут, который определяет другие атрибуты
5. Кандидат в ключ: Минимальные уникальные идентификаторы
6. Первичный ключ: Выбранный кандидат в ключ
7. Частичная зависимость: Зависимость от части составного ключа
8. Транзитивная зависимость: Косвенная зависимость через неключевые атрибуты

Практические примеры

Первая нормальная форма (1NF)

-- Проблема: неатомарные значения и группы повторений
CREATE TABLE Bestellungen_Schlecht (
    bestell_id INT,
    kunde VARCHAR(100),
    artikel_liste VARCHAR(500), -- Nicht-atomar
    telefonnummern VARCHAR(200) -- Wiederholungsgruppe
);

-- Решение 1NF: атомарные значения
CREATE TABLE Bestellungen_1NF (
    bestell_id INT PRIMARY KEY,
    kunden_id INT,
    bestelldatum DATE
);

CREATE TABLE Bestellpositionen (
    bestell_id INT,
    positionsnummer INT,
    artikel_id INT,
    menge INT,
    PRIMARY KEY (bestell_id, positionsnummer)
);

CREATE TABLE Kunden_Telefone (
    kunden_id INT,
    telefonnummer VARCHAR(20),
    PRIMARY KEY (kunden_id, telefonnummer)
);

Вторая нормальная форма (2NF)

-- Проблема: частичные зависимости
CREATE TABLE Bestellpositionen_2NF_Problem (
    bestell_id INT,
    artikel_id INT,
    menge INT,
    artikel_name VARCHAR(100), -- Зависит только от artikel_id
    preis DECIMAL(10,2),       -- Зависит только от artikel_id
    PRIMARY KEY (bestell_id, artikel_id)
);

-- Решение 2NF: исключить частичные зависимости
CREATE TABLE Bestellpositionen_2NF (
    bestell_id INT,
    artikel_id INT,
    menge INT,
    PRIMARY KEY (bestell_id, artikel_id)
);

CREATE TABLE Artikel (
    artikel_id INT PRIMARY KEY,
    artikel_name VARCHAR(100),
    preis DECIMAL(10,2)
);

Третья нормальная форма (3NF)

-- Проблема: транзитивные зависимости
CREATE TABLE Mitarbeiter_3NF_Problem (
    mitarbeiter_id INT PRIMARY KEY,
    name VARCHAR(100),
    abteilungs_id INT,
    abteilungs_name VARCHAR(100), -- Транзитивно: abteilungs_id → abteilungs_name
    standort VARCHAR(50)           -- Транзитивно: abteilungs_id → standort
);

-- Решение 3NF: исключить транзитивные зависимости
CREATE TABLE Mitarbeiter_3NF (
    mitarbeiter_id INT PRIMARY KEY,
    name VARCHAR(100),
    abteilungs_id INT
);

CREATE TABLE Abteilungen (
    abteilungs_id INT PRIMARY KEY,
    abteilungs_name VARCHAR(100),
    standort VARCHAR(50)
);

Нормальная форма Бойса-Кодда (BCNF)

-- Проблема: таблица в 3NF но не в BCNF
CREATE TABLE Projektmitarbeiter_BCNF_Problem (
    projekt_id INT,
    mitarbeiter_id INT,
    rolle VARCHAR(50),
    PRIMARY KEY (projekt_id, mitarbeiter_id)
);

-- Функциональные зависимости:
-- projekt_id, mitarbeiter_id → rolle
-- projekt_id, rolle → mitarbeiter_id (Детерминант не кандидат в ключ!)

-- Решение BCNF
CREATE TABLE Projektrollen (
    projekt_id INT,
    rolle VARCHAR(50),
    mitarbeiter_id INT,
    PRIMARY KEY (projekt_id, rolle)
);

Четвертая нормальная форма (4NF)

-- Проблема: многозначные зависимости
CREATE TABLE Mitarbeiter_Kenntnisse_4NF_Problem (
    mitarbeiter_id INT,
    programmiersprache VARCHAR(50),
    projekt_id INT,
    PRIMARY KEY (mitarbeiter_id, programmiersprache, projekt_id)
);

-- MVDs: mitarbeiter_id →→ programmiersprache, mitarbeiter_id →→ projekt_id

-- Решение 4NF: разделение многозначных зависимостей
CREATE TABLE Mitarbeiter_Sprachen (
    mitarbeiter_id INT,
    programmiersprache VARCHAR(50),
    PRIMARY KEY (mitarbeiter_id, programmiersprache)
);

CREATE TABLE Mitarbeiter_Projekte (
    mitarbeiter_id INT,
    projekt_id INT,
    PRIMARY KEY (mitarbeiter_id, projekt_id)
);

Пятая нормальная форма (5NF)

-- Проблема: зависимости от соединения
CREATE TABLE Lieferanten_Produkte_Kunden_5NF_Problem (
    lieferant_id INT,
    produkt_id INT,
    kunden_id INT,
    PRIMARY KEY (lieferant_id, produkt_id, kunden_id)
);

-- JD: *(Lieferanten_Produkte, Produkte_Kunden, Lieferanten_Kunden)*

-- Решение 5NF: декомпозиция на проекции
CREATE TABLE Lieferanten_Produkte (
    lieferant_id INT,
    produkt_id INT,
    PRIMARY KEY (lieferant_id, produkt_id)
);

CREATE TABLE Produkte_Kunden (
    produkt_id INT,
    kunden_id INT,
    PRIMARY KEY (produkt_id, kunden_id)
);

CREATE TABLE Lieferanten_Kunden (
    lieferant_id INT,
    kunden_id INT,
    PRIMARY KEY (lieferant_id, kunden_id)
);

Процесс нормализации

Шаг 1: Определение зависимостей

-- Анализ функциональных зависимостей
-- Пример: система заказов
-- bestell_id → bestelldatum, kunden_id
-- kunden_id → kundenname, adresse
-- bestell_id, artikel_id → menge, einzelpreis
-- artikel_id → artikelname, lagerbestand

Шаг 2: Применение нормальных форм

-- 1NF: обеспечить атомарность значений
-- 2NF: исключить частичные зависимости
-- 3NF: исключить транзитивные зависимости
-- BCNF: детерминанты как кандидаты в ключи
-- 4NF: исключить многозначные зависимости
-- 5NF: исключить зависимости от соединения

Шаг 3: Проверка

-- Тест на аномалии
INSERT INTO ... -- Аномалии добавления?
UPDATE ...     -- Аномалии обновления?
DELETE ...     -- Аномалии удаления?

Достоинства и недостатки

Преимущества нормализации

• Целостность данных: Избежание избыточности и несогласованности
• Удобство обслуживания: Изменения требуются только в одном месте
• Эффективность памяти: Снижение избыточности
• Согласованность: Единообразное представление данных
• Масштабируемость: Лучшая производительность на больших объемах

Недостатки

• Производительность: Требуется больше соединений
• Сложность: Более сложная структура данных
• Производительность записи: Распределенные обновления
• Избыточная инженерия: Чрезмерная нормализация может быть неэффективной

Стратегии денормализации

Целенаправленная избыточность для производительности

-- Денормализованная версия для отчетности
CREATE TABLE Bestellungen_Report (
    bestell_id INT PRIMARY KEY,
    bestelldatum DATE,
    kundenname VARCHAR(100), -- Избыточно из таблицы клиентов
    artikelname VARCHAR(100), -- Избыточно из таблицы товаров
    menge INT,
    gesamtpreis DECIMAL(10,2)
);

Частые вопросы на экзаменах

1. Когда таблица находится в 3NF но не в BCNF? Когда детерминант не является кандидатом в ключ (пример: сотрудники проекта с ролями).

2. В чем разница между 3NF и BCNF? BCNF строже: каждый детерминант должен быть кандидатом в ключ.

3. Объясните многозначные зависимости! X →→ Y означает, что для каждого значения X множество значений Y независимо от других атрибутов.

4. Когда более высокие нормальные формы (4NF, 5NF) практически значимы? Для очень сложных структур данных с множеством связей, в основном в академических или специализированных системах.

Основные источники

1. https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
2. https://www.gatech.edu/coe/cse/database-normalization
3. https://www.sql-tutorial.ru/sql-normalization-complete.html

---

Рекомендуемая литература: Базы данных