Основы проектирования реляционных баз данных

Рис. 3.2. База данных "Питание" (см. п. 2.3)

1. Каждая таблица состоит из  однотипных строк и имеет уникальное  имя.

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

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

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

5. Полное информационное содержание  базы данных представляется в  виде явных значений данных и такой метод представления является единственным. В частности, не существует каких-либо специальных "связей" или указателей, соединяющих одну таблицу с другой. Так, связи между строкой с БЛ = 2 таблицы "Блюда" на рис. 3.2 и строкой с ПР = 7 таблицы продукты (для приготовления Харчо нужен Рис), представляется не с помощью указателей, а благодаря существованию в таблице "Состав" строки, в которой номер блюда равен 2, а номер продукта – 7.

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

3.3. Манипулирование реляционными  данными

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

Предложив реляционную модель данных, Э.Ф.Кодд создал и инструмент для удобной  работы с отношениями – реляционную  алгебру. Каждая операция этой алгебры  использует одну или несколько таблиц (отношений) в качестве ее операндов и продуцирует в результате новую таблицу, т.е. позволяет "разрезать" или "склеивать" таблицы (рис. 3.3).

Рис. 3.3. Некоторые операции реляционной  алгебры

Созданы языки манипулирования  данными, позволяющие реализовать все операции реляционной алгебры и практически любые их сочетания. Среди них наиболее распространены SQL (Structured Query Language – структуризованный язык запросов) и QBE (Quere-By-Example – запросы по образцу) [3, 5]. Оба относятся к языкам очень высокого уровня, с помощью которых пользователь указывает, какие данные необходимо получить, не уточняя процедуру их получения.

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

Глава 4. Введение в проектирование реляционных баз данных

4.1. Цели проектирования

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

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

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

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

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

При проектировании информационной системы  необходимо провести анализ целей этой системы и выявить требования к ней отдельных пользователей (сотрудников организации) [2, 3, 4, 6, 8, 9, 10]. Сбор данных начинается с изучения сущностей организации и процессов, использующих эти сущности (подробнее в приложении Б). Сущности группируются по "сходству" (частоте их использования для выполнения тех или иных действий) и по количеству ассоциативных связей между ними (самолет – пассажир, преподаватель – дисциплина, студент – сессия и т.д.). Сущности или группы сущностей, обладающие наибольшим сходством и (или) с наибольшей частотой ассоциативных связей объединяются в предметные БД. (Нередко сущности объединяются в предметные БД без использования формальных методик – по "здравому смыслу".) Для проектирования и ведения каждой предметной БД (нескольких БД) назначается АБД, который далее занимается детальным проектированием базы.

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

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

4.2. Универсальное отношение

Предположим, что проектирование базы данных "Питание" (рис. 3.2) начинается с выявления атрибутов и подбора данных, образец которых (часть блюд изготовленных и реализованных 1/9/94 г.) показан на рис. 4.1.

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

 

 

Блюдо

Вид

Рецепт

Порций

Дата Р

Продукт

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

Вес (г)

Поставщик

Город

Страна

Вес (кг)

Цена ($)

Дата П

Лобио

Закуска

Лом.

158

1/9/94

Фасоль

3070

200

"Хуанхэ"

Пекин

Китай

250

0.37

24/8/94

Лук

450

40

"Наталка"

Киев

Украина

100

0.52

27/8/94

Масло

7420

30

"Лайма"

Рига

Латвия

70

1.55

30/8/94

Зелень

180

10

"Даугава"

Рига

Латвия

15

0.99

30/8/94

Харчо

Суп

...

144

1/9/94

Мясо

1660

80

"Наталка"

Киев

Украина

100

2.18

27/8/94

Лук

450

30

"Наталка"

Киев

Украина

100

0.52

27/8/94

Томаты

240

40

"Полесье"

Киев

Украина

120

0.45

27/8/94

Рис

3340

50

"Хуанхэ"

Пекин

Китай

75

0.44

24/8/94

Масло

7420

15

"Полесье"

Киев

Украина

50

1.62

27/8/94

Зелень

180

15

"Наталка"

Киев

Украина

10

0.88

27/8/94

Шашлык

Горячее

...

207

1/9/94

Мясо

1660

180

"Юрмала"

Рига

Латвия

200

2.05

30/8/94

Лук

450

40

"Полесье"

Киев

Украина

50

0.61

27/8/94

Томаты

240

100

"Полесье"

Киев

Украина

120

0.45

27/8/94

Зелень

180

20

"Даугава"

Рига

Латвия

15

0.99

30/8/94

Кофе

Десерт

...

235

1/9/94

Кофе

2750

8

"Хуанхэ"

Пекин

Китай

40

2.87

24/8/94

Рис. 4.1. Данные, необходимые  для создания базы данных "Питание"

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

Блюдо

Вид

Рецепт

Порций

Дата Р

Продукт

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

Вес (г)

Поставщик

Город

Страна

Вес (кг)

Цена ($)

Дата П

Лобио

Закуска

Лом.

158

1/9/94

Фасоль

3070

200

"Хуанхэ"

Пекин

Китай

250

0.37

24/8/94

Лобио

Закуска

Лом

108

1/9/94

Лук

450

40

"Наталка"

Киев

Украина

100

0.52

27/8/94

Лобио

Закуска

Лом

108

1/9/94

Масло

7420

30

"Лайма"

Рига

Латвия

70

1.55

30/8/94

Лобио

Закуска

Лом

108

1/9/94

Зелень

180

10

"Даугава"

Рига

Латвия

15

0.99

30/8/94

Харчо

Суп

...

144

1/9/94

Мясо

1660

80

"Наталка"

Киев

Украина

100

2.18

27/8/94

Харчо

Суп

...

144

1/9/94

Лук

450

30

"Наталка"

Киев

Украина

100

0.52

27/8/94

Харчо

Суп

...

144

1/9/94

Томаты

240

40

"Полесье"

Киев

Украина

120

0.45

27/8/94

Харчо

Суп

...

144

1/9/94

Рис

3340

50

"Хуанхэ"

Пекин

Китай

75

0.44

24/8/94

Харчо

Суп

...

144

1/9/94

Масло

7420

15

"Полесье"

Киев

Украина

50

1.62

27/8/94

Харчо

Суп

...

144

1/9/94

Зелень

180

15

"Наталка"

Киев

Украина

10

0.88

27/8/94

Шашлык

Горячее

...

207

1/9/94

Мясо

1660

180

"Юрмала"

Рига

Латвия

200

2.05

30/8/94

Шашлык

Горячее

...

207

1/9/94

Лук

450

40

"Полесье"

Киев

Украина

50

0.61

27/8/94

Шашлык

Горячее

...

207

1/9/94

Томаты

240

100

"Полесье"

Киев

Украина

120

0.45

27/8/94

Шашлык

Горячее

...

207

1/9/94

Зелень

180

20

"Даугава"

Рига

Латвия

15

0.99

30/8/94

Кофе

Десерт

...

235

1/9/94

Кофе

2750

8

"Хуанхэ"

Пекин

Китай

40

2.87

24/8/94

Рис. 4.2. Универсальное  отношение "Питание"

4.3. Почему проект БД может  быть плохим?

Начинающий проектировщик будет  использовать отношение "Питание" (рис. 4.2) в качестве завершенной БД. Действительно, зачем разбивать отношение "Питание" на несколько более мелких отношений (см. например, рис. 3.2), если оно заключает в себе все данные? А разбивать надо потому, что при использовании универсального отношения возникает несколько проблем:

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

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

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

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

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

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

Блюда Блюдо Вид Лобио Закуска Харчо Суп Шашлык Горячее Кофе Десерт ... ...	Рецепты Блюдо Рецепт Лобио Ломаную очищ ... ...	Расход  Блюдо Порций Дата_Р Лобио 158 1/9/94 Харчо 144 1/9/94 Шашлык 207 1/9/94 Кофе 235 1/9/94 ... ... ...
Продукты  Продукт Калор. Фасоль 3070 Лук 450 Масло 7420 Зелень 180 Мясо 1660 ... ...	Состав  Блюдо Продукт Вес (г) Лобио Фасоль 200 Лобио Лук 40 Лобио Масло 30 Лобио Зелень 10 Харчо Мясо 80 ... ... ...	Поставщики  Поставщик Город Страна "Полесье" Киев Украина "Наталка" Киев Украина "Хуанхэ" Пекин Китай "Лайма" Рига Латвия "Юрмала" Рига Латвия ... ... ...
Поставки  Поставщик Город Продукт Вес (кг) Цена ($) Дата_П "Полесье" Киев Томаты 120 0.45 27/8/94 "Полесье" Киев Масло 50 1.62 27/8/94 "Полесье" Киев Лук 50 0.61 27/8/94 "Наталка" Киев Лук 100 0.52 27/8/94 ... ... ... ... ... ...