Проектирование реляционной базы данных
Задача проектирования БД для предметной области состоит в том, чтобы обеспечить поддержку не только любых ныне используемых, но и будущих приложений. Таким образом, БД создают основу для обработки неформализованных, изменяющихся и неизвестных запросов и приложений, для которых невозможно заранее определить требования к данным. Это позволяет в дальнейшем строить на основе предметных БД достаточно стабильные информационные системы, т. е. системы, в которых большинство изменений можно осуществить без переписывания старых приложений.
Задача проектирования БД — это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Такой проект БД можно создать, используя методологию нормализации отношений.
Универсальное отношение
Рассмотрим задачу проектирования БД на базе сводной таблицы, пример которой приведен на рис. 6.1. Предложенная таблица отражает результаты сдачи сессии (шкала оценок: 0 — незачет; 1 — зачет; 2, 3, 4, 5 — экзаменационная оценка).
ФИО студента | Семестр | Дисциплина | Форма отчетности | Оценка | Количество часов | ФИО преподавателя |
Иванов В. П. | Английский язык | зачет | Цветкова А.Ю. | |||
Математический анализ | зачет | Рыбин К. К. | ||||
Математический анализ | экзамен | Раков И.И. | ||||
Программирование | зачет | Незабудкина З.П. | ||||
Программирование | экзамен | Зайчиков А. А. | ||||
Линейная алгебра | зачет | Волков Г. И. | ||||
Линейная алгебра | экзамен | А | Волков Г.И. | |||
История Отечества | экзамен | Москвин А. П. | ||||
Петрова А Л. | Английский язык | зачет | Цветкова А.Ю. | |||
Математический анализ | зачет | Рыбин К. К. | ||||
Математический анализ | экзамен | Раков И.И. | ||||
Программирование | зачет | Незабудкина З.П. | ||||
Программирование | экзамен | Зайчиков А. А. | ||||
Линейная алгебра | зачет | Волков Г.И. | ||||
Линейная алгебра | экзамен | Волков Г. И. | ||||
История Отечества | экзамен | Москвин А. П. | ||||
Сидоров К.К. | Английский язык | зачет | Цветкова А.Ю. | |||
Математический анализ | зачет | Карпов К.Ю. | ||||
Математический анализ | экзамен | Раков И.И. | ||||
Алгоритмы и структуры данных | экзамен | Зайчиков А. А. | ||||
Теория вероятностей и математическая статистика | экзамен | Соболев И.Г. | ||||
Операционные системы, среды и оболочки | зачет | Незабудкина З.П. | ||||
Операционные системы, среды и оболочки | экзамен | Незабудкина З.П. | ||||
Экономическая теория | зачет | Лабиринтов Е.Н. |
Рис. 13. Исходные данные для создания БД «Сессия»
|
Этот вариант Таблицы «Сессия» не является отношением, так как большинство ее столбцов не атомарны. Атомарными являются лишь значения столбцов «ФИО студента», «Семестр». Остальные столбцы таблицы — множественные.
Для преобразования данных в отношение необходимо реконструировать таблицу с помощью простого процесса вставки, результат которой показан на рис. 14.
|
ФИО студента | Семестр | Дисциплина | Форма отчетности | Оценка | Количество часов | ФИО преподавателя |
Иванов В. П. | Английский язык | зачет | Цветкова А.Ю. | |||
Математический анализ | зачет | Рыбин К. К. | ||||
Математический анализ | экзамен | Раков И.И. | ||||
Программирование | зачет | Незабудкина З.П. | ||||
Программирование | экзамен | Зайчиков А. А. | ||||
Линейная алгебра | зачет | Волков Г. И. | ||||
Линейная алгебра | экзамен | А | Волков Г.И. | |||
История Отечества | экзамен | Москвин А. П. | ||||
Петрова А Л. | Английский язык | зачет | Цветкова А.Ю. | |||
Математический анализ | зачет | Рыбин К. К. | ||||
Математический анализ | экзамен | Раков И.И. | ||||
Программирование | зачет | Незабудкина З.П. | ||||
Программирование | экзамен | Зайчиков А. А. | ||||
Линейная алгебра | зачет | Волков Г.И. | ||||
Линейная алгебра | экзамен | Волков Г. И. | ||||
История Отечества | экзамен | Москвин А. П. | ||||
Сидоров К.К. | Английский язык | зачет | Цветкова А.Ю. | |||
Математический анализ | зачет | Карпов К.Ю. | ||||
Математический анализ | экзамен | Раков И.И. | ||||
Алгоритмы и структуры данных | экзамен | Зайчиков А. А. | ||||
Теория вероятностей и математическая статистика | экзамен | Соболев И.Г. | ||||
Операционные системы, среды и оболочки | зачет | Незабудкина З.П. | ||||
Операционные системы, среды и оболочки | экзамен | Незабудкина З.П. | ||||
Экономическая теория | зачет | Лабиринтов Е.Н. |
|
Рис. 14 Преобразование данных
Такое преобразование приводит к возникновению большого объема избыточных данных.
Таблица на рис. 14 представляет собой корректное отношение. Такое отношение называют универсальным отношением проектируемой БД. В одно универсальное отношение включаются все представляющие интерес атрибуты, и оно может содержать все данные, которые предполагается размещать в БД в будущем. При проектировании некоторых БД универсальное отношение может использоваться в качестве отправной точки.
Однако при использовании универсального отношения возникают, по крайней мере, две проблемы.
1. Избыточность данных. Значения столбцов таблицы многократно повторяются. Повторяются также и некоторые наборы значений столбцов, например, данные о дисциплине.
2. Потенциальная противоречивость. Если при вводе данных, например, количества часов для дисциплины «Английский язык», была допущена ошибка, то для ее исправления необходимо найти все строки, содержащие сведения об этой дисциплине, и во всех этих строках произвести изменения. Более того, при заполнении такой таблицы могут быть использованы различные формы записи одного и того же значения, например: «Англ. язык» и «Английский язык», «Мат. анализ» и «Математический анализ».
Решение этих проблем состоит в разделении данных и связей, т. е. в выделении в отдельные таблицы сведений о студентах, преподавателях, дисциплинах и результатах сдачи экзаменов (рис. 15).
Студенты Преподаватели Дисциплины
№ | ФИО студента | № | ФИО преподавателя | № | Дисциплина | ||
Иванов В. П. | Волков Г. И. | Алгоритмы и структуры данных | |||||
Петрова А.П. | Зайчиков А. А. | Английский язык | |||||
Сидоров К.К. | Карпов К. Ю. | История Отечества | |||||
Лабиринтов Е. Н. | Линейная алгебра | ||||||
Москвин А. П. | Математический анализ | ||||||
Незабудкина З. П. | Операционные системы, среды и оболочки | ||||||
Пиков И. И. | Программирование | ||||||
Рыбин К. К. | Теория вероятностей и математическая статистика | ||||||
Соболев И. Г. | Экономическая теория | ||||||
Цветкова А. Ю. |
Рис. 15 Разделение данных и связей
Заменим в таблицах «Результаты сессии» и «Учебный план» конкретные значения на их номера в других таблицах и получим, помимо значительного упрощения процедуры модификации текстовых значений, дополнительные возможности по включению строк в таблицы «Студенты», «Преподаватели», «Дисциплины», что значительно расширяет возможности БД.
Учебный план
№ | Дисциплина | Семестр | Кол-во часов | Форма отчетности | Преподаватель |
зачет | |||||
экзамен | |||||
зачет | |||||
экзамен | |||||
зачет | |||||
экзамен | |||||
зачет | |||||
экзамен | |||||
зачет | |||||
зачет | |||||
экзамен | |||||
экзамен | |||||
экзамен | |||||
зачет | |||||
экзамен | |||||
зачет |
Результаты сессии
Студент | Учебный план | Оценка |
Рис. 16. Разделение универсального отношения «Сессия»
Теперь при изменении названия «Математический анализ» на «Мат. анализ» исправляется единственное значение в таблице «Дисциплины». И даже если оно вводится с ошибкой, то это не может повлиять на связь между дисциплиной, преподавателем и студентом (в связующей таблице «Результаты сессии» используются номера дисциплин учебного плана, а не их названия).