Рейс<--->> Расписание

Нормализация баз данных

 

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

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

S

SC

SST

В качестве примера рассмотрим таблицу поставщиков и 2 варианта ее декомпозиции:

 

код_п	статус	город
п3		Париж
п5		Лондон

 

код_п	статус
п3
SST п5

 

код_п	город
п3	Париж
SC п5	Лондон

 

 

а)

 

код_п	статус
п3
п5

 

статус	город
	Париж
	Лондон

б)

 

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

1. В случае (а) информация не утрачивается, поскольку таблицы SST и SC все еще содержат данные о том, что поставщик п3 имеет статус 30 и находится в Париже, а поставщик п5 имеет статус 30 и находится в Лондоне. Иначе говоря, первая декомпозиция действительно является декомпозицией без потерь.

2. В случае (б), наоборот, некоторая информация утрачивается, поскольку оба поставщика имеют статус 30, но при этом нельзя сказать, какой из них в каком городе находится. Иначе говоря, вторая декомпозиция не является декомпозицией без потерь (полной декомпозицией).

 

Первая нормальная форма (1НФ)

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

Пример: Рейс (номер_рейса, пункт_назначения, Расписание)

Расписание (день, время_вылета)

Пусть имеются следующие данные о рейсах:

Р101 Владивосток пон. 9:40

вт. 9:30

пятн. 10:30

Р800 Москва пон. 7:30

чет. 7:30

пятн. 7:30

Преобразовать эти данные в 1НФ можно 2 способами:

1) В составной таблице Рейс заменить таблицу Расписание соответствующими атрибутами:

 

Рейс (номер_рейса, пункт_назначения, день, время_вылета)

номер	пункт_назначения	день	время_вылета
Р101	Владивосток	пон.	9:40
Р101	Владивосток	вт.	9:30
Р101	Владивосток	пятн.	10:30
Р800	Москва	пон	7:30
Р800	Москва	чет.	7:30
Р800	Москва	пятн.	7:30

Недостатки этого способа:

а) избыточность; б) необходимость определения нового ключа.

 

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

Рейс (номер_рейса, пункт_назначения)

Расписание (день, время_вылета, номер_рейса) – будем считать, что 2 разных самолета не могут вылететь одновременно!)

Рейс<--->> Расписание

В таблице Расписание можно избавиться от составного ключа день, время_вылета, добавив уникальное поле, например, код_расписания, и выбрав его в качестве первичного ключа:

Расписание (код_расписания,день, время_вылета, номер_рейса),

однако делать этого до полной нормализации не рекомендуется.

 

Вторая нормальная форма (2НФ)

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

Пример:

Пусть имеется таблица Поставка, содержащая данные о поставщиках (идентифицируемых номером), поставляемых ими товарах и их ценах.

Поставка (номер_поставщика, товар, цена)

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

номер_поставщика, товар ® цена

товар ® цена

Можно отметить неполную функциональную зависимость атрибута цена от ключа.

Это приводит к следующим аномалиям:

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

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

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

Разложение таблицы Поставка на две таблицы устранит неполную функциональную зависимость:

Поставка (номер_поставщика, товар)

Цена_товара (товар, цена)

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

 

Третья нормальная форма (3НФ)

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

Пример:

Пусть имеется таблица Получение (фирма, склад, объем), которое содержит информацию о фирмах, получающих товары со складов. В таблице имеются функциональные зависимости:

фирма ® склад (фирма получает товары только с одного склада)

склад ® объем

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

Преобразование таблицы в 3НФ, устраняет рассмотренные аномалии.

Получение (фирма, склад); Склад_объем (склад, объем).

 

Таблица Рейс, полученная 1-м способом приведения к 1НФ не находится в 2НФ, а полученная 2-м способом – находится в 3НФ.

 

Получив 3НФ, мы можем сказать, что наша модель данных нормализована. Существуют и другие нормальные формы, однако в большинстве случаев 3НФ достаточно, чтобы гарантировать правильность проекта базы данных.

ЗАДАНИЕ

 

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

2. Создание БД средствами СУБД. Реализовать нормализованную базу данных средствами MS Access. Создать запрос «Исходные данные», отображающий все данные начальной (ненормализованной) таблицы.

ВАРИАНТЫ исходных данных:

1. Дан подробный план парка, на котором отдельно указано каждое дерево. Все деревья снабжены индивидуальными номерами.

ДЕРЕВЬЯ

номер_дерева	порода	высота	вечнозеленое
	бук		нет
	падуб		да
	бук		нет
	ясень		нет

 

1. Ниже приведена часть записей, помещенных в таблицу «КОНФЕТЫ».

КОНФЕТЫ

рецепт	ингредиент	граммы	калории_на_гр
ирис	сахар		3.7
ирис	масло		7.8
ирис	мука		3.5
ирис	патока		3.2
тянучка	сахар		3.7
тянучка	масло		7.8
тянучка	сгущен_молоко		4.5

 

1. В таблице фиксированы приезды людей в различные города. У всех визитеров разные фамилии, нет городов с одинаковыми названиями.

ВИЗИТЫ

дата	фамилия	профессия	город	страна
	Джонс	бухгалтер	Эфтон	Уайландия
	Смит	программист	Ситон	Эксландия
	Смит	программист	Эйтон	Эксландия
	Смит	программист	Эфтон	Уайландия
	Найт	инженер	Дитон	Зедландия
	Янг	инженер	Ситон	Эксландия

 

1. Переезд из одного города в другой всегда проходит по неизменному маршруту. В день проезжает не более одного автобуса по маршруту.

ПОЕЗДКИ

откуда	куда	расстояние	дата	водитель	время
Уинклби	Коклтон			Маршалл	3.4
Уинклби	Коклтон			Арнольд	2.8
Коклтон	Макгов			Маршалл	4.1

 

1. Имена полей и часть записей приведены ниже. Два конкретных шахматиста могут сыграть не более одной партии в день.

ШАХМАТЫ

дата	участник_1	участник_2	победитель	время
	Грамбиг	Пивич	Пивич	3.4
	Грамбиг	Смит	Смит	2.5
	Грамбиг	Пивич	Пивич	1.4
	Смит	Пивич	Смит	5.2

 

1. Ниже приведены записи зоологического файла.

ЗВЕРИ_В_НЕВОЛЕ

зоопарк	животное	зона_обитания	сторож
Эйтон	кенгуру	Австралия	Найт
Эйтон	верблюд	Аравия	Понсонби
Битон	эму	Австралия	Карузерс
Битон	верблюд	Аравия	Герсдлстон

 

1. ОРАНЖЕРЕЯ

поместье	садовые_цветы	сезон_цветов
Гейблз	нарциссы	весна
Гейблз	розы	лето
Козикот	колокольчики	весна
Козикот	розы	лето

 

вид_спорта	победитель	год_рождения
прыжки_в_длину	Армстронг
бег_на_100_м	Маршалл
100_м_с_барьерами	Маршалл
прыжки_с_шестом	Уильямс

1. СОРЕВНОВАНИЯ

 

1. БАР

фамилия	напиток	количество	цена_за_порцию
Армстронг	виски
Армстронг	херес
Бек	виски
Найт	херес

 

владелец	дата_рожд	№_регистр	дата_регистр
Армстронг	06.1960	AHC134T	06.1979
Армстронг	06.1960	BCY529	05.1980
Бек	05.1959	AHD339H	10.1972
Найт	07.1961	ОУУ796Р	1.1976

1. АВТОИНСПЕКЦИЯ

 

1. АВТОМОБИЛИ

код_машины	модель	тип_кузова	мощность_двигателя	цвет	дата_выпуска
А1А	ЗАЗ 968	купе		белый
А2А	ВАЗ 2105	седан		красный
А1В	ЗАЗ 968	купе		желтый
А3И	ВАЗ 2105	седан		синий
А2В	ВАЗ 21011	универсал		синий

 

12. ДОРОЖНАЯ_СЛУЖБА

№_дороги	протяженность	город	население
А3		Арби
А3		Титон
А4		Арби
А4		Эсфилд

 

 

13. ВРАЧИ

код_врача	фамилия_врача	стаж_работы	пациенты
А1	Иванов		Васильев, 45 лет Медведев, 27 лет
А2	Петров		Попов, 30 лет Щеглов, 30 лет Комаров, 38 лет
А3	Сидоров		Федоров, 35 лет Мухин, 38 лет

 

14. ЛЕЧЕНИЕ

№_назначения	дата_назначения	код_пациента	врач	стаж_работы	лекарство
	21.01.98	В1	Петров		аспирин
	23.01.98	В5	Сидоров		йод
	23.01.98	В1	Петров		спирт
	24.01.98	В2	Иванов		парацетамол

 

15. ПОЕЗДА

номер	пункт_отправления	пункт_назначения	отправление
	Краснодар	Москва	пон. 10:15, путь №4 ср. 12:30, путь №1 суб. 3:50, путь №3
	Москва	С.-Петербург	пон. 6:50, путь №2 ср. 6:50, путь №2 пятн. 6:50, путь №2