2 Нормализация таблиц в реляционной модели бд

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

Аномалии модификации данных

В качестве примера возьмём отношение со следующими атрибутами (ключевые атрибуты выделены подчёркиванием):

ПОСТАВКИ (Номер поставки, Название товара, Цена товара, Количество, Дата поставки, Название поставщика, Адрес поставщика)

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

1. Аномалия обновления: в отношении ПОСТАВКИ она может возникнуть, если у какого-либо поставщика изменился адрес. Изменения должны быть внесены во все кортежи, соответствующие поставкам этого поставщика; в противном случае данные будут противоречивы.

2. Аномалия удаления: при удалении записей обо всех поставках одного поставщика все данные о поставщике будут утеряны.

3. Аномалия добавления: в нашем примере она возникнет, если с поставщиком заключен договор, но поставок от него еще не было. Информация о таком поставщике не может быть внесена в отношение ПОСТАВКИ, т.к. для него не определён ключ (номер поставки и название товара) и другие обязательные поля.

Для решения проблемы аномалии модификации данных при проектировании РБД проводится нормализация отношений.

Нормализация отношений

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

Нормализация – процесс проверки и реорганизации сущностей и атрибутов с целью удовлетворения требований к реляционной модели данных. ( требования см в вопросе .Реляционная модель организации данных. Ее достоинства и недостатки )

В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм:

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

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

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

• нормальная форма Бойса-Кодда (BCNF);

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

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

Основные свойства нормальных форм:

• каждая следующая нормальная форма в некотором смысле лучше предыдущей;

• при переходе к следующей нормальной форме свойства предыдущих нормальных свойств сохраняются.

Результат нормализации – информация о каждом факте хранится в одном месте.

Процесс нормализации - последовательное приведение структуры данных к нормальным формам.

Нормальная форма – формализованные требования к организации данных.

Нормальные формы основаны на понятии функциональной зависимости атрибутов отношения

Определение 1. Функциональная зависимость

В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X ->R.Y.

Определение 2. Полная функциональная зависимость

Функциональная зависимость R.X ->R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X.

Определение 3. Транзитивная функциональная зависимость

Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z -/-> R.X.

.

Требования 1НФ- все атрибуты сущности содержат атомарные значения и среди атрибутов нет повторяющихся групп.

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

Алгоритм приведения отношения к первой нормальной форме.

1. разделить сложное отношение;

2. перенести в нее все «повторяющиеся» атрибуты;

3. выбрать ключевой атрибут (первичный ключ) для нового отношения;

4. установить связь от прежнего отношения к новому. Первичный ключ прежнего отношения мигрирует в новое отношение и станет его вторичным ключем.

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

Алгоритм приведения сущности ко второй нормальной форме.

1 выделить атрибуты, которые зависят от части первичного ключа;

2 создать новую сущность;

3 поместить атрибуты, зависящие от части первичного ключа в их собственную новую сущность;

4 установить связь от прежней сущности к новой. Часть первичного ключа прежней сущности мигрирует в новую сущность и станет ее первичным ключем.

2НФ позволяет избежать следующих аномалий при выполнении операций:

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

- Вставки. Невозможно ввести данные о сотруднике, если он не участвует в проекте.

- Удаления. Если сотрудник прекращает работать над проектом, данные о нем теряются.

Требования 3НФ – Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 2NF, и каждый не ключевой атрибут не является транзитивно зависимым от какого-либо ключа R.

Отношение находится в третьей нормальной форме и никакой не ключевой атрибут не зависит от другого не ключевого атрибута.

Алгоритм приведения отношения к третьей нормальной форме

1 создать новое отношение и перенести в нее атрибуты с одной и той же зависимостью от не ключевого атрибута;

2 использовать атрибут(ы), определяющий эту зависимость, в качестве первичного ключа нового отношения;

3 установить связь от нового отношения к старому.

3НФ позволяет избежать следующих аномалий при выполнении операций:

- Обновления. Имеет место дублирование данных об окладе, если должность занимают несколько сотрудников.

- Вставки. Невозможно ввести данные об окладе, если нет сотрудника, занимающего эту должность.

- Удаления. В случае удаления из таблицы сотрудника, занимающего уникальную должность данные об окладе теряются

Введём понятие многозначной зависимости. Многозначная зависимость существует, если заданным значениям атрибута X соответствует множество, состоящее из нуля (или более) значений атрибута Y (X–»Y). Если в отношении присутствуют многозначные зависимости, то схема отношения должна находиться в 4НФ.

Различают тривиальные и нетривиальные многозначные зависимости. Тривиальной называется такая многозначная зависимость X–»Y, для которой Y Ì  X или X  U Y = R, где R – рассматриваемое отношение. Тривиальная многозначная зависимость не нарушает 4НФ. Если хотя бы одно из двух этих условий не выполняется (т.е. Y не является подмножеством X или X   U  Y состоит не из всех атрибутов R), то такая многозначная зависимость называется нетривиальной.

Четвертая нормальная форма (4НФ).

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