Нормализация отношений: примеры 1НФ-3НФ

Содержание

1. 1. Реляционная модель и аномалии3
2. 2. Первая нормальная форма: атомарность5
3. 3. Вторая и третья нормальные формы7
4. 4. Итоги нормализации и практические выводы10

1. Реляционная модель и аномалии

Реляционная модель, предложенная Эдгаром Коддом в 1970 году, строится на простой и строгой идее: вся информация представляется в виде плоских таблиц. Строка такой таблицы описывает конкретный объект или событие, а столбцы, называемые атрибутами, задают его свойства. Например, таблица «Сотрудники» может содержать атрибуты «Табельный номер», «ФИО» и «Отдел». Каждая строка уникальна, а порядок строк и столбцов не имеет значения. Такая структура интуитивно понятна, однако за кажущейся простотой скрывается серьёзная проблема: неудачное проектирование таблиц порождает избыточность данных.

Представьте, что в таблице «Сотрудники» хранится не только имя работника, но и название его отдела, а также адрес офиса этого отдела. Если в отделе работает пятьдесят человек, адрес офиса будет повторён в пятидесяти строках. Это не просто трата дискового пространства. Избыточность становится источником ошибок и логических противоречий, которые в теории баз данных называют аномалиями.

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

не относится.

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

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

2. Первая нормальная форма: атомарность

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

Атомарность означает, что значение нельзя разбить на части, которые в запросах используются независимо. На практике это требование часто трактуют прагматично: атрибут считается неделимым, если его фрагменты не нужны для поиска или сортировки. Например, поле «ФИО» может быть атомарным для одной системы, но если нужна выборка по фамилии, его придётся расщепить. Впрочем, классическое определение 1НФ говорит о структуре, а не о смысловой нагрузке значения.

Главная проблема, которую решает 1НФ, это повторяющиеся группы и многозначные атрибуты. Представьте таблицу клиентов, где в поле «Телефон» номера перечислены через запятую: 555-01-23, 555-45-67. С такой структурой нельзя выполнить простой запрос «найти клиента с номером 555-45-67» без дополнительных манипуляций со строкой. Хуже того, чтобы добавить второй номер, придётся изменять запись, а не вставлять новую строку.

Приведение к 1НФ делается двумя способами: разбиением на отдельные строки либо выносом данных в связанную таблицу. Разберём первый вариант. Исходная таблица «Клиенты» с колонками «ID», «Имя», «Телефон» хранит у клиента Петрова два номера в одной ячейке. После преобразования получаем три строки: Петров с первым номером, Петров со вторым, а ключ становится составным (ID + Телефон). Это рабочее решение, хотя оно порождает дублирование данных клиента, а это ведёт к аномалиям, описанным в первой главе.

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

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

Классический пример из учебника Дейта: таблица поставщиков, где в колонке «Детали» коды перечислены через точку с запятой. Преобразование в 1НФ даёт по одной строке на каждую деталь, но первичный ключ становится составным «Номер поставщика + Код детали». Это неизбежная плата за атомарность. Альтернатива с отдельной таблицей связей сложнее в реализации, зато значительно удобнее при росте объёма данных.

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

3. Вторая и третья нормальные формы

После приведения таблиц к первой нормальной форме остаётся ещё немало проблем. Атомарность значений сама по себе не гарантирует отсутствия избыточности. Здесь вступает в силу вторая нормальная форма, которую Кодд описал в 1971 году в статье о дальнейшей нормализации реляционной модели.

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

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

Частичная зависимость устраняется путём декомпозиции таблицы на несколько связанных таблиц. Исходную таблицу разделяют на две: «Поставщики» с ключом (Код_поставщика) и атрибутами названия и города, и «Поставки» с составным ключом (Код_поставщика, Код_детали) и количеством. Связь между ними поддерживается через внешний ключ Код_поставщика. Теперь название и город хранятся ровно один раз, а в таблице поставок остаётся только то, что действительно зависит от обоих элементов ключа. В результате избыточность

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

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

Тот же Дейт приводит пример с таблицей «Сотрудники», где первичным ключом служит табельный номер. Сотрудник имеет отдел, а каждый отдел расположен в конкретном здании. Атрибут «здание» зависит от первичного ключа не напрямую, а через атрибут «отдел». Пока всё выглядит логично, но при переводе отдела в другое здание придётся обновить все строки сотрудников этого отдела. Ошибка хотя бы в одной строке создаст ситуацию, где один отдел числится в двух зданиях одновременно. Декомпозиция на таблицы «Сотрудники» и «Отделы» решает проблему: здание хранится один раз в таблице отделов, а сотрудники ссылаются на отдел через внешний ключ.

Практический эффект нормализации до 3НФ хорошо виден на конкретных цифрах. Если в исходной таблице «Поставки» содержится 1000 записей о поставках от 50 поставщиков, то после приведения к 2НФ название каждого поставщика будет храниться один раз вместо двадцати в среднем. Это сокращает объём хранимых данных и время на их обновление. Приведение к 3НФ в таблице сотрудников с 500 записями и 10 отделами уменьшает количество повторений названия здания с пятидесяти до одного на отдел.

Важно понимать, что нормальные формы применяются последовательно. Таблица, находящаяся в 3НФ, автоматически находится и во 2НФ, и в 1НФ. Кодд определил третью нормальную форму

в той же работе 1971 года, что и вторую, но позже, в 1974 году, Рональд Фейгин уточнил это определение, введя понятие многозначных зависимостей и четвёртой нормальной формы. Однако для практического проектирования баз данных достижение 3НФ считается достаточным в большинстве случаев, поскольку она устраняет почти все аномалии, связанные с избыточностью.

4. Итоги нормализации и практические выводы

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

Почему именно 3НФ стала точкой остановки для большинства проектировщиков? Ответ лежит в соотношении выгоды и сложности. Дальнейшие нормальные формы, например нормальная форма Бойса-Кодда или четвёртая, требуют анализа тонких случаев пересекающихся ключей и многозначных зависимостей. На практике такие ситуации встречаются редко, а стоимость их устранения высока: схема дробится на множество мелких таблиц, которые усложняют написание запросов. Разработчик получает модель, которую трудно читать и поддерживать, ради устранения проблем, которые вряд ли проявятся. Поэтому эмпирическое правило звучит так: проектируй до 3НФ, а дальше смотри по ситуации.

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

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

Примеры, разобранные во второй и третьей главах, наглядно демонстрируют этот баланс. Когда таблица с заказами и товарами была приведена к 3НФ, мы получили отдельные сущности для клиентов, продуктов и связей между ними. Избыточность исчезла, но запрос «вывести все заказы клиента с полными названиями товаров» теперь требует трёх соединений. Для учебной базы это нормально, для производственной системы с миллионами записей может оказаться слишком медленно. Решение всегда принимается индивидуально: где-то оставляют 3НФ, где-то сознательно откатываются до 2НФ, а где-то добавляют вычисляемые столбцы для ускорения отчётов.

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