21. Проектирование БД методом нормальных форм. Аномалии обработки данных. Функциональные зависимости. Нормальные формы.

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

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

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

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

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

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

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

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

Каждой нормальной форме соответствует некоторый набор ограничений. Отношение находится в определенной нормальной форме, если оно удовлетворяет набору ограничений этой формы. Основные свойства нормальных форм: - каждая следующая нормальная форма в некотором смысле лучше предыдущей; - при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются.

В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм: - первая нормальная форма (1NF); - вторая нормальная форма (2NF); - третья нормальная форма (3NF); - нормальная форма Бойса-Кодда (BCNF); - четвертая нормальная форма (4NF); - пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF).

Процесс нормализации основан на понятии функциональной зависимости. Функциональные зависимости позволяют накладывать определенные ограничения на реляционную схему. Идея состоит в том, что значение одного атрибута в кортеже определяет значение другого атрибута. Например, в каждом кортеже отношения R Код_материала определяет Наименование; Код_материала определяет Тип (табл. 10). Можно записать функциональные зависимости: Код_материала ->Наименование Код_материала ->Тип Для дальнейшего изложения нам потребуется несколько определений.

Определение 1. Функциональная зависимость Пусть A и B – атрибуты в отношении R. Атрибут В функционально зависит от атрибута А, если в любой момент времени каждому значению атрибута А соответствует в точности одно значение атрибута В. Функциональная зависимость записывается следующим образом: A->B. Данная запись означает, что если два кортежа в таблице R имеют одно и тоже значение атрибута A, то они имеют одно и тоже значение атрибута B. Атрибут в левой части называется детерминантом, т.к. его значение определяет значение атрибута в правой части. Ключи таблицы являются детерминантами.

Определение 6. Отношение находится в первой нормальной форме (1NF), если значения его атрибутов атомарны. Исходное отношение строится таким образом, чтобы оно было в 1NF.

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

Определение 8. Отношение находится в третьей нормальной форме (3NF), если выполняются ограничения второй нормальной формы и все неключевые атрибуты взаимно независимы и полностью зависят от первичного ключа (т. е. в отношении отсутствуют транзитивные зависимости неключевых атрибутов от первичного ключа).

В качестве первичного ключа выберем составной ключ Код_заказа-Код_материала, однозначно определяющий каждый кортеж отношения. Данное отношение находится в 1NF, т. к. все значения столбцов являются атомарными. Эта таблица содержит избыточные данные. например, одни и те же сведения о клиенте повторяются в записи о каждом заказанном материале. Результатом избыточности являются следующие аномалии: - адрес клиента можно ввести в базу данных тогда, когда он заказал хотя бы одну ткань; - при удалении записи о заказанной ткани одновременно удаляются сведения о заказе и клиенте, его разместившем; - при смене адреса клиента, необходимо обновлять все записи и заказанных им материалах.

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

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

Для приведения исходной таблицы ко второй нормальной форме поля Код_заказа, Код_клиента, Город_клиента, Дата_поставки перемещаются в новую таблицу Поставки1, при этом Код_заказа – первичный ключ новой таблицы.

Вторая таблица Заказы будет содержать составной первичный ключ Код_заказа-Код_материала и поле Количество.

Проанализируем полученные таблицы. В таблице Заказы не наблюдается явная избыточность данных. Однако для таблицы Поставки1 можно указать следующие аномалии: - адрес конкретного клиента может содержаться в базе только тогда, когда есть заказы; - удаление сведений о заказе в таблице Поставки1 приведет к удалению сведений о клиентах; - при изменении адреса заказчика придется обновить все кортежи в таблице Поставки1.

Устранить эти аномалии позволяет третья нормальная форма. Считается, что таблица соответствует третьей нормальной форме, если она находится во второй нормальной форме и ее неключевые атрибуты взаимно независимы и зависят только от первичного ключа. В отношении Поставки1 существует транзитивная зависимость между неключевыми атрибутами Город_клиента и Код_клиента (Код_клиента -> Город_клиента).

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

Для приведения таблицы Поставки1 к третьей нормальной форме создадим новую таблицу Клиенты (табл. 14) и переместим в нее атрибуты Код_клиента и Город_клиента. Атрибут Город_клиента из таблицы Поставки1 удалим, а атрибут Код_клиента оставим в качестве внешнего ключа (табл. 16). Таблицу Заказы оставим без изменения (табл. 15).

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