Транзитивная функциональная зависимость

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

2НФ

Пример:

Пусть отношение называется СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_№, СОТР_ЗАРПЛ, ОТД_№, ПРОЕКТ_№, СОТР_ЗАДАН).

Можно выделить составной первичный ключ: СОТР_№, ПРОЕКТ_№.

Здесь может возникнуть проблема: аномалия, которая не позволяет корректно выполнять операции над данными, а именно:

· добавление сотрудника, который не выполнил ещё ни одного проекта – аномалия включения;

· если удалить какой-то кортеж, связанный с завершением проекта, то параллельно теряется информация, что сотрудник работает в каком-то отделе – аномалия исключения;

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

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

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

Чтобы от аномалий избавиться надо перейти в 2НФ. Отношение находится в 2НФ, если оно находится в 1НФ, и каждый неключевой атрибут полностью зависит от первичного ключа. Чтобы получить полностью функциональные зависимости, разделим отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ на 2 части:

СОТРУДНИКИ-ОТДЕЛЫ (СОТР_№, СОТР_ЗАРПЛ, ОТД_№)

первичный ключ СОТР_№

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

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_№, ПРОЕКТ_№, СОТР_ЗАДАН)

первичный ключ: СОТР_№, ПРОЕКТ_№

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

2НФ не является совершенной, поэтому строится 3НФ. Рассмотрим отношение в 2НФ. Видно, что функциональная зависимость является транзитивной: она является следствием двух функциональных зависимостей и , т.е. зарплата связана с отделом. Наличие (*) приводит к тому, что в БД нельзя внести информацию о зарплате в отделе до тех пор, пока в этом отделе не появится хотя бы один сотрудник. Т.е. транзитивная зависимость между неключевыми атрибутами сохраняет аномалии включения и исключения. Следовательно, процедуру нормализации нужно продолжать.

3НФ – это отношение в 2НФ, в котором каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Подвергнем декомпозиции отношение СОТРУДНИКИ-ОТДЕЛЫ:

СОТРУДНИКИ (СОТР_№, ОТДЕЛ_№)

Ключ СОТР_№

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

ОТДЕЛЫ (ОТД_№, СОТР_ЗАРПЛ)

Ключ ОТД_№

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

ПРИМЕЧАНИЕ: отношение, находящееся в 1НФ может быть подвергнуто декомпозиции, если имеются функциональные зависимости между неключевыми атрибутами и элементами составного ключа. Выделение таких функционально зависимых атрибутов в отдельное отношение и есть основа декомпозиции. Дальнейшая нормализация должна выделять отношения, описывающие именно такие транзитивные зависимости. В выделенных отношениях в качестве ключевого может быть выбран любой атрибут.

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

НФ Бойса-Кодда

Рассмотрим отношение СОТРУДНИКИ-ПРОЕКТЫ (СОТР_№, СОТР_ИМЯ, ПРОЕКТ_№, СОТР_ЗАРПЛ). Возможные первичные ключи: СОТР_№, ПРОЕКТ_№ и СОТР_ИМЯ, ПРОЕКТ_№. Функциональные зависимости:

Такое отношение находится в 3НФ, но здесь есть функциональные зависимости атрибутов отношения от атрибутов, являющихся частью первичного ключа. И аномалия замещения будет состоять в том, что, чтобы изменить имя сотрудника с данным номером согласованным образом придётся изменить все кортежи, включающие номер сотрудника. Поэтому НФ Бойса-Кодда преследует выделение полных функциональных зависимостей. Для этого вводится понятие детерминант – это любой атрибут, который полностью функционально зависит от другого атрибута. И отношение находится в НФ Бойса-Кодда в том случае, если каждый детерминант является ключом. Для приведения СОТРУДНИКИ-ПРОЕКТЫ в НФ Бойса-Кодда можно произвести декомпозицию к двум отношениям: СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ. Т.к. детерминантом может быть либо СОТР_ИМЯ, либо СОТР_№, то

4НФ: Дополним модель условиями:

1. отдельные проекты: задание1, задание2, …;

2. если сотрудник работает над проектом, то он принимает участие во всех заданиях проекта (дополнительное условие).

Описать эту ситуацию можно отношением ПРОЕКТЫ (ПРОЕКТ_№, ПРОЕКТ_СОТР, ПРОЕКТ_ЗАДАН). Уникальный ключ – комбинация всех атрибутов. Многозначные зависимости – отношение R со схемой R(A B C) содержит многозначную зависимость R.A ® ®R.B (атрибут А многозначно зависит от атрибута В). если множество значений В, которое соответствует паре (А, С): В~( А, С), зависит только от А, и на зависит от С.

Т.е. многозначная зависимость между ПРОЕКТ_№ ® ® ПРОЕКТ_СОТР

СОТР~(ПРОЕКТ, ЗАДАН) зависит от ПРОЕКТ и не зависит от ЗАДАН.

Недостатки многозначной зависимости: если необходимо присоединить сотрудника к проекту, то нужно добавить столько кортежей, сколько существует заданий в данном проекте.

ПРОЕКТ_№ ® ® ПРОЕКТ_СОТР

ПРОЕКТ_№ ® ® ПРОЕКТ_ЗАДАНИЕ

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

Если R (A, B, C) и существует многозначная зависимость R.A ® ® R.B, то R1 (A, B) и R2 (A, С). Причём по R1 и R2 исходное R может быть восстановлено полностью путём естественного соединения.

– ПРОЕКТЫ – СОТРУДНИКИ (ПРОЕКТ_№, ПРОЕКТ_СОТР);

– ПРОЕКТЫ – ЗАДАНИЯ (ПРОЕКТ_№, ПРОЕКТ_ЗААДАН).

Уловие естественного соединения: ПРОЕКТ_№ из ПРОЕКТЫ – СОТРУДНИКИ = ПРОЕКТ_№ из ПРОЕКТЫ – ЗАДАНИЯ.