Нормализация базы данных: 1НФ, 2НФ и 3НФ на одном примере

Таблица с аномалиями, функциональные зависимости и ключ, пошаговое приведение к 3НФ, коротко про НФБК и как оформить нормализацию в лабораторной.

Определения нормальных форм легко выучить и трудно применить: на лабораторной дают таблицу и просят «привести к 3НФ», а непонятно, с чего начать. Ниже одна таблица шаг за шагом приводится к третьей нормальной форме. На каждом шаге видно, какая зависимость мешает и как её убрать. В конце коротко про НФБК и порядок оформления ответа. Итоговая схема проверена в SQLite.

Исходная таблица и её проблемы

Деканат ведёт ведомость экзаменов так:

Зачётка ФИО Группа Куратор Экзамены
1001 Белова А. ИВТ-21 Орлов С. П. Базы данных (Соколова Е. А., 144 ч): 5; Программирование (Ким Д. В., 180 ч): 4
1002 Волков Б. ИВТ-21 Орлов С. П. Базы данных (Соколова Е. А., 144 ч): 3
1003 Гусева В. ИВТ-22 Лебедева Т. И. Программирование (Ким Д. В., 180 ч): 5

В столбце «Экзамены» лежит целый список. По такой таблице простым запросом SQL не посчитать средний балл по дисциплине и не найти всех, кто получил 3. Это первое нарушение.

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

Нормализация опирается на функциональные зависимости (ФЗ). Запись X → Y значит: если в двух строках совпадает X, то совпадает и Y. Например, Зачётка → ФИО: один номер зачётки всегда принадлежит одному студенту.

ФЗ берутся из смысла данных, а не из конкретных строк. Для нашей ведомости правила такие: у группы один куратор, у дисциплины один лектор и фиксированное число часов, студент получает по дисциплине одну оценку. Отсюда:

  1. Зачётка → ФИО, Группа
  2. Группа → Куратор
  3. Дисциплина → Лектор, Часы
  4. {Зачётка, Дисциплина} → Оценка

Данные могут опровергнуть зависимость, но не доказать её. Проверить, что в таблице нет противоречий, можно запросом. Он работает с таблицей в 1НФ из следующего раздела, в SQL столбцы названы латиницей: course для дисциплины, lecturer для лектора, hours для часов. Если запрос вернул строку, у дисциплины нашлись два разных лектора или два разных числа часов:

SELECT course
FROM exams_1nf
GROUP BY course
HAVING COUNT(DISTINCT lecturer) > 1 OR COUNT(DISTINCT hours) > 1;

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

Таблица в 1НФ, если в каждой ячейке одно значение и нет повторяющихся групп. Разворачиваем список экзаменов в отдельные строки:

Зачётка ФИО Группа Куратор Дисциплина Лектор Часы Оценка
1001 Белова А. ИВТ-21 Орлов С. П. Базы данных Соколова Е. А. 144 5
1001 Белова А. ИВТ-21 Орлов С. П. Программирование Ким Д. В. 180 4
1002 Волков Б. ИВТ-21 Орлов С. П. Базы данных Соколова Е. А. 144 3
1003 Гусева В. ИВТ-22 Лебедева Т. И. Программирование Ким Д. В. 180 5

Теперь нужен ключ: набор столбцов, который однозначно определяет строку и из которого ничего нельзя выбросить. Проверим {Зачётка, Дисциплина}, собрав всё, что из него выводится (это называют замыканием):

  • из Зачётка получаем ФИО, Группа;
  • из Группа получаем Куратор;
  • из Дисциплина получаем Лектор, Часы;
  • из пары получаем Оценка.

Вышли все восемь столбцов, значит, это ключ. Одной Зачётки мало: из неё не выводятся дисциплина и оценка. Одной Дисциплины тоже мало.

Таблица в 1НФ, но работать с ней всё ещё неудобно. В ней три аномалии:

  • Обновление. Лектор «Баз данных» сменился, и менять его нужно в каждой строке с этой дисциплиной. Пропустили одну, и в базе два лектора одного курса.
  • Вставка. Новую дисциплину «Сети» не добавить, пока по ней нет ни одной оценки: без студента не заполнить зачётку, а часть ключа не может быть пустой.
  • Удаление. Удалили единственную строку Гусевой, и вместе с ней пропало, что куратор ИВТ-22 Лебедева.

Вторая нормальная форма

Таблица во 2НФ, если она в 1НФ и каждый неключевой столбец зависит от всего ключа, а не от его части. Неключевой столбец не входит ни в один ключ. Если все ключи таблицы состоят из одного столбца, таблица в 1НФ уже во 2НФ.

Бумажная таблица, разрезанная ножницами на несколько аккуратных карточек, рядом войлочные скрепки

Наш ключ составной, и две зависимости бьют по его части:

  • ФИО, Группа и через группу Куратор зависят только от Зачётки;
  • Лектор и Часы зависят только от Дисциплины.

Полностью от ключа зависит только Оценка. Выносим частичные зависимости в отдельные таблицы, а в исходной оставляем ключ и оценку:

  • Студенты(Зачётка, ФИО, Группа, Куратор), ключ Зачётка;
  • Дисциплины(Дисциплина, Лектор, Часы), ключ Дисциплина;
  • Оценки(Зачётка, Дисциплина, Оценка), ключ {Зачётка, Дисциплина}.

Разбиение идёт по правилу: при зависимости X → Y одна таблица получает X и Y, другая X и всё остальное. По теореме Хита такое разбиение без потерь: соединение частей по X возвращает исходную таблицу, без лишних и пропавших строк.

Аномалии с дисциплинами исчезли: лектор хранится в одной строке, а «Сети» добавляются в Дисциплины без студентов.

Третья нормальная форма

Таблица в 3НФ, если она во 2НФ и неключевые столбцы не зависят от других неключевых. Иначе говоря, нет транзитивных зависимостей вида ключ → A → B, где A не ключ.

В Студентах такая цепочка есть: Зачётка → Группа → Куратор. Куратор зависит от группы, а не от студента. Из-за этого куратор повторяется у каждого студента группы, а удаление последнего студента группы стирает её куратора. Выносим зависимость Группа → Куратор:

  • Группы(Группа, Куратор), ключ Группа;
  • Студенты(Зачётка, ФИО, Группа), ключ Зачётка, Группа ссылается на Группы.

В Дисциплинах и Оценках транзитивных зависимостей нет. Итоговая схема в SQL, где record_book хранит номер зачётки, student ФИО, curator куратора, grade оценку:

CREATE TABLE groups (
    group_name TEXT PRIMARY KEY,
    curator    TEXT NOT NULL
);

CREATE TABLE students (
    record_book TEXT PRIMARY KEY,
    student     TEXT NOT NULL,
    group_name  TEXT NOT NULL REFERENCES groups(group_name)
);

CREATE TABLE courses (
    course   TEXT PRIMARY KEY,
    lecturer TEXT NOT NULL,
    hours    INTEGER NOT NULL
);

CREATE TABLE grades (
    record_book TEXT REFERENCES students(record_book),
    course      TEXT REFERENCES courses(course),
    grade       INTEGER NOT NULL,
    PRIMARY KEY (record_book, course)
);

Проверка без потерь: соединяем четыре таблицы и сравниваем с таблицей в 1НФ через EXCEPT в обе стороны. Здесь exams_1nf хранит таблицу из раздела про 1НФ, столбцы в том же порядке. Второй запрос такой же, только части стоят наоборот. Оба вернули пустой результат, то есть строки совпали полностью.

SELECT s.record_book, s.student, g.group_name, g.curator,
       c.course, c.lecturer, c.hours, gr.grade
FROM grades gr
JOIN students s ON s.record_book = gr.record_book
JOIN groups g   ON g.group_name = s.group_name
JOIN courses c  ON c.course = gr.course
EXCEPT
SELECT * FROM exams_1nf;

Коротко про НФБК

Нормальная форма Бойса-Кодда строже третьей: в каждой нетривиальной зависимости X → Y левая часть X должна быть ключом или содержать ключ. 3НФ допускает одно исключение: X может быть не ключом, если Y входит в какой-нибудь ключ. Таблица в 3НФ, но не в НФБК, встречается редко и обычно с двумя пересекающимися составными ключами.

Классический пример: Практика(Студент, Дисциплина, Преподаватель). Каждый преподаватель ведёт практику только по одной дисциплине, по одной дисциплине их несколько, а студент по дисциплине ходит к одному преподавателю. Зависимости: {Студент, Дисциплина} → Преподаватель и Преподаватель → Дисциплина. Ключей два: {Студент, Дисциплина} и {Студент, Преподаватель}. Все столбцы входят в ключи, поэтому 3НФ выполнена. А Преподаватель → Дисциплина нарушает НФБК: преподаватель не ключ.

Разбиение: Преподаватели(Преподаватель, Дисциплина) и Прикрепления(Студент, Преподаватель). Цена у него есть: правило «у студента по дисциплине один преподаватель» больше не обеспечивается ключом одной таблицы. Его придётся проверять отдельно. Это стоит написать в выводах лабораторной.

Как оформить нормализацию в лабораторной

  1. Исходная таблица с примером данных и её ключ.
  2. Аномалии обновления, вставки и удаления на конкретных строках.
  3. Список ФЗ, и для каждой одно предложение, из какого правила она следует.
  4. Ключ с проверкой через замыкание.
  5. Для каждой нормальной формы: определение одной фразой, какая зависимость его нарушает, на какие таблицы делим, их ключи и внешние ключи.
  6. Итоговая схема в виде CREATE TABLE или диаграммы.
  7. Проверка: соединение итоговых таблиц даёт исходную, каждая ФЗ обеспечивается ключом какой-то таблицы.

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

Как с этим помогает Tilpi

Загрузите методичку по базам данных в материалы предмета (с тарифа «Лайт»), и чат задания будет разбирать нормализацию по определениям из неё. Он объясняет решение по шагам, а рядом с ответом даёт ссылку на страницу методички. Можно попросить проверить ваш список зависимостей и ключ или задать вам вопросы по теме перед защитой. Чат по приложенному к сообщению файлу работает и без тарифа: нужна подтверждённая почта, действуют бесплатные лимиты.

ИИ для учёбы. По вашим лекциям.

Пришлите в чат лекцию, задачу или свой текст. Тилпи объяснит тему со ссылкой на страницу, разберёт решение по шагам и проверит оформление.

Разобрать нормализацию в Tilpi