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

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

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

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

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

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

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

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

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

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

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

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

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

 

Физическая модель

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

На слайде (слайд 11) представлены таблицы базы данных «Сессия» с типами данных столбцов и предлагаемыми ограничениями целостности.

Лекция 14 (DB _ l 14. ppt).