Аномалии и избыточность данных

Вторая и третья нормальные формы 2НФ и 3НФ предназначены для исключения аномалий обновления данных при работе с БД и избавления от информационной избыточности в отношениях.

Пример 2.12. Дано отношение (табл. 2.3, 2.4):

РАСПИСАНИЕ (Рейс, Дата, Экипаж, Время),

где атрибут ( Рейс, Дата) – составной ключ отношения.

 

Требуется преобразовать отношение, чтобы оно удовлетворяло F-зависимости Рейс→ Время:

РАСПИСАНИЕ Таблица 2.3

Рейс	Дата	Экипаж	Время
	5.01 7.01 9.01		11:40 19:10 10:15

Нужно обновить отношение, изменив время вылета с 11:40 на 10:15 для рейса 112, вылетающего 5 января.

Команда замены примет вид::

СН (РАСПИСАНИЕ; 112; 5.01; 1; Время = 10:15),

а отношение:.

РАСПИСАНИЕ Таблица 2.4

Рейс	Дата	Экипаж	Время
	5.01 7.01 9.01		10:15 19:10 10:15

В новом отношении имеется

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

РАСПИСАНИЕ-ЭКИПАЖ (Рейс, Дата. Экипаж)

и РАСПИСАНИЕ-ВРЕМЯ (Рейс, Время)

РАСПИСАНИЕ-ЭКИПАЖ РАСПИСАНИЕ-ВРЕМЯ

 

Рейс	Дата	Экипаж		Рейс	Время
	5.01				10:15
	7.01				19:10
	9.01

Рис. 2.1. Отношения БД РАСПИСАНИЕ

В результате аномалия обновления исчезла и понизилась избыточность данных (одна строка Рейс- Время вместо двух в исходной таблице). Теперь, чтобы изменить время вылета достаточно изменить его в од­ном кортеже. Это результат того, что схема отношения была приведена ко 2НФ (атрибут Экипаж зависит только от Рейс, Дата, а Время – только от Рейс).

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

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

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

Определения:

1. Атрибут называется посторонним для функциональной зависимо­сти X→Y, если он может быть удален из правой или левой части функцио­нальной зависимости без изменений транзитивного замыкания F+ множе­ства F (transitive,англ.- переходный).

2. Функциональная зависимость X→Y называется редуцированной слева (справа), если X(Y) не содержит атрибута Z, постороннего для функциональ­ной зависимости X→Y (reduce, англ. – уменьшать). Зависимость, редуцированная слева и справа, называ­ется просто редуцированной. Если функциональная зависимость X→Y редуцированна слева, то Y является полностью зависящим от X. В противном случае Y частично зависит от X.

Удаление посторонних атрибутов в начале производят слева, а потом справа. Если после этого исключить зависимости вида Х→ О, то посторонних атрибутов в F-зависимости не останется.

Пример 2.13. Функциональная зависимость

F= {(Рейс, Дата) → (Бригада, Путь); Рейс→Путь}.

Здесь атрибут Путьчастично зависит от (Рейс, Дата), а Бригада полностью зависит от (Рейс, Дата), т.е. F-зависимость редуцирована слева относи

тельно атрибута Бригада и не редуцирована по атрибуту Путь.

Для приведения ко 2НФ нужно ввести новое отношение РЕЙС(Рейс, Путь).

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

Пример 2.14. Дано отношение ПОСТАВКА:

ПОСТАВКА (№ поставщика, Товар, Цена).

Поставщик может поставлять различные товары, один и тот же товар могут поставлять разные поставщики. Ключ составной - №_поставщика, Товар.

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

в отношении ПОСТАВКА объединены две семантические связи: №_поставщика -Товар и Товар – Цена. Поскольку Цена зависит только от Товар и не зависит от № поставщика, функциональная зависимость атрибута Цена от ключа является неполной.

Для приведения отношения к полной F-зависимости, т.е.ко 2НФ, нужно разложить его на два отношения:

ПОСТАВКА (№_поставщика, Товар);

ЦЕНА_ТОВАР (Товар, Цена)

Связь между отношениями - по атрибуту Товар. Изменение цены вызовет модификацию лишь одного кортежа второго отношения ЦЕНА_ТОВАР.

Пример 2.15. Дано отношение БОЛЬНИЦА со схемой:

БОЛЬНИЦА

Дата

ФИО пац.

Адрес пац.

ФИО врача

Лечение

Лекарство

Побочный эффект

Разобьем его на три отношения, находящихся во 2НФ:

ВРАЧ (№ врача, ФИО_врача);

ПАЦИЕНТ (№ пац, ФИО_ пац, Адрес_пац);

ЛЕЧЕНИЕ (№ врача. № пац. Дата, Лечение,

Лекарство, Побоч_эффект)

Введение в качестве ключей новых атрибутов №_врача и №_пац. вместо символьных ФИО_врача.и ФИО_пац. является избыточным, но на практике более целесообразно по двум причинам:

1.Фамилии врача и пациента могут совпасть даже в пределах одной больницы, а №_врача и №_пац кодируются администратором БД.

2. Замена символьных ключей числовыми №_врача и №_пац приводит к некоторому уменьшению объема данных (числовые данные имеют меньшую длину) и к большему быстродействию при связывании, индексировании и выполнении других операций над отношениями.

Схема всей БД имеет 2НФ относительно F, если схема каждого её отношения находится относительно F во 2НФ.

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

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

Согласно аксиоме транзитивности F5: если А→В и В→ С,то А→ С, т.е. С транзитивно зависит от А.

Пример 2.16. Дано отношение ЛЕЧЕНИЕ:

ЛЕЧЕНИЕ (№врача, № пациента, Дата, Лечение,

Лекарство, Побочный эффект)

В нём существуют F-зависимости:

А В

(№_врача, №_пац, Дата) →Лекарство;

В С

Лекарство → Побоч_эффект,

т.е. транзитивная зависимость выполняется через атрибут Лекарство.

Это приводит к:

• аномалии изменения – при изменении сведений о побочном эффекте лекарства требуется просмотреть всю БД для корректного изменения данных;

• аномалия включения - нельзя включить в БД лекарство, не назначенное ни одному больному.

Преобразование отношения в ЗНФ устраняет эти аномалии.

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

Нормализованная БД рассматриваемого примера имеет вид:

ВРАЧ (№ врача, ФИО_ врача);

ПАЦИЕНТ (№_пац, ФИО_пац, Адрес_пац);

ЛЕЧЕНИЕ (№ врача, Ном пац,. Дата, Лечение, Лекарство);

ЛЕКАРСТВО (Лекарство, Побоч_эффект).

Любая БД должна находится как минимум в ЗНФ. Любая схема отношения, находящаяся в ЗНФ относительно F, находится во 2НФ относительно F.

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

Приведение к 3НФ выполняется через разбиение схемы отношения R на две схемы R1 и R2 согласно транзитивной f-зависимости. Если схемы R1 и R2 не окажутся в 3НФ, то декомпозиция продолжается.