Лекция 7. Тема 2.1. Концептуальное и логическое проектирование баз данных. Нормализация отношений

1. Базы данных

«КАЗАНСКИЙ НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ
ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ИМ. А.Н. ТУПОЛЕВА-КАИ»
(КНИТУ-КАИ)
ИНСТИТУТ КОМПЬЮТЕРНЫХ ТЕХНОЛОГИЙ И ЗАЩИТЫ ИНФОРМАЦИИ
КАФЕДРА АВТОМАТИЗИРОВАННЫХ СИСТЕМ ОБРАБОТКИ ИНФОРМАЦИИ И УПРАВЛЕНИЯ
Базы данных
Гаптуллазянова Гульшат Ильдусовна
Ст. преподаватель каф. АСОИУ
Казань 2022

2. Лекция 7. Тема 2.1. Концептуальное и логическое проектирование баз данных. Нормализация отношений

3. Нормализация отношений.

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

4. Нормализация отношений.

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

5. Нормализация отношений.

Рассмотрим отношение
Студенты (№гр, №зач, Разм_стип, Фамилия)
В отношении следующие ФЗ:
№зач --> №гр
№зач --> Фамилия
№зач --> Разм_стип
Атрибут «№зач» не является функционально зависимым от
атрибута «Разм_стип», т.к. несколько студентов могут иметь
одинаковую стипендию, также фамилия не может определять «№зач»,
т.к. могут быть однофамильцы.

6. Нормализация отношений.

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

7. Нормализация отношений.

Рассмотрим отношение
Студенты (№гр, №зач, Фамилия, Дисциплина, Оценка)
В отношении следующие ФЗ:
№зач, Дисциплина --> Оценка
№зач, Дисциплина --> №гр
№зач, Дисциплина --> Фамилия
№зач --> №гр
№зач --> Фамилия
В отношении «Студенты» атрибут «Оценка» является полностью
зависимым от составного ключа «№зач+Дисциплина», остальные
атрибуты зависят от части сложного ключа, только от атрибута
«№зач».

8. Нормализация отношений.

Определение 4. Транзитивная зависимость. Пусть X, Y и Z –
три атрибута отношения R. Если Z зависит от Y, а Y – от X, то
Z зависит от X. Если при этом обратное соответствие
неоднозначно, т.е. X не зависит от Y или Y не зависит от Z, то
говорят, что Z транзитивно зависит от X.
Если X --> Y и Y --> Z, но Y --> X и Z --> Y, то говорят что X
--> Z

9. Нормализация отношений.

Рассмотрим отношение
Студенты(№гр, №зач, Разм_стип, Фамилия, Специальность)
В отношении следующие функциональные зависимости:
№зач --> №гр
№зач --> Фамилия
№зач --> Разм_стип
№зач --> Специальность
№гр --> Специальность
Например, атрибут «Специальность» зависит от атрибута «№гр»,
который в свою очередь зависит от атрибута «№зач», т.о.
«Специальность» транзитивно зависит от атрибута «№зач».

10. Нормализация отношений.

Определение 5. Возможный ключ. Первичный ключ.
Возможным ключом отношения называется набор атрибутов, который
однозначно определяет значения всех остальных атрибутов отношения,
т.е. однозначно определяет кортеж отношения (строку).
Служащие (Таб№, Фамилия, Паспорт, ИНН, Страх_Свид)
Возможных ключей может быть несколько, среди них выбирают один
главный и он называется первичным ключом (Таб№). Атрибуты,
входящие в него, называются первичными.
Основными называются атрибуты, которые входят хотя бы в один из
возможных ключей.
Неключевым называется любой атрибут отношения, не входящий в
состав ни одного возможного ключа отношения.

11. Нормализация отношений.

Определение 6. Детерминант.
Каждый атрибут или набор атрибутов, от которого функционально
полно зависит другой атрибут, называется детерминантом
отношения.
Если X -> Y и Y функционально полно зависит от X, то
говорят, что X – детерминант Y.
Первичный ключ является детерминантом, так как его значение
однозначно определяет значение любого атрибута отношения.

12. Нормализация отношений.

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

13. Нормализация отношений.

Основная операция метода декомпозиции – операция проекции.
Например, дано отношение R(А, В, С, D, E, …), в котором есть ФЗ
С -> D. Для устранения этой зависимости выполняют декомпозицию
отношения R на два новых отношения R1(А, В, С, E, …) и R2(С, D).
Отношение R2 является проекцией отношения R на атрибуты С и D.
Выделяют несколько НФ:
1НФ – 2НФ — 3НФ — НФБойса-Кодда – 4НФ — 5НФ.
Каждая следующая НФ улучшает свойства схемы, причем
свойства предыдущей НФ сохраняются. Достаточной считается 3НФ.

14. Нормализация отношений.

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

15. Нормализация отношений.

16. Нормализация отношений.

Определение 9. Отношение R находится в 1НФ тогда и только
тогда, когда все его атрибуты простые.
Для получения 1НФ нужно исключить повторяющиеся группы,
получить плоскую таблицу, за счет добавления строк для каждого
элемента в повторяющихся группах.

17. Нормализация отношений.

R1
Таб№ Имя
ФИО
Оклад Комната
ребенка
211 Саша
Иванов
1500
12
211 Женя
Иванов
1500
12
211 Ваня
Иванов
1500
12
358 Вова
Петров
1700
12
360 Таня
Сидоров
2300
15
360 Саша
Сидоров
2300
15
Недостатки:
1. Появляется избыточность.
2. Аномалия обновления.
3. Аномалия включения.
Телефон
616
616
616
616
305
305
Возраст
ребенка
10
7
3
5
8
6

18. Нормализация отношений.

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

19. Нормализация отношений.

R2
Таб№ Имя Возраст
Ребенка
211 Саша
10
211 Женя
7
211 Ваня
3
358 Вова
5
360 Таня
8
360 Саша
6
Таб№
R3
ФИООклад
211
358
360
Иванов
Петров
Сидоров
Комн. Телефон
1500
1700
2300
Недостатки:
1. Номер телефона многократно повторяется.
2. Телефон – характеристика комнаты, а не сотрудника.
3. Аномалия обновления, включения и удаления.
12
12
15
616
616
305

20. Нормализация отношений.

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

21. Нормализация отношений.

Расщепляем R3 на 2 отношения R4 и R5.
R4
Таб№ ФИО Оклад
Комн.
211
Иванов
1500
12
358
Петров
1700
12
360
Сидоров 2300
15
Получили три отношения R2, R4, R5.
Комн
R5
Телефон
12
15
616
305

22. Нормальная форма Бойса-Кодда

Если в отношении имеется зависимость атрибутов составного ключа
от неключевых атрибутов, то необходимо перейти к усиленной 3НФ или
нормальной форме Бойса-Кодда (НФБК).
Определение 12. Отношение R находится в НФБК, если оно является
отношением в 3НФ, и в нем отсутствуют зависимости ключей (атрибутов
составного ключа) от неключевых атрибутов.
Если отношение находится в НФБК, то автоматически и в 3НФ.
Рассмотрим пример отношения сдачи экзаменов студентами:
Экзамены (Ид_студ, №_зач_кн, Дисциплина, Дата, Оценка)
Ид_студ – это некий уникальный номер студента, введенный для
электронного учета.

23. Нормальная форма Бойса-Кодда

Отношение имеет два возможных ключа:
Ид_студ, Дисциплина, Дата
№_зач_кн, Дисциплина, Дата
Функциональные зависимости в этом отношении:
Ид_студ, Дисциплина, Дата --> Оценка
Ид_студ, Дисциплина, Дата --> №_зач_кн
№_зач_кн --> Ид_студ
№_зач_кн, Дисциплина, Дата --> Оценка
№_зач_кн, Дисциплина, Дата --> Ид_студ
Ид_студ --> №_зач_кн

24. Нормальная форма Бойса-Кодда

25. Нормальная форма Бойса-Кодда

Эти варианты равнозначны. Остановимся на втором из них.
Ид_студ и №_зач_кн являются детерминантами, но не
являются возможными ключами, т.к. каждый из возможных
ключей состоит из трех атрибутов. Таким образом это отношение не
имеет НФБК. Разобьем его на два отношения:

26. Нормальная форма Бойса-Кодда

В отношении R2 №_зач_кн и
Ид_студ являются
детерминантами и в то же время возможными ключами, т.е.
отношение в НФБК.
Набор не избыточных ФЗ полученных путем удаления всех
избыточных ФЗ с помощью правил вывод (нормализации)
называется минимальным покрытием. Но минимальное
покрытие может быть не единственным.

27. Итоговый алгоритм проектирования:

1. Строится универсальное отношение, т.е. проверяется 1НФ.
2. Определяются ФЗ и строится минимальное покрытие:
Выделяются возможные ключи и детерминанты, полные и
неполные, транзитивные зависимости и удаляются избыточные
ФЗ.
3. Полученное минимальное покрытие используется для
дальнейшей декомпозиции.
4. Если минимальных покрытий несколько, то производится их
сравнение.
Каждая следующая нормальная форма удаляет избыточные ФЗ.

28.

Пример 1. Проектирования схемы РБД методом нормализации отношений.
В исходной таблице «Кафедры – Студенты» представлены следующие
поля:
Ш_каф, Наим_каф, ФИО_зав_каф, Тел_каф, Ном_группы, ФИО_старосты,
Кол_студентов, Ном_зач, ФИО_студента
Ограничения:
1. За кафедрой закреплено несколько групп (эти группы выпускаются на
кафедре), но каждая группа закреплена за одной кафедрой.
2. В группе обучается много студентов, но каждый студент обучается только в
одной группе.
Вариант а) Таблица построена относительно каждой кафедры.
Вариант б) Таблица построена относительно каждого студента (выполнить
самостоятельно).
Ном_зач, ФИО, Ном_гр, ФИО_ст, Кол_ст, Ш_каф, Наим, ФИО_зав, Тел

29.

Пример 2. Проектирования схемы РБД методом нормализации
отношений.
В исходной таблице «Студенты - Оценки» представлены следующие
поля:
Ном_зач, ФИО_студента, Ном_группы, Специальность_группы,
Шифр_предмета, Наим_предмета, Дата_сдачи_экзамена, Оценка
Ограничения:
1. Каждый студент может сдавать экзамены по нескольким предметам, а
также несколько раз в разные дни по одному и тому же предмету.
2. В группе обучается много студентов, но каждый студент обучается
только в одной группе. У всех студентов одной группы специальность
одинаковая.
Самостоятельная работа над заданием.

30. Спасибо за внимание!

30