BCNF и денормализация

В прошлом уроке мы довели таблицу школы до 3NF, и аномалии исчезли. Но 3NF не закрывает всех случаев: бывают таблицы в 3NF, где факт всё ещё повторяется. Этот урок про форму построже, нормальную форму Бойса-Кодда, и про обратное движение: когда хорошую схему сознательно портят ради скорости чтения.

Репетиторы

Школа нанимает репетиторов. Правила такие:

• каждый репетитор ведёт ровно один предмет;
• у студента по каждому предмету один репетитор;
• по одному предмету работает несколько репетиторов.

tutoring
student | subject    | tutor
--------+------------+---------
Петя    | математика | Орлова
Петя    | физика     | Зуев
Аня     | математика | Орлова
Олег    | математика | Белов

student, subject → tutor
tutor            → subject

Найдём ключи замыканием. Колонка student не встречается справа, значит, она в каждом ключе. subject⁺ даёт всю таблицу. tutor⁺ тоже: по tutor узнаём subject. Ключей два: (student, subject) и (student, tutor).

Проверим 3NF. Неключевых колонок нет вообще: каждая входит хотя бы в один ключ. Формально 3NF разрешает зависимость tutor → subject, потому что subject входит в ключ. Таблица в 3NF.

А аномалии на месте. Предмет Орловой записан в каждой строке её студентов: переведи её на физику и обновляй все строки. Нового репетитора без студентов не записать: student входит в любой ключ. Причина в зависимости tutor → subject, у которой слева не ключ.

Нормальная форма Бойса-Кодда

BCNF, нормальная форма Бойса-Кодда, формулируется в одну строку: левая часть каждой нетривиальной зависимости должна быть суперключом. Без исключений.

3NF делает поблажку: зависимость от неключа терпима, если справа колонка из какого-нибудь ключа. BCNF поблажек не делает. Поэтому любая таблица в BCNF находится и в 3NF, а обратное неверно, как показали репетиторы. На практике расхождение встречается редко: нужны два пересекающихся составных ключа. В большинстве схем 3NF и BCNF совпадают.

Лечение то же, что раньше: выносим зависимость-нарушителя в свою таблицу.

tutors(tutor, subject)          tutor → subject, ключ tutor
assignments(student, tutor)     ключ (student, tutor)

Предмет каждого репетитора теперь записан один раз, а нового репетитора можно добавить без студентов.