2NF: устранение partial dependencies

Таблица в 1NF: значения атомарны, repeating groups нет, первичный ключ есть. Но 1NF — лишь синтаксический минимум. Она ничего не говорит о том, правильно ли распределены атрибуты по таблицам. Таблица в 1NF может быть набита избыточностью и страдать всеми аномалиями из прошлых уроков.

Вторая нормальная форма (2NF) делает первый содержательный шаг к устранению избыточности. Она работает с конкретным дефектом — partial dependency (частичной зависимостью). В этом уроке мы дадим точное определение 2NF, разберём, что такое partial dependency, и пошагово приведём реальную таблицу из 1NF в 2NF.

Prime и non-prime атрибуты — повторим

Определение 2NF опирается на два термина из урока про функциональные зависимости. Закрепим их, потому что дальше они работают в каждом предложении.

• Prime attribute (ключевой атрибут) — атрибут, входящий хотя бы в один candidate key таблицы.
• Non-prime attribute (неключевой атрибут) — атрибут, не входящий ни в один candidate key.

Возьмём таблицу строк заказа в 1NF:

ORDER_ITEMS
order_id | product_id | product_name | unit_price | qty
PK = {order_id, product_id}

Здесь единственный candidate key — пара {order_id, product_id}. Значит, prime-атрибуты — это order_id и product_id (они входят в ключ). Non-prime — product_name, unit_price, qty (в ключ не входят). 2NF — это правило именно про non-prime атрибуты.

Что такое partial dependency

Partial dependency (частичная зависимость) — это ситуация, когда non-prime атрибут функционально зависит от части составного candidate key, а не от ключа целиком.

Разберём по словам. Во-первых, речь о non-prime атрибуте. Во-вторых, ключ должен быть составным — из двух и более атрибутов; у ключа из одного атрибута «части» нет, поэтому partial dependency для него невозможна в принципе. В-третьих, зависимость идёт от собственного подмножества ключа.

Выпишем FD таблицы ORDER_ITEMS:

{order_id, product_id} -> qty            qty зависит от ВСЕГО ключа    -- полная
product_id             -> product_name   product_name от ЧАСТИ ключа   -- ЧАСТИЧНАЯ
product_id             -> unit_price     unit_price  от ЧАСТИ ключа    -- ЧАСТИЧНАЯ

qty зависит от полного ключа {order_id, product_id} — количество имеет смысл только для пары «заказ + товар». Это полная функциональная зависимость, она в порядке. А вот product_name и unit_price зависят только от product_id — то есть от части составного ключа. Это и есть partial dependencies.

Почему partial dependency — это плохо? Потому что она порождает ровно ту избыточность, которая ведёт к аномалиям. Раз product_name зависит только от product_id, то для одного товара название одинаково во всех строках заказов с этим товаром. Посмотрите на данные:

order_id | product_id | product_name | unit_price | qty
---------+------------+--------------+------------+----
1001     | P-7        | Клавиатура   | 1500       | 2
1002     | P-7        | Клавиатура   | 1500       | 5     <- "Клавиатура", 1500 повторяются
1003     | P-7        | Клавиатура   | 1500       | 1     <- ещё раз
1001     | P-9        | Мышь         | 800        | 1

«Клавиатура» и цена 1500 продублированы в каждой строке с товаром P-7. Это полный набор аномалий: переименование товара требует UPDATE множества строк (риск рассинхрона); новый товар без заказа не вставить, ведь order_id входит в PK (insert anomaly); удаление последней строки заказа с товаром стирает сам факт существования товара (delete anomaly).

Определение 2NF

Таблица находится в 2NF, если она в 1NF и не содержит ни одной partial dependency — то есть каждый non-prime атрибут полностью функционально зависит от всего candidate key (от каждого candidate key, если их несколько).

Из определения следует практичное наблюдение. Partial dependency возможна, только когда ключ составной. Поэтому:

Декомпозиция в 2NF: пошагово

Приведём ORDER_ITEMS к 2NF. Алгоритм: для каждой части составного ключа, от которой что-то частично зависит, создаём отдельную таблицу.

Шаг 1. Найти все candidate keys. Считаем замыкания. {order_id, product_id}+ = все атрибуты — это candidate key. Подмножества: {product_id}+ = {product_id, product_name, unit_price} — не все; {order_id}+ = {order_id} — не все. Других кандидатов нет. Единственный candidate key — {order_id, product_id}.

Шаг 2. Выписать FD и пометить partial. Уже сделали выше: product_id -> product_name и product_id -> unit_price частичные; {order_id, product_id} -> qty полная.

Шаг 3. Вынести каждую группу частично-зависимых атрибутов в свою таблицу. Атрибуты, зависящие от product_id, переезжают в таблицу PRODUCTS, где product_id становится первичным ключом:

PRODUCTS (2NF)
product_id PK | product_name | unit_price
--------------+--------------+-----------
P-7           | Клавиатура   | 1500
P-9           | Мышь         | 800

Шаг 4. В исходной таблице оставить полностью-зависимые атрибуты и ключи. В ORDER_ITEMS остаётся qty (зависит от полного ключа) и сам составной ключ; product_id превращается в foreign key на PRODUCTS:

ORDER_ITEMS (2NF)
order_id | product_id FK | qty       PK = {order_id, product_id}
---------+---------------+----
1001     | P-7           | 2
1002     | P-7           | 5
1003     | P-7           | 1
1001     | P-9           | 1

CREATE TABLE products (
    product_id   TEXT PRIMARY KEY,
    product_name TEXT NOT NULL,
    unit_price   NUMERIC NOT NULL
);
CREATE TABLE order_items (
    order_id   INT  NOT NULL,
    product_id TEXT NOT NULL REFERENCES products(product_id),
    qty        INT  NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Шаг 5. Проверить результат. PRODUCTS: ключ product_id одноатомный, значит 2NF выполняется автоматически. ORDER_ITEMS: единственный non-prime атрибут qty зависит от полного ключа {order_id, product_id} — partial dependency нет. Обе таблицы в 2NF.

Проверим, что аномалии ушли. Переименование товара — UPDATE одной строки в PRODUCTS, рассинхрон невозможен. Новый товар — INSERT INTO products без всякого заказа. Удаление последней строки заказа с товаром трогает только ORDER_ITEMS, строка товара в PRODUCTS цела. И декомпозиция lossless: соединив ORDER_ITEMS и PRODUCTS по product_id, мы в точности восстановим исходную таблицу — общий атрибут product_id является ключом таблицы PRODUCTS, что гарантирует соединение без лишних строк (формальный критерий разберём в модуле про углублённую нормализацию).

Как быстро увидеть нарушение 2NF

На практике редко выписывают полный набор FD формально. Полезно иметь быстрый способ заметить partial dependency «на глаз», а затем подтвердить его проверкой.

Эвристика такая. Partial dependency возможна, только если у таблицы составной ключ. Поэтому первый вопрос всегда: ключ из одного атрибута или из нескольких? Если из одного — таблица уже в 2NF, дальше не смотрим. Если ключ составной — смотрим на non-prime атрибуты и для каждого спрашиваем: «этот атрибут зависит от всей комбинации ключа или достаточно его части?»

Прогоним вопрос по ORDER_ITEMS(order_id, product_id, product_name, unit_price, qty):

• qty — «сколько штук?» Ответ требует знать и заказ, и товар. Зависит от всего ключа. В порядке.
• product_name — «как называется?» Достаточно знать product_id; order_id для ответа не нужен. Зависит от части — partial dependency.
• unit_price — «какая цена?» Достаточно product_id. Зависит от части — partial dependency.

Признак-маркер избыточности, который виден прямо в данных: если значение non-prime атрибута повторяется во всех строках с одним значением части ключа — это почти наверняка partial dependency. «Клавиатура» повторяется в каждой строке с product_id = P-7 независимо от order_id — сигнал, что product_name привязан к product_id, а не к полному ключу.

быстрая проверка 2NF:
  1. ключ одноатомный?         -> да: таблица в 2NF, стоп
  2. для каждого non-prime A:  нужен ли ВЕСЬ ключ, чтобы определить A?
       нужна только часть      -> partial dependency, 2NF нарушена
  3. маркер в данных: A повторяется на каждое значение части ключа

Worked example: ещё одна таблица

Закрепим на таблице студентов в 1NF:

ENROLLMENTS
student_id | course_id | student_name | course_title | grade
PK = {student_id, course_id}
FD: student_id -> student_name
    course_id  -> course_title
    {student_id, course_id} -> grade

student_name зависит от student_id — partial. course_title зависит от course_id — partial. grade зависит от полного ключа — полная. Здесь две части ключа порождают зависимости, значит будет две новые таблицы:

STUDENTS(student_id PK, student_name)
COURSES(course_id PK, course_title)
ENROLLMENTS(student_id FK, course_id FK, grade)   PK = {student_id, course_id}

student_name теперь хранится один раз на студента, course_title — один раз на курс, а ENROLLMENTS содержит только оценку — факт, который действительно принадлежит паре «студент + курс». Все три таблицы в 2NF.

Попробуй сам

Дана таблица назначений в 1NF:

PROJECT_WORK
emp_id | project_id | emp_name | emp_rate | project_name | hours_logged
PK = {emp_id, project_id}

FD: emp_id -> emp_name, emp_id -> emp_rate, project_id -> project_name, {emp_id, project_id} -> hours_logged.

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

1. Назовите prime- и non-prime атрибуты таблицы.
2. Выпишите все FD и для каждой укажите, partial она или полная. Обоснуйте каждое решение.
3. Приведите таблицу к 2NF. Сколько новых таблиц получится и почему именно столько? Укажите PK и FK каждой.
4. Покажите на конкретной операции один update anomaly, который существовал в исходной таблице и исчез после декомпозиции.