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

Реляционная модель – набор взаимосвязанных отношений.

Формульная запись отношения: , часть отношения может быть помечена как первичный ключ .

Основные требования к отношениям:

─ атомарность к атрибутам

─ уникальность кортежей

Пример: Расписание приема врачей поликлиники.

Включает данные:

─ врач (ФИО, специалист)

─ прием (день, время, кабинет)

Отношение Расп(врач(ФИО, специалист), прием(день, время, кабинет)) – недопустимо.

Правильная запись таблицы:

Прием(ФИО, специалист, день, время, кабинет). При этом имеем составной ключ: ФИО, день.

Для повышения уникальности введем уникальный номер:

Прием(табN, день, ФИО, специальность, время, кабинет)

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

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

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

Повторяемость сочетаний, в свою очередь, ведет к аномальностям обновления:

1)Аномальность изменения: приводит к необходимости множественного изменения.

2) Аномалия удаления: возможность потери информации при удалении сопутствующих данных.

3) Аномалия добавления: например, нельзя ввести данные по врачу, не ведущему прием.

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

Рассмотрим отношение График.

Атрибуты РЕЙС ДАТА являются ключом отношения График, и это отношение должно также удовлетворять функциональной зависимости РЕЙС→ГАЛЕРЕЯ. Пусть требуется обновить отношение, указав значение ключа и задавая значения всем остальным атрибутам. Однако если выполнить операцию

ИЗМЕНИТЬ (График; 112, 6 июня, ПИЛОТ=Иванов, ГАЛЕРЕЯ=8),

то отношение перестанет удовлетворять функциональной зависимости РЕЙС→ГАЛЕРЕЯ. Чтобы избежать нарушения функциональной зависимости, необходимо после каждого выполнения операции обновления просмотреть полученное отношение и везде (во всех кортежах), где появляется указанный в операторе номер рейса, изменить номер галереи на указанный в операторе. А требовалось всего лишь изменить один кортеж. Кроме того, информация о связи между номером рейса и номером галереи дублируется с рассмотренном отношении, что ведет к избыточности информации.

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

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

Для устранения избыточности выполняется нормализация.

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

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

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

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

Определены 6 нормальных форм:

1НФ, 2НФ, 3НФ, НФБК (Б – Бойс, К – Кодд), 4НФ, 5НФ.

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

Каждая следующая нормальная форма является в некотором смысле более ограниченной, но и более желательной, чем предшествующая. Это связано с тем, что (N + 1)-я нормальная форма не обладает некоторыми недостатками, свойственным N-йнормальной форме.

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

1НФ требует:

─ атомарность значений атрибутов

─ уникальность кортежей.

Схема отношения R находится в первой нормальной форме (1НФ), если значения в домене D(A) являются атомарными для каждого атрибута А в JR. Другими словами, значения в домене не являются ни списками, ни множествами простых или сложных значений.

Определить понятие атомарности трудно: значение атомарное в одном приложении, может быть неатомарным в другом. Можно руководствоваться общим принципом, что значение неатомарно, если в приложении оно используется по частям.

Пример.Имеется отношение Сотрудники:

Если понадобится указать только фамилии сотрудников, то указанное отношение не находится в 1НФ, так как требуемые значения являются частью атрибута ФИО. Чтобы отношение в таких условиях находилось в 1НФ, атрибут ФИО должен быть разбит на части, как показано ниже.

Последующие нормальные формы используют понятие функциональной зависимости:

Создание баз данных преследует две основные цели:

─ понизить избыточность хранимых данных;

─ повысить их надежность.

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

Один из способов формализации этих знаний – установление зависимостей между элементами данных.

Функциональная зависимость является обобщением понятия ключа. Она имеет место тогда, когда значения кортежа на одном множестве атрибутов единственным образом определяют эти значения на другом множестве атрибутов.

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

Либо А – функционально определяет В, либо В – функционально зависит от А.

Пример: отношение График (ПИЛОТ, РЕЙС, ДАТА, ВРЕМЯ-ВЫЛЕТА).

В отношении имеются следующие функциональные зависимости:

1. ВРЕМЯ функционально зависит от РЕЙСА.

2. РЕЙС функционально зависит от {ПИЛОТ, ДАТА, ВРЕМЯ}.

3. ПИЛОТ функционально зависит от {РЕЙС, ДАТА}.

Символически можно записать это так:

2НФ требует:

─ удовлетворение требований в 1НФ

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

Полная функциональная зависимость -зависимость от всего ключа в целом. Если лишь от части ключа, то - частичная зависимость. Для примера с расписанием приёма врачей:

табN => ФИО табN, день => время

табN => Специальность табN => кабинет

Для устранения частичной зависимости её выносят в отдельное отношение:

Прием1(табN, день, время)

Прием2(табN, ФИО, специальность, кабинет)

Получили 2НФ.

3НФ:связана с понятием транзитивной зависимости.

Если А зависит от В, В зависит от С, то А транзитивно зависит от С.

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

Получим 3НФ:

Прием1(табN, день, время)

Прием21(табN, ФИО, кабинет)

Прием22(кабинет, специальность)

3НФ – это отношение, удовлетворяющее требованиям 2НФ и не имеющее транзитивной зависимости неключевых атрибутов.

НФБК учитывает нескольких потенциальных (возможных) ключей.

Пусть в отношении Прием1 имеется ещё один атрибут: паспорт.

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

Прием11(табN, день, время)

Прием12(табN, паспорт)

4НФ и 5НФ на практике почти не встречаются.

Недостатки нормализации:

─ при нормализации происходит дробление отношений: число отношений увеличивается

─ излишняя нормализация тормозит выполнение запросов

При низких характеристиках исполнения функций СУБД рекомендуется выполнить денормализацию.

Варианты денормализации:

1) Соединение совместно использованных таблиц

2) Дублирование отдельных столбцов в смежные таблицы

Механизмом нормализации можно пользоваться как технологией проектирования.

Общая схема при этом следующая: выявляются все необходимые данные, сводятся в единое отношение – универсальное отношение. В нем выявляются неполные функциональные зависимости - выполняется переход к 2НФ. Выявляются транзитивные зависимости – выполняется переход к 3НФ и т.д.

Такой подход можно использовать:

1) Для проектирования небольших баз данных (до 50 атрибутов)

2) Для нормализации отдельных частей базы, полученных другими методами

Резюме:

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

Приведение к первой нормальной форме (1НФ)

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

Приведение к 2НФ

Обладает свойствами 1НФ + полной функциональной зависимостью атрибутов от ключа. Она состоит в том, что атрибут зависит от всего ключа, а не от части. Иначе - частичная функциональная зависимость. Для устранения частичной функциональной зависимости, ее описание выносится в отдельное отношение:

ВРЕМЯ - зависит от полного ключа

СПЕЦ - зависит от части ключа

КАБ - зависит от части ключа

Приведение к 3НФ

Обладает свойствами 2НФ + не транзитивной зависимостью атрибутов от ключа:

В С Д

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

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