«Нормальные формы» — это правила нормализации баз данных, предназначенные для предотвращения дублирования данных и аномалий при изменении информации в реляционных базах данных.
Вторая нормальная форма (2NF) требует, чтобы каждый столбец зависел от всего ключа таблицы, а не только от его части. Нормальная форма Бойса — Кодда (BCNF) ужесточает это требование: никакой столбец не должен зависеть ни от чего, кроме ключа.
Классическая мнемоническая фраза «ключ, весь ключ и ничего, кроме ключа» объединяет все три нормальные формы в одной строке. В этом руководстве мы подробно разберём, что именно означает каждое правило, и рассмотрим практические примеры, которые можно применить при проектировании собственных структур данных.
Что такое нормальные формы и почему они важны?
Правильное моделирование данных имеет решающее значение при проектировании новой базы данных. Первый шаг этого процесса — применение набора правил построения таблиц, которые называются нормальными формами.
Эти правила обеспечивают ссылочную целостность, устраняют избыточность данных, позволяют эффективно выполнять запросы и помогают предотвращать аномалии при изменении информации. Первое из этих правил, вполне закономерно называемое первой нормальной формой, мы рассматривали в первой части этой серии.
Это вторая часть полного цикла статей Ли Ашера, посвящённого нормальным формам — совокупности правил проектирования баз данных.
Первую нормальную форму (1NF) запомнить довольно просто. А вот формальные определения форм более высокого порядка порой кажутся специально придуманными для того, чтобы всех запутать.
У этой статьи две цели. Во-первых, предложить простой и запоминающийся способ усвоить эти правила. И, что ещё важнее, показать, почему они вообще существуют и каких проблем позволяет избежать их соблюдение.
Чтобы запомнить правила нормализации, достаточно одной мнемонической фразы, которая наверняка знакома всем любителям судебных драм:
Ключ, весь ключ и ничего, кроме ключа
В этой фразе три части, каждая из которых соответствует отдельному правилу. Рассмотрим их по порядку.
Вам также может быть интересно: Руководство Джо Селко по нормальным формам баз данных.
Ключ: каждый столбец должен зависеть от ключа таблицы
Правило «Ключ» означает, что у каждой таблицы должен быть ключ, а каждый столбец этой таблицы должен зависеть от него. Иными словами, значение ключа однозначно определяет значения всех остальных данных в строке.
Рассмотрим таблицу из простой базы данных POS (Point of Sale — система регистрации продаж), в которой учитываются покупки в розничном магазине:
Как видно, sale_id является первичным ключом таблицы, а значения sale_amount, tax_amount и total непосредственно зависят от этого ключа.
Однако столбец tax_rate от ключа не зависит. Такие постоянные характеристики следует вынести в отдельную таблицу справочных данных, а не дублировать во всех строках основной таблицы.
Первое правило настолько простое, что на нём можно было бы даже не останавливаться. Следующее несколько сложнее.
Весь ключ: объяснение второй нормальной формы (2NF)
Правило «Весь ключ» требует, чтобы каждый столбец таблицы зависел от ключа целиком.
Это требование имеет смысл только в том случае, когда ключ состоит из нескольких частей. Таким образом, мы подошли к понятию составных ключей.
Рассмотрим следующую таблицу, в которой представлены сведения о записи студентов на университетские курсы и полученных ими итоговых оценках:
Поскольку студент может проходить один и тот же курс несколько раз — например, в разные семестры, — уникальный ключ каждой строки образуется сочетанием трёх столбцов: student_id, course_id и term.
Оценка студента зависит от всех трёх значений, поэтому это правило соблюдается. Но столбец instructor зависит только от двух из них: course_id и term.
Как видно по повторяющимся именам преподавателей, это приводит к дублированию данных и порождает так называемую аномалию обновления. Чтобы изменить один факт — имя преподавателя, ведущего конкретный курс, — приходится редактировать несколько строк.
Это не только снижает производительность, но, что гораздо важнее, создаёт возможность возникновения противоречий: в разных строках, относящихся к одному и тому же занятию, могут оказаться разные преподаватели.
Главный вывод заключается в следующем: каждый факт должен храниться только в одном месте. В моделировании данных этот принцип называют единым источником достоверных данных (Single Source of Truth, SSOT).
Решить проблему можно, разделив исходную таблицу на две новые:
Это правило «Весь ключ» формально называется второй нормальной формой (2НФ). Теперь вы его точно не забудете.
Трёхкомпонентный ключ такой таблицы довольно неудобен в использовании. Поэтому в большинстве организаций дополнительно создают суррогатный ключ, позволяющий однозначно идентифицировать каждую строку. Например, это может быть enrollment_id или аналогичный идентификатор.
Некоторые организации вообще добавляют суррогатные ключи во все таблицы, даже если у них уже имеется естественный ключ, состоящий из одного столбца.
Однако это не меняет принципиального положения: суррогатный ключ заменяет естественный ключ, но не изменяет функциональные зависимости между столбцами.
Вам также может быть интересно: Как находить и отслеживать ссылки на объекты в SQL Server (руководство по зависимостям).
Ничего, кроме ключа: объяснение нормальной формы Бойса — Кодда (НФБК)
Правило «Ничего, кроме ключа» требует, чтобы столбцы таблицы зависели исключительно от самого ключа этой таблицы.
На этот раз начнём с таблицы, которая полностью соответствует данному правилу.
Представим исследовательскую медицинскую организацию, которая хочет проводить статистический анализ групп крови людей, у которых диагностированы определённые виды рака.
Для каждого случая заболевания в таблице сохраняется группа крови пациента:
Если выразить правило для этой таблицы с помощью функциональных зависимостей, используемых при моделировании данных, оно будет выглядеть следующим образом:
case_id >>> blood_type
Не пугайтесь такой записи. Стрелка просто означает, что значение слева определяет значение справа. Таким образом, зная case_id, мы можем однозначно определить группу крови пациента.
Впоследствии организация расширяет базу данных, добавляя имя и адрес пациента, чтобы иметь возможность связываться с ним напрямую. Для этого в таблицу включают два новых столбца:
В результате появляются ещё две функциональные зависимости:
case_id >>> patient_name
case_id >>> patient_address
Однако этот список пока неполный. Адрес и группа крови пациента также зависят от его имени:
patient_name >>> patient_address
patient_name >>> blood_type
Теперь у нас появились столбцы, значения которых зависят не только от ключа таблицы, но и от другого столбца. Следовательно, правило нарушено.
Чтобы устранить это нарушение, необходимо вынести сведения о пациентах в отдельную таблицу.
Это правило формально называется нормальной формой Бойса — Кодда (BCNF). Название связано с именем доктора Эдгара Кодда, которому принадлежит ряд основополагающих идей реляционной модели данных.
Именно его фамилия увековечена в полной версии нашей мнемонической фразы: «Ключ, весь ключ и ничего, кроме ключа. Да поможет мне Кодд!»
Какие аномалии обновления и удаления предотвращает BCNF
Если не устранить описанное нарушение, мы столкнёмся сразу с двумя проблемами: аномалией обновления и аномалией удаления. Аномалия обновления возникнет, если заболевание пациента рецидивирует и сведения о нём снова появятся в таблице. В результате один и тот же пациент может оказаться представлен несколькими строками, причём данные в них потенциально будут различаться. Аномалия удаления проявляется иначе: удаление записи о случае заболевания пациента одновременно приведёт к потере связи между его именем, адресом и группой крови.
А как же третья нормальная форма?
Вы могли заметить, что мы рассмотрели 2NF и BCNF, но ничего не сказали о третьей нормальной форме (3NF). Дело в том, что BCNF представляет собой более строгое развитие третьей нормальной формы. По сути, эти две формы работают одинаково, за исключением необычного случая, когда таблица имеет несколько пересекающихся составных ключей. Если коротко: таблица в BCNF по определению находится и в 3NF, а практически любая таблица, удовлетворяющая 3NF, одновременно соответствует и BCNF.
В чём основные различия между BCNF и 3NF?
Различие между этими двумя формами на самом деле очень простое. Хотя по большинству университетских курсов, посвящённых этой теме, такого и не скажешь. В третьей нормальной форме допускается, чтобы столбец не зависел от ключа таблицы целиком, если сам этот столбец входит в состав другого ключа.
Рассмотрим это на конкретном примере. Предположим, в компании действует программа профессионального развития, в которой сотрудникам предлагается участвовать в командах различной направленности. Один сотрудник может состоять в нескольких командах. При этом ему назначается только один наставник, а каждый наставник может быть закреплён лишь за одной командой, хотя в одной команде может работать несколько наставников.
Вот пример данных:
Если представить ограничения этой таблицы с помощью функциональных зависимостей, получим следующее:
(employee, team) >>> mentor
(mentor) >>> team
Проблема заключается во второй зависимости: значение столбца team определяется значением mentor, который не является «всем ключом». Следовательно, таблица не соответствует BCNF. Однако третьей нормальной форме она соответствует, поскольку сам столбец team входит в состав одного из ключей. В терминологии моделирования данных такой столбец называется ключевым атрибутом. Но в чём же именно заключается проблема такой структуры и почему стоит перейти от неё к BCNF? Всё дело в печально известной аномалии удаления. Если сотрудник «Schrute, D.» покинет команду «Office Conflict Resolution», сведения о том, что Смит является наставником этой команды, также будут потеряны. Разделение исходной таблицы на две новые таблицы, соответствующие BCNF, позволяет устранить эту проблему.
Всегда ли можно привести таблицу к BCNF? Нет — существует исключение
Любую таблицу можно привести как минимум к третьей нормальной форме. Однако таблицу, уже находящуюся в 3NF, не всегда удаётся преобразовать в BCNF без потери некоторой функциональной зависимости. Рассмотрим пример графика дежурств медсестёр в больнице, где указано, какие медсёстры закреплены за пациентами:
Каждая медсестра может обслуживать нескольких пациентов, но работает только в одну смену. При этом за каждым пациентом в каждой смене закреплена только одна медсестра. В терминах моделирования данных эти два функциональных ограничения записываются следующим образом:
nurse >>> shift
(patient, shift) >>> nurse
Возможные кандидатные ключи здесь — (patient, nurse) и (patient, shift). Видите, как они пересекаются? Именно такой случай является единственным исключением, когда таблица соответствует 3NF, но не соответствует BCNF.
Почему эта таблица соответствует 3NF, но не BCNF?
Эта таблица не соответствует BCNF, поскольку столбец shift зависит от столбца nurse. Зная имя медсестры, мы можем однозначно определить её смену. Однако сама медсестра не является возможным ключом таблицы. Третья нормальная форма, тем не менее, допускает такую зависимость, поскольку столбец shift входит в состав ключа (patient, shift). Следовательно, он является ключевым атрибутом. А теперь посмотрим, что произойдёт, если разделить эту таблицу, соответствующую 3НФ, на две отдельные таблицы, каждая из которых будет отвечать требованиям BCNF:
Теперь теряется ограничение, согласно которому одному пациенту в течение одной смены может быть назначена только одна медсестра. Обратите внимание: я немного изменил исходные данные. Теперь у пациента «Mallon, Mary» две медсестры назначены на одну и ту же смену. В первоначальной таблице такая ситуация была невозможна.
У этого подхода есть ограничения. Восстановить утраченное ограничение и обеспечить его соблюдение можно, например, только при объединении двух таблиц с помощью операции соединения. И всё же такой вариант лучше, чем избыточность данных и аномалии удаления, возникающие при сохранении исходной таблицы в третьей нормальной форме. Чтобы понять почему, вернёмся к первоначальной таблице в BCNF и назначим одну медсестру сразу трём разным пациентам:
Если медсестра Бартон сменит график дежурств, придётся изменить три строки, а не одну. Это нарушает принцип единого источника достоверных данных: сведения о смене конкретной медсестры должны храниться только в одном месте. Кроме того, если у медсестры вообще нет пациентов, в первоначальной структуре таблицы невозможно представить информацию о том, в какой смене она работает.
Что дальше: четвёртая и пятая нормальные формы
Оставшиеся два правила — четвёртая нормальная форма и пятая нормальная форма. В университетских курсах по моделированию данных их рассматривают редко. И это досадно, поскольку ситуации, для решения которых предназначены эти формы, встречаются в реальных базах данных довольно часто. Их применение мы рассмотрим в третьей части этой серии.
Комментариев нет:
Отправить комментарий