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

1.

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

2.

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

3.

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

4.

Основные виды зависимостей
•функциональные;
•многозначные;
•транзитивные.

5.

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

6.

Код
сотрудника
ФИО
Должность
Номер
Наименование
отдела
отдела
Квалификация
7513
Иванов И.И.
Программист
128
Отдел
проектирования
C
7513
Иванов И.И.
Программист
128
Отдел
проектирования
Java
9842
Сергеева С.С.
Администратор БД
42
Финансовый отдел
DB2
6651
Петров П.П.
Программист
128
Отдел
проектирования
VB
6651
Петров П.П.
Программист
128
Отдел
проектирования
Java
9006
Николаев Н.Н.
Системный
администратор
128
Отдел
проектирования
Windows
9006
Николаев Н.Н.
Системный
администратор
128
Отдел
проектирования
Linux

7.

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

8.

Преподаватель (Код преподавателя, Фамилия,
Должность, Оклад)
Код преподавателя
Фамилия
Должность
Оклад
001
Долгова
Высшая категория
30000
002
Калинина
Зав. циклом
38000
003
Жук
Зав. циклом
38000
004
Хрущева
Высшая категория
30000
005
Гайфутдинова
Первая категория
25000
006
Рамазанова
Высшая категория
30000
007
Ильина
Первая категория
25000

9.

Многозначная зависимость.
• Один атрибут отношения многозначно определяет другой атрибут
того же отношения, если для каждого значения первого атрибута
существует множество соответствующих значений второго
атрибута. Многозначные зависимости могут быть:
• один-ко-многим (1:М);
• многие-к-одному (М:1);
• многие-ко-многим (М:М).

10.

Предмет (Код предмета, Название, Цикл, Объем часов,
Преподаватели)
Код предмета
Название
Цикл
Объем
часов
Преподаватели
01
Программирование
Общепрофессиональный
200
Долгова
02
Высшая математика
Общепрофессиональный
80
Долгова, Калинина,
Рамазанова
03
История
Гуманитарный
50
Гайфутдинова
04
Базы данных
Профессиональный
70
Жук, Хрущева
05
Обществознание
гуманитарный
60
Ильина
06
Веб-дизайн
Профессиональный
150
Жук
01
Программирование
Общепрофессиональный
200
Калинина

11.

Побочные явления и аномалии нежелательных
функциональных зависимостей:
• избыточность данных;
• аномалии обновления;
• аномалии удаления;
• аномалии ввода.

12.

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

13.

Избыточность данных
Код предмета
Название
Цикл
Объем
часов
01
Программирование
Общепрофессиональный
200
02
Высшая математика
Общепрофессиональный
80
03
История
Гуманитарный
50
04
Базы данных
Профессиональный
70
05
Обществознание
Гуманитарный
60
06
Веб-дизайн
Профессиональный
150

14.

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

15.

Аномалии обновления
Код предмета
Название
Цикл
Объем
часов
Преподаватели
01
Программирование
Общепрофессиональный
200
Долгова, Калинина
02
Высшая математика
Общепрофессиональный
80
Долгова, Калинина,
Рамазанова
03
История
Гуманитарный
50
Гайфутдинова
04
Базы данных
Профессиональный
70
Жук, Хрущева
05
Обществознание
гуманитарный
60
Ильина
06
Веб-дизайн
Профессиональный
150
Жук

16.

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

17.

Аномалии ввода
Аномалии ввода возникают при добавлении в
таблицу новых записей, обычно в поля с
ограничениями NOT NULL (не пустые).
Когда в отношение на данный момент времени
невозможно ввести однозначную информацию.

18.

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

19.

Нормальные формы
• первая нормальная форма (1НФ);
• вторая нормальная форма (2НФ);
• третья нормальная форма (3НФ);
• нормальная форма Бойса-Кодда (БКНФ);
• четвертая нормальная форма (4НФ);
• пятая нормальная форма, или нормальная форма проекции
соединения (5НФ или ПС/НФ).

20.

Основные свойства нормальных форм:
• каждая следующая нормальная форма, в
некотором смысле, улучшает свойства
предыдущей;
• при переходе к следующей нормальной форме
свойства предыдущих нормальных форм
сохраняются.

21.

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

22.

Нормализация РБД на примере расходной
накладной

23.

1 НФ
Номер
накладн
ой
Дата
Покупат
ель
Город
Адрес
234
10.01.20
02
ООО
«Геракл
»
Москва
234
10.01.20
02
ООО
«Геракл
»
235
10.01.20
02
209
209
Товар
Количес
тво
Ед. изм.
Цена
Общая
стоимос
ть
у.л.
тушенк 1000
Стромынк а
а, 20
банки
25
25000
Москва
у.л.
сахар
Стромынк
а, 20
50
кг
10
500
ООО
«Геракл
»
Москва
у.л.
макар
Стромынк оны
а, 20
300
кг
10
3000
15.01.20
02
ООО
«Заря»
Казань
у.л.
сахар
Галеева, 3
65
кг
20
1300
15.01.20
02
ООО
«Заря»
Казань
у.л.
макар
Галеева, 3 оны
100
кг
10
1000

24.

Функциональные зависимости
• Первичный ключ: Номер накладной, Товар
Функциональные зависимости от частей первичного ключа:
• Номер накладной
Покупатель
Товар
Ед. изм.
• Номер накладной
Дата
Товар
Цена
• Номер накладной
Город
• Номер накладной
Адрес
Функциональные зависимости от первичного ключа:
• Номер накладной, Товар
Количество
• Номер накладной, Товар
Общая стоимость

25.

Декомпозиция отношений
НАКЛАДНАЯ
Номер
накладной
Дата
234
209
2НФ
Товар
ПОКУПАТЕ
ЛЬ
Город
Адрес
Товар
Ед.
изм
Цена
10.01.20 ООО
02
«Геракл»
Москва
у.л. Стромынка,
20
тушенка
банки
25
15.01.20 ООО
02
«Заря»
Казань
у.л. Галеева, 3
сахар
кг
10
макароны
кг
10
Отпуск товаров
Номер
накладной
Товар
Количество Общая
стоимость
234
тушенка 1000
25000
234
сахар
50
500
234
макаро
ны
300
3000

26.

Приведение к 3 НФ. Транзитивная связь
НАКЛАДНАЯ
Номер
накладной
Дата
ПОКУПАТЕЛЬ
Город
Адрес
234
10.01.2002
ООО «Геракл»
Москва
ул.
Стромынка,
20
209
15.01.2002
ООО «Заря»
Казань
ул. Галеева, 3
256
17.02.2002
ООО «Геракл»
Москва
ул.
Стромынка,
20

27.

Транзитивная зависимость в отношении
«Накладная»
• Номер накладной
• Номер накладной
• Номер накладной
• Покупатель
• Покупатель
Город
Адрес
Покупатель
Город
Адрес

28.

3 НФ
Покупатель
НАКЛАДНАЯ
Номер
накладной
Дата
ПОКУПАТЕЛЬ
Покупатель
Город
Адрес
234
10.01.2002
ООО «Геракл»
ООО «Геракл»
Москва
ул.
Стромынка, 20
209
15.01.2002
ООО «Заря»
ООО «Заря»
Казань ул. Галеева,3
256
17.02.2002
ООО «Геракл»

29.

Приведение к 3 НФ. Транзитивная связь
Номер
Товар
накладной
Колич
ество
Общая
стоимость
234
Тушенка
1000
25000
234
Сахар
50
500
234
макароны
300
3000

30.

Транзитивная зависимость в отношении
«Отпуск товаров»
• Номер накладной, Товар
• Номер накладной, Товар
Количество
Количество
Общая
стоимость
Общая стоимость

31.

3 НФ
Отпуск товаров
Товар
Номер
накладной
Товар
Количество
Товар
Ед.
изм
Цена
234
тушенка
1000
тушенка
банки 25
234
сахар
50
сахар
кг
10
234
макароны
300
макароны
кг
10

32.

Нормализованная РБД «Расходная накладная»
НАКЛАДНАЯ
Номер
накладной
Дата
234
209
Товар
ПОКУПАТЕ
ЛЬ
Товар
Ед.
изм
Цена
10.01.20 ООО
02
«Геракл»
тушенка
банки
25
15.01.20 ООО
02
«Заря»
сахар
кг
10
макароны
кг
10
Отпуск товаров
Номер
накладной
Товар
Количество
234
тушенка 1000
Покупатель
Город
Адрес
234
сахар
50
ООО
«Геракл»
Москва
ул.
Стромынка, 20
234
макаро
ны
300
ООО «Заря»
Казань ул. Галеева,3
Покупатель

33.

Практическая работа
• Приведение базы данных «Квалификация сотрудников» к
нормальной форме

34.

Код
сотрудника
ФИО
Должность
Номер
отдела
Наименование
отдела
Квалификация
7513
Иванов И.И.
Программист
128
Отдел
проектирования
9842
Сергеева С.С.
Администратор БД
42
Финансовый отдел
6651
Петров П.П.
Программист
128
Отдел
проектирования
VB, Java
9006
Николаев Н.Н.
Системный
администратор
128
Отдел
проектирования
Windows, Linux
C, Java
DB2

35.

Код
сотрудника
ФИО
Должность
Номер
Наименование
отдела
отдела
Квалификация
7513
Иванов И.И.
Программист
128
Отдел
проектирования
C
7513
Иванов И.И.
Программист
128
Отдел
проектирования
Java
9842
Сергеева С.С.
Администратор БД
42
Финансовый отдел
DB2
6651
Петров П.П.
Программист
128
Отдел
проектирования
VB
6651
Петров П.П.
Программист
128
Отдел
проектирования
Java
9006
Николаев Н.Н.
Системный
администратор
128
Отдел
проектирования
Windows
9006
Николаев Н.Н.
Системный
администратор
128
Отдел
проектирования
Linux

36.

Код
сотрудника
ФИО
Должность
Номер
Наименование
отдела
отдела
7513
Иванов И.И.
Программист
128
Отдел проектирования
9842
Сергеева С.С.
Администратор БД
42
Финансовый отдел
6651
Петров П.П.
Программист
128
Отдел проектирования
9006
Николаев Н.Н.
Системный администратор
128
Отдел проектирования
Код сотрудника
Квалификация
7513
C
7513
Java
9842
DB2
6651
VB
6651
Java
9006
Windows
9006
Linux

37.

Код
ФИО
сотрудника
Должность
Номер
отдела
7513
Иванов И.И.
Программист 128
9842
Сергеева С.С.
Администрат
42
ор БД
6651
Петров П.П.
Программист 128
Номер
отдела
Наименование
отдела
42
Финансовый отдел
128
Отдел
проектирования