Нормализация отношений базы данных

Функциональные зависимости

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

Функциональная зависимость описывает связь между атрибутами отношения. Например, если в отношении R, содержащем атрибуты A и В, атрибут В функционально зависит от атрибута А (обозначается А®В), то каждое значение атрибута А связано только с одним значением атрибута В.

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

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

Пусть имеется отношение R(НомерСотрудника, Должность). Зная значение атрибута НомерСотрудника, можно определить должность, занимаемую этим сотрудником. Иначе говоря, атрибут Должность функционально зависит от атрибута НомерСотрудника (НомерСотрудника® Должность).

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

Одновременное соблюдение двух функциональных зависимостей А®В и В®А говорит о существовании взаимно-однозначной функциональной зависимости. Например, пусть имеется отношение Предприятие (НазваниеПредприятия, РасчетныйСчетПредприятия). Если ввести ограничения, что каждое предприятие имеет только один уникальный расчетный счет, названия предприятий не повторяются, мы имеем следующие функциональные зависимости:

F_{Предприятие}= {НаименованиеПредприятия®РасчетныйСчетПредприятия;

РасчетныйСчетПредприятия®НаименованиеПредприятия}.

Еще одним случаем является отсутствие функциональной зависимости. Например, отношение СпортивныеСекции (Секция, Плата):

F_{СпортивныеСекции}= {Секция Плата;

Плата Секция}.

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

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

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

F_{СдачаПроекта}= {ФИОСотрудника, НазваниеПроекта®ДатаСдачи;

ФИОСотрудника, НазваниеПроекта®СтоимостьПроекта;

ФИОСотрудника, ДатаСдачи®НазваниеПроекта;

ФИОСотрудника, ДатаСдачи®СтоимостьПроекта}.

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

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

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

Для определения ключевых элементов произвольного отношения существует следующая методика, состоящая из 3х этапов:

1) для отношения R выписываются ограничения в словесной форме, которые влияют на наличие или отсутствие функциональных зависимостей;

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

3) объединение атрибутов, стоящих слева и будет давать вероятный ключ.

 

Нормальные формы

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

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

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

Общее понятие нормализации подразделяется на несколько нормальных форм.

Рассмотрим пример ненормализованной (табл. 1) и нормализованной таблиц.

Таблица 1

Пример ненормализованной таблицы

КодПоставщика	КодПродукта	Продукт
		Сахар Соль Мука
		Кукуруза Соль Пшено
		Кефир Молоко

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

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

Любая таблица, удовлетворяющая определению отношения, находится в первой нормальной форме (1НФ). Основные характеристики таблицы в 1НФ:

· В каждой строке таблицы должны содержаться данные, соответствующие некоторому объекту или его части;

· В каждом столбце должны находиться данные, соответствующие одному из атрибутов отношения;

· В каждой ячейке таблицы должно находиться только единственное значение;

· У каждого столбца должно быть уникальное имя;

· Все строки (записи) в таблице должны быть различными;

· Порядок расположения столбцов и строк в таблице не имеет значение.

Таблица в 1НФ приведена в табл. 2.

Таблица 2

Пример таблицы в 1НФ

КодПоставщика	КодПродукта	Продукт
		Сахар
		Соль
		Мука
		Кукуруза
		Соль
		Пшено
		Кефир

 

Вторая нормальная форма

Каждая таблица в 1НФ должна иметь первичный ключ. Он может состоять из одного или более атрибутов. В последнем случае ключ называется составным. Чтобы таблица была во 2НФ, все ее неключевые столбцы должны однозначно определяться всем ключом, т.е. всеми его компонентами, а не некоторыми из них.

Рассмотрим пример отношения Секции (Фамилия, Секция, Плата), показанный в табл. 3.

Таблица 3

Отношение Секции (Фамилия, Секция, Плата)

Фамилия	Секция	Плата
Дмитриев	Футбол
Ткачева	Волейбол
Колесников	Шахматы
Ларина	Теннис
Кислов	Футбол

Ключом в данном отношении является {Фамилия, Секция}, но он содержит функциональную зависимость Секция ® Плата. Левая часть этой зависимости является лишь частью составного ключа. Отношение Секции имеет аномалии удаления и добавления. Так, если мы захотим удалить запись с фамилией «Колесников», то потеряем стоимость шахматной секции. Мы не можем добавить запись о новой секции, пока в нее кто-то не запишется.Данных аномалий можно было бы избежать, если бы атрибут Плата зависел от всего ключа (однозначно определялся всем ключом).

Отношение Секции (Фамилия, Секция, Плата) в 1НФ можно разбить на два отношения во 2НФ:

§ Секция_Спортсмен (Фамилия, Секция);

§ Секция_Плата (Секция, Плата).

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

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

Рассмотрим в качестве примера отношение Проживание_Студентов (НомерСтудента, Общежитие, Плата), представляющее сведения о студентах, проживающих в общежитиях (табл. 4).

Таблица 4

Пример отношения с транзитивной зависимостью

НомерСтудента	Общежитие	Плата
	№ 2
	№6
	№1
	№2
	№1

Ключом в этом отношении является НомерСтудента. Плата однозначно определяется атрибутом Общежитие, т.е. имеется функциональная зависимость Общежитие ® Плата. Поскольку один студент проживает только в одном общежитии, и каждое общежитие взимает со всех проживающих в нем студентов одинаковую плату, в отношении есть и функциональная зависимость НомерСтудента® Общежитие. Таким образом, возникает транзитивная зависимость НомерСтудента® Плата.

Ключом отношения Проживание_Студентов является НомерСтудента, который представляет собой одиночный атрибут, и, следовательно, отношение находится во 2НФ. Несмотря на это, в рассматриваемом отношении существует аномалия удаления. Удалив вторую запись, мы потеряем не только тот факт, что студент №536 проживает в общежитии №6, но и тот факт, сколько стоит проживание в этом общежитии.

Чтобы устранить указанную аномалию, следует декомпозировать исходное отношение Проживание_Студентов (НомерСтудента, Общежитие, Плата) на два (рис. 16):

§ Проживание (НомерСтудента, Общежитие);

§ Стоимость (Общежитие, Плата).

НомерСтудента	Общежитие		Общежитие	Плата
	№ 2		№ 2
	№6		№6
	№1		№1
	№2		№2
	№1		№1

Рис. 16. Отношения, не имеющие транзитивных зависимостей

Отношение находится в 3НФ, если оно находится во 2НФ и в нем отсутствует транзитивная зависимость непервичного атрибута от ключа.