Нормализация отношений. Первая и вторая нормальные формы

Предисловие


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

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

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

Используемые термины


Атрибут — свойство некоторой сущности. Часто называется полем таблицы.
Домен атрибута — множество допустимых значений, которые может принимать атрибут.
Кортеж — конечное множество взаимосвязанных допустимых значений атрибутов, которые вместе описывают некоторую сущность (строка таблицы).
Отношение — конечное множество кортежей (таблица).
Схема отношения — конечное множество атрибутов, определяющих некоторую сущность. Иными словами, это структура таблицы, состоящей из конкретного набора полей.
Проекция — отношение, полученное из заданного путём удаления и (или) перестановки некоторых атрибутов.
Функциональная зависимость между атрибутами (множествами атрибутов) X и Y означает, что для любого допустимого набора кортежей в данном отношении: если два кортежа совпадают по значению X, то они совпадают по значению Y. Например, если значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom. Обозначение: {X} -> {Y}.

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


Отношение находится в первой нормальной форме (сокращённо 1НФ), если все его атрибуты атомарны, то есть если ни один из его атрибутов нельзя разделить на более простые атрибуты, которые соответствуют каким-то другим свойствам описываемой сущности.

Будем называть исходное отношение основным, а значение неатомарного атрибута — подчинённым.

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

Следует пояснить сказанное на примере. Рассмотрим отношение, имеющее атрибуты «Код сотрудника», «ФИО», «Должность», «Проекты». Очевидно, что один сотрудник может работать над несколькими проектами. Предположим, что проект описывается идентификатором, названием и датой сдачи.
Код сотрудника ФИО Должность Проекты
1 Иванов Иван Иванович Программист ID: 123; Название: Система управления паровым котлом; Дата сдачи: 30.09.2011
ID: 231; Название: ПС для контроля и оповещения о превышениях ПДК различных газов в помещении; Дата сдачи: 30.11.2011
ID: 321; Название: Модуль распознавания лиц для защитной системы; Дата сдачи: 01.12.2011
Легко заметить, что не все атрибуты этого отношения атомарны (неделимы). В частности, атрибут «Проекты» можно разделить на три более простых атрибута: «Код проекта», «Название», «Дата сдачи», а значение этого атрибута для сотрудника Иван Иванович Иванов содержит несколько кортежей — информацию о трёх проектах.

Примечание: с некоторой точки зрения атрибут «ФИО» можно также считать неатомарным и в таком случае его также следует разделить на более простые, как «Фамилия», «Имя», «Отчество».

Теперь настало время рассмотреть алгоритм нормализации отношения до 1НФ.
  1. Создать новое отношение, схема которого будет получена путём слияния основной и подчинённой схем исходного отношения в одну.
  2. Для каждого кортежа исходного отношения включить в новое столько строк, сколько кортежей содержится в подчинённом отношении этого кортежа.
  3. Заполнить значения атрибутов нового отношения, соответствующих атрибутам подчинённого отношения.
  4. Заполнить строки нового отношения значениями атомарных атрибутов исходного.
Применим этот алгоритм к приведённому выше отношению. Схема нового отношения будет состоять из 6 атрибутов: «Код сотрудника», «ФИО», «Должность», «Код проекта», «Название», «Дата сдачи». Для одного единственного кортежа заданного отношения, добавим в новое три строки, по одной для каждого проекта (по количеству кортежей в подчинённом отношении). Теперь можно заполнить значения разделённых атрибутов кортежами из подчинённого отношения. Затем перенесём в каждую из этих строк значения атомарных атрибутов: «Код сотрудника», «ФИО», «Должность» (как Вы уже догадались, все три строки будут содержать одинаковые значения этих атрибутов).

Результат будет выглядеть так:
Код сотрудника ФИО Должность Код проекта Название Дата сдачи
1 Иванов Иван Иванович Программист 123 Система управления паровым котлом 30.09.2011
1 Иванов Иван Иванович Программист 231 ПС для контроля и оповещения о превышениях ПДК различных газов в помещении 30.11.2011
1 Иванов Иван Иванович Программист 321 Модуль распознавания лиц для защитной системы 01.12.2011

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


Ясно, что отношение, находящееся в 1НФ, также может обладать избыточностью. Для её устранения предназначена вторая нормальная форма. Но прежде чем приступить к её описанию, сначала следует выявить недостатки первой.

Пусть исходное отношение содержит информацию о поставке некоторых товаров и их поставщиках.
Код поставщика Город Статус города Код товара Количество
1 Москва 20 1 300
1 Москва 20 2 400
1 Москва 20 3 100
2 Ярославль 10 4 200
3 Ставрополь 30 5 300
3 Ставрополь 30 6 400
4 Псков 15 7 100
Заранее известно, что в этом отношении содержатся следующие функциональные зависимости:
{ {Код поставщика, Код товара} -> { Количество},
{Код поставщика} -> {Город},
{Код поставщика} -> {Статус},
{Город} -> {Статус} }

Первичный ключ в отношении: {Код поставщика, Код товара}.

Очевидно, что отношение обладает избыточностью: оно описывает две сущности — поставку и поставщика. В связи с этим возникают следующие аномалии:
  • Аномалия вставки. В отношение нельзя добавить информацию о поставщике, который ещё не поставил ни одного товара.
  • Аномалия удаления. Если от поставщика была только одна поставка, то при удалении информации о ней будет удалена и вся информация о поставщике.
  • Аномалия обновления. Если необходимо изменить какую-либо информацию о поставщике (например, поставщик переехал в другой город), то придётся изменять значения атрибутов во всех записях о поставках от него.
Физический смысл избыточности исходного отношения заключается в том, что оно описывает не одну сущность, а двепоставку и поставщика.

Чтобы устранить эти аномалии, необходимо разбить исходное отношение на проекции:
  1. В первую следует включить первичный ключ и все неключевые атрибуты явно зависимые от него.
  2. В остальные проекции (в данном случае она одна) будут включены неключевые атрибуты, зависящие от первичного ключа неявно, вместе с той частью первичного ключа, от которой эти атрибуты зависят явно.
В итоге будут получены два отношения:
Код поставщика Код товара Количество
1 1 300
1 2 400
1 3 100
2 4 200
3 5 300
3 6 400
4 7 100
Первому отношению теперь соответствуют следующие функциональные зависимости:
{Код поставщика, Код товара} -> {Количество}
Код поставщика Город Статус города
1 Москва 20
2 Ярославль 10
3 Ставрополь 30
4 Псков 15
Второму отношению соответствуют:
{ {Код поставщика} -> {Город},
{Код поставщика} -> {Статус},
{Город} -> {Статус} }

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

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

Литература


Для более глубокого и основательного изучения рассмотренной темы, рекомендуется книга «Введение в системы баз данных» Криса Дж. Дейта, на основе материалов которой и была написана данная статья.
Поделиться публикацией
Комментарии 23
    +1
    Название статьи хорошая. Почему-то первой мыслью было про личные, человеческие отношения.
      +2
      «Нормализация имеет своей целью избавиться от избыточности в отношениях и модифицировать их структуру таким образом, чтобы процесс работы с ними не был обременён различными посторонними сложностями.»
        +1
        Карнеги, прям :)
      0
      Спасибо, как раз в моем учебном заведении начался курс баз данных. Ваша статья как нельзя кстати :)
        +2
        Спасибо, как раз в моем учебном заведении через 8 часов сдавать ГОСы, среди вопросов к которым есть и НФ. Жаль, до утра автор, видимо, не опубликует про 3НФ, 4НФ, 5НФ, НФБК )
        0
        А где же 3-я, 4-я формы? Продолжение будет?
          +1
          >> И этим постом хотелось бы начать небольшую серию публикаций, посвящённых нормальным формам…
          +14
          Забавно, что в университетах нас учат всем этим нормальным формам, а жизнь впоследствии учит денормализации.
            +2
            Я всегда чисто интуитивно проектировал базу и в первый раз читаю о этих формах. Кажется я почти сразу стал делать правильно… Возможно это потому, что знакомился с базами данных уже на готовом проекте. Практика?
              +3
              Когда проектируешь для реального проекта, сразу думаешь какие запросы будешь делать. Таким образом проблемы выявляются интуитивнее.
              У меня так-же, про нормальные формы узнал уже после того как проектировал базы не один раз, и успешно.
                +4
                Теория — инструмент помогающий проектировать и выявлять проблемы не только интуитивно.
              0
              Мне одному показалось, что материал и по стилю, и по содержанию имеет больше отношения к реляционной алгебре, чем к классическим (SQL) РСУБД? А, главное, явно не для начинающих?
                +3
                Показалось. В основе работы классических Реляционных СУБД лежит фундамент той самой алгебры. Не знаю насколько это для начинающих, но это базовые основы, самое вкусное дальше (но и сложнее).
                  0
                  Так я про то же. В основе работы кассового аппарата лежит фундамент алгебры, матанализа, операционного и дискретного исчисления. Вот только нужно ли термины типа «интеграл», «производная», «шаг дискретизации» объяснять кассиру, а тем боле покупателю?
                    +1
                    Понимаете, при работе с кассовым аппаратом тоже применяется некая стандартная терминология. Кнопки обычно не называют «вот эти штучки», хотя, возможно, кому-то так называть и проще :)

                    Я это к тому, что использование некорректных терминов может и добавит кому-то понимания, но по сути лишь внесёт путаницу и покажет некомпетентность автора. Тем более, я постарался предупредить возможные вопросы, добавив мини-словарь.
                  +1
                  Теорию функциональных зависимостей и нормальных форм знать надо для более глубокого понимания вопроса. Конечно на практике можно проектировать базы данных, забывая о НФ и ФЗ, однако, например та же теория ФЗ говорит о том, что всегда существует минимальный набор ФЗ для предметной области, из которого можно вывести все остальные ФЗ. Более того, есть формальные правила вывода этого минимального набора. Отсюда следует, что правильно сформулировав функциональные зависимости можно автоматически получить правильно спроектированную базу данных.

                  Ситуация с нормальными формами является одним из воплощений закона дырявых абстракций. Абстракция есть, но ее как бы нет. И большинство начинающих разработчиков думает «а зачем оно мне?». Но понимание более низкоуровневых процессов позволяет посмотреть на вопрос с другой точки зрения и понять некие скрытые взаимосвязи (например, множественные join'ы возможны потому что декартово произведение отношений в свою очередь тоже является отношением).
                  +1
                  Ребят вам не надоело? Ну все же украдено написано до нас citforum.ru/database/advanced_intro/
                    +1
                    Было про вторую в этом же блоге:

                    habrahabr.ru/blogs/refactoring/113260/
                    habrahabr.ru/blogs/refactoring/113274/
                      +2
                      Собравших писать о нормальных формах, я посчитал, что будет неверным просто пропустить вторую, мотивируя тем, что про неё уже говорили тут. К тому же, мы с тем автором подошли немного с разных сторон к этой теме.
                      +2
                      А мне эта понравилась. Очень доходчиво написано.
                      И вопрос: А в последней таблице разве не 4 строчки должно быть?
                        0
                        да, должно быть, так как одинаковых кортежей в отношении не может быть
                          0
                          Спасибо. Не обратил внимание. Исправил.
                          0
                          Когда будете писать продолжение, опишите максимально подробно 4НФ и 5НФ. Это, по крайней мере для меня, крайне непонятные вещи.

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

                          Самое читаемое