Конспект лекции: Нормализация реляционных баз данных
Дисциплина: ОП.08 Основы проектирования баз данных
Раздел программы (§2.2 с.7): Тема 2. Взаимосвязи в моделях и реляционный подход к построению моделей
Тема: Практическое занятие №2: нормализация реляционной БД (1НФ, 2НФ, функциональные зависимости)
1. Зачем нужна нормализация: анатомия аномалий
Когда начинающий разработчик проектирует базу данных для предметной области, самое частое интуитивное решение — поместить все возможные сведения в одну гигантскую универсальную таблицу (например, плоский журнал в Excel). На первый взгляд это кажется удобным: не нужно писать операции соединения JOIN, а все данные видны перед глазами. Однако реляционная наука доказывает, что подобная денормализованная структура таит в себе колоссальную опасность разрушения целостности данных.
Нормализация — это формальный математический метод анализа и преобразования схемы отношений реляционной базы данных с целью ликвидации избыточности данных, потенциальной противоречивости и аномалий модификации при сохранении всей смысловой полноты исходной информации.
Три смертных греха денормализованной таблицы:
- Аномалия вставки (Insertion Anomaly): Невозможность добавить факт об одном объекте без фиксации фиктивных или посторонних данных о другом объекте. Например, в плоской таблице заказов нельзя зарегистрировать нового клиента в системе до тех пор, пока он не совершит хотя бы одну покупку (поскольку номер заказа входит в первичный ключ и не может иметь значение
NULL).
- Аномалия удаления (Deletion Anomaly): Удаление записи об одном факте приводит к невосполнимой утрате сведений о совершенно другом объекте. Если покупатель отменил свой единственный заказ, и мы удаляем строку из плоской таблицы, вместе с заказом физически стирается вся контактная информация о клиенте!
- Аномалия модификации (Update Anomaly): Дублирование данных одного объекта в сотнях строк приводит к тому, что при изменении одного реквизита (например, номера телефона клиента или цены товара) требуется обновить множество кортежей. Если транзакция прервется или обновит лишь часть строк, база данных окажется в катастрофическом рассогласованном состоянии.
2. Теория функциональных зависимостей (Functional Dependencies)
Математическим фундаментом реляционной нормализации является концепция функциональной зависимости (сокращенно FD), предложенная Эдгаром Коддом в 1970 году.
Определение: Пусть задано отношение $R$ со схемой атрибутов $U$. Говорят, что атрибут или группа атрибутов $Y$ функционально зависит от атрибута или группы атрибутов $X$ (записывается как $X \rightarrow Y$), если во всякий момент времени в отношении $R$ не может существовать двух различных кортежей с одинаковым значением $X$, но разными значениями $Y$.
Атрибут $X$ называется детерминантом зависимости. Если мы знаем точное значение $X$, значение $Y$ определяется единственным и однозначным образом.
Классификация функциональных зависимостей:
- Тривиальная зависимость: Зависимость $X \rightarrow Y$ тривиальна, если $Y \subseteq X$ (например,
{Номер_заказа, Артикул} → Артикул). Она выполняется автоматически всегда.
- Полная функциональная зависимость: Зависимость $X \rightarrow Y$ называется полной, если атрибут $Y$ зависит от всего составного набора $X$ в целом, но не зависит ни от какого строгого подмножества $X' \subset X$.
Пример: {Номер_заказа, Код_товара} → Количество_в_заказе. Значение количества имеет смысл только для конкретного товара внутри конкретного заказа!
- Частичная (неполная) функциональная зависимость: Зависимость $X \rightarrow Y$ называется частичной, если атрибут $Y$ функционально зависит от собственного подмножества составного ключа $X$.
Пример: Пусть составной ключ отношения равен {Номер_заказа, Код_товара}. Зависимость {Номер_заказа, Код_товара} → Название_товара является частичной, поскольку в реальности Код_товара → Название_товара, то есть название товара зависит лишь от половины составного ключа!
3. Сквозной практический пример: эволюция отношения «Заказы клиентов»
Рассмотрим шаг за шагом превращение неструктурированной ведомости заказов интернет-магазина в строгую реляционную модель 2НФ.
Шаг 0. Исходное денормализованное состояние (Не-1НФ)
Исходный документ содержит неатомарные значения (списки товаров в одной строке) и повторяющиеся группы:
| Номер заказа |
Дата заказа |
ФИО покупателя |
Телефон покупателя |
Состав заказа (Товары, цены и кол-во) |
| 101 |
2026-10-01 |
Иванов Иван Иванович |
+7 (922) 111-22-33 |
SSD 1TB (5000 руб., 2 шт.), Мышь беспроводная (1500 руб., 1 шт.) |
| 102 |
2026-10-01 |
Петров Петр Сергеевич |
+7 (922) 444-55-66 |
Клавиатура механическая (4000 руб., 1 шт.) |
Проблема: Ячейка «Состав заказа» содержит составные неатомарные массивы данных. СУБД не может эффективно искать по названию товара, индексировать цены или вычислять сумму без медленного строкового парсинга.
Шаг 1. Приведение к Первой нормальной форме (1NF)
Критерий 1НФ: Отношение находится в 1НФ, если все используемые домены содержат исключительно скалярные (атомарные, неделимые) значения, отсутствуют повторяющиеся группы и массивы, а кортежи уникально идентифицируются первичным ключом.
Раскрываем составные списки: каждая товарная позиция выносится в отдельную строку. Для уникальной идентификации кортежа требуется составной первичный ключ: {Номер_заказа, Артикул_товара}.
| Номер заказа* |
Артикул* |
Дата заказа |
ФИО покупателя |
Телефон |
Название товара |
Цена товара |
Количество |
| 101 |
ART-881 |
2026-10-01 |
Иванов И.И. |
+7 (922) 111-22-33 |
SSD 1TB |
5000.00 |
2 |
| 101 |
ART-105 |
2026-10-01 |
Иванов И.И. |
+7 (922) 111-22-33 |
Мышь беспроводная |
1500.00 |
1 |
| 102 |
ART-220 |
2026-10-01 |
Петров П.С. |
+7 (922) 444-55-66 |
Клавиатура механическая |
4000.00 |
1 |
* Составной первичный ключ: {Номер_заказа, Артикул}.
Шаг 2. Построение диаграммы функциональных зависимостей (FD-анализ)
Проанализируем предметные зависимости атрибутов от ключа и его компонентов:
Анализ функциональных зависимостей:
1. {Номер_заказа} → {Дата_заказа, ФИО_покупателя, Телефон} [ЧАСТИЧНАЯ ЗАВИСИМОСТЬ!]
2. {Артикул} → {Название_товара, Цена_товара} [ЧАСТИЧНАЯ ЗАВИСИМОСТЬ!]
3. {Номер_заказа, Артикул} → {Количество} [ПОЛНАЯ ФУНКЦИОНАЛЬНАЯ ЗАВИСИМОСТЬ]
Диагноз 1НФ: Отношение страдает от частичных функциональных зависимостей! Значения Дата_заказа, ФИО_покупателя и Телефон зависят только от левой части ключа (Номер_заказа), а Название_товара и Цена_товара — только от правой части ключа (Артикул). Это порождает дублирование: при 10 товарах в заказе ФИО покупателя и телефон будут продублированы 10 раз!
Шаг 3. Приведение ко Второй нормальной форме (2NF)
Критерий 2НФ: Отношение находится в 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от первичного ключа отношения (то есть в схеме полностью ликвидированы частичные зависимости неключевых атрибутов от составного ключа).
Механизм декомпозиции: Выносим каждую группу частично зависимых атрибутов вместе с определяющей их частью первичного ключа в отдельное отношение. Исходный составной ключ сохраняется в промежуточной таблице позиций:
Итоговая схема декомпозиции в 2НФ (Декомпозиция без потерь — Lossless-Join):
1. Отношение «Заказы» (Orders):
Схема: Заказы (Номер_заказа, Дата_заказа, ФИО_покупателя, Телефон)
Первичный ключ: Номер_заказа (простой атомарный ключ).
Функциональная зависимость: Номер_заказа → Дата_заказа, ФИО_покупателя, Телефон (полная).
2. Отношение «Товары» (Products):
Схема: Товары (Артикул, Название_товара, Цена_товара)
Первичный ключ: Артикул (простой атомарный ключ).
Функциональная зависимость: Артикул → Название_товара, Цена_товара (полная).
3. Отношение «Позиции_заказа» (Order_Items):
Схема: Позиции_заказа (Номер_заказа, Артикул, Количество)
Первичный ключ: Составной {Номер_заказа, Артикул}.
Внешние ключи (FK): Номер_заказа REFERENCES Заказы(Номер_заказа), Артикул REFERENCES Товары(Артикул).
Функциональная зависимость: {Номер_заказа, Артикул} → Количество (строго полная).
Доказательство декомпозиции без потерь: Исходное отношение восстанавливается без единой потери информации и без появления ложных кортежей (spurious tuples) с помощью естественного соединения проекций:
Универсальная_таблица = (Заказы ⋈ Позиции_заказа) ⋈ Товары.
Все аномалии манипулирования устранены: товар можно завести без заказа, удаление заказа не уничтожает товар, изменение цены товара выполняется строго в одной строке таблицы «Товары».
4. Контрольные вопросы для самопроверки
- Какое математическое условие должно выполняться, чтобы функциональная зависимость $X \rightarrow Y$ признавалась полной?
- В каком случае отношение, находящееся в 1НФ, автоматически находится во 2НФ без выполнения дополнительной декомпозиции? (Подсказка: когда первичный ключ отношения является простым, то есть состоит ровно из одного атрибута!).
- Чем опасны частичные функциональные зависимости с точки зрения объема дисковой памяти и целостности транзакций СУБД?
- Сформулируйте суть теоремы Хита о декомпозиции отношений без потерь соединения.
- В чем фундаментальное отличие декомпозиции без потерь данных (Lossless-Join) от сохранения функциональных зависимостей (Dependency Preservation)?