Следующий этап проектирования — построение даталогической модели. В рассматриваемом случае задача этого этапа — преобразование ER-диаграммы в реляционную схему.
Реляционный подход, в основе которого лежит принцип разделения данных и связей, обеспечивает, с одной стороны, независимость данных, а с другой — более простые способы хранения и обновления.
Первые шаги преобразования состоят в превращении каждой сущности в отношение (таблицу). Связь типа М:М, которую называют «сущность—связь», тоже превращается в отдельное отношение. Каждое свойство становится атрибутом — столбцом соответствующей таблицы.
После реализации этих шагов получаем реляционную схему, изображенную на рис. 19, где представлены таблицы «Студенты», «Сводная ведомость», «Учебный план» и «Кадровый состав», отображающие соответственно сущности «Студент», «Сводная ведомость», «Дисциплина учебного плана» и «Преподаватель».
Далее необходимо преобразовать связи во внешние ключи. Связь «многие ко многим», реализуемая отношением «Сводная ведомость», должна содержать уникальные идентификаторы сущностей — участников связи. При этом, если для однозначной идентификации студента достаточно добавить в таблицу столбец ID_Cmyдент, то однозначная идентификация дисциплины потребует добавления в таблицу столбцов Наименование, Семестр и Форма_отчетности. Хранение всей этой информации явно приведет к избыточности данных и их потенциальной противоречивости (например, если при переносе дисциплины на другой семестр обновить только строку таблицы «Учебный план», то содержимое таблицы «Сводная ведомость» станет неактуальным).
Рис. 19. Реляционная схема после первого этапа преобразования
Для ликвидации избыточности и потенциальной противоречивости данных добавим в таблицу «Учебный план» столбец ID_ План, содержимое которого будет однозначно идентифицировать каждую строку таблицы. Теперь этот новый столбец станет первичным ключом, и одноименный столбец должен быть добавлен в таблицу «Сводная ведомость».
Связь «Читает» предполагает добавление в таблицу «Учебный план» столбца ID Преподаватель. Реляционная схема со связями представлена на рис. 20.
Рис. 20. Реляционная схема со связями
Нормализация таблиц
Все построенные таблицы находятся в первой нормальной форме, так как каждый столбец таблицы неделим и в рамках одной таблицы нет столбцов с одинаковыми по смыслу значениями.
Таблица «Сводная ведомость» через столбцы ID _Студент и ID_ План связывает информацию о студенте с информацией о конкретной дисциплине и фиксирует оценку, полученную студентом. Оценка и дата сдачи экзамена (зачета) однозначно зависят от содержимого столбцов ID_ Студент и ID_ План, которые представляют собой составной первичный ключ. Таким образом, все таблицы имеют первичные ключи, которые однозначно определяют строки и неизбыточны, и можно говорить о том, что таблицы находятся во второй нормальной форме.
Рассмотрим подробнее таблицу «Учебный_план», которая содержит перечень дисциплин текущего учебного плана. Первичным ключом таблицы служит столбец ID_ План, который однозначно характеризует каждую дисциплину учебного плана с точностью до семестра, т. е. для дисциплин, протяженность изучения которых более одного семестра, в таблице будет отведено столько строк, сколько семестров длится изучение дисциплины. Тогда хранение наименований дисциплин в таблице «Учебный_план» становится избыточным: например, если изучение английского языка длится шесть семестров, то наименование «Английский язык» будет повторено в шести записях и есть вероятность сделать шесть различных ошибок при вводе одного и того же наименования.
Чтобы избежать этого, проведем декомпозицию отношения «Учебный план», выделив наименования дисциплин в отдельное отношение. В результате получим дополнительную таблицу «Дисциплины» со столбцами ID _Дисциплина и Наименование, а столбец Наименование в таблице «Учебный_план» заменим столбцом ID_Д исциплина, сформировав тем самым вторичный ключ, связывающий новую таблицу с таблицей «Учебный_план».
Таблица «Студенты»
Наименование столбца | Тип данных | Ограничения |
ID_Студент | Целое число | Значение уникально |
Фамилия | Строка символов размером 30 | Значение не должно быть пустым |
Имя | Строка символов размером 15 | Значение не должно быть пустым |
Отчество | Строка символов размером 20 | Значение не должно быть пустым |
Номер группы | Целое число | Значение не должно быть пустым |
Адрес | Строка символов размером 30 | |
Телефон | Строка символов размером 8 |
Таблица «Дисциплины»
Наименование столбца | Тип данных | Ограничения |
ID_Дисциплина | Целое число | Значение уникально |
Наименование | Строка символов размером 20 | Значение уникально |
Таблица «Кадровый_состав»
Наименование столбца | Тип данных | Ограничения |
ID_Преподаватель | Целое число | Значение уникально |
Фамилия | Строка символов размером 30 | Значение не должно быть пустым |
Имя | Строка символов размером 15 | Значение не должно быть пустым |
Отчество | Строка символов размером 20 | Значение не должно быть пустым |
Должность | Строка символов размером 20 | Значение не должно быть пустым |
Кафедра | Строка символов размером 3 | Значение не должно быть пустым |
Адрес | Строка символов размером 30 | |
Телефон | Строка символов размером 8 |
Таблица «Учебный план»
Наименование столбца | Тип данных | Ограничения |
ID_План | Целое число | Значение уникально |
ID_Дисциплина | Целое число | Значение не должно быть пустым |
Семестр | Целое число | Значение не должно быть пустым и должно находиться в интервале от 1 до 10 |
Количество часов | Целое число | |
ID_Преподаватель | Целое число |
Таблица «Сводная ведомость»
Наименование столбца | Тип данных | Ограничения |
ID_Студент | Целое число | Значение не должно быть пустым |
ID_План | Целое число | Значение не должно быть пустым |
Оценка | Целое число | Значение не должно быть пустым и должно находиться в интервале от 0 до 5 |
Дата сдачи | Дата-время | Значение не должно быть пустым, по умолчанию — текущая дата |