Нормальные формы, пример нормализации в базе данных

Кодд представил концепцию нормализации и то, что сейчас известно как первая нормальная форма (1NF) в 1970 году. Кодд продолжал определять вторую нормальную форму (2NF) и третью нормальную форму (3NF) в 1971 году, также Кодд и Рэймонд Ф. Бойс определили нормальную форму Бойса-Кодда (BCNF) в 1974 году.

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

Нормальные формы (от наименее нормализованных до наиболее нормализованных):

UNF: ненормализованная форма
1NF: первая нормальная форма
2NF: вторая нормальная форма
3NF: третья нормальная форма
EKNF: Элементарный ключ, нормальная форма
BCNF: нормальная форма Бойса-Кодда
4NF: четвертая нормальная форма
ETNF: нормальная форма основного кортежа
5NF: пятая нормальная форма
DKNF: нормальная форма ключа домена
6NF: шестая нормальная форма

	UNF (1970)	1NF (1970)	2NF (1971)	3NF (1971)	EKNF (1982)	BCNF (1974)	4NF (1977)	ETNF (2012)	5NF (1979)	DKNF (1981)	6NF (2003)
Первичный ключ (без дубликатов)	Возможно	Да	Да	Да	Да	Да	Да	Да	Да	Да	Да
Нет повторяющихся групп	Возможно	Да	Да	Да	Да	Да	Да	Да	Да	Да	Да
Атомарные столбцы (ячейки имеют одно значение)	Нет	Да	Да	Да	Да	Да	Да	Да	Да	Да	Да
Нет частичных зависимостей (значения зависят от каждого ключа-кандидата)	Нет	Нет	Да	Да	Да	Да	Да	Да	Да	Да	Да
Нет транзитивных зависимостей (значения зависят только от ключей-кандидатов)	Нет	Нет	Нет	Да	Да	Да	Да	Да	Да	Да	Да
Каждая нетривиальная функциональная зависимость включает в себя либо суперключ, либо подраздел элементарного ключа.	Нет	Нет	Нет	Нет	Да	Да	Да	Да	Да	Да	Не определено
Нет избыточности от какой-либо функциональной зависимости	Нет	Нет	Нет	Нет	Нет	Да	Да	Да	Да	Да	Не определено
У каждой нетривиальной многозначной зависимости есть суперключ	Нет	Нет	Нет	Нет	Нет	Нет	Да	Да	Да	Да	Не определено
Компонентом каждой явной зависимости соединения является суперключ	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Да	Да	Да	Не определено
Каждая нетривиальная зависимость соединения подразумевается ключом-кандидатом	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Да	Да	Не определено
Каждое ограничение является следствием ограничений домена и ключевых ограничений	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Да	Не определено
Каждая зависимость присоединения тривиальна	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Нет	Да

Пример пошаговой нормализации

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

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

Тем не менее, стоит отметить, что нормальные формы за пределами 4NF представляют в основном академический интерес, так как проблемы, которые они решают, редко появляются на практике.

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

Исходные данные

Пусть таблица базы данных имеет следующую структуру:

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

Удовлетворение 1NF

Чтобы удовлетворить 1NF, значения в каждом столбце таблицы должны быть атомарными. В исходной таблице Subject содержит набор значений субъекта, что означает, что он не соответствует.

Один из способов достижения 1NF - разделить дубликаты на несколько столбцов, используя повторяющиеся группы 'subject':

Хотя теперь таблица формально соответствует 1NF (является атомарной), проблема с этим решением очевидна - если книга содержит более трех предметов, ее нельзя добавить в базу данных, не изменив ее структуру.

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

Простое разделение исходных данных на несколько таблиц нарушит связь между данными. Это означает, что отношения между вновь введенными таблицами должны быть определены. Обратите внимание, что столбец "Publisher ID" в таблице "Book" является внешним ключом, реализующим отношение "многие к одному" (many-to-one) между книгой и издателем.

Книга может соответствовать многим предметам, а предмет может соответствовать многим книгам. Это означает, что также необходимо определить отношение "многие ко многим" (many-to-many), достигнув путем создания таблицы ссылок:

Вместо одной таблицы в ненормализованной форме теперь есть 4 таблицы, соответствующие 1NF.

Удовлетворение 2NF

Таблица Book имеет один ключ-кандидат, составной ключ {Title, Format}. Рассмотрим следующий фрагмент таблицы:

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

Чтобы нормализовать эту таблицу, сделайте {Title} (простым) ключом, чтобы каждый неключевой атрибут зависел от всего ключа, и удалите Price в отдельную таблицу, чтобы сохранить ее зависимость от Format:

Теперь таблица Book соответствует 2NF.

Удовлетворение 3NF

Таблица в третьей нормальной форме (3NF) - это таблица в 2NF, которая не имеет транзитивных зависимостей. Обратите внимание на таблицу книг с большим количеством строк (предыдущие для краткости опущены):

Идентификатор жанра и название жанра зависят от первичного ключа {Title}, но они не зависят друг от друга. Зависимость, скажем, Genre Name от первичного ключа может быть выведена из зависимости Genre Name от Genre ID и Genre ID от первичного ключа. Поскольку заголовков больше, чем жанров, эта зависимость вводит избыточные данные в таблицу Book, которые можно устранить, абстрагируя зависимость Genre Name от Genre ID в своей собственной таблице:

Таблица Book теперь в третьей нормальной форме. Хотя таблицы в 1NF по определению являются нормализованными, для обозначения 3NF обычно используется термин "нормализованный".

Удовлетворение EKNF

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

Удовлетворение BCNF

Реляционная схема R считается в нормальной форме Бойса-Кодда (BCNF), если для каждой из ее зависимостей X → Y выполняется одно из следующих условий:

X → Y является тривиальной функциональной зависимостью (то есть Y является подмножеством X)
X - суперключ для схемы R

Рассмотрим таблицу в 3NF из предыдущего шага:

Существует нетривиальная зависимость, нарушающая BCNF - {Author} → {Author Nationality}. Поэтому таблица должна быть разложена:

Теперь каждый атрибут представляет факт о ключе, весь ключ и ничего, кроме ключа. Поэтому BCNF была достигнута.

Удовлетворение 4NF

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

Поскольку эта структура таблицы состоит из составного первичного ключа, она не содержит неключевых атрибутов и уже находится в BCNF (и, следовательно, также удовлетворяет всем предыдущим нормальным формам). Однако, если мы предположим, что все доступные книги предлагаются в каждой области, мы можем заметить, что Title не однозначно связано с определенным Location, и поэтому таблица не удовлетворяет 4NF.

Это означает, что для удовлетворения четвертой нормальной формы эту таблицу также необходимо разложить:

Теперь каждая запись однозначно идентифицируется суперключем, поэтому 4NF удовлетворяется.

Удовлетворение ETNF

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

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

Эта таблица в 4NF, но Supplier ID равен объединению его проекций: { { Supplier ID , Book } , { Book, Franchisee ID } , { Franchisee ID , Supplier ID } }. Ни один компонент этой зависимости соединения не является суперключем (единственным суперключем является весь заголовок), поэтому таблица не удовлетворяет ETNF и может быть дополнительно разложена:

Разложение производит соответствие ETNF.

Удовлетворение 5NF

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

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

Что произойдет, если мы попытаемся выполнить JOIN для этих таблиц? Запрос вернет следующие данные:

По-видимому, JOIN возвращает на три строки больше, чем следует - давайте попробуем добавить еще одну таблицу, чтобы прояснить отношение. Мы заканчиваем с тремя отдельными таблицами:

Что теперь вернет JOIN? На самом деле невозможно объединить эти три таблицы. Это означает, что невозможно разложить Франчайзи - Расположение книги без потери данных, поэтому таблица уже удовлетворяет 5NF.

К. Дж. Дейт утверждает, что только база данных в 5NF действительно "нормализована".

Удовлетворение DKNF

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

По логике, толщина определяется количеством страниц. Это означает, что это зависит от страниц, которые не являются ключом. Давайте установим пример соглашения о том, что книга объемом до 350 страниц считается "тонкой", а книга объемом более 350 страниц считается "толстой".

Это соглашение технически является ограничением, но оно не является ни ограничением домена, ни ключевым ограничением; поэтому мы не можем полагаться на ограничения домена и ключевые ограничения для сохранения целостности данных.

Другими словами - ничто не мешает нам поставить, например, "толстый" для книги только с 50 страницами - и это заставляет таблицу нарушать DKNF.

Чтобы решить эту проблему, мы можем создать таблицу с перечислением, которая определяет толщину, и удалить этот столбец из исходной таблицы:

Таким образом, нарушение целостности домена было устранено, и таблица находится в DKNF.

Удовлетворение 6NF

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

Это означает, например, таблицу Publishers, созданную при создании 1NF

должна быть декомпозирована на 2 таблицы

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

Поиск по этому блогу

Alex tools