Практическое занятие №2: нормализация реляционной БД

Смертные грехи плоской таблицы

• Избыточность: одни и те же ФИО, адреса и названия повторяются миллионы раз.
• Расход дисковой памяти: раздувание размера файлов таблиц и индексов в разы.
• Медленные транзакции: блокировки огромных таблиц при малейшем апдейте.

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

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

1. Аномалия вставки

Нельзя зафиксировать факт существования сущности А без сущности Б. Нельзя внести нового поставщика на склад, пока он не оформил накладную (ключ не терпит NULL).

2. Аномалия модификации

Изменение номера телефона клиента требует обновления 10 000 строк. Сбой транзакции оставляет БД в разорванном, рассогласованном состоянии.

3. Аномалия удаления

Удаляя строку закрытого заказа с уникальным товаром, мы непреднамеренно физически стираем всю карточку товара с его характеристиками и ценой.

Строгое определение FD

Говорят, что атрибут Y функционально зависит от X (X → Y), если во всякий момент времени в отношении любые два кортежа с равными значениями X имеют абсолютно равные значения Y:

t1[X] = t2[X] ⇒ t1[Y] = t2[Y]

Компоненты зависимости

• X — Детерминант (Determinant): независимый атрибут или набор атрибутов, однозначно диктующий значение.
• Y — Зависимая часть (Dependent): значение, которое жестко привязано к детерминанту.
• Пример: ИНН_организации → Название_компании.

Полная зависимость (Full FD)

Атрибут Y зависит от всего составного ключа в целом и не зависит ни от одной его части:

{Номер_накладной, Артикул} → Количество

Количество имеет смысл строго в контексте конкретной накладной и конкретного товара!

Частичная зависимость (Partial FD)

Атрибут Y зависит лишь от части составного ключа:

{Номер_накладной, Артикул} → Название_товара
НО: Артикул → Название_товара

Название товара не зависит от накладной! Это грубое нарушение требований 2НФ!

1. Атомарность значений

В каждой ячейке хранится ровно одно неделимое значение (скаляр). Никаких списков, массивов или составных строк через запятую.

2. Уникальность строк

Каждая строка таблицы обязана быть различима благодаря наличию первичного ключа (простого или составного).

3. Отсутствие повторов

Запрещены повторяющиеся столбцы типа Товар_1, Товар_2, Товар_3. Данные разворачиваются построчно.

Золотое правило проектировщика

Если первичный ключ отношения является простым (состоит из одного столбца), то отношение автоматически находится во 2НФ (в нем физически не может быть части составного ключа)!

Метод нормализации в 2НФ

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

Частичная зависимость 1

Накладная → {Дата, Поставщик}

Дата поставки и имя поставщика зависят строго от номера накладной, а не от состава товаров.

Частичная зависимость 2

Артикул → {Название_товара, Цена}

Характеристики товара зависят только от артикула и одинаковы во всех накладных.

Полная зависимость

{Накладная, Артикул} → Количество

Количество поставленного товара определяется строго пересечением накладной и артикула.

1. Накладные (Invoices)

Номер_накладной (PK)
Дата_поставки
Поставщик

Ключ простой → 2НФ выполнена!

2. Товары (Products)

Артикул_товара (PK)
Название_товара
Цена_закупки

Ключ простой → 2НФ выполнена!

3. Позиции_накладной

Номер_накладной (PK, FK)
Артикул_товара (PK, FK)
Количество

Полная зависимость от ключа!

Теорема Хита

Декомпозиция отношения R(A, B, C) на R1(A, B) и R2(A, C) является декомпозицией без потерь, если общий атрибут A функционально определяет хотя бы одно из отношений (то есть является его ключом):

A → B или A → C

Естественное соединение без потерь

Исходная универсальная ведомость восстанавливается формулой:

R = (Накладные ⋈ Позиции) ⋈ Товары

Ни одна строка не потеряна, и не возникло ни одного фиктивного «призрачного» кортежа!

Вставка безопасна

Новый товар можно добавить в таблицу «Товары» в любой момент, даже если на складе еще не оформлено ни одной накладной.

Удаление безопасно

Аннулирование ошибочной накладной удаляет записи из «Накладные» и «Позиции», но карточка товара остается в таблице «Товары» нетронутой.

Обновление безопасно

Смена цены товара или названия поставщика выполняется строго в одной строке одной таблицы. Рассинхронизация физически невозможна!

Вариант 1: Оптовые поставки

Универсальная ведомость {Номер_накладной, Артикул, Дата, Поставщик, Название_товара, Ед_изм, Цена, Кол-во}. Ключ: {Накладная, Артикул}.

Вариант 2: Регистрация пациентов

Журнал регистратуры {Номер_талона, СНИЛС, ФИО, Дата_рождения, Номер_кабинета, ФИО_врача, Специальность, Диагноз}. Ключ: {Талон, СНИЛС}.

Вариант 3: Успеваемость студентов

Сводная ведомость {Номер_зачетки, Код_дисциплины, ФИО_студента, Группа, Название_дисциплины, Семестр, Часы, Оценка}. Ключ: {Зачетка, Дисциплина}.

Вариант 4: Путевые листы автоколонны

Журнал выездов {Номер_листа, Код_авто, Дата_выезда, Марка, Госномер, Табельный_номер_водителя, ФИО_водителя, Пробег}. Ключ: {Лист, Код_авто}.

Алгоритм из 4 шагов

1. Определить составной первичный ключ отношения {A, B}.
2. Выписать все нетривиальные FD предметной области.
3. Классифицировать FD: выделить зависимости от {A}, от {B} и от полного ключа {A, B}.
4. Выполнить проекции: создать таблицы для A, для B и связующую таблицу {A, B} с внешними ключами.

Критерии оценивания (макс. 4 балла)

• 2 балла: корректный вывод формул FD и разделение на полные/частичные.
• 1 балл: безупречная декомпозиция отношений без потерь (Lossless-Join) с PK и FK.
• 1 балл: доказательство устранения аномалий и прохождение чек-листа самопроверки.