Привет Хабр! В прошлой статье мы детально разобрали функциональные зависимости. Возможно, после нее у вас, как и у многих, остался закономерный вопрос: зачем нам вообще так париться, выискивая эти зависимости? Как это применяется в проектировании баз данных?
Естественно, можно спроектировать базу данных, вообще не заботясь ни о каких правилах. И она даже будет работать! Все будет прекрасно ровно до первого ее реального использования в продакшене. При проектировании «абы-как» возникают три типовые проблемы: избыточность, аномалии обновления, аномалии удаления.
И вот это уже плохо.
Избыточность данных приводит к засорению базы данных повторяющимися данными. При необходимости правки придется изменять данные во всех местах, где они повторяются. Один раз забыл и привет несогласованность.
В рамках этой статьи мы будем работать с реляционными базами данных.
Взглянем на пример:
ID предмета | Наименование предмета | Материал |
1 | Стул | Металл |
2 | Стол | Массив дерева |
3 | Кровать | ЛДСП |
4 | Шкаф | Массив дерева |
5 | Комод | ЛДСП |
В этом примере в столбце Материал массив дерева и ЛДСП повторяется несколько раз. Повторение одного и того же значения в нескольких строках и создает избыточность данных.
Аномалии обновления
Это ошибка, которая вытекает из избыточности. Она возникает, когда данные изменяют в одном месте и не обновляют в другом. Таким образом, нарушается целостность информации – база данных содержит противоречащие друг другу сведения об одном и том же объекте.
Пример:
Студент | Курс | Преподаватель | Платформа |
Иван | Python | Петров | Платформа А |
Мария | Python | Петров | Платформа А |
Петр | С++ | Иванова | Платформа Б |
Анна | С++ | Иванова | Платформа Б |
Иван | С++ | Иванова | Платформа Б |
Допустим, платформа А провела ребрендинг и теперь называется Платформа В. Нам нужно найти все упоминания старого названия и заменить на новое. Мы меняем данные, но что-то идет не так, и данные Марии остаются нетронутыми.
Студент | Курс | Преподаватель | Платформа |
Иван | Python | Петров | Платформа В |
Мария | Python | Петров | Платформа А |
Петр | С++ | Иванова | Платформа Б |
Анна | С++ | Иванова | Платформа Б |
Иван | С++ | Иванова | Платформа Б |
Вследствие этой ошибки в БД произошел хаос. Ложь и правда живут вместе, но система даже не подозревает неладное. Все потому что изменение данных должно происходить за одну операцию и во всей БД сразу. Будь у нас миллион строк в нескольких таблицах, каждая из которых содержит избыточную информацию о Платформе, нам бы пришлось изменять их всех. Если где-то забыли – несогласованность. Это и есть аномалия обновления.
Аномалия удаления
Когда мы говорим об аномалии удаления, мы имеем в виду неожиданную потерю данных, которые косвенно относились к удаленной информации. Хотели удалить что-то определенное, а потеряли больше, чем планировали.
Взглянем сюда:
Студент | Курс | Преподаватель | Платформа |
Иван | Python | Петров | Платформа А |
Мария | Python | Петров | Платформа А |
Петр | С++ | Иванова | Платформа Б |
Анна | SQL | Иванова | Платформа Б |
Иван | SQL | Иванова | Платформа Б |
Допустим студент Петр внезапно решил бросить обучение и отчислился. Его запись удаляется из БД администратором: удаляется строка с Петром.
Студент | Курс | Преподаватель | Платформа |
Иван | Python | Петров | Платформа А |
Мария | Python | Петров | Платформа А |
Анна | SQL | Иванова | Платформа А |
Иван | SQL | Иванова | Платформа А |
Помимо Петра из БД исчезает информация о существовании курса С++ и платформы Б. Удаленная информация не была связана напрямую с Петром, однако, теперь информация вообще перестала существовать. Своим отчислением Петр заодно закрыл целую программу подготовки.
Облачные базы данных
Создайте готовую базу данных в облаке за 5 минут. Поддерживаем PostgreSQL, MySQL, Redis и не только.
Декомпозиция и нормализация
С этого момента в игру вступает нормализация. По сути это просто процесс декомпозиции — разбиения одной исходной таблицы на несколько маленьких. Но нужно убедиться, что при разбиении сохранятся все важные связи. Чтобы при соединении разбитых маленьких таблиц получалась точь-в-точь наша исходная таблица.
Декомпозиция без потерь — это разбиение, при котором после обратного соединения (JOIN) мы получаем ровно исходную таблицу без новых строк и без потерь.
Сначала посмотрим на пример неудачного разбиения:
Студент | Курс | Лектор |
Иван | Математика | Петров |
Иван | Физика | Смирнов |
Мария | Математика | Петров |
Мария | Информатика | Соколова |
Петр | Физика | Смирнов |
Петр | Информатика | Соколова |
Разделяем эту таблицу на 2:
Студенты_Лекторы
Студент | Лектор |
Иван | Петров |
Иван | Смирнов |
Мария | Петров |
Мария | Соколова |
Петр | Смирнов |
Петр | Соколова |
Курсы_Лекторы
Курс | Лектор |
Математика | Петров |
Физика | Смирнов |
Информатика | Соколова |
При JOIN соединении этих двух таблиц по полю Лектор и получаем:
Студент | Лектор | Курс |
Иван | Петров | Математика |
Иван | Смирнов | Физика |
Иван | Соколова | Информатика |
Мария | Петров | Математика |
Мария | Соколова | Информатика |
Петр | Смирнов | Физика |
Петр | Соколова | Информатика |
Петр | Петров | Математика |
Появились строки, которых не было в исходной таблице, то есть выполнили декомпозицию с потерями и исходные данные не восстановились.
Иван — Информатика и Петр — Математика
Это произошло по причине того, что Лектор не определяет курс однозначно. Один лектор может быть связан с несколькими курсами и с несколькими студентами.
При JOIN произошло перемножение строк (комбинирование всех возможных вариантов) по совпадающему значению Лектор
Разделим немного по-другому:
«Студенты_Курсы»
Студент | Курс |
Иван | Математика |
Иван | Физика |
Мария | Математика |
Мария | Информатика |
Петр | Физика |
Петр | Информатика |
«Курсы_Лекторы»
Курс | Лектор |
Математика | Петров |
Физика | Смирнов |
Информатика | Соколова |
Выполняем JOIN по Курс и получаем:
Студент | Курс | Лектор |
Иван | Математика | Петров |
Иван | Физика | Смирнов |
Мария | Математика | Петров |
Мари�� | Информатика | Соколова |
Петр | Физика | Смирнов |
Петр | Информатика | Соколова |
Все получилось в точности как в исходной таблице – нет лишних строк и потерь данных. По Теорема Хита - если некоторая декомпозиция выполняется в соответствии с определенной функциональной зависимостью, то она будет выполнена без потерь. Иначе говоря:
Пусть
является отношением, где
,
и
— непересекающиеся множества атрибутов. Если
удовлетворяет функциональной зависимости
, то исходная таблица
Если у вас есть функциональная зависимость столбца и столбец , то вы можете разделить таблицу на 2 без потерь:
· Таблица столбцов и
· Таблица столбцов и
Вернемся к нормализации. Цель нормализации исключить избыточность данных, благодаря которой возникают аномалии наподобие аномалии удаления и обновления.
Всего существует несколько нормальных форм:
Ненормализованная форма или нулевая нормальная форма (UNF)
Первая нормальная форма (1NF)
Вторая нормальная форма (2NF)
Третья нормальная форма (3NF)
Нормальная форма Бойса-Кодда (BCNF)
Четвертая нормальная форма (4NF)
Пятая нормальная форма (5NF)
Доменно-ключевая нормальная форма (DKNF)
Шестая нормальная форма (6NF)
В реальности используется нормализация вплоть до Нормальной формы Бойса-Кодда. Последующие формы не нашли широкого применения в жизни и существуют в основном в теории.
Первая нормальная форма
Первая нормальная форма (1НФ) – база, с которой все начинается. Условия для приведения таблицы к 1НФ:
Не должно быть дублирующих строк
В каждой ячейке хранится атомарное значение (нельзя делить дальше без потери смысла)
В столбце данные одного типа
Устранение списков и массивов в любом их роде
«Пример ненормализованной грязной UNF таблицы»
НомерЧека | Покупатель | Товары | Цена | Кассир |
1 | Александр | Молоко, Хлеб | 80, 50 | Анна |
2 | Дарья | Сок, Кетчуп, Молоко | 100, 400, 80 | Дмитрий |
3 | Александр | Пиво | 50 | Анна |
В процессе приведения таблицы к 1НФ разбиваем повторяющиеся группы на отдельные строки.
НомерЧека | Покупатель | Товары | Цена | Кассир |
1 | Александр | Молоко | 80 | Анна |
1 | Александр | Хлеб | 50 | Анна |
2 | Дарья | Сок | 100 | Дмитрий |
2 | Дарья | Кетчуп | 400 | Дмитрий |
2 | Дарья | Молоко | 80 | Дмитрий |
3 | Александр | Пиво | 50 | Анна |
Вторая нормальная форма
Таблица находится в 1НФ
Каждый неключевой атрибут неприводимо зависит от каждого ее потенциального ключа
Слово «неприводимо» означает, что атрибут должен зависеть от всего составного ключа целиком, и никак от его кусочка. Это также называется полная функциональная зависимость. Смотрим на пример:
«Яблоки»
Товар | Филиал | Цена в филиале |
Яблоки | Северный | 100 руб |
Бананы | Южный | 110 руб |
Бананы | Северный | 90 руб |
Яблоки | Южный | 120 руб |
В этой таблице столбцы Товар и Филиал – составной первичный ключ. Цена в филиале не входит в состав ключа и зависит сразу от 2 значений ключа. По названию товара нельзя получить значение цены, как и по названию филиала. Только зная значения полного ключа мы сможем получить цену товара в конкретном филиале.
Переходим к нормализации.
Для начала введем составной первичный ключ для нашей 1НФ. Чтобы находится в 2НФ, отношение должно быть организовано так, чтобы каждый его неключевой атрибут зависел от полного набора атрибутов потенциального ключа. Нарушение этого условия приведет к избыточности (и не только).
«Чек_1NF»
НомерЧека | Товар | Покупатель | Цена | Кассир |
1 | Молоко | Александр | 80 | Анна |
1 | Хлеб | Александр | 50 | Анна |
2 | Сок | Дарья | 100 | Дмитрий |
2 | Кетчуп | Дарья | 400 | Дмитрий |
2 | Молоко | Дарья | 80 | Дмитрий |
3 | Пиво | Александр | 50 | Анна |
Здесь НомерЧека и Товар это составной первичный ключ. Неключевые атрибуты должны зависеть от всего составного ключа(и от НомерЧека и от Товар), однако в нашем случае это не выполняется.
Покупатель зависит от НомерЧека (чтобы узнать покупателя хватит только номера чека)
Кассир зависит от НомерЧека
Цена зависит от Товар
Для устранения этих частичных зависимостей создадим дополнительные таблицы
«Покупатели»
ID_Покупателя | Имя |
1 | Александр |
2 | Дарья |
«Кассиры»
ID_Кассира | Имя |
1 | Анна |
2 | Дмитрий |
Также выносим в отдельную таблицу атрибуты зависящие только от НомерЧека
«ЗаголовкиЧеков»
НомерЧека | ID_Покупателя | ID_Кассира |
1 | 1 | 1 |
2 | 2 | 2 |
3 | 1 | 1 |
Чтобы цена зависела от товаров, а не от чека выносим товары в отдельную таблицу
«Товары»
ID_Товара | Товар | Цена |
1 | Молоко | 80 |
2 | Хлеб | 50 |
3 | Сок | 100 |
4 | Кетчуп | 400 |
5 | Пиво | 50 |
«Чеки и товары»
НомерЧека | ID_Товара |
1 | 1 |
1 | 2 |
2 | 3 |
2 | 4 |
2 | 1 |
3 | 5 |
После всех махинаций мы пришли к 2НФ. Ура! Но это еще не предел.
Третья нормальная форма
Таблица находится в 2НФ
Не содержит транзитивных зависимостей между неключевыми атрибутами и первичным ключом
По удачным обстоятельствам почти вся наша БД после приведения к 2НФ автоматически стала 3НФ и местами даже BCNF.
Единственный нюанс — это таблица «Товары». Причина станет понятна ниже.
А сейчас немного изменим пример, чтобы наглядно показать разницу между 2НФ и 3 НФ. Для товаров добавим поставщиков и их города.
«Товары и поставщики_2NF»
ID_Товара | Товар | Цена | Поставщик | Город_поставщика |
1 | Молоко | 80 | ООО “Ромашка” | Москва |
2 | Хлеб | 50 | ООО “Ромашка” | Москва |
3 | Сок | 100 | ООО “Букашка” | Санкт-Петербург |
4 | Кетчуп | 400 | ООО “Цветочек” | Казань |
5 | Пиво | 50 | ООО “Букашка” | Санкт-Петербург |
Наблюдаем появление транзитивной зависимости:
ID_Товара → Поставщик → Город_поставщика
В 3НФ не должно быть транзитивных зависимостей. Решаем эту проблему вынося Поставщик и Город_поставщика в отдельную таблицу.
«Товары и поставщики_3NF»
ID_Товара | Товар | Цена | ID_Поставщика |
1 | Молоко | 80 | 1 |
2 | Хлеб | 50 | 1 |
3 | Сок | 100 | 2 |
4 | Кетчуп | 400 | 3 |
5 | Пиво | 50 | 2 |
«Поставщики»
ID_Поставщика | Поставщик | Город |
1 | ООО “Ромашка” | Москва |
2 | ООО “Букашка” | Санкт-Петербург |
3 | ООО “Цветочек” | Казань |
Поздравляю, 3НФ достигнута. Теперь вам стало понятно, почему таблицы «Товары» из прошлого примера не совсем соответствовала 3НФ.
Напомню как выглядит таблица «Товары»
«Товары»
ID_Товара | Товар | Цена |
1 | Молоко | 80 |
2 | Хлеб | 50 |
3 | Сок | 100 |
4 | Кетчуп | 400 |
5 | Пиво | 50 |
Мы можем увидеть транзитивную зависимость вида ID_Товара → Товар → Цена
Транзитивная зависимость ID_Товара → Товар → Цена возникает только в том случае, если выполняется функциональная зависимость Товар → Цена. Если одному значению атрибута Товар может соответствовать несколько разных значений Цена, то функциональная зависимость Товар → Цена не выполняется. В этом случае транзитивной зависимости нет, и таблица находится в 3НФ.
Переходим к нормальной форме Бойса-Кодда.
Нормальная форма Бойса-Кодда (НФБК)
Таблица находится в 3НФ
Каждый детерминант должен быть суперключом
В 3НФ допускаются ситуации, когда детерминант (левая часть функциональной зависимости) не является ключом, но зависимая часть входит в состав какого-либо потенциального ключа. В НФБК такое запрещено.
Проще говоря, НФБК требует, чтобы любая зависимость шла только от ключа.
Пример нарушения НФБК
Добавим в таблицу телефоны городов, чтобы получить зависимость Город → Телефон_города.
«Поставщики»
ID_Поставщика | Поставщик | Город | Телефон_города |
1 | ООО “Ромашка” | Москва | +7-495-000-01 |
2 | ООО “Букашка” | Санкт-Петербург | +7-812-000-01 |
3 | ООО “Цветочек” | Казань | +7-843-000-01 |
4 | ООО “Ягодка” | Москва | +7-495-000-01 |
Поскольку город не суперключ (Москва встречается несколько раз) и при этом он определяет другой атрибут, то эта зависимость нарушает НФБК. Исправляем данную проблему выносом телефон города в отдельную таблицу
«Города»
Город | Телефон_города |
Москва | +7-495-000-01 |
Санкт-Петербург | +7-812-000-01 |
Казань | +7-843-000-01 |
«Поставщики»
ID_Поставщика | Поставщик | Город |
1 | ООО “Ромашка” | Москва |
2 | ООО “Букашка” | Санкт-Петербург |
3 | ООО “Цветочек” | Казань |
4 | ООО “Ягодка” | Москва |
Теперь наша зависимость Город → Телефон_города вынесена отдельно. В каждой таблице все зависимости идут от ключей. Достигнута Нормальная форма Бойса-Кодда.
Дальнейшее использование нормализации не является целесообразным решением ввиду снижения производительности и в значительном увеличении количества таблиц. Хорошая база данных – это база, которая достаточно нормализована, чтобы не создавать аномалий для пользователя базы данных, и в то же время обладает прекрасной производительностью.
На практике чаще всего используется третья нормальная форма (3НФ).
Благодарю за прочтение!