Нормализация в SQL: 1NF, 2NF, 3NF и BCNF

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

Для лучшего понимания рассмотрим следующую схему: Студент (Имя, Адрес, Тема, Класс)

В этой схеме есть несколько проблем или недостатков.

1) Избыточность : адрес студента повторяется для каждого предмета, на который он зарегистрирован.

2) Аномалия обновления : возможно, мы обновили адрес в одном кортеже (строке), оставив его без изменений в других строках. Таким образом, у нас не будет постоянного уникального адреса для каждого студента.

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

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

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

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

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

На сегодняшний день существует семь нормальных форм:

• Первая нормальная форма (1NF)
• Вторая нормальная форма (2NF)
• Третья нормальная форма (3NF)
• Нормальная форма Бойса-Кодда (BCNF)
• Четвертая нормальная форма (4NF)
• Пятая нормальная форма (5NF)
• Шестая или нормальная форма доменного ключа (6NF)

Читать: Типы представлений в SQL

Первая нормальная форма (1NF или минимальная форма)

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

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

Здесь его нет в 1 НФ. Столбец Телефонные номера не является атомарным или не имеет скалярного значения, т.е. имеет более одного значения, что не может быть разрешено в 1 NF.

Сделать это 1 NF

• Сначала мы разобьем (разложим) нашу единственную таблицу на две.
• Каждая таблица должна содержать информацию только об одном объекте.

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

Оформить заказ: самые распространенные вопросы и ответы на собеседованиях по SQL

Вторая нормальная форма

Каждая нормальная форма имеет больше ограничивающих критериев, чем ее предшественница. Таким образом, любая таблица, находящаяся во второй нормальной форме (2NF) или выше, по определению также находится в 1NF. С другой стороны, таблица, находящаяся в 1NF, может находиться во 2NF, а может и не находиться; если он находится во 2НФ, он может быть или не быть в 3НФ, и так далее.

Говорят, что таблица 1NF находится во 2NF тогда и только тогда, когда ни один из ее непростых атрибутов функционально не зависит от части (правильного подмножества) ключа-кандидата. (Атрибут nonprime не принадлежит ни одному ключу-кандидату.)

Обратите внимание, что если в таблице 1NF нет составных ключей-кандидатов (ключи-кандидаты, состоящие из более чем одного атрибута), таблица автоматически находится во 2NF.

Проверьте, если отношение R (A, B, C, D, E) с FD установлено как { BC ? Д, АС? БЭ, Б? E } находится во 2NF?

• Как мы видим, замыкание AC есть (AC)+ = {A, C, B, E, D} с применением алгоритма принадлежности. Но ни одно из его подмножеств не может определить все атрибуты отношения сами по себе, поэтому ключом-кандидатом для этого отношения является AC. Более того, ни A, ни C не могут быть получены из какого-либо другого атрибута отношения, поэтому будет только 1 ключ-кандидат, которым является {AC}.
• Здесь {A, C} — простые атрибуты, а {B, D, E} — непростые атрибуты.
• Отношение R уже находится в 1-й нормальной форме, поскольку реляционная СУБД в 1NF не допускает многозначный или составной атрибут.

ДО Н.Э ? D находится во 2-й нормальной форме, потому что BC не является правильным подмножеством ключа-кандидата AC,

переменный ток? BE находится во 2-й нормальной форме, поскольку сам AC является ключом-кандидатом, а

Б? E находится во 2-й нормальной форме B не является правильным подмножеством ключа-кандидата AC.

Таким образом, данное отношение R находится во 2-й нормальной форме.

Третья нормальная форма

Говорят, что таблица находится в 3НФ тогда и только тогда, когда для каждой из ее функциональных зависимостей.

X → A выполняется хотя бы одно из следующих условий:

• X содержит A (то есть X → A является тривиальной функциональной зависимостью), или
• X является суперключом, или
• А является первичным атрибутом (т. е. А присутствует в ключе-кандидате)

Другое определение 3NF гласит, что каждый неключевой атрибут R нетранзитивно зависит (т. е. напрямую зависит) от первичного ключа R. Это означает, что ни один непервичный атрибут (не являющийся частью ключа-кандидата) функционально не зависит от других непервичных атрибутов. Если существуют две зависимости, такие что A ? B и BC, то из этих FD мы можем получить A ? C. Эта зависимость AC транзитивна.

Пример 3NF:

Рассмотрим следующее отношение Order (Order#, Part, Supplier, UnitPrice, QtyOrdered) с заданным набором FD:

Заказ# ? Деталь, поставщик, количество заказанных и поставщик, деталь? Цена за единицу)

Здесь Order# является ключом к отношению.

Используя аксиомы Амстронга, получаем

Заказ# ? Часть, Заказ? Поставщик и заказ? Кол-воЗаказано.

Заказ# ? Часть, Поставщик и Поставщик, Часть? Цена за единицу, оба дают Заказ №? Цена за единицу.

Таким образом, мы видим, что все непростые атрибуты зависят от ключа (Заказ №). Однако существует транзитивная зависимость между Order# и UnitPrice. Так что это отношение не находится в 3НФ. Как мы делаем это в 3NF?

Мы не можем хранить UnitPrice любой Детали, поставляемой каким-либо Поставщиком, если только кто-то не разместит заказ на эту Деталь. Поэтому нам придется разложить таблицу, чтобы она следовала 3НФ, следующим образом.

Заказ (Заказ №, Деталь, Поставщик, Кол-воЗаказа) и Основная цена (Деталь, Поставщик, Цена за единицу).

Теперь нет никаких транзитивных зависимостей. Отношение находится в 3НФ.

Читайте также: SQL для науки о данных

Изучайте онлайн-курсы по программному обеспечению от лучших университетов мира. Участвуйте в программах Executive PG, Advanced Certificate Programs или Master Programs, чтобы ускорить свою карьеру.

Заключение

Это еще не все, что нужно для нормализации, например, BCNF, 4NF, 5NF и 6NF. Короче говоря, BCNF — это не что иное, как расширение 3NF, так как последнее правило 3NF здесь не применяется. Все функциональные зависимости должны иметь ключевые атрибуты слева и ни одного справа. (BCNF также называется 3.5NF). Однако нормальные формы из 4NF и выше практически не применяются в обычной практике.

Если вам интересно узнать больше о разработке полного стека, ознакомьтесь с программой Executive PG upGrad и IIIT-B в разработке программного обеспечения с полным стеком, которая предназначена для работающих профессионалов и предлагает более 500 часов тщательного обучения, более 9 проектов, и задания, статус выпускника IIIT-B, практические практические проекты и помощь в трудоустройстве в ведущих фирмах.