Лекция 5. Нормализация

Определение

Нормализация — процесс организации данных в базе с двумя основными целями: устранение избыточности данных и обеспечение целостности данных.

Избыточность и аномалии

• Аномалия добавления (вставки). Невозможно добавить данные об одном объекте, не добавив данные о другом. Пример: чтобы добавить студента, посещающего «Математику», приходится добавлять и саму «Математику», если её ещё нет в БД.

• Аномалия удаления. При удалении одних данных мы невольно теряем другие. Пример: если только один студент посещал «Математику», то, удаляя его, мы потеряем и всю информацию о предмете.

• Аномалия обновления. Чтобы изменить значение, его приходится обновлять в нескольких местах. Пример: если имя студента дублируется в нескольких строках, фиксить нужно каждое вхождение.

Функциональная зависимость

Функциональная зависимость $A \to B$ означает, что каждому значению атрибута $A$ соответствует ровно одно значение атрибута $B$. Зная $A$, мы однозначно определяем $B$.

• $\text{НомерПаспорта} \to \text{ФИО}$ — зная номер паспорта, мы найдём только одного человека.
• $\text{НомерЗаказа} \to \text{ДатаЗаказа},\ \text{Сумма}$ — зная номер заказа, узнаём его дату и сумму.

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

Переменная отношения находится в 1НФ тогда и только тогда, когда в любом допустимом значении отношения каждый кортеж содержит только одно значение каждого из атрибутов.

Проблема. Пусть у человека более одного телефона.

• Создавать атрибуты phone1, phone2, …? Непонятно, сколько столбцов делать, и UNIQUE уже не сработает (одинаковые номера могут оказаться в разных столбцах).
• Складывать все номера в одну ячейку? Поиск конкретного номера и UNIQUE-проверки становятся медленными и неудобными.

Решение. Несколько раз запишем одного и того же человека, но в каждом кортеже — другой номер телефона (отдельная таблица «человек—телефон»).

Формальное определение (википедия). Отношение находится в 1НФ тогда и только тогда, когда:

• в отношении нет повторяющихся групп (атрибутов с одинаковым смыслом);
• все атрибуты атомарны;
• у отношения есть ключ.

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

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

Пример: пусть есть составной потенциальный ключ (ИСУ, Предмет). Неключевой атрибут «Имя» зависит только от ИСУ, то есть от части ключа — частичная зависимость.

Решение. Декомпозиция: разделяем таблицу так, чтобы все неключевые атрибуты полностью зависели от ключа.

Невозможно перейти ко 2НФ, не приведя сначала отношение к 1НФ.

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

Переменная отношения находится в 3НФ тогда и только тогда, когда она находится во 2НФ и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключа.

Пример: «Человек → Кафедра → Номер_телефона_кафедры». Номер не зависит от человека напрямую, только от кафедры.

Решение. Декомпозиция: вынести «Кафедру» в отдельную таблицу с её собственным телефоном.

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

Переменная отношения находится в НФБК, когда она находится в 3НФ и ключевые атрибуты составного ключа не зависят от неключевых атрибутов (точнее: для каждой нетривиальной ФЗ $X \to Y$ детерминант $X$ является суперключом).

Таблицы с простым первичным ключом, находящиеся в 3НФ, автоматически соответствуют НФБК.

Высшие нормальные формы

• 4НФ. Борется с многозначными зависимостями — когда один атрибут определяет множество независимых значений других атрибутов.
• 5НФ. Борется с зависимостями соединения без потерь — когда отношение можно без искажений восстановить только из трёх и более проекций.

Многозначные зависимости

$X$ многозначно определяет $Y$ тогда и только тогда, когда для каждого значения $X$ существует набор значений $Y$, и этот набор не зависит от значений других атрибутов $Z$.

Если зафиксировать значение $X$, то получим независимые друг от друга множества соответствующих значений $Y$ и $Z$.

Многозначная зависимость возникает, когда у одной сущности есть две или более независимых характеристики «многие-ко-многим».

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

Переменная отношения находится в 4НФ, если она находится в НФБК и не содержит нетривиальных многозначных зависимостей.

Пример 4НФ

Преподаватель Иванов ведёт два предмета (Математика, Физика) и у него два студента (Петров, Сидоров). Чтобы отразить все парные комбинации, приходится создать 4 строки.

• Аномалия вставки. Чтобы добавить Иванову третьего студента, нужно вставить две новых строки: одну для Математики, другую для Физики.
• Аномалия удаления. Если Сидоров перестал изучать Физику у Иванова — какую строку удалить? Удаление одной строки нарушит целостность данных.

Решение. Декомпозиция на два отношения: (Преподаватель, Предмет) и (Преподаватель, Студент).

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

Отношение находится в 5НФ, если каждая нетривиальная зависимость соединения в нём следует из его потенциальных ключей.

Отношение в 5НФ не может быть без потерь декомпозировано на несколько меньших отношений и потом корректно восстановлено. Любая такая попытка приведёт либо к потере данных, либо к появлению «фантомных» данных.

Пример 5НФ

Бизнес-правило: «Если поставщик $S$ поставляет деталь $P$, и деталь $P$ используется в проекте $J$, и поставщик $S$ работает над проектом $J$, то поставщик $S$ поставляет деталь $P$ для проекта $J$».

Это циклическое ограничение: таблица должна быть замкнута относительно этого правила. Если есть пары $(S,P)$, $(P,J)$ и $(S,J)$, то должна существовать и тройка $(S,P,J)$.

Аномалия. Если декомпозировать таблицу на любые две проекции (например, (Поставщик, Деталь) и (Деталь, Проект)) и обратно их соединить — получим фиктивные (ложные) строки.

Решение. Перепроектирование отношений.

Пример: staff_property_inspection

Отчёт об осмотрах объектов недвижимости сотрудниками.

Проблема. Повторяющиеся группы: адрес объекта и имя сотрудника дублируются для каждого осмотра.

1НФ

• Устранены повторяющиеся группы. Первичный ключ — (property_no, inspection_date).
• property_address частично зависит только от property_no — это нарушает 2НФ.

2НФ

Устранили частичную зависимость, вынеся адрес в отдельную таблицу. Неключевые атрибуты зависят от всего первичного ключа. Атрибут staff_name транзитивно зависит от первичного ключа через staff_no, что нарушает 3НФ.

3НФ

Устранены транзитивные зависимости. Зависимость (staff_no, inspection_date) → car_registration нарушает НФБК, так как детерминант не является потенциальным ключом.

НФБК

DELETE

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ];

ON DELETE

Поведение при удалении строки, на которую ссылается внешний ключ:

• RESTRICT — запрещает удаление, если есть зависимые строки (немедленная проверка).
• NO ACTION — то же, но проверка откладывается до конца транзакции (по умолчанию).
• SET NULL — в зависимых строках значение внешнего ключа становится NULL.
• SET DEFAULT — в зависимых строках устанавливается значение по умолчанию.
• (Также есть CASCADE — каскадное удаление зависимых строк.)