Нормализация базы данных — это процесс структурирования реляционной базы данных в соответствии с серией так называемых нормальных форм с целью уменьшения избыточности данных и улучшения целостности данных . Впервые была предложена британским ученым-компьютерщиком Эдгаром Ф. Коддом как часть его реляционной модели .
Нормализация подразумевает организацию столбцов (атрибутов) и таблиц (отношений) базы данных для обеспечения того, чтобы их зависимости были надлежащим образом реализованы ограничениями целостности базы данных. Это достигается путем применения некоторых формальных правил либо с помощью процесса синтеза (создание нового проекта базы данных), либо декомпозиции (улучшение существующего проекта базы данных).
Основная цель первой нормальной формы, определенной Коддом в 1970 году, состояла в том, чтобы разрешить запрашивать и обрабатывать данные с использованием «универсального подъязыка данных», основанного на логике первого порядка . [1] Примером такого языка является SQL , хотя Кодд считал его имеющим серьезные недостатки. [2]
Цели нормализации за пределами 1NF (первой нормальной формы) были сформулированы Коддом следующим образом:
- Освободить коллекцию отношений от нежелательных зависимостей вставки, обновления и удаления.
- Уменьшить необходимость реструктуризации набора отношений по мере введения новых типов данных и тем самым увеличить срок службы прикладных программ.
- Сделать реляционную модель более информативной для пользователей.
- Сделать сбор отношений нейтральным по отношению к статистике запросов, поскольку эта статистика может меняться с течением времени.
— Э. Ф. Кодд, «Дальнейшая нормализация реляционной модели базы данных» [3]
При попытке изменить (обновить, вставить или удалить) отношение в отношениях, которые не были достаточно нормализованы, могут возникнуть следующие нежелательные побочные эффекты:
Полностью нормализованная база данных позволяет расширять ее структуру для размещения новых типов данных без слишком большого изменения существующей структуры. В результате приложения, взаимодействующие с базой данных, подвергаются минимальному влиянию.
Нормализованные отношения и отношения между одним нормализованным отношением и другим отражают реальные концепции и их взаимосвязи.
Кодд ввел концепцию нормализации и то, что сейчас известно как первая нормальная форма (1NF) в 1970 году. [4] Кодд продолжил определять вторую нормальную форму (2NF) и третью нормальную форму (3NF) в 1971 году, [5] а Кодд и Рэймонд Ф. Бойс определили нормальную форму Бойса–Кодда (BCNF) в 1974 году. [6]
Рональд Фейгин ввел четвертую нормальную форму (4NF) в 1977 году и пятую нормальную форму (5NF) в 1979 году. Кристофер Дж. Дейт ввел шестую нормальную форму (6NF) в 2003 году.
Неформально, отношение реляционной базы данных часто описывается как «нормализованное», если оно соответствует третьей нормальной форме. [7] Большинство отношений 3NF свободны от аномалий вставки, обновления и удаления.
Нормальные формы (от наименее нормализованной до наиболее нормализованной) следующие:
Нормализация — это метод проектирования базы данных, который используется для проектирования таблицы реляционной базы данных до более высокой нормальной формы. [9] Процесс является прогрессивным, и более высокий уровень нормализации базы данных не может быть достигнут, если предыдущие уровни не были удовлетворены. [10]
Это означает, что, имея данные в ненормализованной форме (наименее нормализованной) и стремясь достичь наивысшего уровня нормализации, первым шагом будет обеспечение соответствия первой нормальной форме , вторым шагом будет обеспечение соответствия второй нормальной форме и так далее в указанном выше порядке, пока данные не будут соответствовать шестой нормальной форме .
Однако стоит отметить, что нормальные формы за пределами 4NF в основном представляют академический интерес, поскольку проблемы, для решения которых они предназначены, редко встречаются на практике. [11]
Данные в следующем примере были намеренно разработаны так, чтобы противоречить большинству нормальных форм. На практике часто можно пропустить некоторые шаги нормализации, поскольку данные уже нормализованы в некоторой степени. Исправление нарушения одной нормальной формы также часто исправляет нарушение более высокой нормальной формы. В примере для нормализации на каждом шаге была выбрана одна таблица, что означает, что в конце некоторые таблицы могут быть недостаточно нормализованы.
Пусть существует таблица базы данных со следующей структурой: [10]
В этом примере предполагается, что у каждой книги только один автор.
Таблица, соответствующая реляционной модели, имеет первичный ключ , который однозначно идентифицирует строку. В нашем примере первичный ключ — это составной ключ { Title, Format} (обозначенный подчеркиванием):
В первой нормальной форме каждое поле содержит одно значение. Поле не может содержать набор значений или вложенную запись.
Тема содержит набор значений темы, что означает, что она не соответствует.
Для решения проблемы субъекты извлекаются в отдельную таблицу субъектов : [10]
Вместо одной таблицы в ненормализованной форме теперь имеются две таблицы, соответствующие 1NF.
Напомним, что таблица Book ниже имеет составной ключ {Title, Format} , который не будет удовлетворять 2NF, если некоторое подмножество этого ключа является детерминантом. На этом этапе нашего дизайна ключ не финализирован как первичный ключ , поэтому он называется потенциальным ключом . Рассмотрим следующую таблицу:
Все атрибуты, которые не являются частью ключа-кандидата, зависят от Title , но только Price также зависит от Format . Чтобы соответствовать 2NF и удалить дубликаты, каждый атрибут, не являющийся атрибутом ключа-кандидата, должен зависеть от всего ключа-кандидата, а не только от его части.
Чтобы нормализовать эту таблицу, сделайте {Title} (простым) потенциальным ключом (первичным ключом), чтобы каждый атрибут, не являющийся потенциальным ключом, зависел от всего потенциального ключа, и удалите Price в отдельную таблицу, чтобы сохранить ее зависимость от Format :
Теперь обе таблицы Book и Price соответствуют 2NF .
Таблица Book по-прежнему имеет транзитивную функциональную зависимость ({Author Nationality} зависит от {Author}, который зависит от {Title}). Аналогичные нарушения существуют для издателя ({Publisher Country} зависит от {Publisher}, который зависит от {Title}) и для жанра ({Genre Name} зависит от {Genre ID}, который зависит от {Title}). Следовательно, таблица Book не находится в 3NF. Чтобы решить эту проблему, мы можем поместить {Author Nationality}, {Publisher Country} и {Genre Name} в их собственные соответствующие таблицы, тем самым устранив транзитивные функциональные зависимости:
Нормальная форма элементарного ключа (EKNF) находится строго между 3NF и BCNF и нечасто обсуждается в литературе. Она предназначена для того, чтобы «охватить основные качества как 3NF, так и BCNF» , избегая при этом проблем обеих (а именно, что 3NF «слишком прощающая», а BCNF «склонна к вычислительной сложности»). Поскольку она редко упоминается в литературе, она не включена в этот пример.
Предположим, что база данных принадлежит франшизе книжного ритейлера, у которого есть несколько франчайзи, владеющих магазинами в разных местах. И поэтому ритейлер решил добавить таблицу, содержащую данные о наличии книг в разных местах:
Поскольку эта структура таблицы состоит из составного первичного ключа , она не содержит никаких неключевых атрибутов и уже находится в BCNF (и, следовательно, также удовлетворяет всем предыдущим нормальным формам). Однако, предполагая, что все доступные книги предлагаются в каждой области, Title не привязан однозначно к определенному Location и, следовательно, таблица не удовлетворяет 4NF .
Это означает, что для удовлетворения четвертой нормальной формы эту таблицу также необходимо разложить:
Теперь каждая запись однозначно идентифицируется суперключом , поэтому 4NF выполняется.
Предположим, что франчайзи также могут заказывать книги у разных поставщиков. Пусть отношение также подчиняется следующему ограничению:
Эта таблица находится в 4NF , но идентификатор поставщика равен соединению его проекций: {{Идентификатор поставщика, Название}, {Идентификатор франчайзи}, {Идентификатор франчайзи, идентификатор поставщика}}. Ни один компонент этой зависимости соединения не является суперключом (единственным суперключом является весь заголовок), поэтому таблица не удовлетворяет ETNF и может быть дополнительно разложена: [12]
Разложение обеспечивает соответствие ETNF.
Чтобы обнаружить таблицу, не удовлетворяющую 5NF , обычно необходимо тщательно изучить данные. Предположим, что таблица из примера 4NF с небольшим изменением данных и давайте проверим, удовлетворяет ли она 5NF :
Разложение этой таблицы снижает избыточность, в результате чего получаются следующие две таблицы:
Запрос, объединяющий эти таблицы, вернет следующие данные:
JOIN возвращает на три строки больше, чем должно; добавление еще одной таблицы для уточнения связи приводит к получению трех отдельных таблиц:
Что теперь вернет JOIN? На самом деле, объединить эти три таблицы невозможно. Это означает, что невозможно было разложить Franchisee - Book - Location без потери данных, поэтому таблица уже удовлетворяет 5NF .
CJ Date утверждал, что только база данных в 5NF является по-настоящему «нормализованной». [13]
Давайте посмотрим на таблицу Book из предыдущих примеров и посмотрим, удовлетворяет ли она доменно-ключевой нормальной форме :
Логично, что Толщина определяется количеством страниц. Это значит, что она зависит от Pages , что не является ключом. Давайте приведем пример соглашения, согласно которому книга объемом до 350 страниц считается «тонкой», а книга объемом более 350 страниц считается «толстой».
Это соглашение технически является ограничением, но оно не является ни ограничением домена, ни ограничением ключа; поэтому мы не можем полагаться на ограничения домена и ограничения ключа для сохранения целостности данных.
Другими словами – ничто не мешает нам указать, например, «Толстая» для книги, в которой всего 50 страниц – и это сделает таблицу нарушающей ДКНФ .
Чтобы решить эту проблему, создается таблица, содержащая перечисление, определяющее толщину , и этот столбец удаляется из исходной таблицы:
Таким образом, нарушение целостности домена устранено, и таблица находится в DKNF .
Простое и интуитивно понятное определение шестой нормальной формы заключается в том, что «таблица находится в 6NF , когда строка содержит первичный ключ и не более одного другого атрибута» . [14]
Это означает, например, что таблица Publisher была разработана при создании 1NF:
необходимо дополнительно разложить на две таблицы:
Очевидным недостатком 6NF является разрастание таблиц, необходимых для представления информации об одной сущности. Если таблица в 5NF имеет один столбец первичного ключа и N атрибутов, представление той же информации в 6NF потребует N таблиц; многополевые обновления одной концептуальной записи потребуют обновлений нескольких таблиц; а вставки и удаления аналогично потребуют операций по нескольким таблицам. По этой причине в базах данных, предназначенных для обслуживания потребностей онлайн-обработки транзакций (OLTP), 6NF не следует использовать.
Однако в хранилищах данных , которые не допускают интерактивных обновлений и которые специализированы для быстрых запросов к большим объемам данных, некоторые СУБД используют внутреннее представление 6NF, известное как столбчатое хранилище данных . В ситуациях, когда количество уникальных значений столбца намного меньше количества строк в таблице, столбчатое хранилище позволяет значительно экономить пространство за счет сжатия данных. Столбчатое хранилище также позволяет быстро выполнять запросы диапазона (например, показывать все записи, где определенный столбец находится между X и Y или меньше X.)
Однако во всех этих случаях разработчику базы данных не нужно вручную выполнять нормализацию 6NF, создавая отдельные таблицы. Некоторые СУБД, которые специализируются на хранении данных, такие как Sybase IQ , используют столбчатое хранилище по умолчанию, но разработчик все равно видит только одну многостолбцовую таблицу. Другие СУБД, такие как Microsoft SQL Server 2012 и более поздние версии, позволяют указать «индекс columnstore» для конкретной таблицы. [15]