normalizatsia

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

Основы проектирования баз
данных

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

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

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

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

4.

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

5. СТРУКТУРА НОРМАЛИЗАЦИИ:

В теории реляционных баз данных обычно выделяется следующая
последовательность нормальных форм:
первая нормальная форма (1NF);
вторая нормальная форма (2NF);
третья нормальная форма (3NF);
нормальная форма Бойса-Кодда (BCNF);
четвертая нормальная форма (4NF);
пятая нормальная форма, или нормальная форма проекции-соединения (5NF или
PJ/NF).
Нормализация таблиц базы данных - первый шаг на пути проектирования
структуры реляционной базы данных.
База данных считается нормализованной, если ее таблицы представлены как
минимум в третьей нормальной форме. Часто многие таблицы нормализуются
до четвертой нормальной формы, иногда, наоборот, производится
денормализация.
Главная цель нормализации базы данных - устранение избыточности и
дублирования информации. В идеале при нормализации надо добиться, чтобы
любое значение хранилось в базе в одном экземпляре, причем значение это не
должно быть получено расчетным путем из других данных, хранящихся в базе.

6. Аномалии и избыточное дублирование данных

АНОМАЛИИ И ИЗБЫТОЧНОЕ ДУБЛИРОВАНИЕ ДАННЫХ
• Наличие первого из них допускается в БД, а избыточное дублирование данных может приводить к
проблемам при их об обработке.
• Приведём пример обоих вариантов дублирования.
• Пример не избыточного дублирования данных представляет отношение СГ (студент-группа) с
атрибутами Студент и Группа:
• Для студентов, обучающихся в одной группе, номер группы совпадает. Для каждого студента номер
группы - уникальный атрибут. Поэтому ни один из номеров групп не является избыточным.
Студент
Иванов
Петров
Сидоров
Группа
307
307
400

7. Аномалии и избыточное дублирование данных

АНОМАЛИИ И ИЗБЫТОЧНОЕ ДУБЛИРОВАНИЕ ДАННЫХ
• Пример избыточного дублирования (избыточности) представляет отношение
СГК (студент-группа-куратор), которые, в отличии от отношения СГ, дополнено
атрибутом Куратор.
• Вполне естественно, что студенты одной группы находятся под руководством
одного и того же куратора. В рассматриваемом отношении имеется
избыточное дублирование данных:
Студент
Иванов
Петров
Сидоров
Группа
307
307
400
Куратор
Епишина
Епишина
Прошкин

8. Аномалии и избыточное дублирование данных

АНОМАЛИИ И ИЗБЫТОЧНОЕ ДУБЛИРОВАНИЕ ДАННЫХ
• Возможный способ данных из этой ситуации в создании двух отношений СГ и
ГК, полученных путём декомпозиции исходного отношения СГК:
Студент
Группа
Иванов
307
Петров
307
Сидоров
400
Группа
Куратор
307
Епишина
400
Прошкин

9. Исключение избыточности дублирования

ИСКЛЮЧЕНИЕ ИЗБЫТОЧНОСТИ ДУБЛИРОВАНИЯ
• Процедура декомпозиции отношения СГК на 2 отношения СГ и ГК является основной процедурой нормализации отношений.
• Избыточное дублирование создает проблемы при обработке кортежей отношениями, названной Эдгаром Коддом «Аномалии
обновления отношений».
• Аномалиями будем называть такую ситуацию в таблица БД, которая приводит к противоречиям в БД, либо существенно
усложняет обработку данных.
• Выделяют 3 основных вида аномалий:
Аномалии модификаций (редактирование)
Аномалии удаления
Аномалии добавления
• Аномалии модификации проявляются в том, что изменение значения одного данного может повлечь за собой просмотри всей
таблицы и соответствующее изменение некоторых других записей таблицы.
• Аномалии удаления состоят в том, что при удалении какого-либо данного из таблицы может пропасть и другая информация,
которая не связана напрямую с удаляемым данным.
• Аномалии добавления возникают в случаях, когда информацию в таблицу нельзя поместить до тех пор, пока она неполная,
либо вставка новой записи требует дополнительного просмотра таблицы.

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Первая нормальная форма (1НФ)
• Отношение находится в 1НФ, если все его атрибуты являются простыми, все
используемые домены должны содержать только скалярные значения.
• Не должно быть повторений строк в таблице.
• Например, есть таблица «Автомобили»:
Фирма
Модели
BMW
M5, X5M, M1
Nissan
GT-R

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной
ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является
атомарным.
• Преобразуем таблицу к 1НФ:
Фирма
Модели
BMW
M5
BMW
X5M
BMW
M1
Nissan
GT-R

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной
ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является
атомарным.
• Преобразуем таблицу к 1НФ:
Фирма
Модели
BMW
M5
BMW
X5M
BMW
M1
Nissan
GT-R

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

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

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

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

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

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

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

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

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

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

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

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

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Вторая нормальная форма (2НФ)
• Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут
неприводимо зависит от Первичного Ключа(ПК).
• Неприводимость означает, что в составе потенциального ключа отсутствует меньшее
подмножество атрибутов, от которого можно также вывести данную функциональную
зависимость.
• Например, дана таблица:
Модель
Фирма
Цена
Скидка
M5
BMW
5500000
5%
X5M
BMW
6000000
5%
M1
BMW
2500000
5%
GT-R
Nissan
5000000
10%

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Таблица находится в первой нормальной форме, но не во второй.
• Цена машины зависит от модели и фирмы.
• Скидка зависит от фирмы, то есть зависимость от первичного ключа неполная.
Исправляется это путем декомпозиции на два отношения, в которых не
ключевые атрибуты зависят от первичного ключа.
Модель
Фирма
Цена
M5
BMW
5500000
X5M
BMW
6000000
M1
BMW
2500000
GT-R
Nissan
5000000
Фирма
Скидка
BMW
5%
Nissan
10%

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

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

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Третья нормальная форма (3НФ)
• Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут
нетранзитивно зависит от первичного ключа.
• Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых
может относиться к нескольким записям таблицы в отдельные таблицы.
• Рассмотрим таблицу:
Модель
Магазин
Телефон
BMW
Риал-авто
87-33-98
Audi
Риал-авто
87-33-98
Nissan
Некст-Авто
94-54-12

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

НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ
• Таблица находится во 2НФ, но не в 3НФ.
• В отношении атрибут «Модель» является первичным ключом. Личных телефонов у автомобилей нет, и телефон зависит
исключительно от магазина.
• Таким образом, в отношении существуют следующие функциональные зависимости: Модель → Магазин, Магазин → Телефон,
Модель → Телефон.
• Зависимость Модель → Телефон является транзитивной, следовательно, отношение не находится в 3НФ.
• В результате разделения исходного отношения получаются два отношения, находящиеся в 3НФ:
Риал-авто 87-33-98
Риал-авто 87-33-98
Некст-Авто 94-54-12
Модель
Магазин
BMW
Риал-авто
Audi
Риал-авто
Nissan
Некст-Авто

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

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

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

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

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

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

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

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

28. Процедура нормализации включает разбиение, или декомпозицию данного отношения на другие отношения, причем декомпозиция должна

быть обратимой, т.е. выполняться без потерь информации (декомпозиция
без потерь). Вопрос о том, происходит ли утрата информации при
декомпозиции, тесно связан с концепцией функциональной зависимости.
В качестве примера рассмотрим отношение S {S#, STATUS, CITY} и две
его возможные декомпозиции (а) и (б).

29.

в случае а) сохранение информации в полном объеме означает, что при
обратном соединении отношений SST и SC будет получено исходное
отношение. В случае б) такого может не получиться. Обратимость
означает, что исходное отношение равно соединению его проекций.
Теорема Хеза.
Пусть R{A,B,C} является отношением,
где A, B, C – атрибуты этого отношения.
Если R удовлетворяет зависимости
A B, то R равно соединению его
проекций {A,B} и {A,C}.

30. Основные свойства нормальных форм:

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

31. НАЗНАЧЕНИЕ НОРМАЛИЗАЦИИ

Устранить аномалии обновления отношений (При проведении
нормализации базы данных происходит устранение или серьезное
сокращение избыточности, дублирования данных):
1. Аномалии вставки
2. Аномалии удаления
3. Аномалия обновления
Значительно сокращается вероятность появления противоречивых
данных.
Облегчается администрирование базы и обновление информации
в ней, сокращается объем занятого базой дискового пространства.

32.

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