Лекция №5 Нормализация отношений

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

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

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

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

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

Аномалии-удаления — при удалении какого либо кортежа из таблицы может пропасть информация, которая не связана на прямую с удаляемой записью.

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

 

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

Например, есть таблица «Автомобили»:

 

Фирма	Модели
BMW	M5, X5M, M1
Nissan	GT-R

 

 

Фирма	Модели
BMW	M5
BMW	X5M
BMW	M1
Nissan	GT-R

Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным. Преобразуем таблицу к 1НФ:

 

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

Например, дана таблица:

 

Модель	Фирма	Цена	Скидка
M5	BMW		5%
X5M	BMW		5%
M1	BMW		5%
GT-R	Nissan		10%

Таблица находится в первой нормальной форме, но не во второй. Цена машины зависит от модели и фирмы. Скидка зависит от фирмы, то есть зависимость от первичного ключа неполная. Исправляется это путем декомпозиции на два отношения, в которых не ключевые атрибуты зависят от ПК.

Модель	Фирма	Цена
М5	BMW
Х5М	BMW
М1	BMW
GT-R	Nissan

 

 

Фирма	Скидка
BMW	5%
Nissan	10%

 

Рассмотрим таблицу:

 

Модель	Магазин	Телефон
BMW	Риал-авто	87-33-98
Audi	Риал-авто	87-33-98
Nissan	Некст-Авто	94-54-12

 

Модель – первичный ключ данного отношения. Здесь мы видим, что Магазин функционально зависит от Модели, а телефон от Магазина. Таким образом телефон зависит и от Модели тоже, хоть и косвенно. Поэтому можно считать, что отношение находится во 2НФ. Такого рода зависимости между атрибутами:

Модель->Магазин->Телефон

называются транзитивными.

Отношение находится в 3НФ, если оно находится во 2НФ и не имеет транзитивных зависимостей.

Магазин	Телефон
Риал-Авто	87-33-98
Некст-Авто	95-54-12

 

Модель	Магазин
BMW	Риал-авто
Audi	Риал-авто
Nissan	Некст-Авто

 

Отношение находится в НФБК если каждый детерминант является потенциональным ключом.

Пусть требуется хранить данные о поставках деталей некоторыми поставщиками. Предположим, что наименования поставщиков являются уникальными. Кроме того, каждый поставщик имеет свой уникальный номер. Данные о поставках можно хранить в следующем отношении:

Номер поставщика PNUM	Наименование поставщика PNAME	Номер детали DNUM	Поставляемое количество VOLUME
	Фирма 1
	Фирма 1
	Фирма 1
	Фирма 2
	Фирма 2
	Фирма 3

Таблица 1 Отношение "Поставки"

Данное отношение содержит два потенциальных ключа - { PNUM, DNUM } и { PNAME, DNUM } и находится в 3НФ.

Отношение "Поставки" не находится в НФБК, т.к. имеются зависимости (PNUM PNAME и PNAME PNUM), детерминанты которых не являются потенциальными ключами.

Исправляем с помощью декомпозиции:

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

Номер поставщика PNUM	Наименование поставщика PNAME
	Фирма 1
	Фирма 2
	Фирма 3

 

Номер поставщика PNUM	Номер детали DNUM	Поставляемое количество VOLUME

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

Рассмотрим следующий пример. Пусть требуется учитывать данные об абитуриентах, поступающих в ВУЗ. При анализе предметной области были выделены следующие требования:

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

Предположим, что нам требуется хранить данные о том, какие предметы должен сдавать каждый абитуриент. Попытаемся хранить данные в одном отношении "Абитуриенты-Факультеты-Предметы":

Абитуриент	Факультет	Предмет
Иванов	Математический	Математика
Иванов	Математический	Информатика
Иванов	Физический	Математика
Иванов	Физический	Физика
Петров	Математический	Математика
Петров	Математический	Информатика

Таблица 7 Отношение "Абитуриенты-Факультеты-Предметы"

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

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

 

 

Номер Абитуриента	Номер Факультета	Номер Предмета

Таблица 8 Модифицированное отношение "Абитуриенты-Факультеты-Предметы"

Номер Абитуриента	Абитуриент
	Иванов
	Петров

Таблица 9 Отношение "Абитуриенты"

Номер Факультета	Факультет
	Математический
	Физический

Таблица 10 Отношение "Факультеты"

Номер Предмета	Предмет
	Математика
	Информатика
	Физика

Таблица 11 Отношение "Предметы"

Теперь каждое наименование встречается только в одном месте.

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

Аномалия вставки. При попытке добавить в отношение "Абитуриенты-Факультеты-Предметы" новый кортеж, например (Сидоров, Математический, Математика), мы обязаны добавить также и кортеж (Сидоров, Математический, Информатика), т.к. все абитуриенты математического факультета обязаны иметь один и тот же список сдаваемых предметов. Соответственно, при попытке вставить в модифицированное отношении кортеж (3, 1, 1), мы обязаны вставить в него также и кортеж (3, 1, 2).

Аномалия удаления. При попытке удалить кортеж (Иванов, Математический, Математика), мы обязаны удалить также и кортеж (Иванов, Математический, Информатика) по той же самой причине.

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

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

Декомпозиция отношения "Абитуриенты-Факультеты-Предметы" для устранения указанных аномалий не может быть выполнена на основе функциональных зависимостей, т.к. это отношение не содержит никаких функциональных зависимостей. Это отношение является полностью ключевым, т.е. ключом отношения является все множество атрибутов. Но ясно, что какая-то взаимосвязь между атрибутами имеется. Эта взаимосвязь описывается понятием многозначной зависимости.

Отношение "Абитуриенты-Факультеты-Предметы" находится в НФБК, но не в 4НФ. Согласно теореме Фейджина, это отношение можно без потерь декомпозировать на отношения:

Факультет	Абитуриент
Математический	Иванов
Физический	Иванов
Математический	Петров

Таблица 12 Отношение "Факультеты-Абитуриенты"

Факультет	Предмет
Математический	Математика
Математический	Информатика
Физический	Математика
Физический	Физика

Таблица 13 Отношение "Факультеты-Предметы"

В полученных отношениях устранены аномалии вставки и удаления, характерные для отношения "Абитуриенты-Факультеты-Предметы".

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

Существует также 5, 6 НФ, Доменно-ключевая НФ. При желании вы можете изучить их самостоятельно.