Первая нормальная форма — Основы реляционных баз данных
Чтобы облегчить считываемость информации в таблице, программисты приводят данные, которые представлены в реляционной модели, к нормальной форме. В этом уроке мы узнаем, что это за форма, а также разберем ее первый уровень.
Нормальная форма
Возьмем для примера интернет-магазин, в котором продается электроника. Когда пользователь делает заказ, в базу данных заносится запись об этом. В нее входит вся необходимая информация: данные пользователя, какой товар он купил, сколько он стоил и адрес доставки. Затем эти данные используются всеми подразделениями интернет-магазина — от бухгалтеров до службы доставки.
Таблица order_items
| first_name | last_name | address | item | price |
|---|---|---|---|---|
| Сергей | Иванов | Москва, ул. Промышленная | утюг | 15.00 |
| Иван | Петров | Самара, ул. Энгельса | кофеварка | 5000.00 |
| Виктор | Сидоров | Омск, ул. Дворцовая | утюг, телевизор | 1000.00, 6500.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
В первой строке последнего столбца цена указана в долларах, в остальных записях — это рубли. Последняя запись повторяет предыдущую, потому что этот заказ выполнил тот же человек, но сделал это в другое время.
В этой табличке много повторяющейся информации. Приведем ее к правильной структуре с точки зрения реляционной модели. Для этого приведем данные к нормальной форме — это требования, которые минимизируют избыточность данных, потенциально приводящих к логическим ошибкам.
Всего существует шесть нормальных форм, которые включают определенные требования. С каждым следующим уровнем требования все жестче, так как включают в себя предыдущие уровни.
В рамках курса мы разберем три нормальные формы. В этом уроке познакомимся с первой.
Первая нормальная форма
Первая нормальная форма сводится к трем правилам:
- Каждая ячейка таблицы может хранить только одно значение
- Все данные в одной колонке могут быть только одного типа
- Каждая запись в таблице должна однозначно отличаться от других записей
Разберем каждое правило подробнее.
Каждая ячейка – одно значение
Вернемся к примеру выше. У одной записи поля item и price содержат два значения через запятую. У такого способа организации данных много недостатков. Например, пропадает возможность делать обычную выборку по условиям:
-- Как найти записи о всех проданных утюгах? SELECT * FROM order_items WHERE item = "?"; Другая проблема связана с типами данных. Поле price в таблице order_items имеет числовой тип (numeric). Если мы захотим хранить там более одного значения, то тип превратится в строковый, а все данные станут обычными строками.
При такой организации невозможно проверить корректность данных и формат числа. Становится проблематично выполнить агрегирующие запросы, например, считать выручку за определенный месяц одним запросом.
Чтобы избавиться от перечислений в ячейках, можно создать новые записи:
| first_name | last_name | address | item | price |
|---|---|---|---|---|
| Сергей | Иванов | Москва, ул. Промышленная | утюг | 15.00 |
| Иван | Петров | Самара, ул. Энгельса | кофеварка | 5000.00 |
| Виктор | Сидоров | Омск, ул. Дворцовая | утюг | 1000.00 |
| Виктор | Сидоров | Омск, ул. Дворцовая | телевизор | 6500.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
Теперь на одной строке находится информация только по одному товару. Так мы избавились от перечислений в поле, что позволит выполнять агрегирующие запросы, а также не будет путаницы с типами данных.
Данные одного типа
Снова вернемся к таблице. Верхняя запись в ней содержит цену в долларах, хотя все остальные цены указаны в рублях. Технически база никак не укажет на это. И доллары, и рубли представлены числами, но с точки зрения программы у этих чисел разная природа.
Разные данные в рамках одного поля тоже не дают выполнить агрегирующие запросы, например, поиск сумм, максимального, минимального. Еще усложняется обработка данных на уровне кода. В коде придется каким-то образом понимать, что из себя представляют данные.
Вот еще несколько примеров с похожей ситуацией:
- Хранение даты свадьбы в поле «день рождения»
- Хранение номера телефона вместо адреса в поле «адрес»
Исправленная версия таблицы:
| first_name | last_name | address | item | price |
|---|---|---|---|---|
| Сергей | Иванов | Москва, ул. Промышленная | утюг | 1000.00 |
| Иван | Петров | Самара, ул. Энгельса | кофеварка | 5000.00 |
| Виктор | Сидоров | Омск, ул. Дворцовая | утюг | 1000.00 |
| Виктор | Сидоров | Омск, ул. Дворцовая | телевизор | 6500.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
Мы сконвертировали цену утюга в первой строке из долларов в рубли. Теперь у данных в поле price один тип. Так программе будет легче выполнять агрегирующие запросы.
Уникальные записи
Последние две записи в таблице выглядят идентично, хотя это два разных заказа. Их сделал один человек, но в разное время:
| first_name | last_name | address | item | price |
|---|---|---|---|---|
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
| Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
Реляционная модель требует от нас уникальности каждой записи. Иначе нельзя понять, что к чему относится и с какой записью нужно работать при изменениях. Можно начать править не то и потерять важную информацию. При этом мы не можем полагаться на порядок данных внутри таблицы, так как он не гарантирован.
Реализовать уникальность можно несколькими способами, например, добавить новое поле с датой заказа, которое сделает запись уникальной. Этот способ не очень надежный и не очень удобный в работе. Придется постоянно анализировать весь набор полей.
Лучше добавить первичный ключ (PRIMARY KEY) — поле или набор полей, которые содержат уникальное значение для каждой записи. Первичный ключ не может меняться, его значение однозначно определяет любую запись в таблице.
Разберем два вида первичного ключа:
- Естественный — когда используются значения из окружающего мира, например, email, ФИО или паспортные данные. При этом нужно убедиться, что ключ не будет повторяться. Такие первичные ключи используют редко из-за их ненадежности. Часто они не уникальны и могут изменяться или повторяться. Например, номер паспорта меняется при смене документа
- Суррогатный — когда используются автоматически генерируемые уникальные значения. Такой ключ поддерживается любой базой данных «из коробки». Иногда это просто числа, а иногда — сложные число-буквенные строки или хеши
Добавим в нашу таблицу первичный ключ:
| id | first_name | last_name | address | item | price |
|---|---|---|---|---|---|
| 8 | Сергей | Иванов | Москва, ул. Промышленная | утюг | 1000.00 |
| 2 | Иван | Петров | Самара, ул. Энгельса | кофеварка | 5000.00 |
| 7 | Виктор | Сидоров | Омск, ул. Дворцовая | утюг | 1000.00 |
| 4 | Виктор | Сидоров | Омск, ул. Дворцовая | телевизор | 6500.00 |
| 9 | Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
| 6 | Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
Первичный ключ принято создавать первым полем с названием id. Для первичного ключа обязательно указывать PRIMARY KEY в описании таблицы:
-- Первичный ключ только один на таблицу CREATE TABLE products ( id bigint PRIMARY KEY, first_name varchar(255), last_name varchar(255), address varchar(255), item varchar(255), price numeric -- специальный тип данных, который подходит под работу с деньгами. Обеспечивает высокую точность при расчетах. ); Такой ключ все еще нужно формировать самостоятельно, но теперь база данных сама следит за уникальностью. При попытке создать запись с повторяющимися первичными ключами возникнет ошибка.
Выводы
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
Нормализация отношений. Первая и вторая нормальные формы
Нормализация отношений (таблиц) — одна из основополагающих частей теории реляционных баз данных. Нормализация имеет своей целью избавиться от избыточности в отношениях и модифицировать их структуру таким образом, чтобы процесс работы с ними не был обременён различными посторонними сложностями. При игнорировании такого подхода эффективность проектирования стремительно снижается, что вкупе с прочими подобными вольностями может привести к критическим последствиям.
Любому специалисту, по роду своей деятельности так или иначе связанному с проектированием реляционных баз данных, полезно понимать и уметь осуществить нормализацию отношений. И этим постом хотелось бы начать небольшую серию публикаций, посвящённых нормальным формам, имеющую целью дать тем читателям Хабрахабра, которые по различным обстоятельствам ещё не освоили эту тему, возможность легко заполнить этот пробел в знаниях.
Статья не имеет своей целью подробное и точное изложение принципов нормализациии, поскольку это, очевидно, невозможно в рамках блога в силу больших объёмов информации, необходимых для публикации при таком подходе. Кроме этого, для такой цели существует большое количество литературы, написанной прекрасными специалистами. Моя же задача, как я считаю, заключается в том, чтобы популярно продемонстрировать и объяснить основные принципы.
Используемые термины
Атрибут — свойство некоторой сущности. Часто называется полем таблицы.
Домен атрибута — множество допустимых значений, которые может принимать атрибут.
Кортеж — конечное множество взаимосвязанных допустимых значений атрибутов, которые вместе описывают некоторую сущность (строка таблицы).
Отношение — конечное множество кортежей (таблица).
Схема отношения — конечное множество атрибутов, определяющих некоторую сущность. Иными словами, это структура таблицы, состоящей из конкретного набора полей.
Проекция — отношение, полученное из заданного путём удаления и (или) перестановки некоторых атрибутов.
Функциональная зависимость между атрибутами (множествами атрибутов) X и Y означает, что для любого допустимого набора кортежей в данном отношении: если два кортежа совпадают по значению X, то они совпадают по значению Y. Например, если значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom. Обозначение: -> .
Первая нормальная форма
Отношение находится в первой нормальной форме (сокращённо 1НФ), если все его атрибуты атомарны, то есть если ни один из его атрибутов нельзя разделить на более простые атрибуты, которые соответствуют каким-то другим свойствам описываемой сущности.
Будем называть исходное отношение основным, а значение неатомарного атрибута — подчинённым.
Для того, чтобы нормализовать исходное отношение, атрибуты которого неатомарны, необходимо объединить схемы основного и подчинённого отношений. Кроме того, если, например, таблица, соответствующая ненормализованному отношению уже содержится в БД и заполнена информацией, задача усложняется тем, что значение неатомарного атрибута может в свою очередь содержать несколько кортежей.
Следует пояснить сказанное на примере. Рассмотрим отношение, имеющее атрибуты «Код сотрудника», «ФИО», «Должность», «Проекты». Очевидно, что один сотрудник может работать над несколькими проектами. Предположим, что проект описывается идентификатором, названием и датой сдачи.
| Код сотрудника | ФИО | Должность | Проекты |
| 1 | Иванов Иван Иванович | Программист | ID: 123; Название: Система управления паровым котлом; Дата сдачи: 30.09.2011 ID: 231; Название: ПС для контроля и оповещения о превышениях ПДК различных газов в помещении; Дата сдачи: 30.11.2011 ID: 321; Название: Модуль распознавания лиц для защитной системы; Дата сдачи: 01.12.2011 |
Легко заметить, что не все атрибуты этого отношения атомарны (неделимы). В частности, атрибут «Проекты» можно разделить на три более простых атрибута: «Код проекта», «Название», «Дата сдачи», а значение этого атрибута для сотрудника Иван Иванович Иванов содержит несколько кортежей — информацию о трёх проектах.
Примечание: с некоторой точки зрения атрибут «ФИО» можно также считать неатомарным и в таком случае его также следует разделить на более простые, как «Фамилия», «Имя», «Отчество».
- Создать новое отношение, схема которого будет получена путём слияния основной и подчинённой схем исходного отношения в одну.
- Для каждого кортежа исходного отношения включить в новое столько строк, сколько кортежей содержится в подчинённом отношении этого кортежа.
- Заполнить значения атрибутов нового отношения, соответствующих атрибутам подчинённого отношения.
- Заполнить строки нового отношения значениями атомарных атрибутов исходного.
Результат будет выглядеть так:
| Код сотрудника | ФИО | Должность | Код проекта | Название | Дата сдачи |
| 1 | Иванов Иван Иванович | Программист | 123 | Система управления паровым котлом | 30.09.2011 |
| 1 | Иванов Иван Иванович | Программист | 231 | ПС для контроля и оповещения о превышениях ПДК различных газов в помещении | 30.11.2011 |
| 1 | Иванов Иван Иванович | Программист | 321 | Модуль распознавания лиц для защитной системы | 01.12.2011 |
Вторая нормальная форма
Ясно, что отношение, находящееся в 1НФ, также может обладать избыточностью. Для её устранения предназначена вторая нормальная форма. Но прежде чем приступить к её описанию, сначала следует выявить недостатки первой.
Пусть исходное отношение содержит информацию о поставке некоторых товаров и их поставщиках.
| Код поставщика | Город | Статус города | Код товара | Количество |
| 1 | Москва | 20 | 1 | 300 |
| 1 | Москва | 20 | 2 | 400 |
| 1 | Москва | 20 | 3 | 100 |
| 2 | Ярославль | 10 | 4 | 200 |
| 3 | Ставрополь | 30 | 5 | 300 |
| 3 | Ставрополь | 30 | 6 | 400 |
| 4 | Псков | 15 | 7 | 100 |
- Аномалия вставки. В отношение нельзя добавить информацию о поставщике, который ещё не поставил ни одного товара.
- Аномалия удаления. Если от поставщика была только одна поставка, то при удалении информации о ней будет удалена и вся информация о поставщике.
- Аномалия обновления. Если необходимо изменить какую-либо информацию о поставщике (например, поставщик переехал в другой город), то придётся изменять значения атрибутов во всех записях о поставках от него.
- В первую следует включить первичный ключ и все неключевые атрибуты явно зависимые от него.
- В остальные проекции (в данном случае она одна) будут включены неключевые атрибуты, зависящие от первичного ключа неявно, вместе с той частью первичного ключа, от которой эти атрибуты зависят явно.
| Код поставщика | Код товара | Количество |
| 1 | 1 | 300 |
| 1 | 2 | 400 |
| 1 | 3 | 100 |
| 2 | 4 | 200 |
| 3 | 5 | 300 |
| 3 | 6 | 400 |
| 4 | 7 | 100 |
Первому отношению теперь соответствуют следующие функциональные зависимости:
->
| Код поставщика | Город | Статус города |
| 1 | Москва | 20 |
| 2 | Ярославль | 10 |
| 3 | Ставрополь | 30 |
| 4 | Псков | 15 |
Такое разбиение устранило аномалии, описанные выше: можно добавить информацию о поставщике, который ещё не поставлял товар, удалить информацию о поставке без удаления информации о поставщике, а также легко обновить информацию в случае если поставщик переехал в другой город.
Теперь можно сформулировать определение второй нормальной формы, до которого, скорее всего, читатель уже смог догадаться самостоятельно: отношение находится во второй нормальной форме (сокращённо 2НФ) тогда и только тогда, когда оно находится в первой нормальной форме и каждый его неключевой атрибут неприводимо зависим от первичного ключа.
Литература
Для более глубокого и основательного изучения рассмотренной темы, рекомендуется книга «Введение в системы баз данных» Криса Дж. Дейта, на основе материалов которой и была написана данная статья.
- реляционные базы данных
- нормализация отношений
- нормальные формы
- первая нормальная форма
- 1НФ
- вторая нормальная форма
- 2НФ
Нормализация отношений. Шесть нормальных форм
В данной теме я затрону 6 нормальных форм и методы приведения таблиц в эти формы.
Процесс проектирования БД с использование метода НФ является итерационным и заключается в последовательном переводе отношения из 1НФ в НФ более высокого порядка по определенным правилам. Каждая следующая НФ ограничивается определенным типом функциональных зависимостей и устранением соответствующих аномалий при выполнении операций над отношениями БД, а также сохранении свойств предшествующих НФ.
Используемые термины
Атрибут — свойство некоторой сущности. Часто называется полем таблицы.
Домен атрибута — множество допустимых значений, которые может принимать атрибут.
Кортеж — конечное множество взаимосвязанных допустимых значений атрибутов, которые вместе описывают некоторую сущность (строка таблицы).
Отношение — конечное множество кортежей (таблица).
Схема отношения — конечное множество атрибутов, определяющих некоторую сущность. Иными словами, это структура таблицы, состоящей из конкретного набора полей.
Проекция — отношение, полученное из заданного путём удаления и (или) перестановки некоторых атрибутов.
Функциональная зависимость между атрибутами (множествами атрибутов) X и Y означает, что для любого допустимого набора кортежей в данном отношении: если два кортежа совпадают по значению X, то они совпадают по значению Y. Например, если значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom. Обозначение: -> .
Нормальная форма — требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц).
Метод нормальных форм (НФ) состоит в сборе информации о объектах решения задачи в рамках одного отношения и последующей декомпозиции этого отношения на несколько взаимосвязанных отношений на основе процедур нормализации отношений.
Цель нормализации: исключить избыточное дублирование данных, которое является причиной аномалий, возникших при добавлении, редактировании и удалении кортежей(строк таблицы).
Аномалией называется такая ситуация в таблице БД, которая приводит к противоречию в БД либо существенно усложняет обработку БД. Причиной является излишнее дублирование данных в таблице, которое вызывается наличием функциональных зависимостей от не ключевых атрибутов.
Аномалии-модификации проявляются в том, что изменение одних данных может повлечь просмотр всей таблицы и соответствующее изменение некоторых записей таблицы.
Аномалии-удаления — при удалении какого либо кортежа из таблицы может пропасть информация, которая не связана на прямую с удаляемой записью.
Аномалии-добавления возникают, когда информацию в таблицу нельзя поместить, пока она не полная, либо вставка записи требует дополнительного просмотра таблицы.
Первая нормальная форма
Отношение находится в 1НФ, если все его атрибуты являются простыми, все используемые домены должны содержать только скалярные значения. Не должно быть повторений строк в таблице.
Например, есть таблица «Автомобили»:
| Фирма | Модели |
| BMW | M5, X5M, M1 |
| Nissan | GT-R |
Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным. Преобразуем таблицу к 1НФ:
| Фирма | Модели |
| BMW | M5 |
| BMW | X5M |
| BMW | M1 |
| Nissan | GT-R |
Вторая нормальная форма
Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут неприводимо зависит от Первичного Ключа(ПК).
Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость.
Например, дана таблица:
| Модель | Фирма | Цена | Скидка |
| M5 | BMW | 5500000 | 5% |
| X5M | BMW | 6000000 | 5% |
| M1 | BMW | 2500000 | 5% |
| GT-R | Nissan | 5000000 | 10% |
Таблица находится в первой нормальной форме, но не во второй. Цена машины зависит от модели и фирмы. Скидка зависят от фирмы, то есть зависимость от первичного ключа неполная. Исправляется это путем декомпозиции на два отношения, в которых не ключевые атрибуты зависят от ПК.
| Модель | Фирма | Цена |
| M5 | BMW | 5500000 |
| X5M | BMW | 6000000 |
| M1 | BMW | 2500000 |
| GT-R | Nissan | 5000000 |
| Фирма | Скидка |
| BMW | 5% |
| Nissan | 10% |
Третья нормальная форма
Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа. Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых может относиться к нескольким записям таблицы в отдельные таблицы.
| Модель | Магазин | Телефон |
| BMW | Риал-авто | 87-33-98 |
| Audi | Риал-авто | 87-33-98 |
| Nissan | Некст-Авто | 94-54-12 |
Таблица находится во 2НФ, но не в 3НФ.
В отношении атрибут «Модель» является первичным ключом. Личных телефонов у автомобилей нет, и телефон зависит исключительно от магазина.
Таким образом, в отношении существуют следующие функциональные зависимости: Модель → Магазин, Магазин → Телефон, Модель → Телефон.
Зависимость Модель → Телефон является транзитивной, следовательно, отношение не находится в 3НФ.
В результате разделения исходного отношения получаются два отношения, находящиеся в 3НФ:
| Магазин | Телефон |
| Риал-авто | 87-33-98 |
| Некст-Авто | 94-54-12 |
| Модель | Магазин |
| BMW | Риал-авто |
| Audi | Риал-авто |
| Nissan | Некст-Авто |
Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы)
Определение 3НФ не совсем подходит для следующих отношений:
1) отношение имеет два или более потенциальных ключа;
2) два и более потенциальных ключа являются составными;
3) они пересекаются, т.е. имеют хотя бы один общий атрибут.
Для отношений, имеющих один потенциальный ключ (первичный), НФБК является 3НФ.
Отношение находится в НФБК, когда каждая нетривиальная и неприводимая слева функциональная зависимость обладает потенциальным ключом в качестве детерминанта.
Предположим, рассматривается отношение, представляющее данные о бронировании стоянки на день:
| Номер стоянки | Время начала | Время окончания | Тариф |
| 1 | 09:30 | 10:30 | Бережливый |
| 1 | 11:00 | 12:00 | Бережливый |
| 1 | 14:00 | 15:30 | Стандарт |
| 2 | 10:00 | 12:00 | Премиум-В |
| 2 | 12:00 | 14:00 | Премиум-В |
| 2 | 15:00 | 18:00 | Премиум-А |
- «Бережливый»: стоянка 1 для льготников
- «Стандарт»: стоянка 1 для не льготников
- «Премиум-А»: стоянка 2 для льготников
- «Премиум-B»: стоянка 2 для не льготников.
Отношение находится в 3НФ. Требования второй нормальной формы выполняются, так как все атрибуты входят в какой-то из потенциальных ключей, а неключевых атрибутов в отношении нет. Также нет и транзитивных зависимостей, что соответствует требованиям третьей нормальной формы. Тем не менее, существует функциональная зависимость Тариф → Номер стоянки, в которой левая часть (детерминант) не является потенциальным ключом отношения, то есть отношение не находится в нормальной форме Бойса — Кодда.
Недостатком данной структуры является то, что, например, по ошибке можно приписать тариф «Бережливый» к бронированию второй стоянки, хотя он может относиться только к первой стоянки.
Можно улучшить структуру с помощью декомпозиции отношения на два и добавления атрибута Имеет льготы, получив отношения, удовлетворяющие НФБК (подчёркнуты атрибуты, входящие в первичный ключ.):
| Тариф | Номер стоянки | Имеет льготы |
| Бережливый | 1 | Да |
| Стандарт | 1 | Нет |
| Премиум-А | 2 | Да |
| Премиум-В | 2 | Нет |
Бронирование
| Тариф | Время начала | Время окончания |
| Бережливый | 09:30 | 10:30 |
| Бережливый | 11:00 | 12:00 |
| Стандарт | 14:00 | 15:30 |
| Премиум-В | 10:00 | 12:00 |
| Премиум-В | 12:00 | 14:00 |
| Премиум-А | 15:00 | 18:00 |
Четвертая нормальная форма
Отношение находится в 4НФ, если оно находится в НФБК и все нетривиальные многозначные зависимости фактически являются функциональными зависимостями от ее потенциальных ключей.
В отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С.
Предположим, что рестораны производят разные виды пиццы, а службы доставки ресторанов работают только в определенных районах города. Составной первичный ключ соответствующей переменной отношения включает три атрибута: .
Такая переменная отношения не соответствует 4НФ, так как существует следующая многозначная зависимость:
→
→
То есть, например, при добавлении нового вида пиццы придется внести по одному новому кортежу для каждого района доставки. Возможна логическая аномалия, при которой определенному виду пиццы будут соответствовать лишь некоторые районы доставки из обслуживаемых рестораном районов.
Для предотвращения аномалии нужно декомпозировать отношение, разместив независимые факты в разных отношениях. В данном примере следует выполнить декомпозицию на и .
Однако, если к исходной переменной отношения добавить атрибут, функционально зависящий от потенциального ключа, например цену с учётом стоимости доставки ( → Цена), то полученное отношение будет находиться в 4НФ и его уже нельзя подвергнуть декомпозиции без потерь.
Пятая нормальная форма
Отношения находятся в 5НФ, если оно находится в 4НФ и отсутствуют сложные зависимые соединения между атрибутами.
Если «Атрибут_1» зависит от «Атрибута_2», а «Атрибут_2» в свою очередь зависит от «Атрибута_3», а «Атрибут_3» зависит от «Атрибута_1», то все три атрибута обязательно входят в один кортеж.
Это очень жесткое требование, которое можно выполнить лишь при дополнительных условиях. На практике трудно найти пример реализации этого требования в чистом виде.
Например, некоторая таблица содержит три атрибута «Поставщик», «Товар» и «Покупатель». Покупатель_1 приобретает несколько Товаров у Поставщика_1. Покупатель_1 приобрел новый Товар у Поставщика_2. Тогда в силу изложенного выше требования Поставщик_1 обязан поставлять Покупателю_1 тот же самый новый Товар, а Поставщик_2 должен поставлять Покупателю_1, кроме нового Товара, всю номенклатуру Товаров Поставщика_1. Этого на практике не бывает. Покупатель свободен в своем выборе товаров. Поэтому для устранения отмеченного затруднения все три атрибута разносят по разным отношениям (таблицам). После выделения трех новых отношений (Поставщик, Товар и Покупатель) необходимо помнить, что при извлечении информации (например, о покупателях и товарах) необходимо в запросе соединить все три отношения. Любая комбинация соединения двух отношений из трех неминуемо приведет к извлечению неверной (некорректной) информации. Некоторые СУБД снабжены специальными механизмами, устраняющими извлечение недостоверной информации. Тем не менее, следует придерживаться общей рекомендации: структуру базы данных строить таким образом, чтобы избежать применения 4НФ и 5НФ.
Пятая нормальная форма ориентирована на работу с зависимыми соединениями. Указанные зависимые соединения между тремя атрибутами встречаются очень редко. Зависимые соединения между четырьмя, пятью и более атрибутами указать практически невозможно.
Доменно-ключевая нормальная форма
Переменная отношения находится в ДКНФ тогда и только тогда, когда каждое наложенное на неё ограничение является логическим следствием ограничений доменов и ограничений ключей, наложенных на данную переменную отношения.
Ограничение домена – ограничение, предписывающее использовать для определённого атрибута значения только из некоторого заданного домена. Ограничение по своей сути является заданием перечня (или логического эквивалента перечня) допустимых значений типа и объявлением о том, что указанный атрибут имеет данный тип.
Ограничение ключа – ограничение, утверждающее, что некоторый атрибут или комбинация атрибутов является потенциальным ключом.
Любая переменная отношения, находящаяся в ДКНФ, обязательно находится в 5НФ. Однако не любую переменную отношения можно привести к ДКНФ.
Шестая нормальная форма
Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ.
Идея «декомпозиции до конца» выдвигалась до начала исследований в области хронологических данных, но не нашла поддержки. Однако для хронологических баз данных максимально возможная декомпозиция позволяет бороться с избыточностью и упрощает поддержание целостности базы данных.
Для хронологических баз данных определены U_операторы, которые распаковывают отношения по указанным атрибутам, выполняют соответствующую операцию и упаковывают полученный результат. В данном примере соединение проекций отношения должно производится при помощи оператора U_JOIN.
| Таб.№ | Время | Должность | Домашний адрес |
| 6575 | 01-01-2000:10-02-2003 | слесарь | ул.Ленина,10 |
| 6575 | 11-02-2003:15-06-2006 | слесарь | ул.Советская,22 |
| 6575 | 16-06-2006:05-03-2009 | бригадир | ул.Советская,22 |
Переменная отношения «Работники» не находится в 6НФ и может быть подвергнута декомпозиции на переменные отношения «Должности работников» и «Домашние адреса работников».
Должности работников
| Таб.№ | Время | Должность |
| 6575 | 01-01-2000:10-02-2003 | слесарь |
| 6575 | 16-06-2006:05-03-2009 | бригадир |
Домашние адреса работников
| Таб.№ | Время | Домашний адрес |
| 6575 | 01-01-2000:10-02-2003 | ул.Ленина,10 |
| 6575 | 11-02-2003:15-06-2006 | ул.Советская,22 |
Литература
Для более глубокого и основательного изучения рассмотренной темы, рекомендуется книга «Введение в системы баз данных» Криса Дж. Дейта, на основе материалов которой и была написана данная статья.
- реляционные базы данных
- БД
- нормальные формы
- нормализация отношений
- mysql
Нормальные формы: первая и вторая
Первая и вторая нормальные формы разработаны Эдгаром Коддом и являются достаточно «самоочевидными». Самоочевидность заключается в том, что отношения в первой и второй нормальной формах обладают интуитивно понятными базовыми свойствами, которые логично требовать от отношений, используемых в рамках баз данных.
Первая нормальная форма
Первая нормальная форма эквивилентна отношению в строгом смысле этого слова. Каждый из данных критериев отвечает за запрет конструкций определенного вида.
Запрещенные конструкции
Повторяющиеся группы
| CourseId | Lecturer | Phone (1) | Phone (2) |
|---|---|---|---|
| 1 | Корнеев Г. А. | 111-11-11 | |
| 2 | Киракозов А. Х. | 222-22-22 | 333-33-33 |
| 3 | Кудряшов Б. Д. | 444-44-44 | 555-55-55 |
| 4 | Сегаль А. С. | 666-66-66 |
В отношениях такого вида сложно обеспечивать консистентность данных. Рассмотрим пример выше. Первая возникающая проблема заключается в том, что при появлении преподавателя с более, чем двумя телефонами, придется изменять целиком структуру отношения. Вторая проблема – на выполнение запроса «проверить, что никакие два преподавателя не имеют одинаковый телефон» и других запросов, аналогичных данному, потребуется экспоненциальное от количества полей с данными о телефонах время.
Неатомарные атрибуты
| CourseId | Lecturer | Phones |
|---|---|---|
| 1 | Корнеев Г. А. | 111-11-11 |
| 2 | Киракозов А. Х. | 222-22-22 |
| 333-33-33 | ||
| 3 | Кудряшов Б. Д. | 444-44-44 |
| 555-55-55 | ||
| 4 | Сегаль А. С. | 666-66-66 |
Потенциальным решением проблемы повторяющихся групп является группировка атрибутов по смыслу. На примере показано, как несколько телефонов из прошлого отношения были сгруппированы в единый атрибут, что позволяет не изменять структуру отношения в зависимости от максимального количества телефонов у одного человека. Однако проблема большого времени работы проверки корректности данных все еще остается.
Отсутствие ключа
Отношение без ключа формально не является отношением. Отсутствие ключа говорит о повторяющихся записях, а отношения рассматриваются как подмножества декартового произведения других множеств, что в явном виде запрещает повторы.
Приведение в 1НФ
Для того, чтобы привести произвольное отношение [math]R[/math] в 1НФ, достаточно:
- Рассмотреть все наборы атрибутов, имеющих одинаковый смысл
- Для каждого фиксированного значения оставшихся атрибутов сделать по записи на каждое значение выбранных:
- рассмотрим повторяющиеся атрибуты [math]A_1, \ldots, A_k[/math]
- рассмотрим оставшиеся атрибуты [math]B_1, \ldots, B_n[/math]
- построим такое отношение [math]T[/math] на атрибутах [math]B_1, \ldots, B_n, A[/math] , что [math]\ \in T \Longleftrightarrow \ \in \pi_R \land a \in \bigcup\limits_^k \pi_R[/math]
- Аналогичную процедуру повторить для всех неатомарных атрибутов
Отношение, использованое в примерах выше, после приведения в 1НФ будет выглядеть как
| CourseId | Lecturer | Phone |
|---|---|---|
| 1 | Корнеев Г. А. | 111-11-11 |
| 2 | Киракозов А. Х. | 222-22-22 |
| 2 | Киракозов А. Х. | 333-33-33 |
| 3 | Кудряшов Б. Д. | 444-44-44 |
| 3 | Кудряшов Б. Д. | 555-55-55 |
| 4 | Сегаль А. С. | 666-66-66 |
Аномалии
Переход в 1НФ не уменьшает выразительную способность «разрешенных» отношений, но при этом исправляет только самые простые аномалии, поэтому в отношениях в 1НФ, не приведенных хотя бы во 2НФ, могут возникать аномалии более сложного вида.
| Определение: |
| Аномалия вставки – зависимость возможности записать обладающие собственным независимым смыслом данные от наличия другой связанной информации. |
В рассмотренном выше примере невозможно записать информацию о телефоне конкретного преподавателя, если он не читает ни один курс (таким образом, возможность записать [math]\mathrm[/math] для конкретного [math]\mathrm[/math] зависит от наличия соответствующего [math]\mathrm[/math] , хотя напрямую они не зависят друг от друга).
| Определение: |
| Аномалия удаления – невозможность удалить часть данных, не удалив никакую связанную с ней информацию. |
В рассмотренном выше, опять же, примере невозможно удалить информацию о том, что конкретный преподаватель читает конкретный курс, не потеряв его номер телефона (как и в случае с аномалией вставки, возможность хранить [math]\mathrm[/math] зависит от существования соответствующего [math]\mathrm[/math] ).
| Определение: |
| Аномалия изменения – ситуация, в которой частичное изменение данных нарушает целостность базы данных. |
В рассмотренном примере если один преподаватель ведет один курс и имеет два телефона, при изменении [math]\mathrm[/math] в одной из соответствующих ему записей будет невозможно восстановить какой курс на самом деле ведет преподаватель (записи с разными [math]\mathrm[/math] , но одинаковыми [math]\mathrm[/math] и [math]\mathrm[/math] , должны всегда поддерживаться в таком же состоянии).
Вторая нормальная форма
Вторая нормальная форма позволяет избавиться от некоторых аномалий, возникающих в отношениях в 1НФ.
Запрещенные конструкции
Во 2НФ запрещено, чтобы какие-либо атрибуты функционально зависели от части ключа. Рассмотрим следующий пример, уже приведенный в 1НФ:
| CourseId | Year | Lecturer | Exam |
|---|---|---|---|
| 1 | 2020 | Корнеев Г. А. | yes |
| 2 | 2019 | Киракозов А. Х. | no |
| 2 | 2020 | Киракозов А. Х. | no |
| 3 | 2019 | Левина А. Б. | yes |
| 3 | 2020 | Чепурной А. И. | yes |
В данном отношении можно выделить следующие функциональные зависимости: [math]\mathrm \rightarrow \mathrm[/math] (наличие экзамена зависит только от предмета) и [math]\mathrm, \mathrm \rightarrow \mathrm[/math] (каждый год только один преподаватель читает конкретный предмет). Таким образом, ключ в данном отношении – [math]\mathrm, \mathrm[/math] , но при этом [math]\mathrm[/math] зависит только от части ключа.
Отношения в 1НФ имеют аномалии вставки и удаления (нельзя хранить информацию про экзамен, не зная лектора) и изменения (можно изменить информацию про экзамен по предмету только для одного года). От этих аномалий можно избавиться, если убрать функциональные зависимости от части ключа.
Приведение во 2НФ
Отношение в 1НФ приводится к 2НФ декомпозицией по «мешающим» функциональным зависимостям. На примере выше такая зависимость только одна – [math]\mathrm \rightarrow \mathrm[/math] .
| Определение: |
| Декомпозиция отношения [math]R[/math] , состоящего из наборов атрибутов [math]A, B, C[/math] , по функциональной зависимости [math]A \rightarrow B[/math] – пара отношений [math]\pi_ R[/math] и [math]\pi_ R[/math] . |
Декомпозиция рассмотренного примера по «лишней» функциональной зависимости дает следующий результат:
| CourseId | Year | Lecturer |
|---|---|---|
| 1 | 2020 | Корнеев Г. А. |
| 2 | 2019 | Киракозов А. Х. |
| 2 | 2020 | Киракозов А. Х. |
| 3 | 2019 | Левина А. Б. |
| 3 | 2020 | Чепурной А. И. |
| CourseId | Exam |
|---|---|
| 1 | yes |
| 2 | no |
| 3 | yes |
После данной декомпозиции, как можно заметить, информация об экзамене по предмету и информация о преподавателе по предмету в конкретный год стали независимыми. Это значит, что больше нет аномалий, свойственных 1НФ: вставка, удаление и изменение данных не затрагивают не связанную с ними напрямую информацию.
Аномалии
Аномалия, свойственная 2НФ, возникает, когда какой-то атрибут зависит от ключа транзитивно через множество неключевых атрибутов. Рассмотрим следующий пример:
| CourseId | Year | Lecturer | Phone |
|---|---|---|---|
| 1 | 2020 | Корнеев Г. А. | 111-11-11 |
| 2 | 2019 | Киракозов А. Х. | 222-22-22 |
| 2 | 2020 | Киракозов А. Х. | 222-22-22 |
| 3 | 2019 | Левина А. Б. | 333-33-33 |
| 3 | 2020 | Чепурной А. И. | 444-44-44 |
В нем есть две базовые функциональные зависимости: [math]\mathrm, \mathrm \rightarrow \mathrm[/math] и [math]\mathrm \rightarrow \mathrm[/math] . Несмотря на то, что данное отношение находится во 2НФ, в нем все еще имеют место все три аномалии 1НФ – аномалии вставки, удаления и изменения (информация о телефонах и о преподавании никак не разделена). Для исправления аномалий 2НФ отношение переводят в третью нормальную форму и выше.