Normalization. Теория нормализации

1. Normalization

Александр Загоруйко © 2019
Normalization

2. Контрольные вопросы

В чём заключается необходимость
перехода от однотабличных БД к
многотабличным?
Можно ли создать таблицу без
первичного ключа?
Примеры составного первичного ключа
Как реализуется связь N:M?
Виды JOIN, их отличия

3. Оптимальность БД

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

4. Устранение неоптимальности

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

5. Понятие нормализации

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

6. Аномалии при работе с БД

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

7. Виды аномалий

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

8. Интуитивная нормализация

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

9. Теория нормализации

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

10. Нормальные формы

Нормализация таблицы включает в себя
определённые требования – так называемые
нормальные формы (НФ). Существует три
базовых НФ:
первая нормальная форма (1НФ)
вторая нормальная форма (2НФ)
третья нормальная форма (3НФ)
Если таблица удовлетворяет
соответствующему требованию, то говорят, что
она приведена к соответствующей НФ.
https://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%BC%D0%B0%D0%BB%
D1%8C%D0%BD%D0%B0%D1%8F_%D1%84%D0%BE%D1%80%D0%BC%D0%B0

11. Определение НФ

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

12. Три нормальные формы

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

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

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

14. Требование атомарности

Требование неделимости (атомарности) означает, что
значение поля не должно делиться на более мелкие
значения. Будет возможным такое деление или нет –
определяется исключительно требованиями
предметной области. Например, может быть поле
«ФИО», предназначенное для хранения фамилии, имени
и отчества человека. Если задача требует использование
полей «Фамилия», «Имя» и «Отчество» по отдельности,
то поле «ФИО» является делимым. Если не требует, то
оно является неделимым. Если в результате анализа
задачи поле оказалось делимым, то его просто следует
разбить на несколько полей – это будет процессом
приведения таблицы к 1НФ.

15. Атомарные атрибуты

В теории баз это атрибуты, которые хранят
единственное значение и не являются ни списком,
ни множеством значений. Иными словами, это
такие данные, разделение которых на
составляющие приводит к потере их смысла с
точки зрения решаемой задачи.
Стоит ли делить ФИО на части? Правильный
ответ: зависит от смысла. Если ФИО с точки
зрения решаемой задачи неделимо, то оно
атомарно. Если делимо, надо нормализовывать и
бить на столбцы.

16. Ещё пример на атомарность

17. Второе требование 1НФ

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

18. Пример нарушения 1НФ

Эта таблица имеет два серьёзных недостатка. Вопервых, бросается в глаза количество нулей, т.е.
дней в которые не продали ни одного экземпляра
той или иной книги. Это реальные потери места на
диске, и при большом объеме БД они могут
вылиться в мегабайты впустую потраченного
места.

19. Идеологическая причина

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

20. Нормализованная таблица

Таким образом, после устранения повторяющихся
групп полей получаем такую таблицу:

21. Процесс приведения к 1НФ

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

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

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

23. Пример

24. Практика

Привести таблицу Sales ко 2НФ:

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

3НФ требует выполнения 2НФ, а также чтобы в
таблице не имелось транзитивных зависимостей
между не-ключевыми полями, т.е. чтобы значение
любого поля таблицы, не входящего в первичный
ключ, не зависело от значения другого поля, не
входящего в первичный ключ.
Проанализируем таблицу «Sales» из примера с
книгами. Очевидно, что поле «Общая стоимость»
зависит от поля «Продано шт.». Раз так, то это
поле лишнее, и его следует удалить.

26. Ещё пример

Если добавить в таблицу Product поле
total_cost, которое будет вычисляться
как price * quantity, это будет
нарушением третьей нормальной
формы.

27. И ещё пример

28. Нормализация: за и против

Несмотря на очевидные преимущества, которые даёт
нормализация, за всё нужно платить. Использование
нормализации также имеет свою цену. С одной стороны
приведение БД к 1, 2 и 3НФ позволяет избавиться от
избыточности и логической противоречивости. Но с
другой стороны, это приводит к заметному увеличению
количества таблиц в БД. В случае если речь идет
большой БД, это может существенно затруднить
разработку. Бывало, разработка БД сворачивалась по
причине того, что по мере увеличения количества таблиц
- их становилось настолько много, что некоторые
разработчики были просто не в состоянии удержать
структуру БД в голове целиком.

29. Накопительные поля

Другая проблема, возникающая при
нормализации, – это проблема
производительности. В первую очередь это
касается 3НФ. Строгое следование
требованиям 3НФ приводит к необходимости
избавиться от так называемых накопительных
полей. Это означает, что при необходимости
подсчитать некие данные, серверу БД придётся
каждый раз выполнять расчёт заново, вместо
того, чтобы воспользоваться промежуточными
накопительными значениями.

30. Следование требованиям НФ

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

31. Домашнее задание

Спроектировать базу данных "Туристическая фирма".
Необходимо хранить информацию:
- о курортах (например: название, описание, рейтинг)
- экскурсиях в рамках курорта (значимые места, памятники, музеи, храмы)
- отелях (название, количество звёзд, количество свободных номеров, типы номеров)
- видах отдыха (например: море, горнолыжный спорт и др.)
- видах транспорта (например: на корабле, поезде, самолете, пеший тур. возможно
несколько видов одновременно!)
- городах, странах (без областей)
- людях, которые поедут/находятся/уже ездили в путешествие (имена, фамилии,
отчества, возраст, пол, национальность, профессия, дата отъезда, цель поездки,
курорт, пр.)
- длительности путешествия (например: год, месяц, неделя, 2 недели, 10 дней 11 ночей)
- стоимости путешествия (с описанием всех составляющих – шведский стол, экскурсии)
- о работниках фирмы и их должностях (например: 3 секретаря, 1 бухгалтер, директор с зарплатами)
- другие таблицы (при необходимости)
Все таблицы должны соответствовать 1, 2 и 3 нормальным формам.