Меньше избыточности благодаря нормализации базы данных

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

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

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

В следующей таблице показаны сохраненные данные о счетах-фактурах вымышленного поставщика офисного оборудования. Джон Паблик заказал для своей компании 10 мониторов, 12 ковриков для мыши и 1 офисное кресло. Заказ от Джейн Доу включает 2 ноутбука и 2 гарнитуры.

В базе данных интернет-магазина данным о счетах-фактурах приписываются номер счета («Inv. no.»), дата, клиент, номер клиента («Cust. no.»), адрес, номер позиции счета («Inv. item no.»), продукт, номер продукта («Prod. no.»), количество («No.») и цена. Каждая строка таблицы представляет собой одну запись данных. Такой набор данных называется кортежем.

Показанный выше раздел базы данных является примером плохого проектирования базы данных. На первый взгляд очевидно, что в таблице много лишних строк. Кроме того, значения в столбцах «клиент» и «адрес» содержат многозначные данные. Такая база данных называется денормализованной. Другими словами, она не соответствует правилам нормализации базы данных.

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

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

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

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

Чтобы проиллюстрировать преобразование реляционной базы данных в первую, вторую и третью нормальные формы, мы рассмотрим отдельные этапы нормализации реляционной базы данных на примере данных в таблице выше.

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

• Все данные являются атомарными
• Все столбцы таблицы содержат одинаковые значения

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

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

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

Для нормализации этих разделов необходимо выполнить следующую процедуру:

1. Разделите все данные с несколькими значениями на отдельные столбцы
2. Проверить значения в каждом столбце на сходство

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

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

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

Таблица, соответствующая второй нормальной форме, должна выполнять все требования первой нормальной формы в дополнение к следующим:

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

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

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

В приведенном примере возможный суперключ получается из атрибутов номера счета («Inv. no.»), номера клиента («Cust. no.») и номера позиции счета («Inv. item no.»), как показано в таблице ниже.

Ключ, состоящий из номера счета, номера клиента и номера позиции счета со значениями {124, 12, 1}, позволяет, например, четко обозначить запись данных, представляющую покупку ноутбука неизвестным:

Однако не вся информация в выбранном суперключе требуется для уникальной идентификации. Для идентификации отдельных записей данных достаточно комбинации номера счета-фактуры и номера позиции счета-фактуры — то есть подмножества суперключа. Такие ключи с минимальным количеством атрибутов называются кандидатами в ключи или альтернативными ключами.

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

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

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

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

Если внимательно посмотреть на таблицу примера, то можно заметить, что предпосылки для второй нормальной формы не выполняются, поскольку столбец даты зависит только от номера счета («Inv. no.»), а не от номера элемента счета («Inv. item no.»). То же самое относится к имени, фамилии, адресу улицы, городу, штату и почтовому индексу.

Чтобы преобразовать таблицу данных во вторую нормальную форму, все атрибуты, полностью зависящие от номера счета-фактуры, были перенесены в отдельную таблицу под названием «Invoice».

Таблица с остальными данными была названа «Invoice item».

После нормализации номер счета-фактуры («Inv. no.») содержится в обеих таблицах и связывает их вместе. Хотя атрибут функционирует как первичный ключ в таблице «Invoice», он используется как внешний ключ в таблице «Invoice item», а также является частью составного первичного ключа таблицы.

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

Если таблица должна быть преобразована к третьей нормальной форме, то должны быть выполнены все предпосылки первой и второй нормальной формы, а также следующие:

• Ни один неключевой атрибут не может быть транзитивно зависим от кандидата в ключи.

Транзитивная зависимость возникает, когда неключевой атрибут зависит от другого неключевого атрибута и, таким образом, косвенно от своего кандидата в ключи.

Данный шаблон базы данных нарушает условия третьей нормальной формы в нескольких местах:

В таблице «Invoice» имя и фамилия, а также адрес улицы, город, штат и почтовый индекс зависят не только от первичного ключа (номера счета), но и от номера клиента.

В таблице «Invoice Item» атрибуты product и price зависят не только от первичного ключа, полученного из номера счета и номера позиции счета, но и от номера продукта. Это специфическое условие также нарушает третью нормальную форму.

Чтобы устранить все зависимости между неключевыми атрибутами, соответствующие атрибуты были перенесены в отдельные таблицы, связанные друг с другом внешними ключами. В результате получились четыре нормализованные таблицы: «Счет-фактура», «Клиент», «Элемент счета-фактуры» и «Продукт».

Первичным ключом таблицы «Счет-фактура» является последовательный номер счета-фактуры. Каждому номеру счета присваивается дата выставления счета и номер клиента.

Более подробная информация о каждом заказчике хранится в таблице «Заказчик». Таблицы «Счет-фактура» и «Клиент» связаны между собой через номер клиента. Он используется как первичный ключ в таблице «Customer» и как внешний ключ в таблице «Invoice».

Таблица «Счет-фактура» является центральной таблицей в базе данных образца, содержащей информацию о том, какие товары должны быть указаны в счете-фактуре, а также о том, сколько товаров было заказано. Последовательный первичный ключ таблицы «Invoice item» формируется из номера счета-фактуры и номера позиции счета-фактуры. Соответствующие товары перечислены только в виде номеров товаров, которые выступают в качестве внешних ключей и связывают таблицу «Invoice item» с таблицей «Products».

Наконец, таблица «Продукты» содержит подробную информацию о соответствующих продуктах, такую как описание продукта и цена. Первичным ключом является серийный номер продукта.

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

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

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

Нормальная форма Бойса-Кодда — это ужесточение третьей нормальной формы. Для 3NF:

• Ни один неключевой атрибут не может быть транзитивно зависим от кандидата в ключи.

В нормальной форме Бойса-Кодда, однако:

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

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

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

В таблице ниже показаны два кандидата на ключ, каждый из которых состоит из двух атрибутов.

• Номер поставщика и номер продукта
• Номер поставщика и номер продукта

Оба ключа позволяют идентифицировать каждую отдельную запись данных. Единственным неключевым атрибутом является номер. Поскольку атрибут номер не зависит транзитивно ни от одного из ключевых кандидатов, таблица соответствует 3НФ.

С другой стороны, она не соответствует нормальной форме Бойса-Кодда, поскольку существует зависимость между атрибутами «номер поставщика» («V no.») и «поставщик» («Vendor»). Атрибут «номер поставщика» транзитивно зависит от ключевого кандидата, объединяющего номера поставщика и продукта; и наоборот, атрибут «поставщик» возникает из ключевого кандидата, объединяющего номер поставщика («V no.») и номер продукта («Prod. no.»).

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

Нормальная форма Бойса-Кодда предотвращает избыточность путем выявления ключевых атрибутов, перечисленных несколько раз пересекающимися ключевыми кандидатами. В приведенном выше примере преобразование к 3,5НФ предотвращает дублирование значений в столбце «Поставщик».

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

• Не существует многозначных зависимостей, если только они не являются тривиальными

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

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

Например, клиент с номером 234 заказал изделия 1-0023-D и 2-0023-D, которые должны быть доставлены по его адресу с почтовым индексом 12345. Для клиента 567 изделия 1-0023-D, 3-0023-D, 4-0023-D и 5-0023-D будут доставлены по почтовому индексу 56789.

Записи данных могут быть идентифицированы только с помощью суперключа, полученного из всех трех атрибутов — номера клиента, номера изделия и почтового индекса. Поскольку не существует неключевых атрибутов, база данных соответствует 3НФ. Более того, поскольку нет нетривиальных транзитивных зависимостей, она также соответствует 3,5НФ. Однако существуют многозначные зависимости: и атрибут номера товара, и атрибут почтового индекса зависят от атрибута номера клиента, но не связаны друг с другом.

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

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

Как показано в примере, четвертая нормальная форма устраняет избыточность, вызванную многозначными зависимостями, в данном случае конкретно в столбце ZIP-кода.

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

• Таблица не может быть разделена на части без потери информации.

Ниже приведен пример, демонстрирующий такой случай, в котором компания управляет веб-сайтом на базе TYPO3 и интернет-магазином Magento. За проекты программного обеспечения отвечают три сотрудника: Мэри Смит, Джордж Миллер и Джо Дэвис, каждый из которых имеет разную квалификацию.

В таблице показано, какая квалификация сотрудника применима к требованиям программного проекта.

Мэри Смит использует свои знания PHP и SQL для проекта Magento и использует SQL и JavaScript для сайта TYPO3. Джордж Миллер также работает с PHP для Magento и на JavaScript для TYPO3. Джо Дэвис участвует только в проекте TYPO3, работая как единственный программист с PHP. Из таблицы также видно, что для Magento требуется знание PHP и SQL, а для проекта TYPO3 — знание PHP, SQL и JavaScript.

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

Чтобы проверить, соответствует ли таблица 5НФ, разделите выходную таблицу «Квалификация сотрудника для развертывания проекта» на три таблицы: «Развертывание проекта», «Квалификация сотрудника» и «Требования к проекту».

Таблица «Развертывание проекта» показывает, кто из сотрудников участвует в каком программном проекте.

Таблица «Квалификация сотрудника» показывает, кто из сотрудников владеет каким языком программирования или языком баз данных.

Таблица «Требования к проекту» показывает, какая квалификация программиста требуется для того или иного проекта.

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

Ответ на этот вопрос дает объединенный запрос к базе данных по всем трем таблицам. Результат удивляет.

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

На практике вы редко встретите формулы базы данных, которые удовлетворяют требованиям 4НФ, но не соответствуют пятой нормальной форме. Однако 5НФ интересна для приложений, в которых новая информация получается из существующих данных.

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

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

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

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