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

Проектирование базы данных выполняется на физическом и логическом уровнях. Решение возникающих проблем на физическом уровне выполняется автоматизировано конкретной СУБД. Логическое проектирование заключается в определении числа и структуры таблиц, формировании запросов к БД, определении отчетных документов, разработке алгоритмов обработки информации, создании форм для ввода и редактирования данных в базе и решении других задач. При определении структур данных в отношениях реляционной модели имеют место следующие проблемы: избыточное дублирование данных и аномалии.

Неизбыточное дублирование данных рассмотрим на следующем примере: Отношение Сотрудник-Телефон с атрибутами Сотрудник и Телефон.

Сотрудник-Телефон
Сотрудник	Телефон
Иванов И.И.	8-903
Петров П.П.	8-902
Сидоров С.С.	8-903
Семенов С.П.	8-903

Избыточное дублирование представлено примером отношения Сотрудник-Телефон-Комната

Сотрудник-Телефон-Комната
Сотрудник	Телефон	Комната
Иванов И.И.	8-903	104
Петров П.П.	8-902	110
Сидоров С.С.	8-903	104
Семенов С.П.	8-903	104

Возможный способ выхода из данной ситуации – это выполнение декомпозиции исходного отношения на два отношения.

Телефон-Комната
Телефон	Комната
8-903	104
8-902	110

Сотрудник-Комната
Сотрудник	Комната
Иванов И.И.	104
Петров П.П.	110
Сидоров С.С.	104
Семенов С.П.	104

Простое (неизбыточное) дублирование допускается в БД. Избыточное дублирование данных может приводить к проблемам при обработке данных.

Аномалия – это такая ситуация в таблицах БД, которая приводит к противоречиям в БД. Виды аномалий:

1. аномалии модификации (редактирования),

2. аномалии удаления,

3. аномалии добавления.

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

Аномалии удаления – при удалении какого-либо данного из таблицы может пропасть и другая информация, которая не связана напрямую с удаляемым данным. Например, при удалении информации о сотруднике Петрове в отношении Сотрудник-Телефон-Комната будет потеряна информация о номере телефона в комнате 110.

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

Проектирование БД начинается с определения всех объектов, сведения о которых будут включены в базу, и определения их атрибутов. Затем атрибуты записываются в виде одной таблицы - исходное отношение.

Пример. Сформировать исходное отношение для БД «Преподаватель_колледжа». Для этого определить какая информация должна храниться в БД, и какую необходимо получать в процессе работы с БД. Получим атрибуты:

ФИО – фамилия и инициалы преподавателя (нет совпадающих).

Должн – должность, занимаемая преподавателем,

Оклад – оклад преподавателя,

Стаж – преподавательский стаж,

Н_Стаж – надбавка за стаж,

Отдел – отдел, в котором числится преподаватель,

Предм – название предмета, читаемого преподавателем,

Группа – номер группы, в которой преподаватель проводит занятия,

ВидЗан- вид занятий, проводимых преподавателем.

Все атрибуты отношения должны иметь простые (атомарные) значения.

ФИО	Долж	Оклад	Стаж	Н_Стаж	Отдел	Предм	Группа	ВидЗан
Иванов	Преп	1000	3	200	3	БД	П-249	Практ
Иванов	Преп	1000	3	200	3	ОС	П-250	Практ
Петров	Преп	1000	9	400	3	ИС	П-249	Практ
Петров	Преп	1000	9	400	3	ЧМ	П-250	Практ
Седов	Ст.пр.	1500	10	500	4	ЧМ	П-250	Лекция

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

Исключение избыточности, а значит аномалий, выполняется путем нормализации отношений.

Зависимости между атрибутами.

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

Функциональная взаимозависимость между А и В(АВ) существует, если существует функциональная зависимость вида АВ и ВА. Например, номер паспорта и ФИО.

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

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

Атрибут С транзитивно зависит от атрибута А, если для атрибутов А, В, С выполняются условия АВ и ВС, но обратная зависимость отсутствует. Например, ФИОДолжОклад.

Взаимно независимые атрибуты – это такие атрибуты, ни один из которых не является функционально зависимым от других атрибутов.

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

• первая нормальная форма (1НФ);

• вторая нормальная форма (2НФ);

• третья нормальная форма (3НФ);

• усиленная третья нормальная форма Бойса-Кодда (БКНФ);

• четвертая нормальная форма (4НФ);

• пятая нормальная форма (5НФ).

Первая нормальная форма. Отношение находится в первой нормальной форме, если все его атрибуты являются простыми (имеют единственное значение). Исходное отношение строится таким образом, чтобы оно находилось в 1НФ. Например, исходное отношение БД «Преподаватель_колледжа».

ФИО	Долж	Оклад	Стаж	Н_Стаж	Отдел	Предм	Группа	ВидЗан
Иванов	Преп	1000	3	200	3	БД	П-249	Практ
Иванов	Преп	1000	3	200	3	ОС	П-250	Практ
Петров	Преп	1000	7	400	3	ИС	П-249	Практ
Петров	Преп	1000	7	400	3	ЧМ	П-250	Практ
Седов	Ст.пр.	1500	10	500	4	ЧМ	П-250	Лекция

Исходное отношение имеет составной ключ ФИО, Предмет, Группа. В этом отношении имеется явное и неявное избыточное дублирование данных. Часть избыточности устраняется при переводе отношения в 2НФ.

Вторая нормальная форма. Отношение находится в 2НФ, если:

1. отношение находится в 1НФ,

2. каждый не ключевой атрибут функционально полно зависит от первичного ключа (составного).

Или 2) любое неключевое поле должно однозначно идентифицироваться ключевыми полями.

Для перевода отношения в 2НФ используется операция проекции, то есть разложения отношения на несколько отношений. Для БД «Преподаватель_колледжа» получим два отношения:

R1:

ключ составной (ФИО,Предмет,Группа)

ФИО	Предм	Группа	ВидЗан
Иванов	БД	П-249	Практ
Иванов	ОС	П-250	Практ
Петров	ИС	П-249	Практ
Петров	ЧМ	П-250	Практ
Седов	ЧМ	П-250	Лекция

R2:

ключ (ФИО)

ФИО	Долж	Оклад	Стаж	Н_Стаж	Отдел
Иванов	Преп	1000	3	200	3
Петров	Преп	1000	7	400	3
Седов	Ст.пр.	1500	10	500	4

Исключена явная избыточность данных (повторение сведений о преподавателе), но осталась неявное дублирование (повторение оклада для должности, повторение сведений о надбавке за стаж).

Третья нормальная форма. Отношение находится в 3НФ, если:

1. отношение находится в 2НФ,

2. каждый неключевой атрибут нетранзитивно зависит от первичного ключа.

Или

2. ни одно из неключевых полей не должно однозначно идентифицироваться значением другого неключевого поля (полей).

Например, в БД «Преподаватель_колледжа» в R1 отсутствуют транзитивные зависимости, но в R2 они имеются:

ФИОДолжнОклад, ФИО Оклад  Должн, ФИОСтажН_Стаж.

Транзитивные зависимости приводят к избыточному дублированию информации в отношении, которое можно устранить операцией проекции на атрибуты, которые являются причиной транзитивных зависимостей. Преобразуем отношение R2 в R3, R4, R5, каждое из которых находится в 3НФ:

R3

ФИО	Долж	Стаж	Отдел
Иванов	Преп	3	3
Петров	Преп	7	3
Седов	Ст.пр.	10	4

R4

Долж	Оклад
Преп	1000
Ст.пр.	1500

R5

Стаж	Н_Стаж
3	200
7	400
10	500

Избыточное дублирование устранено. Если имеется зависимость атрибутов составного ключа от неключевых атрибутов, то нужно перейти к усиленной 3НФ.

Усиленная третья нормальная форма Бойса-Кодда. Отношение находится в БКНФ, если

• оно находится в 3НФ,

• отсутствуют зависимости ключей (атрибутов составного ключа) от неключевых атрибутов.

В примере БД «Преподаватель_колледжа» таких зависимостей нет, и процесс проектирования заканчивается. Таким образом, БД «Преподаватель_колледжа» состоит из таблиц R1, R3, R4, R5. В полученной БД имеется необходимое дублирование данных, но отсутствует избыточное.

R1:

ФИО	Предм	Группа	ВидЗан
Иванов	БД	П-249	Практ
Иванов	ОС	П-250	Практ
Петров	ИС	П-249	Практ
Петров	ЧМ	П-250	Практ
Седов	ЧМ	П-250	Лекция

R3

ФИО	Долж	Стаж	Отдел
Иванов	Преп	3	3
Петров	Преп	7	3
Седов	Ст.пр.	10	4

R4

Долж	Оклад
Преп	1000
Ст.пр.	1500

R5

Стаж	Н_Стаж
3	200
7	400
10	500

На практике построение 3НФ схем отношений в большинстве случаев является достаточным, и на этом процесс проектирования заканчивается.

Пример 1. Сформировать отношения, находящиеся в 3НФ для концептуальных требований БД «Библиотека»:

Книга (название, автор, издательство, дата издания, залоговая цена, наличие в библиотеке)

Библиотекарь (ФИО, дата приема на работу, образование)

Читатель (ФИО, паспорт, телефон, дом.адрес)

Дата выдачи и возврата книги, кем выдана.



План проектирования БД:

Формирование исходного отношения, Нормализация отношений.



Исходное отношение: (1НФ)

Название книги

Автор книги

Издательство

Телефон издательства

Характеристика издательства

Дата издания

Залоговая цена

Наличие в библиотеке

ФИО читателя

Паспорт читателя

Телефон читателя

Дом. адрес читателя

Дата выдачи книги

Дата возврата книги

ФИО библиотекаря

Дата приема на работу

Образование библиотекаря