Проектирование баз данных: нормализация 1NF, 2NF, 3NF и BCNF

Статья представляет собой справочное руководство по нормализации в базах данных с практическими примерами и экзаменационными вопросами.

In a Nutshell

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

Краткое техническое описание

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

Аномалии без нормализации:

• Insert-аномалии: невозможно добавить данные без других информационных элементов
• Update-аномалии: изменения требуют обновлений в нескольких местах
• Delete-аномалии: удаление данных непреднамеренно удаляет другую информацию

Нормальные формы:

• 1NF: атомарные значения, отсутствие повторяющихся групп
• 2NF: 1NF + отсутствие частичных зависимостей
• 3NF: 2NF + отсутствие транзитивных зависимостей
• BCNF: более строгая форма 3NF с более жесткими требованиями

Функциональные зависимости составляют математическую основу: X → Y означает, что значение X однозначно определяет значение Y.

Ключевые моменты для экзаменов

• 1NF: атомарные значения, отсутствие повторяющихся групп, уникальные первичные ключи
• 2NF: соответствие 1NF, отсутствие частичных зависимостей, полная зависимость от первичного ключа
• 3NF: соответствие 2NF, отсутствие транзитивных зависимостей, неключевые атрибуты зависят только от первичного ключа
• BCNF: каждый детерминант является кандидатом в ключ
• Функциональные зависимости: математическая основа нормализации
• Аномалии: проблемы Insert, Update, Delete при денормализованных данных
• IHK-релевантно: важно для моделирования данных и проектирования баз данных
• На практике: компромисс между нормализацией и производительностью

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

1. Функциональная зависимость: X → Y (X однозначно определяет Y)
2. Первичный ключ: уникальная идентификация записей
3. Кандидат в ключ: потенциальные первичные ключи
4. Частичная зависимость: зависимость от части составного ключа
5. Транзитивная зависимость: косвенная зависимость через неключевые атрибуты
6. Детерминант: атрибут, определяющий другие атрибуты
7. Процесс нормализации: пошаговое применение нормальных форм
8. Денормализация: целенаправленная избыточность для оптимизации производительности

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

Ненормализованная таблица (0NF)

-- Проблемная структура с избыточностью и аномалиями
CREATE TABLE Bestellungen (
    bestell_nr INT,
    bestellDatum DATE,
    kundenName VARCHAR(100),
    kundenAdresse VARCHAR(200),
    artikel_nr INT,
    artikelName VARCHAR(100),
    preis DECIMAL(10,2),
    menge INT,
    gesamtpreis DECIMAL(10,2)
);

-- Данные с проблемами
INSERT INTO Bestellungen VALUES 
(1, '2024-01-15', 'Meier', 'Hauptstraße 1', 101, 'Laptop', 999.99, 2, 1999.98),
(1, '2024-01-15', 'Meier', 'Hauptstraße 1', 102, 'Maus', 29.99, 1, 29.99),
(2, '2024-01-16', 'Schmidt', 'Nebenstraße 2', 101, 'Laptop', 999.99, 1, 999.99);

1. нормальная форма (1NF)

-- Атомарные значения, отсутствие повторяющихся групп
CREATE TABLE Bestellungen_1NF (
    bestell_nr INT,
    bestellDatum DATE,
    kundenName VARCHAR(100),
    kundenAdresse VARCHAR(200),
    artikel_nr INT,
    artikelName VARCHAR(100),
    preis DECIMAL(10,2),
    menge INT,
    gesamtpreis DECIMAL(10,2),
    PRIMARY KEY (bestell_nr, artikel_nr)
);

-- Функциональные зависимости:
-- bestell_nr, artikel_nr → menge, gesamtpreis
-- bestell_nr → bestellDatum, kundenName, kundenAdresse
-- artikel_nr → artikelName, preis

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

-- Устранение частичных зависимостей
CREATE TABLE Bestellungen_2NF (
    bestell_nr INT PRIMARY KEY,
    bestellDatum DATE,
    kundenName VARCHAR(100),
    kundenAdresse VARCHAR(200)
);

CREATE TABLE Bestellpositionen (
    bestell_nr INT,
    artikel_nr INT,
    menge INT,
    gesamtpreis DECIMAL(10,2),
    PRIMARY KEY (bestell_nr, artikel_nr),
    FOREIGN KEY (bestell_nr) REFERENCES Bestellungen_2NF(bestell_nr)
);

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

3. нормальная форма (3NF)

-- Устранение транзитивных зависимостей
CREATE TABLE Bestellungen_3NF (
    bestell_nr INT PRIMARY KEY,
    bestellDatum DATE,
    kunden_id INT,
    FOREIGN KEY (kunden_id) REFERENCES Kunden(kunden_id)
);

CREATE TABLE Kunden (
    kunden_id INT PRIMARY KEY,
    kundenName VARCHAR(100),
    kundenAdresse VARCHAR(200)
);

CREATE TABLE Bestellpositionen (
    bestell_nr INT,
    artikel_nr INT,
    menge INT,
    PRIMARY KEY (bestell_nr, artikel_nr),
    FOREIGN KEY (bestell_nr) REFERENCES Bestellungen_3NF(bestell_nr),
    FOREIGN KEY (artikel_nr) REFERENCES Artikel(artikel_nr)
);

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

Аномалии и их решение

Insert-аномалия (без нормализации)

-- Проблема: невозможно добавить клиента без заказа
-- Решение: отдельная таблица клиентов
INSERT INTO Kunden (kunden_id, kundenName, kundenAdresse) 
VALUES (3, 'Mueller', 'Dritter Weg 3');

Update-аномалия (без нормализации)

-- Проблема: адрес клиента требует изменения в нескольких местах
-- Решение: централизованная таблица клиентов
UPDATE Kunden 
SET kundenAdresse = 'Hauptstraße 1a' 
WHERE kunden_id = 1;

Delete-аномалия (без нормализации)

-- Проблема: удаление последнего заказа удаляет данные клиента
-- Решение: отдельные таблицы избегают непреднамеренного удаления
DELETE FROM Bestellpositionen WHERE bestell_nr = 1;
-- Данные клиента остаются в таблице Kunden

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

Правило BCNF

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

-- Пример, соответствующий 3NF но не BCNF
CREATE TABLE Projektmitarbeiter (
    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)
);

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

Преимущества и недостатки

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

• Целостность данных: предотвращение избыточности и несогласованности
• Удобство поддержки: изменения требуются только в одном месте
• Эффективность хранения: снижение избыточности
• Консистентность: единообразное представление данных

Недостатки

• Производительность: необходимо больше операций JOIN
• Сложность: более сложная структура данных
• Производительность записи: распределенные обновления по нескольким таблицам
• Кривая обучения: требует понимания функциональных зависимостей

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

1. В чем разница между 2NF и 3NF? 2NF устраняет частичные зависимости, 3NF устраняет транзитивные зависимости.

2. Когда таблица находится в BCNF? Когда каждый детерминант является кандидатом в ключ (более строго, чем 3NF).

3. Объясните функциональные зависимости. X → Y означает, что значение X однозначно определяет значение Y.

4. Что такое аномалии и как их избежать? Insert, Update и Delete аномалии предотвращаются путем нормализации.

Ключевые источники

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

---

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