4.12. Многозначная зависимость.

Еще один вид избыточности информации в отношении. Многозначная зависимость (МЗ) возникает между атрибутами кортежей отношения в ситуации, когда отношение пытается представить более одной связи типа “многие ко многим”. В этой ситуации определенные атрибуты становятся независимы один от другого и их значения должны присутствовать во всех комбинациях.

Пример:

ПОКУПАТЕЛЬ (ИМЯ, АДРЕС, ЛЮБИМЫЙ ТОВАР, ДИСКОНТНАЯ КАРТА)

ЛЮБИМЫЕ ТОВАРЫ не зависит от ДИСКОНТНОЙ КАРТЫ. Поэтому каждый ЛЮБИМЫЙ ТОВАР встречается в любой комбинации с ДИСКОНТНОЙ КАРТОЙ. Это не похоже на ФЗ Имя->Адрес, т.к. при ФЗ такая комбинация встречается лишь однажды.

Имя Адрес Любимый товар Дисконтная карта

Иван Металлургов, 99 Колбаса Аккорд

Иван Металлургов, 99 Книги Сократ

Иван Металлургов, 99 Колбаса Сократ

Иван Металлургов, 99 Книги Аккорд

В такой ситуации имеет место зависимость некоторой совокупности значений из домена ЛЮБИМЫЙ ТОВАР от поля ИМЯ.

Наличие МЗ X->->Y означает, что если 2 кортежа совпадают в части X, то можно обменять значения компонентов из Y между собой (при этом оставив нетронутыми оставшиеся атрибуты Z) и получить кортежи из того же отношения.

В нашем случае имеют место следующие зависимости:

Имя -> Адрес

Имя ->-> Любимый товар

Имя ->-> Дисконтная карта

Всякая ФЗ является частным случаем МЗ!

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

Отношение находится в 4НФ, если из X->->Y следует что X –суперключ (т. е. либо является ключом, либо включает в себя ключ).

Очень похоже на НФБК: ФЗ -> заменяется на МЗ ->-> .

При этом имеется в виду, что МЗ X->->Y не тривиальна, т.е. Y не есть подмножество X и, кроме того, X и Y не образуют вместе все множество атрибутов отношения.

Соотношение между НФБК и МЗ.

1. Всякая ФЗ есть МЗ.

2. Поэтому, если отношение в 4НФ, то оно и в НФБК.

3. Но отношение может быть в НФБК но не быть в 4НФ.

Если отношение не в 4НФ, то его можно декомпозировать, пользуясь теми же приемами, что и для НФБК.

Пример: Для отношения Покупатель выполним декомпозицию на

Покупатель1 (Имя, Адрес): только Имя->Адрес следовательно 4НФ.

Покупатель2 (Имя, Любимый товар, Дисконтная карта).

В отношении Покупатель2 Имя ->-> Любимый товар, Имя –>-> Дисконтная карта, ключом является комбинация всех 3 атрибутов, следовательно Покупатель2 не в 4 НФ.

Наличие ->-> заставляет нас провести дальнейшую декомпозицию Покупатель2:

Любимые товары (Имя, Любимый товар)

Скидки (Имя, Дисконтная карта)

4.14. Пример нормализации.

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

Данные, которые необходимо учитывать могут быть записаны в таком виде (рис.1):

Товар	НДС	Расход	Дата прод	Поставщик	Город	Страна	Вес (кг)	Цена	Дата П
Апельсины	20%	200	11/03	Центрпродсервис	Петербург	Россия	250	0.37	10/02
				Глобус	Новокузнецк	Россия	100	0.52	11/02
				Витамин	Рига	Латвия	70	1.55	15/02
Бананы	20%	144	10/03	Глобус	Новокузнецк	Россия	100	2.18	17/02
				Центрпродсрвис	Петербург	Россия	100	0.52	18/01
				Витамин	Рига	Латвия	120	0.45	27/01
				Меконг	Ханой	Вьетнам	75	0.44	24/02
Помидоры	10%	207	10/03	Глобус	Новокузнецк	Россия	200	2.05	10/02
				Витамин	Рига	Латвия	50	0.61	12/02
				Центрпродсервис	Петербург	Россия	120	0.45	27/02

Рис. 1. "Товары в магазине" (Это не отношение)

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

Товар	НДС	Расход	Дата прод	Поставщик	Город	Страна	Вес (кг)	Цена	Дата П
Апельсины	20%	200	11/03	Центрпродсервис	Петербург	Россия	250	0.37	10/02
Апельсины	20%	200	11/03	Глобус	Новокузнецк	Россия	100	0.52	11/02
Апельсины	20%	200	11/03	Витамин	Рига	Латвия	70	1.55	15/02
Бананы	20%	144	10/03	Глобус	Новокузнецк	Россия	100	2.18	17/02
Бананы	20%	144	10/03	Центрпродсервис	Петербург	Россия	100	0.52	18/01
Бананы	20%	144	10/03	Витамин	Рига	Латвия	120	0.45	27/01
Бананы	20%	144	10/03	Меконг	Ханой	Вьетнам	75	0.44	24/02
Помидоры	10%	207	10/03	Глобус	Новокузнецк	Россия	200	2.05	10/02
Помидоры	10%	207	10/03	Витамин	Рига	Латвия	50	0.61	12/02
Помидоры	10%	207	10/03	Центрпродсервис	Петербург	Россия	120	0.45	27/02

Рис. 2. Универсальное отношение "Товары в магазине" ( 1НФ )

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

Варианты отношений (зависимостей) между таблицами:

1:1

1:N

N:M

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

Товары Товар НДС Апельсины 20 Бананы 20 Помидоры 10 Расход Товары Расход Дата_прод Апельсины 200 11/03 Бананы 144 11/03 Помидоры 207 10/03

Поставщики Поставщик Город Страна Глобус Новокузнецк Россия Центрпродсервис Петербург Россия Витамин Рига Латвия ... ... ...

Поставки Поставщик Товар Вес (кг) Цена ($) Дата_П Глобус Апельсины 120 0.45 11/02 Глобус Бананы 50 1.62 17/02 Витамин Бананы 50 0.61 27/01 Меконг Бананы 100 0.52 24/02 ... ... ... ... ... 2НФ

Рис.3. Преобразование универсального отношения "Товары в магазине" (I вариант)

ФЗ: поле B ФЗ A  в любой момент для каждого значения A сущ. только 1 значение B.

(A, B – могут быть составными)

КодТ -> Товар, НДС

Поле B находится в полной -> от A  A -> B и (любое подмножество A) -/-> B

Поставщик, Город -> Страна (Город -> Страна)  Поставщик, Город -> Страна – не функционально полна.

Рис2: PK = Товар, ДатаПрод, Поставщик, Город, Дата П

Неключевые поля = НДС, Кол-во,… (Товар -> НДС; Товар, ДатаПрод -> Кол-во)

Т.е. эти поля не связаны с PK полной функциональной зависимостью.

Рис.3: PK = Поставщик, Город но Город -> Страна (это плохо тем, что пока нет поставщика из Владивостока, нельзя сказать что Владивосток находится в России или удалив поставщика можем удалить и принадлежность города стране).

3НФ  2НФ и ни одно из неключевых полей не зависит функционально от любого другого неключевого поля.

Товары НФБК КодТ ->Товар -> НДС 1 Апельсины 20 2 Бананы 20 3 Помидоры 10 Расход КодТ Расход Дата_прод 1 200 11/03 2 144 11/03 3 207 10/03

Поставщики КодП Поставщик Город -> Страна 1 Глобус Новокузнецк Россия 2 Центрпродсервис Петербург Россия 3 Витамин Рига Латвия … ... ... ... 2НФ

Поставки КодП КодТ Вес (кг) Цена ($) Дата_П 1 1 120 0.45 11/02 1 2 50 1.62 17/02 3 2 50 0.61 27/01 4 2 100 0.52 24/02 ... ... ... ... ... 3НФ

<КодТ, Товар>

<КодТ, НДС>

3 НФ

??? Рис.4 Преобразование универсального отношения "Товары в магазине" (II вариант)

Поставщики 3НФ Города 3НФ

КодП	Поставщик	КодГ
1	Глобус	1
2	Центрпродсервис	2
3	Витамин	3

КодГ	Город	Страна
1	Новокузнецк	Россия
2	Петербург	Россия
3	Рига	Латвия

Рис 5. Преобразование отношения Поставщики (Рис. 4) к 2 отношениям.

Для исключения длинных текстов их нумеруют (кодируют) – этим устраняется избыточность. Чтобы изменить, например, имя поставщика, надо изменить его в одном месте.

Рис.4: Товар -> НДС => не 3НФ у таблицы Товары. Чтобы стала 3НФ надо разбить Товары на <КодТ, Товар>, <КодТ, НДС> но это только ухудшит проект.

НФБК: 2НФ и любая ФЗ между полями отношения сводится к полной ФЗ от возможного ключа. Тогда (Рис.4) таблица Товар находится в НФБК т.к. поле Товар – возможный ключ.

Чтобы перевести таблицу Поставщики (рис. 4) в 3НФ: разбить таблицу на Поставщики и Города (рис. 5)

Многозначная зависимость B от A: A=>B  для каждого значения A определено ограниченное множество значений B. (Город => Поставщик)

Полная декомпозиция таблицы – такая совокупность произвольного числа ее проекций, соединение которых полностью совпадает с содержимым таблицы. (Данные таблиц на Рис. 3 естественным соединением преобразуется к таблице на Рис.2 => Таблицы Рис.3 – полная декомпозиция таблицы Рис. 2)

Пример. Соединение таблиц дает исходную таблицу:

SELECT Товары.Товар, Товары.НДС, Расход.Расход,

Расход.ДатаПрод, Поставщики.Поставщик, …

FROM Товары

LEFT JOIN Расход ON Расход.Товары = Товары.Товары

LEFT JOIN Поставки ON Поставки.Товар = Товары.Товар AND …

LEFT JOIN Поставщики ON …