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

 

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

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

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.

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

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

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

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