Нормализация базы данных

Эта статья требует внимания эксперта по базам данных . Подробности смотрите на странице обсуждения . Базы данных WikiProject могут помочь нанять эксперта. ( Март 2018 г. )

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

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

Цели [ править ]

Основная цель первой нормальной формы, определенной Коддом в 1970 году, заключалась в том, чтобы разрешить запрашивать данные и манипулировать ими с использованием «универсального подъязыка данных», основанного на логике первого порядка . ^[1] ( SQL является примером такого подъязыка данных, хотя Кодд считал его серьезно несовершенным. ^[2] )

Цели нормализации за пределами 1NF (первая нормальная форма) были сформулированы Коддом следующим образом:

1. Чтобы освободить коллекцию отношений от нежелательных зависимостей вставки, обновления и удаления.
2. Чтобы уменьшить потребность в реструктуризации коллекции отношений по мере введения новых типов данных и, таким образом, увеличить срок службы прикладных программ.
3. Сделать реляционную модель более информативной для пользователей.
4. Сделать набор отношений нейтральным по отношению к статистике запросов, где эта статистика может меняться с течением времени.

-  EF Codd, «Дальнейшая нормализация реляционной модели базы данных» ^[3]

Обновление аномалия . Сотрудник 519 показан как имеющий разные адреса в разных записях.

Вставки аномалии . До тех пор, пока новому преподавателю, доктору Ньюсому, не будет поручено читать хотя бы один курс, его или ее данные не могут быть записаны.

Удаления аномалия . Вся информация о докторе Гидденсе теряется, если он или она временно перестают посещать какие-либо курсы.

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

• Аномалия обновления. Одна и та же информация может быть представлена ​​в нескольких строках; поэтому обновления отношения могут привести к логическим несоответствиям. Например, каждая запись в отношении «Навыки сотрудников» может содержать идентификатор сотрудника, адрес сотрудника и квалификацию; таким образом, изменение адреса для конкретного сотрудника может потребоваться применить к нескольким записям (по одной для каждого навыка). Если обновление было успешным только частично - адрес сотрудника обновляется в некоторых записях, но не в других - тогда связь остается в несогласованном состоянии. В частности, отношение дает противоречивые ответы на вопрос о том, каков адрес конкретного сотрудника. Это явление известно как аномалия обновления.
• Аномалия вставки. Есть обстоятельства, при которых некоторые факты вообще невозможно зафиксировать. Например, каждая запись в отношении «Факультет и их курсы» может содержать идентификатор факультета, название факультета, дату приема на работу преподавателя и код курса. Таким образом, мы можем записать сведения о любом преподавателе, который преподает хотя бы один курс, но мы не можем записать вновь нанятого преподавателя, который еще не назначен для преподавания каких-либо курсов, за исключением установки кода курса равным нулю. Это явление известно как аномалия вставки.
• Аномалия удаления. При определенных обстоятельствах удаление данных, представляющих определенные факты, влечет за собой удаление данных, представляющих совершенно разные факты. Отношение «Преподаватели и их курсы», описанное в предыдущем примере, страдает от этого типа аномалии, поскольку, если преподаватель временно перестает быть назначенным на какие-либо курсы, мы должны удалить последнюю из записей, в которых этот преподаватель появляется, эффективно также удаление преподавателя, если мы не установили Код курса равным нулю. Это явление известно как аномалия удаления.

Минимизировать редизайн при расширении структуры базы данных [ править ]

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

Нормализованные отношения и отношения между одним нормализованным отношением и другим отражают концепции реального мира и их взаимосвязи.

Пример [ править ]

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

Каждому покупателю соответствует «повторяющаяся группа» транзакций. Таким образом, автоматическая оценка любого запроса, относящегося к транзакциям клиентов, в целом включает два этапа:

1. Распаковка одной или нескольких групп транзакций клиентов, позволяющая исследовать отдельные транзакции в группе, и
2. Получение результата запроса по результатам первого этапа

Например, чтобы узнать денежную сумму всех транзакций, которые произошли в октябре 2003 года для всех клиентов, система должна знать, что она должна сначала распаковать группу транзакций каждого клиента, а затем суммировать суммы всех полученных таким образом транзакций. где Дата сделки приходится на октябрь 2003 года.

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

В модифицированной структуре первичным ключом является {Cust. ID} в первом отношении, {Cust. ID, Тр. ID} во втором отношении.

Теперь каждая строка представляет отдельную транзакцию по кредитной карте, и СУБД может получить интересующий ответ, просто найдя все строки с датой, приходящейся на октябрь, и суммируя их суммы. Структура данных размещает все значения на равных основаниях, открывая каждое из них напрямую СУБД, поэтому каждое потенциально может напрямую участвовать в запросах; тогда как в предыдущей ситуации некоторые значения были встроены в структуры нижнего уровня, которые нужно было обрабатывать специально. Соответственно, нормализованный дизайн поддается обработке запросов общего назначения, тогда как ненормализованный дизайн - нет. Нормализованная версия также позволяет пользователю изменять имя клиента в одном месте и защищает от ошибок, которые возникают, если имя клиента неправильно написано в некоторых записях.

Нормальные формы [ править ]

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

Неформально отношение реляционной базы данных часто описывается как «нормализованное», если оно соответствует третьей нормальной форме. ^{[7] В} большинстве отношений 3NF отсутствуют аномалии вставки, обновления и удаления.

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

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

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

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

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

Однако стоит отметить, что нормальные формы, выходящие за рамки 4NF, представляют в основном академический интерес, поскольку проблемы, которые они существуют, редко возникают на практике. ^[12]

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

Исходные данные [ править ]

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

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

Удовлетворение 1NF [ править ]

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

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

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

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

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

Книга может соответствовать многим предметам, так же как предмет может соответствовать многим книгам. Это означает также необходимость определения отношения « многие ко многим» , что достигается путем создания таблицы ссылок : ^[11]

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

Удовлетворение 2NF [ править ]

Таблица Book имеет один ключ-кандидат (который, следовательно, является первичным ключом ), составной ключ {Title, Format} . ^[13] Рассмотрим следующий фрагмент таблицы:

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

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

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

Удовлетворение 3NF [ править ]

Таблица Book по- прежнему имеет транзитивную функциональную зависимость ({Author Nationality} зависит от {Author}, которая зависит от {Title}). Аналогичное нарушение существует для жанра ({Название жанра} зависит от {Genre ID}, который зависит от {Название}). Следовательно, таблица Book не входит в 3NF. Чтобы сделать это в 3NF, давайте воспользуемся следующей структурой таблицы, тем самым исключив транзитивные функциональные зависимости, поместив {Author Nationality} и {Genre Name} в соответствующие таблицы:

Удовлетворение EKNF [ править ]

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

Удовлетворение 4NF [ править ]

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

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

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

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

Удовлетворение ETNF [ править ]

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

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

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

Декомпозиция обеспечивает соответствие ETNF .

Удовлетворение 5NF [ править ]

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

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

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

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

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

CJ Date утверждал, что только база данных в 5NF действительно «нормализована». ^[17]

Удовлетворение DKNF [ править ]

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

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

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

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

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

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

Удовлетворение 6NF [ править ]

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

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

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

Очевидным недостатком 6NF является большое количество таблиц, необходимых для представления информации об одном объекте. Если таблица в 5NF имеет один столбец первичного ключа и N атрибутов, для представления той же информации в 6NF потребуется N таблиц; обновления нескольких полей одной концептуальной записи потребуют обновления нескольких таблиц; а вставки и удаления аналогичным образом потребуют операций с несколькими таблицами. По этой причине в базах данных, предназначенных для обслуживания нужд онлайн-обработки транзакций , не следует использовать 6NF.

Однако в хранилищах данных , которые не допускают интерактивных обновлений и которые предназначены для быстрого запроса больших объемов данных, некоторые СУБД используют внутреннее представление 6NF, известное как столбцовое хранилище данных . В ситуациях, когда количество уникальных значений столбца намного меньше количества строк в таблице, хранение, ориентированное на столбцы, позволяет значительно сэкономить пространство за счет сжатия данных. Столбцовое хранилище также позволяет быстро выполнять запросы диапазона (например, отображать все записи, в которых конкретный столбец находится между X и Y или меньше X.)

Однако во всех этих случаях разработчику базы данных не нужно выполнять нормализацию 6NF вручную, создавая отдельные таблицы. Некоторые СУБД, специализирующиеся на хранении данных , например Sybase IQ , по умолчанию используют столбчатое хранилище, но разработчик по-прежнему видит только одну таблицу с несколькими столбцами. Другие СУБД, такие как Microsoft SQL Server 2012 и более поздние версии, позволяют указывать «индекс columnstore» для конкретной таблицы. ^[19]

См. Также [ править ]

• Денормализация
• Рефакторинг базы данных

• Разложение соединения без потерь

Примечания и ссылки [ править ]

Дальнейшее чтение [ править ]

• Дата, CJ (1999), Введение в системы баз данных (8-е изд.). Эддисон-Уэсли Лонгман. ISBN 0-321-19784-4 . 
• Кент, W. (1983) Простое руководство по пяти нормальным формам в теории реляционных баз данных , Связь ACM, т. 26. С. 120–125.
• Х.-Ж. Шек П. Структуры данных Pistor для интегрированной системы управления базами данных и поиска информации

Внешние ссылки [ править ]

• Кент, Уильям (февраль 1983 г.). «Простое руководство по пяти нормальным формам в теории реляционных баз данных» . Коммуникации ACM . 26 (2): 120–125. DOI : 10.1145 / 358024.358054 . S2CID  9195704 .
• Основы нормализации базы данных Майк Чаппл (About.com)
• Введение в нормализацию базы данных , часть 2
• Введение в нормализацию базы данных Майка Хиллера.
• Учебник по первым трем нормальным формам от Фреда Коулсона
• Описание основ нормализации баз данных от Microsoft
• Нормализация в СУБД от Чайтаньи (beginnersbook.com)
• Пошаговое руководство по нормализации базы данных
• ETNF - основная нормальная форма кортежа