Доклад: Введение в проектирование реляционных баз данных
Только небольшие организации могут обобществить данные в одной полностью интегрированной базе данных. Чаще всего администратор баз данных (даже если это группа лиц) практически не в состоянии охватить и осмыслить все информационные требования сотрудников организации (т.е. будущих пользователей системы). Поэтому информационные системы больших организаций содержат несколько десятков БД, нередко распределенных между несколькими взаимосвязанными ЭВМ различных подразделений. (Так в больших городах создается не одна, а несколько овощных баз, расположенных в разных районах.)
Отдельные БД могут объединять все данные, необходимые для решения одной или нескольких прикладных задач, или данные, относящиеся к какой-либо предметной области (например, финансам, студентам, преподавателям, кулинарии и т.п.). Первые обычно называют прикладными БД , а вторые – предметными БД (соотносящимся с предметами организации, а не с ее информационными приложениями). (Первые можно сравнить с базами материально-технического снабжения или отдыха, а вторые – с овощными и обувными базами.)
Предметные БД позволяют обеспечить поддержку любых текущих и будущих приложений, поскольку набор их элементов данных включает в себя наборы элементов данных прикладных БД. Вследствие этого предметные БД создают основу для обработки неформализованных, изменяющихся и неизвестных запросов и приложений (приложений, для которых невозможно заранее определить требования к данным). Такая гибкость и приспосабливаемость позволяет создавать на основе предметных БД достаточно стабильные информационные системы, т.е. системы, в которых большинство изменений можно осуществить без вынужденного переписывания старых приложений.
Основывая же проектирование БД на текущих и предвидимых приложениях, можно существенно ускорить создание высокоэффективной информационной системы, т.е. системы, структура которой учитывает наиболее часто встречающиеся пути доступа к данным. Поэтому прикладное проектирование до сих пор привлекает некоторых разработчиков. Однако по мере роста числа приложений таких информационных систем быстро увеличивается число прикладных БД, резко возрастает уровень дублирования данных и повышается стоимость их ведения.
Таким образом, каждый из рассмотренных подходов к проектированию воздействует на результаты проектирования в разных направлениях. Желание достичь и гибкости, и эффективности привело к формированию методологии проектирования, использующей как предметный, так и прикладной подходы. В общем случае предметный подход используется для построения первоначальной информационной структуры, а прикладной – для ее совершенствования с целью повышения эффективности обработки данных.
При проектировании информационной системы необходимо провести анализ целей этой системы и выявить требования к ней отдельных пользователей (сотрудников организации) [2, 3, 4, 6, 8, 9, 10]. Сбор данных начинается с изучения сущностей организации и процессов, использующих эти сущности (подробнее в приложении Б). Сущности группируются по "сходству" (частоте их использования для выполнения тех или иных действий) и по количеству ассоциативных связей между ними (самолет – пассажир, преподаватель – дисциплина, студент – сессия и т.д.). Сущности или группы сущностей, обладающие наибольшим сходством и (или) с наибольшей частотой ассоциативных связей объединяются в предметные БД. (Нередко сущности объединяются в предметные БД без использования формальных методик – по "здравому смыслу".) Для проектирования и ведения каждой предметной БД (нескольких БД) назначается АБД, который далее занимается детальным проектированием базы.
Далее будут рассматриваться вопросы, связанные с проектированием отдельных реляционных предметных БД.
Основная цель проектирования БД – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Так называемый, "чистый" проект БД ("Каждый факт в одном месте") можно создать, используя методологию нормализации отношений. И хотя нормализация должна использоваться на завершающей проверочной стадии проектирования БД, мы начнем обсуждение вопросов проектирования с рассмотрения причин, которые заставили Кодда создать основы теории нормализации.
Универсальное отношение
Предположим, что проектирование базы данных "Питание" (рис. 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.2) в качестве завершенной БД. Действительно, зачем разбивать отношение "Питание" на несколько более мелких отношений (см. например, рис. 3.2), если оно заключает в себе все данные? А разбивать надо потому, что при использовании универсального отношения возникает несколько проблем:
1. Избыточность . Данные практически всех столбцов многократно повторяются. Повторяются и некоторые наборы данных (Блюдо-Вид-Рецепт, Продукт-Калорийность, Поставщик-Город-Страна). Нежелательно повторение рецептов, некоторые из которых намного больше рецепта "Лобио" (см. рис. 2.3). И уж совсем плохо, что все данные о блюде (включая рецепт) повторяются каждый раз, когда это блюдо включается в меню.
2. Потенциальная противоречивость (аномалии обновления) . Вследствие избыточности можно обновить адрес поставщика в одной строке, оставляя его неизменным в других. Если поставщик кофе сообщил о своем переезде в Харбин и была обновлена строка с продуктом кофе, то у поставщика "Хуанхэ" появляется два адреса, один из которых не актуален. Следовательно, при обновлениях необходимо просматривать всю таблицу для нахождения и изменения всех подходящих строк.
3. Аномалии включения . В БД не может быть записан новый поставщик ("Няринга", Вильнюс, Литва), если поставляемый им продукт (Огурцы) не используется ни в одном блюде. Можно, конечно, поместить неопределенные значения в столбцы Блюдо, Вид, Порций и Вес (г) для этого поставщика. Но если появится блюдо, в котором используется этот продукт, не забудем ли мы удалить строку с неопределенными значениями?
По аналогичным причинам нельзя ввести и новый продукт (например, Баклажаны), который предлагает существующий поставщик (например, "Полесье"). А как ввести новое блюдо, если в нем используется новый продукт (Крабы)?
4. Аномалии удаления . Обратная проблема возникает при необходимости удаления всех продуктов, поставляемых данным поставщиком или всех блюд, использующих эти продукты. При таких удалениях будут утрачены сведения о таком поставщике.
Многие проблемы этого примера исчезнут, если выделить в отдельные таблицы сведения о блюдах, рецептах, расходе блюд, продуктах и их поставщиках, а также создать связующие таблицы "Состав" и "Поставки" (рис. 4.3).
Блюда
| Рецепты
| Расход
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Продукты
| Состав
| Поставщики
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Поставки
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Рис. 4.3. Преобразование универсального отношения "Питание" (первый вариант)
Включение . Простым добавлением строк (Поставщики; "Няринга", Вильнюс, Литва) и (Поставки; "Няринга", Вильнюс, Огурцы, 40) можно ввести информацию о новом поставщике. Аналогично можно ввести данные о новом продукте (Продукты; Баклажаны, 240) и (Поставки; "Полесье", Киев, Баклажаны, 50).
Удаление . Удаление сведений о некоторых поставках или блюдах не приводит к потере сведений о поставщиках.
Обновление . В таблицах рис. 4.3 все еще много повторяющихся данных, находящихся в связующих таблицах (Состав и Поставки). Следовательно, в данном варианте БД сохранилась потенциальная противоречивость: для изменения названия поставщика с "Полесье" на "Днепро" придется изменять не только строку таблицы Поставщики, но и множество строк таблицы Поставки. При этом не исключено, что в БД будут одновременно храниться: "Полесье", "Палесье", "Днепро", "Днипро" и другие варианты названий.
Кроме того, повторяющиеся текстовые данные (такие как название блюда "Рулет из телячей грудинки с сосисками и гарниром из разноцветного пюре" или продукта "Колбаса московская сырокопченая") существенно увеличивают объем хранимых данных.
Для исключения ссылок на длинные текстовые значения последние обычно нумеруют: нумеруют блюда в больших кулинарных книгах, товары (продукты) в каталогах и т.д. Воспользуемся этим приемом для исключения избыточного дублирования данных и появления ошибок при копировании длинных текстовых значений (рис. 4.4). Теперь при изменении названия поставщика "Полесье" на "Днепро" исправляется единственное значение в таблице Поставщики. И даже если оно вводится с ошибкой ("Днипро"), то это не может повлиять на связь между поставщиками и продуктами (в связующей таблице Поставки используются номера поставщиков и продуктов, а не их названия).
Блюда
| Рецепты
| Расход
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Продукты
| Состав
| Поставщики
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Поставки
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Рис. 4.4. Преобразование универсального отношения "Питание" (второй вариант)
О нормализации, функциональных и многозначных зависимостях
Нормализация – это разбиение таблицы на две или более, обладающих лучшими свойствами при включении, изменении и удалении данных. Окончательная цель нормализации сводится к получению такого проекта базы данных, в котором каждый факт появляется лишь в одном месте , т.е. исключена избыточность информации. Это делается не столько с целью экономии памяти, сколько для исключения возможной противоречивости хранимых данных.
Как указывалось в п. 3.1, каждая таблица в реляционной БД удовлетворяет условию, в соответствии с которым в позиции на пересечении каждой строки и столбца таблицы всегда находится единственное атомарное значение, и никогда не может быть множества таких значений. Любая таблица, удовлетворяющая этому условию, называется нормализованной (см. таблицы рис. 4.2 – 4.4). Фактически, ненормализованные таблицы, т.е. таблицы, содержащие повторяющиеся группы (см. рис. 4.1), даже не допускаются в реляционной БД.
Всякая нормализованная таблица автоматически считается таблицей в первой нормальной форме , сокращенно 1НФ . Таким образом, строго говоря, "нормализованная" и "находящаяся в 1НФ" означают одно и то же. Однако на практике термин "нормализованная" часто используется в более узком смысле – "полностью нормализованная", который означает, что в проекте не нарушаются никакие принципы нормализации.
Теперь в дополнение к 1НФ можно определить дальнейшие уровни нормализации – вторую нормальную форму (2НФ ), третью нормальную форму (3НФ ) и т.д. По существу, таблица находится в 2НФ, если она находится в 1НФ и удовлетворяет, кроме того, некоторому дополнительному условию, суть которого будет рассмотрена ниже. Таблица находится в 3НФ, если она находится в 2НФ и, помимо этого, удовлетворяет еще другому дополнительному условию и т.д.
Таким образом, каждая нормальная форма является в некотором смысле более ограниченной, но и более желательной , чем предшествующая. Это связано с тем, что "(N+1)-я нормальная форма" не обладает некоторыми непривлекательными особенностями, свойственным "N-й нормальной форме". Общий смысл дополнительного условия, налагаемого на (N+1)-ю нормальную форму по отношению к N-й нормальной форме, состоит в исключении этих непривлекательных особенностей. В п. 4.3 мы выявляли непривлекательные особенности таблицы рис. 4.2 и для их исключения выполняли "интуитивную нормализацию".
Теория нормализации основывается на наличии той или иной зависимости между полями таблицы. Определены два вида таких зависимостей: функциональные и многозначные.
--> ЧИТАТЬ ПОЛНОСТЬЮ <--