Лекция 5 Нормализация БД

1. Нормализация баз данных

2.

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

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

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

4.

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

5.

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

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

Таб. № ФИО
Должность
№
Телефон
кабинета
Дети
1
Иванов
Директор
32
212
Саша(2016)
Маша(2019)
2
Зайцева Бухгалтер
33
213
Петя(2006)
Витя(2017)
Лена(2019)
3
Волков
35
311
-
Менеджер
Дети не являются элементарными данными, следовательно, таблицу нельзя
вводить таким образом, её необходимо преобразовать

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

Таб.
№
PK
ФИО
Должность
№
кабинета
Телефон Имя
ребёнка
Год
рождения
№
ребёнка
PK
1
Иванов
Директор
32
212
Саша
2016
1
2
3
1
2
2
Зайцева Бухгалтер 33
Волков Менеджер 35
Иванов Директор 32
Зайцева Бухгалтер 33
Зайцева Бухгалтер 33
213
311
212
213
213
Петя
Маша
Витя
Лена
2006
2015
2017
2019
1
Теперь все ячейки таблицы являются элементарными неделимыми
(атомарными) данными и таблица может быть помещена в БД
2
2
3

8. Аномалия вставки:

Таб.
№
PK
ФИО
Должность
№
кабинета
Телефон Имя
ребёнка
Год
рождения
№
ребёнка
PK
1
Иванов
Директор
32
212
Саша
2016
1
2
3
1
2
2
Зайцева Бухгалтер 33
Волков Менеджер 35
Иванов Директор 32
Зайцева Бухгалтер 33
Зайцева Бухгалтер 33
213
311
212
213
213
Петя
Маша
Витя
Лена
2006
2015
2017
2019
1
2
2
3
Волков не может быть внесён в базу данных, т.к. у него нет
детей, а эта информация входит в состав ключа

9. Аномалия модификации

Таб.
№
PK
ФИО
Должность
№
кабинета
Телефон Имя
ребёнка
Год
рождения
№
ребёнка
PK
1
Иванов
Директор
32
212
Саша
2016
1
2
1
2
2
Петрова Бухгалтер
Иванов Директор
Зайцева Бухгалтер
Зайцева Бухгалтер
33
32
33
33
213
212
213
213
Петя
Маша
Витя
Лена
2006
2015
2017
2019
1
2
2
3
Зайцева сменила фамилию. Если не принять специальных мер, изменение
базы может быть произведено не полностью, и в базе данных окажется
противоречивая информация.

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

Таб.
№
PK
ФИО
Должность
№
кабинета
Телефон Имя
ребёнка
Год
рождения
№
ребёнка
PK
1
Иванов
Директор
32
212
Саша
2016
1
2
1
2
2
Петрова Бухгалтер
Иванов Директор
Зайцева Бухгалтер
Зайцева Бухгалтер
33
32
33
33
213
212
213
213
Петя
Маша
Витя
Лена
2006
2015
2017
2019
1
2
2
3
Дети, достигшие совершеннолетия, удаляются из БД. Однако,
вместе с удалением Пети, ребенка Петровой, мы удалили и
информацию о самом сотруднике

11. Нормализация

Нормализация – процесс оптимизации
хранения данных (процесс удаления
избыточности данных из таблиц методом
декомпозиции, т.е. разбиения исходного
отношения на множество более простых
отношений).

12. Результаты нормализации

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

13.

Нормализацию можно использовать
как критерий для определения
корректности, целостности и
правильности отношений.
Изменение данных для некоторых
отношений может привести к
нежелательным последствиям,
называемым аномалиями
модификации (modification
anomalies).

14.

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

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

Первая нормальная форма (1 НФ) – это обычное
отношение, на пересечении строк и столбов которого
расположено атомарное значение соответствующего
атрибута, на пересечении строк и столбцов находиться
уникальное значение ключевого атрибута, и не имеется
дублирования кортежей.
Свойства отношений (свойства 1 НФ):
В отношении нет одинаковых кортежей.
Кортежи не упорядочены.
Атрибуты не упорядочены и различаются по наименованию.
Все значения атрибутов атомарные.

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

Алгоритм нормализации (приведение к 1НФ)
Шаг 1. Задаем одно или несколько отношений,
отображающих понятия предметной области.
Шаг 2. Выписываем обнаруженные функциональные
зависимости по модели предметной области.
Исключаем дублирование информации, а именно
значений ключевых атрибутов, кортежей и т.п., формируем
уникальное описание объектов предметной области.
Если на пересечении строки и столбца каждый элемент
является уникальным, то все отношения автоматически
находятся в 1НФ.

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

Пример
Отношение СТУДЕНТ_КАФЕДРА_СЕКЦИЯ (Код
кафедры студента, Номер зачетки, ФИО студента, Телефон
кафедры, Секция и Плата)описывает студенческий
спортивный клуб.
Потенциальный ключ - Номер зачетки студента и
Секция, которую он посещает.

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

Виды аномалий:
Аномалии вставки (INSERT)
Аномалии обновления (UPDATE)
Аномалии удаления (DELETE)
В отношении СТУДЕНТ_КАФЕДРА_СЕКЦИЯ
данные хранятся с большой избыточностью. При
изменении состояния предметной области и
соответствующих изменениях состояния базы
данных возникает большое количество проблем.

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

Аномалии вставки (INSERT)
В отношение СТУДЕНТ_КАФЕДРА_СЕКЦИЯ нельзя
вставить данные о студенте, который пока не посещает
никакую из перечисленных секций. Поскольку атрибут
Секция входит в состав потенциального ключа, и,
следовательно, не может содержать null-значений.
Причина аномалии – хранение в одном отношении
разнородной информации.
Вывод – логическая модель данных неадекватна
модели предметной области.
База данных, основанная на такой модели, будет
работать неправильно.

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

Аномалии обновления (UPDATE)
В отношении СТУДЕНТ_КАФЕДРА_СЕКЦИЯ присутствует
повторения данных в некоторых кортежах. Изменения этих
данных должны одновременно выполняться во всех местах,
иначе отношение станет некорректным.
Таким образом, обновление базы данных одним действием
реализовать невозможно.
Причина аномалии – избыточность данных, также
порожденная тем, что в одном отношении хранится разнородная
информация.
Вывод – увеличивается сложность разработки базы данных.
База данных, основанная на такой модели, будет работать
правильно только при наличии дополнительного программного
кода в виде триггеров.

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

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

22. Основные определения:

Определение 1. Функциональная зависимость
В отношении R атрибут Y функционально зависит от атрибута
X (X и Y могут быть составными) в том и только в том случае,
если каждому значению X соответствует в точности одно
значение Y:
R.X --> R.Y.
Определение 2. Полная функциональная зависимость
Функциональная зависимость R.X --> R.Y называется полной,
если атрибут Y не зависит функционально от любого точного
подмножества X.

23. Основные определения:

Определение 3. Транзитивная функциональная зависимость
Функциональная зависимость R.X--> R.Y называется
транзитивной, если существует такой атрибут Z, что имеются
функциональные зависимости R.X --> R.Z и R.Z --> R.Y и
отсутствует функциональная зависимость R.Z--> R.X.
Определение 4. Неключевой атрибут
Неключевым атрибутом называется любой атрибут
отношения, не входящий в состав первичного ключа.
Определение 5. Взаимно независимые атрибуты
Два или более атрибута взаимно независимы, если ни один
из этих атрибутов не является функционально зависимым от
других.

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

Определение:
Отношение R находится во второй нормальной форме
(2NF) в том и только в том случае, когда оно находится
в 1NF, и каждый неключевой атрибут функционально
полно зависит от ключа R.
Неключевой атрибут – это атрибут, не входящий в состав никакого
потенциального ключа.
Если потенциальный ключ отношения является простым, то отношение
автоматически находится во 2 НФ.

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

Алгоритм перехода от 1 НФ ко 2 НФ.
Отношения находятся в 1НФ.
Шаг 1. Обнаруживаем в отношениях зависимость атрибутов от
части сложного ключа.
Шаг 2. Проводим декомпозицию этих отношений на несколько
отношений:
- те атрибуты, которые зависят от части сложного ключа
выносятся в отдельное отношение вместе с этой частью ключа,
- в исходном отношении остаются все ключевые атрибуты.

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

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

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

Пример
Отношение СТУДЕНТ_КАФЕДРА_СЕКЦИЯ
декомпозируем на два отношения – СТУДЕНТ_КАФЕДРА и
СЕКЦИЯ.
Функциональные зависимости:
Отношение СТУДЕНТ_КАФЕДРА (Код кафедры, Номер
зачетки, ФИО студента, телефон кафедры)
Номер зачетки Код кафедры
Номер зачетки ФИО студента
Номер зачетки Телефон кафедры
Отношение СЕКЦИЯ (Секция, Плата)
Секция Плата

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

Пример
Декомпозиция отношения СТУДЕНТ_КАФЕДРА_СЕКЦИЯ:
а – отношение СТУДЕНТ_КАФЕДРА, б – отношение СЕКЦИЯ

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

Оставшиеся аномалии вставки (INSERT)
В отношение СТУДЕНТ_КАФЕДРА нельзя вставить кортеж
{103, ТИ3110, Пушников А.И., 428-04-37}, т.к. при этом получится, что два
студента одной кафедры под номером 103 (Сидоров К.А. и Пушников А.И.)
имеют разные номера телефонов кафедры, а это противоречит модели
предметной области.
Оставшиеся аномалии обновления (UPDATE)
Одни и те же номера телефонов кафедры повторяются в некоторых
кортежах отношения. Поэтому если на кафедре меняется номер телефона,
то такие изменения необходимо одновременно выполнить во всех местах,
где этот номер телефона встречаются, иначе отношение станет
некорректным.
Оставшиеся аномалии удаления (DELETE)
При удалении некоторых данных по-прежнему может произойти потеря
другой информации.

30. Вторая нормальная форма (примеры)

Таб. №
PK
ФИО
Должность
№ кабинета
Телефон
Имя ребёнка Год рождения
№ ребёнка
PK
1
Иванов
Директор
32
212
Саша
2016
1
2
Зайцева
Бухгалтер
33
213
Петя
2006
1
3
Волков
Менеджер
35
311
1
Иванов
Директор
32
212
Маша
2015
2
2
Зайцева
Бухгалтер
33
213
Витя
2017
2
2
Зайцева
Бухгалтер
33
213
Лена
2019
3
Таб
№
PK
№
ребёнка
PK
Имя
ребёнка
Год
рожден
ия
1
1
Саша
2016
2
1
Петя
2006
1
2
Маша
2015
2
2
Витя
2017
2
3
Лена
2019
-
-
-
Таб.
№
PK
ФИО
Должность
№
Телефон
кабинет
а
1
Иванов
Директор
32
212
2
Зайцев
Бухгалтер
33
213
3
Волков
Менеджер
35
311
4
Волков
Менеджер
35
311

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

Таб. №
PK
ФИО
Должность
№ кабинета
Телефон
1
Иванов
Директор
32
212
2
Зайцев
Бухгалтер
33
213
3
Волков
Менеджер
35
311
4
Кошкина
Менеджер
35
311
Предположим, что в кабинете № 35 изменился телефон. Изменения
требуется синхронно внести в две строки, следовательно, существует
аномалия модификаций.
Пусть есть кабинет, который ещё не занят, тогда невозможно (без
сотрудника) внести информацию о телефоне в этом кабинете.
Следовательно, существует аномалия вставки
Существует аномалия удаления (если в кабинете капитальный ремонт, то
необходимо убрать сотрудников, следовательно, теряются данные о
телефоне).

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

Причина существования аномалий в том, что №
телефона не зависит от ключа (точнее, зависит от
ключа транзитивно, через № кабинета). В
отношении существует зависимость между
неключевыми атрибутами.

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

Определение: Отношение R находится в третьей нормальной
форме (3NF) в том и только в том случае, если находится во
2NF, и каждый неключевой атрибут не является транзитивно
зависимым от какого-либо ключа R.
На практике третья нормальная форма схем отношений
достаточна в большинстве случаев, и приведением к третьей
нормальной форме процесс проектирования реляционной
базы данных обычно заканчивается.

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

№
Телефон
кабинета
PK
32
33
35
Таб. № ФИО
Должность
№
кабинета
1
Иванов
Директор
32
2
Зайцев
Бухгалтер
33
3
Волков
Менеджер
35
4
Кошкина
Менеджер
35
212
213
311

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

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

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

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

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

Пример
Отношение СТУДЕНТ_КАФЕДРА декомпозируем на два
отношения – СТУДЕНТ и КАФЕДРА.
Функциональные зависимости:
Отношение СТУДЕНТ (Код кафедры, Номер зачетки, ФИО
студента)
Номер зачетки Код кафедры
Номер зачетки ФИО студента
Отношение КАФЕДРА (Код кафедры, Телефон кафедры)
Код кафедры Телефон кафедры

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

Пример
Декомпозиция отношения СТУДЕНТ_КАФЕДРА:
а – отношение СТУДЕНТ, б – отношение КАФЕДРА

39. Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы)

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