Аномалии: insert, update, delete

В прошлом уроке мы научились находить функциональные зависимости. Теперь покажем, зачем это нужно на практике. Нормализация существует не ради красоты схемы — она устраняет конкретные классы дефектов, которые называются аномалиями (anomalies). Аномалия — это ситуация, когда из-за структуры таблицы обычная операция (вставка, изменение, удаление строки) приводит к потере данных, к их рассинхрону или к невозможности выполнить операцию вообще.

Важно понять главное: аномалии — это не баги в коде приложения. Это дефекты модели. Их нельзя исправить аккуратным программированием — СУБД физически не способна их предотвратить, пока схема устроена неправильно. Единственное настоящее лекарство — изменить структуру таблицы. Нормализация и есть систематический способ это сделать.

Источник проблемы: избыточность

В основе всех трёх аномалий лежит одна причина — избыточность данных (data redundancy): один и тот же факт хранится в таблице более одного раза. Избыточность возникает, когда в таблицу свалены несколько разных сущностей, связанных функциональными зависимостями, которые «не дотягиваются» до ключа.

Возьмём таблицу, в которой смешаны три сущности — заказ, покупатель и товар:

ORDER_LINES
order_id | customer_id | customer_city | product_id | product_name | qty
---------+-------------+---------------+------------+--------------+----
1001     | 42          | Москва        | P-7        | Клавиатура   | 2
1001     | 42          | Москва        | P-9        | Мышь         | 1
1002     | 42          | Москва        | P-7        | Клавиатура   | 5
1003     | 88          | Казань        | P-7        | Клавиатура   | 1

Ключ таблицы — пара {order_id, product_id} (одна строка на товар в заказе). Посмотрите на повторы. Город покупателя 42 — «Москва» — записан трижды. Название товара P-7 — «Клавиатура» — записано трижды. Это и есть избыточность: факт «покупатель 42 живёт в Москве» хранится в стольких строках, сколько строк заказа сделал этот покупатель.

Почему так вышло, видно через FD. В таблице действуют:

customer_id -> customer_city      -- город зависит ТОЛЬКО от customer_id
product_id  -> product_name       -- название зависит ТОЛЬКО от product_id
{order_id, product_id} -> qty     -- количество зависит от всего ключа

Зависимости customer_id -> customer_city и product_id -> product_name имеют слева часть ключа, а не весь ключ. Факт привязан к части ключа, но физически дублируется на каждую комбинацию с остальной частью ключа. Это структурная причина избыточности — и, как следствие, всех аномалий.

Update anomaly: рассинхрон при изменении

Аномалия обновления (update anomaly): чтобы изменить один факт, нужно обновить несколько строк, и если хоть одна не обновилась — данные стали противоречивыми.

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

UPDATE order_lines SET customer_city = 'Санкт-Петербург' WHERE customer_id = 42;
-- затрагивает 3 строки; если условие задано неточно — обновятся не все

Стоит запросу обновить две строки из трёх (ошибка в WHERE, гонка двух транзакций, ручная правка), и таблица приходит в противоречивое состояние:

order_id | customer_id | customer_city
---------+-------------+------------------
1001     | 42          | Санкт-Петербург
1001     | 42          | Санкт-Петербург
1002     | 42          | Москва             <- рассинхрон!

Теперь на вопрос «в каком городе покупатель 42?» таблица даёт два разных ответа. Это нарушение инварианта — правила customer_id -> customer_city, которое обязано выполняться. И вот ключевой момент: обычная таблица не может этот инвариант защитить. СУБД не знает, что три ячейки customer_city должны быть одинаковыми, — для неё это три независимые ячейки в трёх строках. Чем больше у покупателя заказов, тем больше строк под угрозой и тем выше вероятность рассинхрона.

Insert anomaly: нельзя добавить факт

Аномалия вставки (insert anomaly): новый факт невозможно записать, потому что для строки не хватает данных о другой, не связанной с ним сущности.

Компания завела в каталог новый товар — P-15, «Коврик». Заказов на него ещё нет. Куда записать факт «существует товар P-15 с названием Коврик»? В нашей таблице единственное место для названия товара — ORDER_LINES. Но ключ этой таблицы — {order_id, product_id}, а order_id для несуществующего заказа взять негде:

INSERT INTO order_lines (order_id, product_id, product_name)
VALUES (NULL, 'P-15', 'Коврик');
-- order_id входит в primary key, а PK не может быть NULL -> вставка отклонена

Атрибут order_id входит в первичный ключ, а первичный ключ не может содержать NULL. Вставка невозможна в принципе. Получается абсурд: чтобы внести в систему новый товар, нужно сначала кого-то заставить его заказать. Существование товара оказалось «заложником» существования заказа — двух логически независимых фактов, которые модель ошибочно сцепила в одной таблице.

То же и с покупателем: нельзя зарегистрировать покупателя, пока он не сделал первый заказ, — для строки не будет ни order_id, ни product_id.

Delete anomaly: удаление теряет лишнее

Аномалия удаления (delete anomaly): удаляя строку ради одного факта, мы непреднамеренно теряем другой, всё ещё нужный факт.

Заказ 1003 отменили и удалили его строку:

DELETE FROM order_lines WHERE order_id = 1003;

Посмотрим, что в этой строке хранилось:

order_id | customer_id | customer_city | product_id | product_name | qty
---------+-------------+---------------+------------+--------------+----
1003     | 88          | Казань        | P-7        | Клавиатура   | 1

Строка 1003 была единственной, где встречался покупатель 88. Удалив заказ, мы заодно стёрли единственное упоминание факта «покупатель 88 живёт в Казани». Покупатель 88 не переставал существовать — но из базы он исчез. Удаление одного факта (заказ отменён) уничтожило другой, логически независимый факт (где живёт покупатель). Это и есть delete anomaly.

Аномалия как нарушение инварианта

Полезно увидеть аномалии не как «неудобства», а строго — как нарушение инвариантов. Инвариант — это утверждение, которое в корректной базе обязано выполняться всегда. Каждая функциональная зависимость задаёт инвариант. FD customer_id -> customer_city означает инвариант: «во всех строках с одним customer_id значение customer_city одинаково».

В нормализованной схеме часть инвариантов СУБД стережёт структурно. Если город хранится в единственной строке CUSTOMERS, то инвариант «у покупателя один город» выполняется сам собой: одна ячейка физически не может содержать два разных значения. Структура делает нарушение невозможным.

А вот в денормализованной таблице ORDER_LINES тот же инвариант не защищён ничем. Город размазан по многим строкам, и СУБД не знает, что эти ячейки связаны. Update anomaly — это в точности момент, когда инвариант нарушается: две строки одного покупателя показывают разные города. И ключевой момент — обычная таблица не может этот инвариант объявить. Нет такого ограничения «значения в этих ста ячейках обязаны совпадать»; первичные ключи, CHECK, внешние ключи такого класса правил не выражают.

Инвариант (из FD customer_id -> customer_city):
    для любых двух строк: одинаковый customer_id => одинаковый customer_city

Нормализованная схема:  инвариант выполняется СТРУКТУРНО (один факт — одна ячейка)
Денормализованная:      инвариант НЕ защищён — update anomaly его нарушает

Это и есть точная, инженерная причина нормализации. Нормализация — не про эстетику схемы и не про «правильно выглядит». Это способ переформулировать структуру так, чтобы бизнес-инварианты стали структурно неразрушимыми, а не держались на дисциплине каждого, кто пишет в базу. Аномалия — это симптом; нарушенный инвариант — это диагноз.

Лекарство: декомпозиция по зависимостям

Все три аномалии исчезают, если разложить таблицу так, чтобы каждый факт хранился ровно один раз. Сгруппируем атрибуты вокруг их детерминантов — то есть вокруг левых частей FD:

CUSTOMERS(customer_id PK, customer_city)
    42 | Москва
    88 | Казань

PRODUCTS(product_id PK, product_name)
    P-7 | Клавиатура
    P-9 | Мышь

ORDER_LINES(order_id, product_id, customer_id FK, qty)   PK = {order_id, product_id}
    1001 | P-7 | 42 | 2
    1001 | P-9 | 42 | 1
    1002 | P-7 | 42 | 5

Проверим, что аномалии ушли. Update: город покупателя 42 теперь в одной строке CUSTOMERS — UPDATE задевает ровно одну строку, рассинхрон невозможен физически. Insert: новый товар — это просто INSERT INTO products, заказ не нужен; новый покупатель — INSERT INTO customers. Delete: удаление заказа 1003 трогает только ORDER_LINES, строка покупателя 88 в CUSTOMERS остаётся нетронутой.

Заметьте, по какому принципу прошла декомпозиция: каждая FD с «частичным» детерминантом (customer_id -> customer_city, product_id -> product_name) превратилась в отдельную таблицу, где детерминант стал первичным ключом. Это и есть содержание нормальных форм 2NF и 3NF, к которым мы перейдём в следующих уроках. Аномалии — это «зачем», нормальные формы — это «как».

Попробуй сам

Дана таблица сотрудников и проектов:

ASSIGNMENTS(emp_id, emp_name, dept_id, dept_name, project_id, project_name, hours)
PK = {emp_id, project_id}

FD: emp_id -> emp_name, emp_id -> dept_id, dept_id -> dept_name, project_id -> project_name, {emp_id, project_id} -> hours.

Выполните на бумаге:

1. Опишите конкретный update anomaly: какой факт и в скольких строках придётся менять, если отдел меняет название.
2. Опишите конкретный insert anomaly: какой факт нельзя записать в пустую таблицу и почему именно primary key этому мешает.
3. Опишите конкретный delete anomaly: удаление какой строки может стереть факт о сотруднике или проекте.
4. Разложите таблицу на отдельные таблицы так, чтобы каждый факт хранился один раз. Сгруппируйте атрибуты по детерминантам FD и укажите PK и FK каждой получившейся таблицы.