Обзор нормализации баз данных

Разработанная в 1970-х годах Э.Ф. Коддом, нормализация баз данных является стандартным требованием для многих баз данных. Нормализация — это техника, которая может помочь избежать аномалий данных и других проблем при управлении данными. Она включает преобразование таблицы через различные стадии: первая нормальная форма, вторая нормальная форма, третья нормальная форма и т.д.

Цель нормализации:

• Устранение избыточности данных (и, следовательно, экономия места)
• Упрощение внесения изменений в данные и предотвращение аномалий при их внесении
• Упрощение применения ограничений целостности ссылок
• Создание легкопонимаемой структуры, которая близко соответствует ситуации, которую представляют данные, и позволяет для роста

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

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

Расположение:

• Код местоположения: 11
• Название местоположения: Сады Кирстенбош

содержит следующие три растения:

• Код растения: 431
• Название растения: Leucadendron
• Категория почвы: A
• Описание почвы: Песчаник

• Код растения: 446
• Название растения: Protea
• Категория почвы: B
• Описание почвы: Песчаник/известняк

• Код растения: 482
• Название растения: Erica
• Категория почвы: C
• Описание почвы: Известняк

Расположение:

• Код местоположения: 12
• Название местоположения: Карбонбергские горы

содержит следующие два растения:

• Код растения: 431
• Название растения: Leucadendron
• Категория почвы: A
• Описание почвы: Песчаник

• Код растения: 449
• Название растения: Restio
• Категория почвы: B
• Описание почвы: Песчаник/известняк

Таблицы в реляционной базе данных имеют вид таблицы (MariaDB, как и большинство современных СУБД, представляет собой реляционную базу данных), поэтому давайте перегруппируем эти данные в виде таблицы:

Данные о растениях, представленные в табличной форме

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

Попытка создать таблицу с данными о растениях

Однако такая таблица мало пригодится. Первые три строки фактически представляют собой группу, все они принадлежат одному и тому же местоположению. Если вы возьмете третью строку в отдельности, данные неполны, так как вы не можете определить местоположение, где растёт Erica. Кроме того, в текущем виде таблицы вы не можете использовать код местоположения или любые другие поля в качестве первичного ключа (помните, первичный ключ — это поле или набор полей, которые уникально идентифицируют одну запись). Таблица мало полезна, если вы не можете уникально идентифицировать каждую запись в ней.

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

Каждая запись существует отдельно

Обратите внимание, что код местоположения сам по себе не может быть первичным ключом. Он не уникально идентифицирует строку данных. Таким образом, первичный ключ должен быть комбинацией кода местоположения и кода растения. Вместе эти два поля уникально идентифицируют строку данных. Подумайте об этом. Вы никогда не добавите один и тот же тип растения более одного раза в определенное местоположение. Как только вы знаете, что он встречается в данном местоположении, этого достаточно. Если вы хотите записать количество растений в местоположении (в этом примере вас интересует распространение растений), вам не нужно добавлять новую запись для каждого растения; вместо этого просто добавьте поле «количество». Если по какой-либо причине вы собираетесь добавлять более одного экземпляра комбинации «растение/местоположение», вам нужно добавить что-то ещё в ключ, чтобы сделать его уникальным.

Итак, теперь данные могут быть представлены в табличной форме, но есть и другие проблемы. Таблица хранит информацию о том, что код 11 относится к Садам Кирстенбош трижды! Помимо потерь места, есть ещё одна серьёзная проблема. Внимательно посмотрите на данные ниже.

Аномалия данных

Заметили что-то странное? Поздравляем, если вы это сделали! Название Кирстенбош написано с ошибкой во второй записи. Представьте себе, как трудно будет обнаружить эту ошибку в таблице с тысячами записей! Использование структуры таблицы выше значительно увеличивает вероятность аномалий данных.

Решение простое. Удалите дублирование. Вы ищете частичные зависимости — другими словами, поля, которые зависят от части ключа, а не от всего ключа. Поскольку как код местоположения, так и код растения составляют ключ, вы ищете поля, которые зависят только от кода местоположения или от имени растения.

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

Удаление полей, не зависящих от всего ключа

Очевидно, что вы не можете удалить данные и полностью исключить их из базы данных. Вы удаляете их и помещаете в новую таблицу, содержащую поля с частичной зависимостью и поля, от которых они зависят. Для каждого поля ключа в частичной зависимости вы создаёте новую таблицу (в данном случае оба поля уже являются частью первичного ключа, но это не всегда так). Итак, вы определили название растения, описание почвы и категорию почвы как зависящие от кода растения. Новая таблица будет содержать код растения в качестве ключа, а также название растения, категорию и описание почвы, как показано ниже:

Создание новой таблицы с данными о местоположении

Вы делаете то же самое с данными о местоположении, как показано ниже:

Создание новой таблицы с данными о местоположении

Обратите внимание, как эти таблицы устраняют проблему дублирования, возникшую ранее? Существует только одна запись, содержащая Kirstenbosch Gardens, поэтому вероятность заметить ошибку в написании намного выше. И вы не тратите место, храня имя в различных записях. Обратите внимание, что поля кода местоположения и кода растения дублируются в двух таблицах. Эти поля образуют связь, позволяющую связать различные растения с различными местоположениями. Очевидно, что нет способа устранить дублирование этих полей без потери связи в целом, но гораздо эффективнее хранить небольшой код повторно, чем большой текст.

Но таблица всё ещё не идеальна. По-прежнему существует вероятность появления аномалий. Внимательно изучите таблицу ниже:

Ещё одна аномалия

Проблема в таблице выше заключается в том, что Restio был связан с песчаником, тогда как на самом деле, имея категорию почвы B, он должен быть смесью песчаника и известняка (категория почвы определяет описание почвы в этом примере). Опять же, вы храните данные избыточно. Связь между категорией почвы и описанием почвы хранится полностью для каждого растения. Как и прежде, решение состоит в том, чтобы убрать эти избыточные данные и поместить их в свою собственную таблицу. На данном этапе вы фактически ищете транзитивные отношения, или отношения, где поле, не являющееся ключевым, зависит от другого поля, не являющегося ключевым. Описание почвы, хотя в некотором смысле зависит от кода растения (при рассмотрении на предыдущем шаге, это казалось частичной зависимостью), на самом деле зависит от категории почвы. Поэтому описание почвы должно быть удалено. Ещё раз, удалите его и поместите в новую таблицу вместе с его фактическим ключом (категория почвы), как показано в таблицах ниже:

Данные о растениях после удаления описания почвы

Создание новой таблицы с описанием почвы

Вы снова сократили вероятность аномалий. Теперь невозможно ошибочно предположить, что категория почвы B связана с чем-либо, кроме смеси песчаника и известняка. Связи между описанием почвы и категорией почвы хранятся только в одной таблице — новой таблице почвы, где можно быть более уверенным в их точности.

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

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