Проектирование баз данных

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

- 1. – Из каких отношений (таблиц) должна состоять база данных.

- 2. – Какие атрибуты (заголовки полей) должны быть у этих отношений.

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

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

- вторая нормальная форма (2НФ);

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

- нормальная форма Бойса-Кодда (НФБК);

- четвертая нормальная форма (4НФ);

- пятая нормальная форма (5НФ).

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

Функциональная зависимость. В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y.

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

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

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

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

Взаимно независимые атрибуты. Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других.

Рассмотрим пример проектирования базы данных:

Предположим, что проектирование базы данных «Питание» начинается с выявления атрибутов и подбора данных. Таблица 1.6

Таблица 1.6 «Питание»

Блюдо

Вид

Рецепт

Порций

Дата Р

Продукт

Калорийность

Вес (г)

Поставщик

Город

Страна

Вес (кг)

Цена ($)

Дата П

Лобио

Закуска

…

1/9/94

Фасоль

"Хуанхэ"

Пекин

Китай

0.37

24/8/94

Лук

"Наталка"

Киев

Украина

0.52

27/8/94

Масло

"Лайма"

Рига

Латвия

1.55

30/8/94

Зелень

"Даугава"

Рига

Латвия

0.99

30/8/94

Харчо

Суп

...

1/9/94

Мясо

"Наталка"

Киев

Украина

2.18

27/8/94

Лук

"Наталка"

Киев

Украина

0.52

27/8/94

Томаты

"Полесье"

Киев

Украина

0.45

27/8/94

Рис

"Хуанхэ"

Пекин

Китай

0.44

24/8/94

Масло

"Полесье"

Киев

Украина

1.62

27/8/94

Зелень

"Наталка"

Киев

Украина

0.88

27/8/94

Шашлык

Горячее

...

1/9/94

Мясо

"Юрмала"

Рига

Латвия

2.05

30/8/94

Лук

"Полесье"

Киев

Украина

0.61

27/8/94

Томаты

"Полесье"

Киев

Украина

0.45

27/8/94

Зелень

"Даугава"

Рига

Латвия

0.99

30/8/94

Кофе

Десерт

...

1/9/94

Кофе

"Хуанхэ"

Пекин

Китай

2.87

24/8/94

Этот вариант таблицы "Питание" не является отношением, так как большинство ее строк не атомарны. Атомарными являются лишь значения полей Блюдо, Вид, Рецепт (хотя он и большой), Порций и Дата_Р остальные же поля таблицы– множественные. Для придания таким данным формы отношения необходимо реконструировать таблицу. Наиболее просто это сделать с помощью простого процесса вставки, результат которой показан в таблице 1.7.

Таблица 1.7 Отношение: «Питание»

Блюдо

Вид

Рецепт

Порций

Дата Р

Продукт

Калорийность

Вес (г)

Поставщик

Город

Страна

Вес (кг)

Цена ($)

Дата П

Лобио

Закуска

…

1/9/94

Фасоль

"Хуанхэ"

Пекин

Китай

0.37

24/8/94

Лобио

Закуска

…

1/9/94

Лук

"Наталка"

Киев

Украина

0.52

27/8/94

Лобио

Закуска

…

1/9/94

Масло

"Лайма"

Рига

Латвия

1.55

30/8/94

Лобио

Закуска

…

1/9/94

Зелень

"Даугава"

Рига

Латвия

0.99

30/8/94

Харчо

Суп

...

1/9/94

Мясо

"Наталка"

Киев

Украина

2.18

27/8/94

Харчо

Суп

...

1/9/94

Лук

"Наталка"

Киев

Украина

0.52

27/8/94

Харчо

Суп

...

1/9/94

Томаты

"Полесье"

Киев

Украина

0.45

27/8/94

Харчо

Суп

...

1/9/94

Рис

"Хуанхэ"

Пекин

Китай

0.44

24/8/94

Харчо

Суп

...

1/9/94

Масло

"Полесье"

Киев

Украина

1.62

27/8/94

Харчо

Суп

...

1/9/94

Зелень

"Наталка"

Киев

Украина

0.88

27/8/94

Шашлык

Горячее

...

1/9/94

Мясо

"Юрмала"

Рига

Латвия

2.05

30/8/94

Шашлык

Горячее

...

1/9/94

Лук

"Полесье"

Киев

Украина

0.61

27/8/94

Шашлык

Горячее

...

1/9/94

Томаты

"Полесье"

Киев

Украина

0.45

27/8/94

Шашлык

Горячее

...

1/9/94

Зелень

"Даугава"

Рига

Латвия

0.99

30/8/94

Кофе

Десерт

...

1/9/94

Кофе

"Хуанхэ"

Пекин

Китай

2.87

24/8/94

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

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

2. Потенциальная противоречивость (аномалии обновления). Вследствие избыточности можно обновить адрес поставщика в одной строке, оставляя его неизменным в других. Если поставщик кофе сообщил о своем переезде в Харбин и была обновлена строка с продуктом кофе, то у поставщика "Хуанхэ" появляется два адреса, один из которых не актуален. Следовательно, при обновлениях необходимо просматривать всю таблицу для нахождения и изменения всех подходящих строк.

3. Аномалии включения. В БД не может быть записан новый поставщик ("Няринга", Вильнюс, Литва), если поставляемый им продукт (Огурцы) не используется ни в одном блюде. Можно, конечно, поместить неопределенные значения в столбцы Блюдо, Вид, Порций и Вес (г) для этого поставщика. Но если появится блюдо, в котором используется этот продукт, не забудем ли мы удалить строку с неопределенными значениями? По аналогичным причинам нельзя ввести и новый продукт (например, Баклажаны), который предлагает существующий поставщик (например, "Полесье"). А как ввести новое блюдо, если в нем используется новый продукт (Крабы)?

4. Аномалии удаления. Обратная проблема возникает при необходимости удаления всех продуктов, поставляемых данным поставщиком или всех блюд, использующих эти продукты. При таких удалениях будут утрачены сведения о таком поставщике.

Многие проблемы этого примера исчезнут, если выделить в отдельные таблицы сведения о блюдах, рецептах, расходе блюд, продуктах и их поставщиках, а также создать связующие таблицы "Состав" и "Поставки" (Рис 1.12).

Блюда Блюдо Вид Лобио Закуска Харчо Суп Шашлык Горячее Кофе Десерт ... ...	Рецепты Блюдо Рецепт Лобио Ломаную очищ ... ...	Расход Блюдо Порций Дата_Р Лобио   1/9/94 Харчо   1/9/94 Шашлык   1/9/94 Кофе   1/9/94 ... ... ...
Продукты Продукт Калор. Фасоль   Лук   Масло   Зелень   Мясо   ... ...	Состав Блюдо Продукт Вес (г) Лобио Фасоль   Лобио Лук   Лобио Масло   Лобио Зелень   Харчо Мясо   ... ... ...	Поставщики Поставщик Город Страна "Полесье" Киев Украина "Наталка" Киев Украина "Хуанхэ" Пекин Китай "Лайма" Рига Латвия "Юрмала" Рига Латвия ... ... ...
Поставки Поставщик Город Продукт Вес (кг) Цена ($) Дата_П "Полесье" Киев Томаты   0.45 27/8/94 "Полесье" Киев Масло   1.62 27/8/94 "Полесье" Киев Лук   0.61 27/8/94 "Наталка" Киев Лук   0.52 27/8/94 ... ... ... ... ... ...

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

Включение. Простым добавлением строк (Поставщики: "Няринга", Вильнюс, Литва) и (Поставки: "Няринга", Вильнюс, Огурцы,) можно ввести информацию о новом поставщике. Аналогично можно ввести данные о новом продукте (Продукты: Баклажаны, 240) и (Поставки: "Полесье", Киев, Баклажаны, 50, …).

Удаление. Удаление сведений о некоторых поставках или блюдах не приводит к потере сведений о поставщиках.

Обновление. В таблицах рис. 1.12 все еще много повторяющихся данных, находящихся в связующих таблицах (Состав и Поставки). Следовательно, в данном варианте БД сохранилась потенциальная противоречивость: для изменения названия поставщика с "Полесье" на "Днепро" придется изменять не только строку таблицы Поставщики, но и множество строк таблицы Поставки. При этом не исключено, что в БД будут одновременно храниться: "Полесье", "Палесье", "Днепро", "Днипро" и другие варианты названий. Кроме того, повторяющиеся текстовые данные (такие как название блюда "Рулет из телячьей грудинки с сосисками и гарниром из разноцветного пюре" или продукта "Колбаса московская сырокопченая") существенно увеличивают объем хранимых данных. Для исключения ссылок на длинные текстовые значения последние обычно нумеруют: нумеруют блюда в больших кулинарных книгах, товары (продукты) в каталогах и т.д. Воспользуемся этим приемом для исключения избыточного дублирования данных и появления ошибок при копировании длинных текстовых значений. (Рис. 1.13).

Блюда БЛ Блюдо Вид   Лобио Закуска   Харчо Суп   Шашлык Горячее   Кофе Десерт ... ... ...	Рецепты Блюдо Рецепт Лобио Ломаную очищ ... ...	Расход Блюдо Порций Дата_Р Лобио   1/9/94 Харчо   1/9/94 Шашлык   1/9/94 Кофе   1/9/94 ... ... ...
Продукты ПР Продукт Калор.   Фасоль     Лук     Масло     Зелень     Мясо   ... ... ...	Состав БЛ ПР Вес (г)                               ... ... ...	Поставщики ПОС Поставщик Город Страна   "Полесье" Киев Украина   "Наталка" Киев Украина   "Хуанхэ" Пекин Китай   "Лайма" Рига Латвия   "Юрмала" Рига Латвия ... ... ... ...
Поставки ПОС ПР Вес (кг) Цена ($) Дата_П       0.45 27/8/94       1.62 27/8/94       0.61 27/8/94       0.52 27/8/94 ... ... ... ... ...

Рис 1.13. Решение проблем потенциальной противоречивости путем введения нумерации текстовых значений

Теперь при изменении названия поставщика "Полесье" на "Днепро" исправляется единственное значение в таблице Поставщики. И даже если оно вводится с ошибкой ("Днипро"), то это не может повлиять на связь между поставщиками и продуктами (в связующей таблице Поставки используются номера поставщиков и продуктов, а не их названия).