Основные этапы проектирования баз данных
Цель и задачи проектирования
Только небольшие организации могут обобщить данные в одной полностью интегрированной базе данных. Чаще всего администратор баз данных (даже если это группа лиц) практически не в состоянии охватить все информационные требования сотрудников организации (т.е. будущих пользователей системы). Поэтому информационные системы крупных организаций содержат несколько десятков БД, как правило, распределенных между несколькими взаимосвязанными ЭВМ различных подразделений.
Основывая проектирование БД на текущих и предвидимых приложениях, можно существенно ускорить создание высокоэффективной информационной системы. Желание достичь и гибкости, и эффективности привело к формированию методологии проектирования, использующей как предметный, так и прикладной подходы. В общем случае предметный подход используется для построения первоначальной информационной структуры, а прикладной – для ее совершенствования с целью повышения эффективности обработки данных.
Основная цель проектирования БД – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте.
Так называемый, "чистый" проект можно создать, используя методологию нормализации отношений. Однако нормализация должна использоваться на завершающей проверочной стадии проектирования БД в силу трудоемкости процесса. На начальных стадиях проектирования, как правило, используют метод «сущность-связь».
На начальном этапе проектирования информационной системы необходимо провести анализ целей этой системы и выявить требования к ней отдельных пользователей (сотрудников организации).
Сбор данных начинается с изучения сущностей организации и процессов, использующих эти сущности. Сущности группируются по "сходству" (частоте их использования для выполнения тех или иных действий) и по количеству ассоциативных связей между ними (самолет – пассажир, преподаватель – дисциплина, студент – сессия и т.д.). Сущности или группы сущностей, обладающие наибольшим сходством и (или) с наибольшей частотой ассоциативных связей объединяются в предметные БД.
Этапы проектирования
Рис. 7. Этапы проектирования структуры
БД
Проектирование баз данных с использованием нормализации
Реляционные базы данных должны быть максимально просты и обозримы. В таблицах не допускается вложенность, т.е. каждый столбец является неделимым. Следовательно, первым этапом проектирования базы данных является разделение сложной таблица на ряд простых. В теории нормализации это называется приведением к первой нормальной форме.
Пусть исходная таблица имеет вид:
Табельный номер |
Ф. И. О. |
Профессиональная подготовка |
||
Специальность |
Разряд |
Стаж |
||
000001111 |
Иванов И.И. |
Слесарь Токарь Фрезеровщик |
5 4 6 |
8 5 12 |
000002222 |
000002222 |
Токарь |
3 |
3 |
Столбец “Профессиональная подготовка” является составным. Он содержит повторяющуюся группу данных и сам является таблицей. С такой структурой данных реляционные СУБД не работают. Причина – неопределенная длина записи. Необходимо выделять повторяющиеся группы в отдельные таблицы
Табельный номер |
Фамилия, И. О. |
|
||||
000001111 |
Иванов И.И. |
|
||||
000002222 |
Петров П.П. |
|
||||
|
|
|
|
|||
|
|
|
||||
|
|
|
||||
|
|
|
||||
|
|
|
||||
Табельный номер |
Специальность |
Разряд |
Стаж |
000001111 |
Слесарь |
5 |
8 |
000001111 |
Токарь |
4 |
5 |
000001111 |
Фрезеровщик |
6 |
12 |
000002222 |
Токарь |
3 |
3 |
Межтабличная связь осуществляется по ключевому полю. Как правило, для таблиц разрабатываются экранные формы ввода данных и выходные формы для представления отчетных данных. После приведения к первой нормальной форме становится ясным общее количество таблиц, что позволяет оценить хотя бы приблизительно объем и сложность работы.
В любой таблице имеется один или несколько ключевых столбцов, от которых зависят все остальные столбцы. Ключ служит для поиска данных в таблице и не может иметь значение “не определено”. В рассмотренном выше примере ключом является столбец “Табельный номер”. Если ключ таблицы состоит из нескольких столбцов, необходимо провести проверку на наличие неполных функциональных зависимостей. При неполной функциональной зависимости значение в неключевом столбце определяется не всем ключом, а его частью. Рассмотрим таблицу “Расписание” при кабинетной системе обучения, когда за предметом жестко закреплена аудитория:
Предмет |
Лектор |
Группа |
День |
Время |
Ауд. |
Математика |
Иванов И.И. |
ПС-104 |
Понедельник |
8.00 |
415 |
Физика |
Петров П.П. |
ПС-104 |
Понедельник |
9.45 |
708 |
Физика |
Петров П.П. |
ИС-111 |
Понедельник |
8.00 |
708 |
Математика |
Иванов И.И. |
ИС-111 |
Понедельник |
9.45 |
415 |
Математика |
Иванов И.И. |
ПС-104 |
Среда |
8.00 |
415 |
Физика |
Петров П.П. |
ПС-104 |
Среда |
9.45 |
708 |
Физика |
Петров П.П. |
ИС-111 |
Среда |
8.00 |
708 |
Математика |
Иванов И.И. |
ИС-111 |
Среда |
9.45 |
415 |
В этой таблице ключом является совокупность первых трех столбцов (выделены жирным шрифтом). Однако столбец “Аудитория” зависит только от столбца “Предмет”. Это ведет к избыточности: сведения о том, где читаются математика и физика повторяются столько раз, сколько занятий проводится. При корректировке данных (например, изменении аудитории) избыточность также нежелательна.
Отрицательное влияние неполной функциональной зависимости не исчерпывается затратами ресурсов. Возникают аномалии удаления и включения, делающие систему нединамичной. Если в некоторый период занятия по математике не проводятся, они должны быть исключены из расписания. При этом потеряется информация об аудитории, закрепленной за предметом. При отсутствии занятий нельзя также добавить сведения о том, что кабинет химии расположен в аудитории 322. Это объясняется тем, что значения ключевых полей каждой записи не могут быть неопределенными. Сведения о предмете и аудитории могут присутствовать только вместе со сведениями о лекторе и группе.
Выходом из положения является разбиение исходной таблицы так, чтобы исключить неполную функциональную зависимость. Такое разбиение, при соблюдении ряда дополнительных условий, называется приведением ко второй нормальной форме. В излагаемом примере разбиение будет иметь вид:
Предмет |
Ауд. |
Математика |
415 |
Физика |
708 |
Предмет |
Лектор |
Группа |
День |
Время |
Математика |
Иванов И.И. |
ПС-104 |
Понедельник |
8.00 |
Физика |
Петров П.П. |
ПС-104 |
Понедельник |
9.45 |
Физика |
Петров П.П. |
ИС-111 |
Понедельник |
8.00 |
Математика |
Иванов И.И. |
ИС-111 |
Понедельник |
9.45 |
Математика |
Иванов И.И. |
ПС-104 |
Среда |
8.00 |
Физика |
Петров П.П. |
ПС-104 |
Среда |
9.45 |
Физика |
Петров П.П. |
ИС-111 |
Среда |
8.00 |
Математика |
Иванов И.И. |
ИС-111 |
Среда |
9.45 |
При разбиении используется операция получения проекции. Разбиение должно производиться так, чтобы исходное отношение было восстановимо естественным соединением проекций. Вторая нормальная форма с минимальным числом проекций называется оптимальной.
Анализ функциональных зависимостей позволил выделить еще один вид зависимостей, порождающий некорректности, подобные вышеизложенным. Это транзитивные зависимости, имеющие форму цепочки. Пусть таблица имеет вид:
Лектор |
Организация |
Тип организации |
Иванов И.И. |
Полимер |
НИИ |
Петров П.П. |
ЮУИЭИУ |
ВУЗ |
Сидорова С.С. |
ЮУИЭИУ |
ВУЗ |
Рязанцев Р.Р. |
Полимер |
НИИ |
Яковлев Я.Я. |
Полимер |
НИИ |
Здесь налицо избыточность и аномалии удаления и включения. Нельзя сохранить или добавить информацию о типе организации, если ни один из ее сотрудников не читает лекции. Причина такого явления в том, что неключевой столбец “Тип организации” зависит от ключа “Лектор” не напрямую, а транзитивно через столбец “Наименование организации”.
Некорректности устраняются разбиением исходной таблицы т.е. приведением к третьей нормальной форме. При разбиении используется операция получения проекции. Разбиение должно производиться так, чтобы исходное отношение было восстановимо естественным соединением проекций. Третья нормальная форма с минимальным числом проекций называется оптимальной.
Лектор |
Организация |
|
|
Иванов И.И. |
Полимер |
|
|
Петров П.П. |
ЮУИЭИУ |
|
|
Сидорова С.С. |
ЮУИЭИУ |
|
|
Рязанцев Р.Р. |
Полимер |
|
|
Яковлев Я.Я. |
Полимер |
|
|
|
|
|
|
Организация |
Тип организации |
||
Полимер |
НИИ |
||
ЮУИЭИУ |
ВУЗ |
||
Кроме функциональных зависимостей в таблицах могут иметься многозначные зависимости, т.е. зависимости типа “Многие ко многим”. Пусть имеются два несвязанных многозначных факта:
Присутствие в одной таблице этих фактов порождает проблемы представления данных. Возможны варианты представления:
1) расчлененный формат
Служащий |
Проекты |
Друзья |
Иванов И.И. |
Проект 1 |
|
Иванов И.И. |
Проект 2 |
|
Иванов И.И. |
Проект 3 |
|
Иванов И.И. |
|
Петров |
Иванов И.И. |
|
Сидоров |
2) смешанный формат
минимум записей с пустыми значениями
Служащий |
Проекты |
Друзья |
Иванов И.И. |
Проект 1 |
Петров |
Иванов И.И. |
Проект 2 |
Сидоров |
Иванов И.И. |
Проект 3 |
|
минимум записей с повторами
Служащий |
Проекты |
Друзья |
Иванов И.И. |
Проект 1 |
Петров |
Иванов И.И. |
Проект 2 |
Сидоров |
Иванов И.И. |
Проект 3 |
Петров |
без ограничений
Служащий |
Проекты |
Друзья |
Иванов И.И. |
Проект 1 |
Петров |
Иванов И.И. |
Проект 2 |
Сидоров |
Иванов И.И. |
Проект 3 |
|
Иванов И.И. |
|
Сидоров |
3) “гибрид” всех вариантов
Служащий |
Проекты |
Друзья |
Иванов И.И. |
Проект 1 |
Петров |
Иванов И.И. |
Проект 1 |
Сидоров |
Иванов И.И. |
Проект 2 |
Сидоров |
Иванов И.И. |
Проект 2 |
Петров |
Иванов И.И. |
Проект 3 |
Петров |
Иванов И.И. |
Проект 3 |
Сидоров |
Проблемы в рассмотренных таблицах аналогичны описанным выше. Имеется избыточность, опасность потери данных. Недопустимо также произвольное дублирование значений или их неопределенность, неоднозначность представления данных.
Выходом из положения является приведение к четвертой нормальной форме, согласно которой в таблице не может иметься два и более несвязанных многозначных факта. В рассмотренном примере корректные таблицы будут иметь следующий вид:
Служащий |
Проекты |
Иванов И.И. |
Проект 1 |
Иванов И.И. |
Проект 2 |
Иванов И.И. |
Проект 3 |
|
|
Служащий |
Друзья |
Иванов И.И. |
Петров |
Иванов И.И. |
Сидоров |
Если многозначные факты связаны между собой, например, для выполнения работы над проектом привлекались друзья, указанные выше проблемы не возникают.
Увеличение количества таблиц усложняет систему и, в ряде случаев, ведет к увеличению времени ответа на запрос. Решение о том, что важнее: безызбыточность, однозначность представления данных, отсутствие аномалий удаления и включения или простота организации базы данных и высокая скорость выборки всей совокупности данных принимает разработчик, исходя из приоритетов предметной области.