Принципы нормализации БД. Тема 1.2

1.

ПРИНЦИПЫ
НОРМАЛИЗАЦИИ
БД

2.

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

3.

Дублирование данных
Неизбыточное
Избыточное
Наличие допускается
в базах данных
Может приводить
к проблемам при
обработке данных

4.

Пример неизбыточного
дублирования данных
Сотрудник
Телефон
Иванов И.М.
3721
Петров А.Ю.
4555
Сидоров С.С.
4555
Егоров В.И.
4555

5.

Пример избыточного
дублирования данных
Сотрудник
Возможный способ
декомпозиции
Телефон Н_кабинета
Иванов И.М.
3721
109
Н_кабинета
Телефон
Петров А.Ю.
4555
111
109
3721
Сидоров С.С.
4555
111
111
4555
Егоров В.И.
4555
111
Сотрудник
Н_кабинета
Иванов И.М.
109
Петров А.Ю.
111
Сидоров С.С.
111
Егоров В.И.
111
Пример неудачного
отношения
Сотрудник
Телефон Н_кабинета
Иванов И.М.
3721
109
Петров А.Ю.
4555
111
Сидоров С.С.
-
111
Егоров В.И.
-
111

6.

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

7.

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

8.

Нормальные формы:
■ -
первая нормальная форма (1NF);
■ -
вторая нормальная форма (2NF);
■ -
третья нормальная форма (3NF);
■ -
нормальная форма Бойса-Кодда (BCNF);
■ -
четвертая нормальная форма (4NF);
■ - пятая нормальная форма, или
нормальная форма проекции-соединения
(5NF).

9.

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

10.

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

11.

Таблица Поставщики
Поставщик
Статус
Город
Страна
Адрес
Телефон
поле Страна функционально зависит
составного ключа (Поставщик, Город).
от
Однако эта зависимость не является
функционально полной, так как Страна
функционально зависит и от части ключа – поля
Город.

12.

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

13.

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

14.

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

15.

Пример 2. Таблица УСПЕВАЕМОСТЬ
№ ЗАЧЕТНОЙ
КНИЖКИ
ФИО СТУДЕНТА
МЕСТО
РОЖДЕНИЯ
ДАТА
РОЖДЕНИЯ
1992412-11
Иванов И.В.
25.03.1998 1
4,5
25.03.1998 2
4,1
25.03.1998 3
5
25.03.1998 4
4,8
Данилов О.В.
г.Новосибирс
к
г.Новосибирс
к
г.Новосибирс
к
г.Новосибирс
к
г.Бердск
1992412-11
Иванов И.В.
1992412-11
Иванов И.В.
1992412-11
Иванов И.В.
1992432-11
13.06.1999 1
4,6
1992432-11
Данилов О.В.
г.Бердск
13.06.1999 2
4,2
1992432-11
Данилов О.В.
г.Бердск
13.06.1999 3
4,3
1992432-11
Данилов О.В.
г.Бердск
13.06.1999 4
3,8
КУРС
СРЕДНИЙ
БАЛЛ

16.

Первичным ключом является
совокупность полей №
ЗАЧЕТНОЙ КНИЖКИ, КУРС.
Таблица
Успеваемость
Поля
Первичный
ключ
№
ЗАЧЕТНОЙ Совокупность
КНИЖКИ
двух атрибутов
КУРС
ФИО СТУДЕНТА
МЕСТО РОЖДЕНИЯ
ДАТА РОЖДЕНИЯ
СРЕДНИЙ БАЛЛ

17.

Таблица успеваемость не
находится в 2НФ.
■ Приведение данной таблицы к
2НФ заключается в ее
разбиении (декомпозиции) на
две таблицы, удовлетворяющие
соответствующим требованиям
нормализации.

18.

СТУДЕНТЫ
№
ЗАЧЕТНОЙ
КНИЖКИ
ФАМИЛИЯ
ИМЯ
ОТЧЕСТВО
МЕСТО
РОЖДЕНИЯ
ДАТА
РОЖДЕНИЯ
УСПЕВАЕМОСТЬ СТУДЕНТОВ
№ ЗАЧЕТНОЙ КНИЖКИ
КУРС
СРЕДНИЙ БАЛЛ

19.

Добавим в таблицу СТУДЕНТЫ
два поля: № ГРУППЫ и ФИО
КУРАТОРА:
№
ЗАЧЕТНОЙ ФАМИЛИЯ ИМЯ ОТЧЕСТВО
КНИЖКИ
МЕСТО
РОЖДЕНИЯ
ДАТА
РОЖДЕНИЯ
ФИО
№
КУРАТОР
ГРУППЫ
А
Первичным ключом таблицы СТУДЕНТЫ является
поле № ЗАЧЕТНОЙ КНИЖКИ. Таблица находится в
2НФ, поскольку отсутствуют зависимости полей от
части ключа.

20.

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

21.

СПРАВОЧНИК СТУДЕНТОВ
№
ЗАЧЕТНОЙ ФАМИЛИЯ ИМЯ ОТЧЕСТВО
КНИЖКИ
МЕСТО
РОЖДЕНИЯ
ДАТА
РОЖДЕНИЯ
№
ГРУППЫ
ГРУППЫ
№ ГРУППЫ
ФИО КУРАТОРА
УСПЕВАЕМОСТЬ СТУДЕНТОВ
№ ЗАЧЕТНОЙ
КНИЖКИ
КУРС
СРЕДНИЙ БАЛЛ

22.

НФБК
Таблица находится в нормальной
форме Бойса-Кодда (НФБК), если
и только любая функциональная
зависимость между его полями
сводится к полной
функциональной зависимости
от возможного ключа.

23.

Пример. БД Питание и ее
приведение к 3НФ
Блюдо
Вид
Рецепт
Порций
Дата_
Реализ
ации
Лобио
Закуск
а
Лом.
158
1/9/9
4
Харчо
Суп
144
1/9/9
4
Продук
т
Калори
йность
Вес (г)
Постав
щик
Город
Страна
Вес (кг)
Цена
($)
Дата П
Фасол
ь
3070
200
"Хуанх
э"
Пекин
Китай
250
0.37
24/8/
94
Лук
450
40
"Натал
ка"
Киев
Украин
а
100
0.52
27/8/
94
Масло
7420
30
"Лайм
а"
Рига
Латвия
70
1.55
30/8/
94
Зелень
180
10
"Дауга
ва"
Рига
Латвия
15
0.99
30/8/
94
Мясо
1660
80
"Натал
ка"
Киев
Украин
а
100
2.18
27/8/
94
Лук
450
30
"Натал
ка"
Киев
Украин
а
100
0.52
27/8/
94
Томат
ы
240
40
"Полес
ье"
Киев
Украин
а
120
0.45
27/8/
94
Рис
3340
50
"Хуанх
э"
Пекин
Китай
75
0.44
24/8/
94
Масло
7420
15
"Полес
ье"
Киев
Украин
а
50
1.62
27/8/
94
Зелень
180
15
"Натал
ка"
Киев
Украин
а
10
0.88
27/8/
94

24.

1НФ
Блюдо
Вид
Рецепт
Порци
й
Дата_
Реализ
ации
Продук
т
Калори
йность
Вес (г)
Постав
щик
Город
Страна
Вес
(кг)
Цена
($)
Дата П
Лобио
Закуск
а
Лом.
158
1/9/9
4
Фасоль
3070
200
"Хуанхэ
"
Пекин
Китай
250
0.37
24/8/
94
Лобио
Закуск
а
Лом
108
1/9/9
4
Лук
450
40
"Натал
ка"
Киев
Украин
а
100
0.52
27/8/
94
Лобио
Закуск
а
Лом
108
1/9/9
4
Масло
7420
30
"Лайма
"
Рига
Латвия
70
1.55
30/8/
94
Лобио
Закуск
а
Лом
108
1/9/9
4
Зелень
180
10
"Дауга
ва"
Рига
Латвия
15
0.99
30/8/
94
Харчо
Суп
Лом
144
1/9/9
4
Мясо
1660
80
"Натал
ка"
Киев
Украин
а
100
2.18
27/8/
94
Харчо
Суп
Лом
144
1/9/9
4
Лук
450
30
"Натал
ка"
Киев
Украин
а
100
0.52
27/8/
94
Харчо
Суп
Лом
144
1/9/9
4
Томат
ы
240
40
"Полес
ье"
Киев
Украин
а
120
0.45
27/8/
94
Харчо
Суп
Лом
144
1/9/9
4
Рис
3340
50
"Хуанхэ
"
Пекин
Китай
75
0.44
24/8/
94
Харчо
Суп
Лом
144
1/9/9
4
Масло
7420
15
"Полес
ье"
Киев
Украин
а
50
1.62
27/8/
94
Харчо
Суп
Лом
144
1/9/9
4
Зелень
180
15
"Натал
ка"
Киев
Украин
а
10
0.88
27/8/
94

25.

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

26.

2НФ
Для улучшения проекта, нужно определить первичные ключи таблиц и выявить,
нет ли в таблицах полей, зависящих лишь от части этих ключей. Такое поле есть
только в одной таблице. Это поле Страна в таблице Поставщики. Выделяя его
вместе с ключем Город в таблицу Страны, получим проект:

27.

28.

Задание 1
■ Дано: Сотрудники (№ табельный, Фамилия Имя Отчество, Код
должности, Телефоны, Дата приема или увольнения);
■ Привести к 1НФ.
■ Ошибки:
1) атрибут «Фамилия Имя Отчество» является составным, т. е.
составленным из разнородных
элементов;
2) атрибут «Телефоны» является многозначным, т. е. его значением
является множество значений;
3) атрибут «Дата приема или увольнения» не имеет однозначной
семантики, т. е. в последнем случае не понятно, какая именно дата
внесена.

29.

Задание 2
■ Дано: Аудитории (№ корпуса, № аудитории, Площадь аудитории,
№ табельный коменданта корпуса);
Ключевые поля: № корпуса, № аудитории
■ Привести к 2НФ
■ Ошибка: Атрибут «№ табельный коменданта корпуса»
функционально зависит не от составного ключа «№ корпуса, №
аудитории», а от части этого ключа, т. е. от атрибута «№ корпуса».

30.

Задание 3
■ Дано: Сотрудники (№ табельный, Фамилия, Имя, Отчество, Код
должности, Оклад);
■ Привести к 3НФ
■ Ошибка: неключевой атрибут «Оклад» полностью функционально
зависит от атрибута «Код должности», хотя этот атрибут и не
является ключевым.

31.

Задание 4. Привести таблицу к 3НФ
ФИО
Должн
Иванов И.М. Преп
Оклад
500
Стаж
5
Д_Стаж Каф Предм
100
25 СУБД
Группа
256
ВидЗан
Практ
Иванов И.М. Преп
500
5
100
25
С#
123
Практ
Петров М.И.
Ст.пре
п
Петров М.И. Ст.пре
п
Сидоров Н.Г. Преп
800
7
100
25
СУБД
256
Лекция
800
7
100
25
C#
256
Практ
500
10
150
25
123
Лекция
Сидоров Н.Г. Преп
500
10
150
25
Основ
ы БД
C#
256
Лекция
Егоров В.В.
500
5
100
24
ПЭВМ
244
Лекция
Преп