Как отчисление одного студента может закрыть всю кафедру. Нормализуем БД и избавляемся от аномалий

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

Естественно, можно спроектировать базу данных, вообще не заботясь ни о каких правилах. И она даже будет работать! Все будет прекрасно ровно до первого ее реального использования в продакшене. При проектировании «абы-как» возникают три типовые проблемы: избыточность, аномалии обновления, аномалии удаления.

И вот это уже плохо.

Избыточность данных приводит к засорению базы данных повторяющимися данными. При необходимости правки придется изменять данные во всех местах, где они повторяются. Один раз забыл и привет несогласованность.

В рамках этой статьи мы будем работать с реляционными базами данных.

Взглянем на пример:

В этом примере в столбце Материал массив дерева и ЛДСП повторяется несколько раз. Повторение одного и того же значения в нескольких строках и создает избыточность данных.

Аномалии обновления 

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

Пример:

Допустим, платформа А провела ребрендинг и теперь называется Платформа В. Нам нужно найти все упоминания старого названия и заменить на новое. Мы меняем данные, но что-то идет не так, и данные Марии остаются нетронутыми.

Вследствие этой ошибки в БД произошел хаос. Ложь и правда живут вместе, но система даже не подозревает неладное. Все потому что изменение данных должно происходить за одну операцию и во всей БД сразу. Будь у нас миллион строк в нескольких таблицах, каждая из которых содержит избыточную информацию о Платформе, нам бы пришлось изменять их всех. Если где-то забыли – несогласованность. Это и есть аномалия обновления.

Аномалия удаления

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

Взглянем сюда:

Допустим студент Петр внезапно решил бросить обучение и отчислился. Его запись удаляется из БД администратором: удаляется строка с Петром.

Помимо Петра из БД исчезает информация о существовании курса С++ и платформы Б. Удаленная информация не была связана напрямую с Петром, однако, теперь информация вообще перестала существовать. Своим отчислением Петр заодно закрыл целую программу подготовки.

Декомпозиция и нормализация

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

Декомпозиция без потерь — это разбиение, при котором после обратного соединения (JOIN) мы получаем ровно исходную таблицу без новых строк и без потерь.

Сначала посмотрим на пример неудачного разбиения:

Разделяем эту таблицу на 2:

Студенты_Лекторы

Студент	Лектор
Иван	Петров
Иван	Смирнов
Мария	Петров
Мария	Соколова
Петр	Смирнов
Петр	Соколова

Курсы_Лекторы

Курс	Лектор
Математика	Петров
Физика	Смирнов
Информатика	Соколова

При JOIN соединении этих двух таблиц по полю Лектор и получаем:

Появились строки, которых не было в исходной таблице, то есть выполнили декомпозицию с потерями и исходные данные не восстановились.

Иван — Информатика и Петр — Математика

Это произошло по причине того, что Лектор не определяет курс однозначно. Один лектор может быть связан с несколькими курсами и с несколькими студентами.

При JOIN произошло перемножение строк (комбинирование всех возможных вариантов) по совпадающему значению Лектор

Разделим немного по-другому:

«Студенты_Курсы»                                            

Студент	Курс
Иван	Математика
Иван	Физика
Мария	Математика
Мария	Информатика
Петр	Физика
Петр	Информатика

 «Курсы_Лекторы»

Курс	Лектор
Математика	Петров
Физика	Смирнов
Информатика	Соколова

Выполняем JOIN по Курс и получаем:

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

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

Если у вас есть функциональная зависимость столбца и столбец , то вы можете разделить таблицу на 2 без потерь:

·   Таблица столбцов и

·   Таблица столбцов и

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

Всего существует несколько нормальных форм:

• Ненормализованная форма или нулевая нормальная форма (UNF)

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

• Вторая нормальная форма (2NF)

• Третья нормальная форма (3NF)

• Нормальная форма Бойса-Кодда (BCNF)

• Четвертая нормальная форма (4NF)

• Пятая нормальная форма (5NF)

• Доменно-ключевая нормальная форма (DKNF)

• Шестая нормальная форма (6NF)

В реальности используется нормализация вплоть до Нормальной формы Бойса-Кодда. Последующие формы не нашли широкого применения в жизни и существуют в основном в теории.

Первая нормальная форма

Первая нормальная форма (1НФ) – база, с которой все начинается. Условия для приведения таблицы к 1НФ:

• Не должно быть дублирующих строк

• В каждой ячейке хранится атомарное значение (нельзя делить дальше без потери смысла)

• В столбце данные одного типа

• Устранение списков и массивов в любом их роде

«Пример ненормализованной грязной UNF таблицы»

В процессе приведения таблицы к 1НФ разбиваем повторяющиеся группы на отдельные строки.

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

• Таблица находится в 1НФ

• Каждый неключевой атрибут неприводимо зависит от каждого ее потенциального ключа

Слово «неприводимо» означает, что атрибут должен зависеть от всего составного ключа целиком, и никак от его кусочка. Это также называется полная функциональная зависимость. Смотрим на пример:

«Яблоки»

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

Переходим к нормализации.

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

«Чек_1NF»

Здесь НомерЧека и Товар это составной первичный ключ. Неключевые атрибуты должны зависеть от всего составного ключа(и от НомерЧека и от Товар), однако в нашем случае это не выполняется.

• Покупатель зависит от НомерЧека (чтобы узнать покупателя хватит только номера чека)

• Кассир зависит от НомерЧека

• Цена зависит от Товар

Для устранения этих частичных зависимостей создадим дополнительные таблицы

«Покупатели»

«Кассиры»

Также выносим в отдельную таблицу атрибуты зависящие только от НомерЧека

«ЗаголовкиЧеков»

Чтобы цена зависела от товаров, а не от чека выносим товары в отдельную таблицу

«Товары»

«Чеки и товары»

После всех махинаций мы пришли к 2НФ. Ура! Но это еще не предел.

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

• Таблица находится в 2НФ

• Не содержит транзитивных зависимостей между неключевыми атрибутами и первичным ключом

По удачным обстоятельствам почти вся наша БД после приведения к 2НФ автоматически стала 3НФ и местами даже BCNF.

Единственный нюанс — это таблица «Товары». Причина станет понятна ниже.

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

«Товары и поставщики_2NF»

Наблюдаем появление транзитивной зависимости:
ID_Товара → Поставщик → Город_поставщика

В 3НФ не должно быть транзитивных зависимостей. Решаем эту проблему вынося Поставщик и Город_поставщика в отдельную таблицу.

«Товары и поставщики_3NF»

«Поставщики»

Поздравляю, 3НФ достигнута. Теперь вам стало понятно, почему таблицы «Товары» из прошлого примера не совсем соответствовала 3НФ.

Напомню как выглядит таблица «Товары»

«Товары»

ID_Товара	Товар	Цена
1	Молоко	80
2	Хлеб	50
3	Сок	100
4	Кетчуп	400
5	Пиво	50

Мы можем увидеть транзитивную зависимость вида ID_Товара → Товар → Цена

Транзитивная зависимость ID_Товара → Товар → Цена возникает только в том случае, если выполняется функциональная зависимость Товар → Цена. Если одному значению атрибута Товар может соответствовать несколько разных значений Цена, то функциональная зависимость Товар → Цена не выполняется. В этом случае транзитивной зависимости нет, и таблица находится в 3НФ.

Переходим к нормальной форме Бойса-Кодда.

Нормальная форма Бойса-Кодда (НФБК)

• Таблица находится в 3НФ

• Каждый детерминант должен быть суперключом

В 3НФ допускаются ситуации, когда детерминант (левая часть функциональной зависимости) не является ключом, но зависимая часть входит в состав какого-либо потенциального ключа. В НФБК такое запрещено.

Проще говоря, НФБК требует, чтобы любая зависимость шла только от ключа.

Пример нарушения НФБК

Добавим в таблицу телефоны городов, чтобы получить зависимость Город → Телефон_города.

 «Поставщики»

Поскольку город не суперключ (Москва встречается несколько раз) и при этом он определяет другой атрибут, то эта зависимость нарушает НФБК. Исправляем данную проблему выносом телефон города в отдельную таблицу

«Города»

«Поставщики»

Теперь наша зависимость Город → Телефон_города вынесена отдельно. В каждой таблице все зависимости идут от ключей. Достигнута Нормальная форма Бойса-Кодда.

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

На практике чаще всего используется третья нормальная форма (3НФ).

Благодарю за прочтение!