6.5.2. Построение реляционной схемы

Следующий этап проектирования – построение даталогической мо­дели. В рассматриваемом случае задача этого этапа — преобразова­ние ER-диаграммы в реляционную схему (см. п. 5.5.1).

Реляционный подход, в основе которого лежит принцип разделения данных и связей, обеспечивает, с одной стороны, независимость данных, а с другой – более простые способы хранения и обновления.

Первые шаги преобразования состоят в превращении каждой сущности в отношение (таблицу). Связь типа М:М, которую называют «сущность- связь», тоже превращается в отдельное отношение. Каждое свойство становится атрибутом – столбцом соответствую­щей таблицы.

После реализации этих шагов получаем реляционную схему, изображенную на рис.6.7, где представлены таблицы «Студенты», «Сводная ведомость», «Учебный план» и «Кадровый состав», отображающие соответственно сущности «Студент», «Сводная ведо­мость», «Дисциплина учебного плана» и «Преподаватель».

Рис. 6.7. Реляционная схема после первого этапа преобразования

Далее необходимо преобразовать связи во внешние ключи. Связь «многие ко многим», реализуемая отношением «Сводная ве­домость», должна содержать уникальные идентификаторы сущно­стей — участников связи. При этом, если для однозначной иденти­фикации студента достаточно добавить в таблицу столбец ID_Cmyдент, то однозначная идентификация дисциплины потребует добавления в таблицу столбцов Наименование, Семестр и Фор­ма отчетности. Хранение всей этой информации явно приведет к избыточности данных и их потенциальной противоречивости (на­пример, если при переносе дисциплины на другой семестр обновить только строку таблицы «Учебный план», то содержимое таблицы «Сводная ведомость» станет неактуальным).

Для ликвидации избыточности и потенциальной противоречи­вости данных добавим в таблицу «Учебным план» столбец ID_План, содержимое которого будет однозначно идентифицировать каждую строку таблицы. Теперь этот новый столбец станет первичным клю­чом, и одноименный столбец должен быть добавлен в таблицу «Сводная ведомость».

Связь «Читает» предполагает добавление в таблицу «Учебный план» столбца ID_Преподаватель. Реляционная схема со связями представлена на рис. 6.8.

Рис. 6.8. Реляционная схема со связями

6.5.3. Нормализация таблиц

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

Таблица «Сводная ведомость» через столбцы ID_Студент и ID_План связывает информацию о студенте с информацией о кон­кретной дисциплине и фиксирует оценку, полученную студентом. Оценка и дата сдачи экзамена (зачета) однозначно зависят от содер­жимого столбцов ID_Студент и ID_План, которые представляют собой составной первичный ключ. Таким образом, все таблицы имеют первичные ключи, которые однозначно определяют строки и неизбыточны, и можно говорим, о том, что таблицы находятся во второй нормальной форме.

Рассмотрим подробнее таблицу «Учебный_план», которая содержит перечень дисциплин текущего учебного плана. Первичным ключом таблицы служит столбец ID_План, который однозначно характеризует каждую дисциплину учебного плана с точностью до семестра, т. е. для дисциплин, протяженность изучения которых более одного семестра, в таблице будет отведено столько строк, сколько семестров длится изучение дисциплины. Тогда хранение наименований дисциплин в таблице «Учебный_план» становится избыточным: например, если изучение английского языка длится шесть се­местров, то наименование «Английский язык» будет повторено в шести записях и есть вероятность сделать шесть различных ошибок при вводе одного и того же наименования.

Чтобы избежать этого, проведем декомпозицию отношения «Учебный план», выделив наименования дисциплин в отдельное от­ношение. В результате получим дополнительную таблицу «Дисцип­лины» со столбцами ID_Дисциплина и Наименование, а столбец На­именование в таблице «Учебный план» заменим столбцом ID__Дисциплина, сформировав тем самым вторичный ключ, связывающий новую таблицу с таблицей «Учебный_план».

Теперь можно говорить о базе данных «Сессия», реляционная схема которой представлена следующими пятью таблицами:

• «Студенты» — содержит по одной строке для каждого из студентов;

• «Учебный_план» — содержит по одной строке для отдельной дисциплины отдельного семестра;

• «Дисциплины» — содержит по одной строке для наименования дисциплины;

• «Сводная_ведомость» — содержит по одной строке дня каждого результата сдачи отдельным студентом отдельной дисциплины;

• «Кадровый_состав» — содержит по одной строке для каждого из преподавателей.

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

Рис. 6.9. Структура базы данных «Сессия»

Все таблицы базы данных «Сессия» находятся в третьей нор­мальной форме:

• каждый столбец таблицы неделим, и в рамках одной таблицы нет столбцов с одинаковыми по смыслу значениями (1НФ);

• первичные ключи однозначно определяют запись и неизбыточны, все поля каждой из таблиц зависят от ее первичного ключа (2НФ);

• значение любого поля, не входящего в первичный ключ, не зависит от значения другого поля, тоже не входящего в пер­вичный ключ (3НФ).

Следующий этап проектирования — определение доменов (ти­пов) данных, хранящихся в столбцах таблиц. Параллельно с задани­ем типа необходимо сформулировать ограничения целостности, связанные с типом, – перечень допустимых значений типа.

Исходя из особенностей данных и их функционального назна­чения, требуется задать способ представления и границы возмож­ных изменений для каждого из столбцов таблиц. При этом необхо­димо ответить на вопрос: данные каких типов должны храниться в столбцах и какова их максимальная длина (например, если в столб­це предполагается хранить процентные значения, то достаточно бу­дет целого типа данных длиной 1 байт, так как диапазон возможных значений — от 0 до 255; если для данных столбца выбирается тип «строка символов», то желательно указать максимальный размер данных столбца и т. п.).

Далее, в каждой таблице должны быть выделены столбцы, которые обязательно должны быть заполнены при создании отдельной строки таблицы. Задание такого ограничения целостности не позволит, например, ввести в таблицу «Студенты» строку, в которой не указан номер группы. Если подобные ограничения целостности не будут заданы, в таблице могут появиться строки, которые не будут учтены при выполнении функций по обработке данных: появление в таблице «Студенты» строки без номера группы приведет к ошибке при формировании ведомости.

Следующий важный момент – задание для столбцов значений по умолчанию. Значение по умолчанию впоследствии будет автоматически вводиться в указанный столбец для каждой строки табли­цы. Например, в столбец Дата_сдачи таблицы «Сводная ведомость» при заполнении очередной строки может автоматически заносится текущая дата.

Ниже, на рис.6.10 представлены таблицы базы данных «Сессия» с типами данных столбцом и предлагаемыми ограничениями цело­стности.

Таблица «Студенты»

Наименование столбца	Тип данных	Ограничения
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
Дата сдачи	Дата-время	Значение не должно быть пустым, по умолчанию – текущая дата

Рис.6.10. Таблицы базы данных «Сессия»