Нормализация базы данных, разбираемся с нормальными формами на примере

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

Начнем мы с большой «сырой» таблицы, которую будем нормализовать и разбирать каждый шаг, что мы с ней делаем.

Таблица «Студенты»

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

Что означает каждый столбец

Самый первый шаг — это разобраться что есть что в таблице. Выявим логику и взаимосвязи.

На этом этапе мы можем назвать ключевые столбцы — это студент, группа и предметы.

Что в этой таблице не так?

Немного предвосхищая этапы, попробуем сформулировать что не так в этой таблице.

• Столбцы таблицы, такие как — «Предметы», «Языки» — содержат несколько значений, не атомарные (нарушение 1НФ).
• Хобби зависит только от студента, а не от предмета и группы. В то время как языки зависят от двух ключей — студент и группа (нарушение 2НФ).
• Куратор зависит от группы, а та в свою очередь от студента. Выходит такая транзитивная зависимость куратора от студента, это должно устраняться 3НФ.
• Языки, хобби, предметы — независимые многозначные данные (нарушение 4НФ).
• Возможны ограничения на такие сложные связи как между студентом, предметом и преподавателем, или языком, студентом и группой которые можно разложить (нарушение 5НФ).
• Не указана дата записи, и мы не можем отслеживать изменения со временем (подходит для 6НФ). К примеру, отслеживание переход студента на второй курс и смена группы с 1xx -> 2xx.

Первая нормальная форма (1НФ)

Требования этой формы заключается в атомарности полей и отсутствии дублей строк.

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

Отсутствие повторяющихся кортежей — строки таблицы (кортежи) должны быть уникальными, никаких дубликатов строк в таблице быть не должно.

Наша таблица не содержит дублей, но не обладает атомарностью, давайте это исправим.

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

Отсюда можно предположить, что:

• Петров ведёт Математику,
• Сидоров — Физику,
• Иванова — Химию.

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

Я расписал только данные для студента Иванова, для которого их должно получиться 16 строк (разные комбинации вариантов данных, которые мы атомизируем).

Но учитывая скрытую логику данных, мы получим таблицу поменьше (назовем её студенты_1НФ) — тут на Иванова приходит только 8 строк:

Теперь мы имеем только атомарные поля, а дублей строк не было изначально, т.е. таблица соответствует 1й НФ.

Такая «взрывная комбинация» всех значений — это прямое соблюдение 1НФ.

Вторая нормальная форма (2НФ)

То как у нас размножились строки — это не нормально c т.з. «полезности» таких данных. Это еще называется взрыв Мы сможем решить эту проблему на этапе 4НФ.

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

В начале нормализации мы определили три ключа — студент, предмет и группа. В рамках одной таблицы — это составной ключ, но очевидно, что почти все наши данные в примере не зависят полностью от всего ключа. Чтобы удовлетворить требованиям 2НФ, необходимо провести декомпозицию нашей таблицы так, чтобы образовать таблицы, в которых атрибуты сопоставляются только с ключами, от которых зависят.

Таблица предметы

Так у нас появляется таблица — предметы, куда уходит столбец с преподавателями.

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

Таблица группы

Столбцы куратор и курс — зависят только группы. Они также переносятся в отдельную таблицу.

Как видите, если просто вынести столбцы — то мы получим нарушение по 1НФ — у нас есть дубли кортежей. Убираем их. Остаётся лишь две строки.

Таблица студент_группа_языки

В описании столбца языки, чувствуется какая то не досказанность. Есть бекграунд студента — те языки (один или несколько), которые он уже изучал. А есть языки, которые может или требуется изучать студенту, благодаря выбранной специальности (группе).

В первом случае мы должны связать какие-то языки с ключом студент, во-втором — с ключом группа.

Но пока такого уточнения нет, мы вынесем данные о языках в таблицу с составным ключом — студент+группа.

Можно, к примеру, предположить, что Иванов изучал в школе немецкий, а ВУЗ на его специальности предлагает изучать только английский, т.к. Сидоров в той же группе имеет только один вариант. Т.е. иногда данные можно извлечь, анализируя доступные кейсы. И если кейсов много, то довольно хорошо можно восстановить целостную картину.

Таблица студенты

В результате преобразований таблица студенты_1НФ потеряет несколько столбцов:

Но теперь она не соответствует 1й НФ — есть дубли кортежей. Уберем их и получим таблицу студенты_2НФ:

Третья нормальная форма (3НФ)

Эта форма требует соблюдения 2НФ и должна устранять зависимости между не ключевыми столбцами. Такие зависимости называются транзитивными. Обычно это приводит к появлению новых таблиц и ключей.

В нашем примере мы изначально определили все зависимости между столбцами, и на втором этапе (2НФ), благодаря этому, выполнили требования и 3НФ. Но давайте представим, что мы изначально определили только два ключа — студент и предмет.

Тогда столбцы куратор, курс, язык — все они останутся в таблице студенты_2НФ. А группа — будет обычным атрибутом, а не ключевым столбцом.

Рассмотрим кортежи из такой таблицы студенты:

Мы заметим, что куратор и курс сопоставляются с ключом студент, но зависят на самом деле от другого атрибута студента — его группы, т.е. транзитивно:

Студент -> Группа, а Группа -> Куратор, Курс.

Если после 2НФ выявляются такие зависимости, то требование 3НФ — избавиться от них: и мы создаём таблицу группы на этом этапе, а не на этапе 2НФ.

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

Четвертая нормальная форма (4НФ)

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

Часть многозначных атрибутов — языки и преподаватели мы уже вынесли в отдельные таблицы. Остались предметы и хобби, которые не связаны между собой. Когда мы применили 1НФ — то получили т.н. «взрыв» кортежей — и теперь в нашей таблице студентов множество строк, которые являются отголоском этого взрыва.

4НФ предписывает создать отдельные таблицы для таких атрибутов.

Таблица студент_хобби

Эта таблица описывает связь один-ко-многим для студента и его хобби.

Таблица студент_предмет

Связь один-ко-многим для студента и предмета попадут в эту таблицу.

Таблица студенты

Убрав множественные данные (столбцы предмет и хобби) из нашей основной таблицы, мы опять увидим, что данные не соответствуют 1НФ из-за дублей:

Уберем их и получим таблицу студенты_4НФ:

Пятая нормальная форма (5НФ)

В нашем примере все взаимосвязи укладываются в зависимости одного параметра от другого (т.е. парные связи). Но если требуется явно зафиксировать допустимые комбинации между тройками (или более) сущностей, и их нельзя корректно восстановить из всех возможных парных связей — то 5НФ требует представить таблицу, отражающую только допустимые комбинации.

Cценарий для 5НФ: учебная аудитория

Добавим в наш пример учебную аудиторию, в которой проходит предмет, например Математика. Но при этом мы знаем, что студенты специальности ИТ-101 (Иванов и Сидоров), обучаются только в аудитории 203, а ИТ-102 (Петрова) — в 205, где есть проектор и дополнительные методические материалы.

Т.е. мы видим:

Предмет Математика читается в нескольких группах.

Для каждой группы может быть своя аудитория:

• ИТ-101 — аудитория 203
• ИТ-102 — аудитория 205 (оборудована проектором)

Предмет один и тот же (Математика), но проводится в разных аудиториях, в зависимости от группы.

Описание проблемы

Валидные комбинации всех 3 сущностей можно свести в таблицу:

Если рассматривать пары сущностей по отдельности:

• Группа — Предмет:
  • ИТ-101 изучает Математику
  • ИТ-102 изучает Математику
• Предмет — Аудитория:
  • Математика бывает в 203, бывает в 205

То эти пары верны, но при попытке восстановить тройки по ним, получится такая коллизия:

Появляются ложные комбинации, которых на самом деле не должно быть. Например, ИТ-101 не учит Математику в 205 — но исходя из пар это допустимо. Нужна 5НФ (создание таблицы валидных комбинации сущностей).

Но если добавить еще одну пару сущностей:

• Группа — Аудитория:
  • ИТ-101 бывает в 203
  • ИТ-102 бывает в 205

То по трем таблицам уже можно правильно составить валидные комбинации всех 3 сущностей. Здесь 5НФ уже не понадобится.

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

Шестая нормальная форма (6НФ)

Требование:

• Таблица должна быть в 5НФ.
• Требуется разделение по временным зависимостям: каждая таблица описывает одно значение, в одно время, по одному признаку.

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

Атрибут дата записи

Так как через год студенты, успешно сдавшие сессию, будут переведены на второй курс и изменят нумерацию своих групп на ИТ-201 и ИТ-202, то мы можем добавить атрибут «дата записи», который указывает с какого момента студент стал числиться в соответствующей группе. Кроме того, учеба когда то заканчивается. Значит можно добавить и дату окончания актуальности информации.

Добавим столбцы в таблицу и получим студенты_6НФ:

Мы видим, что студенты были зачислены на первый курс в определенную дату, и до сих пор числятся в соответствующих группах (дата окончания не задана). Перейдя на второй курс мы получим записи, где проставлена дата окончания и новые кортежи с номерами групп у студентов.

Это позволит отслеживать в том числе и переводы студента из группы в группу в рамках одного учебного года.

Похожие модификации мы делаем и для других таблиц, где данные могут меняться со временем. К примеру, студент записывается каждый семестр на соответствующие специальности предметы, значит таблица студент_предмет также пополняется датами.

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

Если после прочтения остались вопросы — это нормально: многие идеи нормализации становятся понятнее только с практикой, спрашивайте в комментариях.