Логическое проектирование и нормализация БД.

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

При проектировании можно выделить последовательность процедур:

1) определение перечня таблиц и связей;

2) определение перечня полей, типов полей, ключевых полей каждой таблицы, установление связей между таблицами через внешние ключи;

3) определение или установление индексов для полей в таблицах;

4) разработка списков (словарей) для полей с перечислительным характером значений данных;

5) установление ограничений целостности по полям таблиц и связей;

6) нормализация таблиц, доработка перечня таблиц и их связей.

Технологически процесс проектирования разделяют на предварительное проектирование таблиц и связей между ними (п.п. 1—5) и последующую нормализацию таблиц — п. 6.

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

С формальной точки зрения нормализацию можно представить как последовательный процесс разбиения и преобразования некоторого набора таблиц для построения связанных таблиц в нормальных формах. Основатель реляционной модели Е. Кодд выделял три нормальные формы — первую, вторую и третью. Этот набор был дополнен нормальной формой Бойса — Кодда и далее — четвертой и пятой нормальными формами. Наиболее простой нормальной формой является первая, суть которой определяется требованием неделимости полей и единственности значений по полям. В табл. 3. приведен пример ненормализованной структуры данных «Оплата коммунальных услуг» (см. концептуальную схему рис. 13), имеющей составное (делимое) поле «Оплата коммунальных услуг» с множественными значениями по полям «Вид услуги», «Оплата», «Тариф».

Таблица. 3. Ненормализованная структура данных «Оплата коммунальных услуг».

№	Адрес	Ф.И.О.	Категория льготы	Оплата коммунальных услуг
Вид услуги	Оплата	Тариф
	Ул. Центральная 2 Кв.1	Попов В. Е.	Нет	Отопление	100 руб.	1,54
Водоотведение	50 руб.	1,03
	Ул. Центральная 2 Кв. 2	Карасева Т. С.	Ветеран войны	Водоотведение	70 руб.	1,03
	Ул. Центральная 3 Кв. 1	Иванов В. Е.	Инвалид	Отопление	80 руб.	0,9
Водоотведение	70 руб.	0,7

Продолжение таблицы 3.

№	Площадь	Кол-во человек	Дата оплаты
			11.01.09
			11.01.09
			11.01.09

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

№	Адрес	Вид услуги	Оплата	Тариф	Ф.И.О.	Категория льготы
	Ул. Центральная 2 Кв.1	Отопление	100 руб.	1,54	Попов В. Е.	Нет
	Ул. Центральная 2 Кв.1	Водоотведение	50 руб.	1,03	Попов В. Е.	Нет
	Ул. Центральная 2 Кв. 2	Водоотведение	70 руб.	1,03	Карасева Т. С.	Ветеран войны
	Ул. Центральная 3 Кв. 1	Отопление	80 руб.	0,9	Иванов В. Е.	Инвалид
	Ул. Центральная 3 Кв. 1	Водоотведение	70 руб.	0,7	Иванов В. Е.	Инвалид

Продолжение таблицы

№	Площадь	Кол-во человек	Дата оплаты
			11.01.09
			11.01.09
			11.01.09
			11.01.09
			11.01.09

Рис. 14. Таблица в первой нормальной форме.

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

Поле-атрибут Y функционально зависит от поля-атрибута Х, если любому значению Х всегда соответствует только одно значение Y. К примеру, значение поля «Ф.И.О. (квартиросъемщика» всегда соответствует одному значению «Адрес». В таблице, находящейся в первой нормальной форме, все неключевые атрибуты функционально зависят от ключа таблицы.

Вторая нормальная форма основывается на понятии полной функциональной зависимости. Функциональная зависимость неключевого поля от составного ключа таблицы называется полной, если он функционально зависит в целом от составного ключа, но не зависит отдельно от любой его части. В примере значение поля «Ф. И. О» определяется только значением поля «Адрес» которое является частью составного ключа. Следовательно, полной функциональной зависимости нет. В полной функциональной зависимости от составного ключа находится поле «Оплачено», так как только комбинация значений «Адрес» и «Вид оплаты» определяет конкретное значение поля «Оплачено».

Для перевода таблицы из первой нормальной формы во вторую необходимо:

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

· построить еще одну или несколько проекций на часть составного ключа с полями, функционально зависимыми только от этой части ключа.

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

Проекция на составной ключ с полями, находящимися в полной функциональной зависимости от него (табл.4):

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

Адрес	Вид услуги	Оплата	Тариф	Дата оплаты
Ул. Центральная 2 Кв.1	Отопление	100 руб.	1,54	11.01.09
Ул. Центральная 2 Кв.1	Водоотведение	50 руб.	1,03	11.01.09
Ул. Центральная 2 Кв. 2	Водоотведение	70 руб.	1,03	11.01.09
Ул. Центральная 3 Кв. 1	Отопление	80 руб.	0,9	11.01.09
Ул. Центральная 3 Кв. 1	Водоотведение	70 руб.	0,7	11.01.09

Проекция на поле ключа с полями, находящимися с данным полем в функциональной зависимости (табл.5):

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

Адрес	Ф.И.О.	Категория льготы	Площадь	Кол-во человек
Ул. Центральная 2 Кв.1	Попов В. Е.	Нет
Ул. Центральная 2 Кв. 2	Карасева Т. С.	Ветеран войны
Ул. Центральная 3 Кв. 1	Иванов В. Е.	Инвалид

В таблицах, находящихся во второй нормальной форме, большинство аномалий, присущих первой форме, устранено. Однако по определенным атрибутам многочисленные ситуации дублирования данных могут сохраниться. Например, для приведенной ниже таблицы 6 «Информация о сотрудниках ЖКХ», находящейся во второй нормальной форме, происходит дублирование информации о телефоне 529505 т.к. атрибут «Телефон» фактически зависит не от атрибута «Ф.И.О.», а от атрибута «Кабинет». Иначе говоря, наблюдается цепочка функциональной зависимости атрибутов «Ф.И.О.», - «Кабинет» - «Телефон», а функциональная зависимость атрибута «Телефон» от атрибута «Ф.И.О.» является лишь логическим следствием такой цепочки зависимостей. В таких ситуациях говорят о транзитивной зависи мости атрибута «Телефон» от атрибута «Ф.И.О.».

Таблица 6. «Информация о сотрудниках ЖКХ»

Ф.И.О.	Должность	Кабинет	Телефон
Попова Т. Е.	Бухгалтер
Карасева Т. А.	Расчетчик
Иванова С. Е.	Расчетчик

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

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

Декомпозиция таблицы в третью нормальную форму путем разделения цепочки транзитивной зависимости «Ф.И.О» — «Кабинет» — «Телефон» представлена на рисунке 15. Внутреннее в этой цепочке поле «Кабинет» стало внешним ключом в первой таблице и первичным ключом во второй.

Ф.И.О	Должность	Кабинет
Попова Т. Е.	Бухгалтер
Карасева Т. А.	Расчетчик
Иванова С. Е.	Расчетчик

Кабинет	Телефон

Рис. 15. Таблица в третьей нормальной форме

На практике третья нормальная форма устраняет большинство аномалий схем таблиц-отношений, а также ситуации дублирования данных, и после декомпозиции исходных таблиц-отношений до третьей нормальной формы процесс нормализации заканчивается. Вместе с тем в некоторых случаях третью нормальную форму можно также «улучшить», в частности приведением таблицы-отношения в нормальную форму Бойса — Кодда.

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

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

Для приведения таблицы в нормальную форму Бойса — Кодда необходимо произвести декомпозицию так, чтобы исключить случаи пересечения по некоторым полям-атрибутам имеющихся детерминантов.

Встречаются также случаи, требующие «улучшения» и нормальной формы Бойса — Кодда. Такие ситуации называют многозначной зависимостью атрибутов. Их устраняет четвертая нормальная форма. Таблица-отношение находится в четвертой нормальной форме тогда и только тогда, когда в случае существования многозначной зависимости атрибута Y от атрибута X все остальные атрибуты зависят только от атрибута X.

Наиболее сложной при нормализации является пятая нормальная форма, связанная с наличием в таблице-отношении зависимостей соединения. Из-за нетривиальности зависимости соединения пятая нормальная форма практически не используется.

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

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

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