Аномалии и нормальные формы 1NF, 2NF, 3NF

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

Таблица, которая врёт

Вот наша таблица и её зависимости из прошлых уроков. Ключ (student_id, course_id).

enrollments_flat
student_id | student_name | course_id | course_title | teacher | room | grade
-----------+--------------+-----------+--------------+---------+------+------
         1 | Петя         |        10 | SQL          | Иванова |  101 |     5
         1 | Петя         |        20 | Python       | Смирнов |  202 |     3
         2 | Аня          |        10 | SQL          | Иванова |  101 |     4
         3 | Олег         |        10 | SQL          | Иванова |  101 |  NULL

student_id → student_name        course_id → course_title, teacher
teacher → room                   student_id, course_id → grade

Таблица работает, пока её только читают. Неприятности начинаются при изменениях. Их называют аномалиями, и их три вида.

Аномалия обновления. Иванова переехала в кабинет 105. Её кабинет записан в трёх строках, по одной на каждого студента курса SQL. Обновишь две, пропустишь третью, и таблица начнёт утверждать, что у Ивановой два кабинета одновременно.

Аномалия вставки. Школа открывает курс «Go» с преподавателем Козловым. Записать его некуда: ключ таблицы включает student_id, а студентов на курсе пока нет. Первичный ключ не бывает NULL, помнишь урок про целостность. Факт «есть курс Go» нельзя сохранить без постороннего факта «на него кто-то записан».

Аномалия удаления. Петя бросил Python. Удаляем его строку, и вместе с ней исчезает единственная запись о том, что курс Python существует и его ведёт Смирнов в кабинете 202. Удалили одно, потеряли другое.

У всех трёх аномалий один корень: в одной таблице хранятся факты о разных вещах. Факт «у курса такой-то преподаватель» повторяется в каждой строке записи на курс. Нормализация раскладывает такие факты по отдельным таблицам, чтобы каждый жил в одном месте.

Первая нормальная форма

1NF требует одного: в каждой клетке одно значение. Никаких списков через запятую и никаких колонок phone1, phone2, phone3. Это правило атомарности из урока про отношения. Наша таблица ему соответствует, едем дальше.