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

1.

Базы данных
Лабораторная работа 10.
Нормализация баз данных.

2.

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

3.

Функциональная зависимость
• Функциональная зависимость: Атрибут В сущности Е
функционально зависит от атрибута А сущности Е, если и только
если каждое значение А в Е связало с ним точно одно значение В
в Е. Другими словами, А однозначно определяет В.
• Полная функциональная зависимость: Атрибут Е сущности В
полностью функционально зависит от ряда атрибутов А сущности
Е, если и только если В функционально зависит от K и не зависит
ни от какого подряда А.

4.

Функциональная зависимость
• В сущности Сотрудник значение атрибутов Фамилия, Имя и Отчество
однозначно определяются значением атрибута Табельный номер,
т.е. атрибуты Фамилия, Имя и Отчество зависят от атрибута
Табельный номер.

5.

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

6.

Виды нормальных форм
• Первая нормальная форма (1NF).
• Сущность Е находится в первой нормальной форме, если и только
если все атрибуты содержат только атомарные значения. Среди
атрибутов не должно встречаться повторяющихся групп, т. е.
нескольких значений для каждого экземпляра.

7.

Первая нормальная форма
• Атрибуты Телефон и Хобби являются нарушением первой нормальной формы. Что
будет, если у сотрудника несколько рабочих телефонов? Запись значения колонки
через разделитель, например "124-56-78, 124-56-79, 124-56-90" или "Аквалангист,
мотоциклист, шахматист", приводит к ряду проблем. Размера поля может не хватить
для хранения данных (нельзя увеличивать список телефонов до бесконечности), по
такой колонке невозможно построить индекс и т. д. и т. п.

8.

Первая нормальная форма
• Сущность, приведенная на рис. 2, не является решением проблемы.
Что будет, если у сотрудника появится четвертый телефон или третье
хобби? Эту информацию будет негде хранить.

9.

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

10.

Первая нормальная форма
Для приведения сущности к первой нормальной форме следует:
• разделить сложные атрибуты на атомарные,
• создать новую сущность,
• перенести в нее все "повторяющиеся" атрибуты,
• выбрать возможный ключ для нового РК (или создать новый РК).
• установить идентифицирующую связь от прежней сущности к новой,
РК прежней сущности станет внешним ключом (FK) для новой
сущности.

11.

Первая нормальная форма
На рис. 3 показана сущность Сотрудник, приведенная к первой
нормальной форме.

12.

Первая нормальная форма
Фирма
Модели
BMW
M5, X5M, M1
Nissan
GT-R
До и после нормализации
Фирма
Модели
BMW
M5
BMW
X5M
BMW
M1
Nissan
GT-R

13.

Виды нормальных форм
• Вторая нормальная форма.
• Сущность Е находится во второй нормальной форме, если она
находится в первой нормальной форме и каждый неключевой
атрибут полностью зависит от первичного ключа, т. е. не
существует зависимостей от части ключа.
• Вторая нормальная форма имеет смысл только для сущностей,
имеющих сложный первичный ключ.

14.

Вторая нормальная форма
• Предположим, сущность Проект содержит информацию о проекте, которым руководит сотрудник, причем
информация содержится как непосредственно о проекте, так и о руководителе проекта (рис.).
• Атрибуты Фамилия, Имя, Отчество и Должность зависят только от атрибута Табельный номер
руководителя, но вовсе не от Наименования проекта. Другими словами, имеется зависимость только от
части ключа.

15.

Вторая нормальная форма
Для приведения сущности ко второй нормальной форме следует:
• выделить атрибуты, которые зависят только от части первичного ключа, создать новую сущность;
• поместить атрибуты, зависящие от части ключа, в их собственную (новую) сущность;
• установить идентифицирующую связь от прежней сущности к новой

16.

Вторая нормальная форма
Вторая нормальная форма позволяет избежать следующих аномалий
при выполнении операций:
• Обновление (UPDATE). Имеет место дублирование данных о
сотруднике, если он руководит несколькими проектами. Если
данные о сотруднике изменяются, необходимо менять несколько
записей (по числу ведомых проектов).
• Вставка (INSERT). Невозможно ввести данные о сотруднике, если он
в данный момент не руководит проектами.
• Удаление (DELETE). Если сотрудник временно прекращает
руководство проектами, данные о нем теряются.

17.

Вторая нормальная форма
Вторая нормальная форма позволяет избежать следующих аномалий
при выполнении операций:
• Обновление (UPDATE). Имеет место дублирование данных о
сотруднике, если он руководит несколькими проектами. Если
данные о сотруднике изменяются, необходимо менять несколько
записей (по числу ведомых проектов).
• Вставка (INSERT). Невозможно ввести данные о сотруднике, если он
в данный момент не руководит проектами.
• Удаление (DELETE). Если сотрудник временно прекращает
руководство проектами, данные о нем теряются.

18.

Виды нормальных форм
• Третья нормальная форма (3NF).
• Сущность Е находится в третьей нормальной форме, если она
находится во второй нормальной форме и неключевые атрибуты
сущности Е зависят от других атрибутов Е.

19.

Третья нормальная форма
• Сущность Сотрудник находится во второй нормальной форме
(имеется только один атрибут первичного ключа, поэтому не
может быть зависимости неключевых атрибутов от части ключа),
но неключевой атрибут Оклад зависит от другого неключевого
атрибута - Должности.

20.

Третья нормальная форма
Для приведения сущности ко второй нормальной форме следует:
• создать новую сущность и перенести в нее атрибуты с одной и той же
зависимостью от неключевого атрибута;
• использовать атрибут(ы), определяющий эту зависимость, в качестве
первичного ключа новой сущности;
• установить неидентифицирующую связь от новой сущности к старой

21.

Третья нормальная форма
• В третьей нормальной форме каждый атрибут сущности зависит от
ключа, от всего ключа целиком и ни от чего другого, кроме как от
ключа. Третья нормальная форма также позволяет избежать ряда
аномалий:
• Обновление (UPDATE). Имеет место дублирование данных об окладе,
если должность занимают несколько сотрудников. Если оклад
соответствующих должности меняется, необходимо менять несколько
записей (по числу сотрудников на одной должности).
• Вставка (INSERT). Невозможно ввести данные об окладе,
соответствующем должности, если в данный момент нет сотрудника,
занимающего эту должность.
• Удаление (DELETE). В случае удаления из таблицы сотрудника,
занимающего уникальную должность, данные об окладе теряются.

22.

Виды нормальных форм
• Четвертая нормальная форма (4NF).
• Отношение R находится в 4NF и том и только в том случае, когда
существует многозначная зависимость между A и B, а все
остальные атрибуты R функционально зависят от A.

23.

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

24.

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

25.

Денормализация.
• В результате нормализации все взаимосвязи данных становятся
правильно определены, исключаются аномалии при оперировании с
данными, модель данных становится легче поддерживать.
• Однако часто нормализация данных не ведет к повышению
производительности ИС в целом. Рассмотрим примеры на рис. 1 и 6.
Для получения полной информации о сотруднике из
ненормализованной структуры данных достаточно обратиться к одной
таблице.
• После приведения структуры данных к третьей нормальной форме
информация о сотруднике содержится уже в четырех таблицах.

26.

Денормализация.
• Хотя общее количество строк в этих таблицах может быть меньше, чем
в исходной (до нормализации), теперь для получения полной
информации о сотруднике серверу БД необходимо обращаться
одновременно к четырем таблицам (объединение таблиц, join).
• Время выполнения запроса с объединением может во много. раз
превосходить время выполнения запроса к одной таблице, другими
словами, в приведенном примере общая производительность ИС в
результате нормализации скорее всего упадет.
• В целях повышения производительности при переходе на физический
уровень приходится сознательно отходить от нормальных форм для
того, чтобы использовать возможности конкретного сервера или ИС в
целом.

27.

Задание по работе
В файле «лаб11_варианты.doc»