Иптышев А.А., Морозов Д.И., Городилов А.А., Иптышев М.А. Базы данных

Подождите немного. Документ загружается.

получить в конечном счете множество таблиц, которые находятся в НФБК и не

содержат каких-либо функциональных зависимостей вида, отличного от А К.

Применим приведенные правила для полной нормализации универсального

отношения «Сессия» (табл.5).

1.Определение первичного ключа таблицы.

Предположим, что каждый студент сдает один раз экзамен (зачет) по

дисциплине учебного плана и получает оценку. Дисциплина учебного плана

однозначно характеризуется наименованием, номером семестра, за который

отчитывается студент, и формой отчетности (так как учебный план предусматривает

сдачу и экзамена, и зачета по одной и той же дисциплине в рамках одного семестра).

Тогда в качестве первичного ключа отношения «Сессия» можно использовать

следующий набор атрибутов:

ФИО студента, Дисциплина, Семестр, Форма отчетности.

2.Выявление атрибутов, функционально зависящих от части составного

ключа.

Каждый из атрибутов – ФИО преподавателя и Количество часов –

функционально зависит только от атрибутов Дисциплина, Семестр и Форма

отчетности, т.е. этот атрибут с совокупностью атрибутов первичного ключа

составит вторую таблицу:

Учебный план (Дисциплина, Семестр, Форма отчетности, Количество

часов и ФИО преподавателя).

Из исходной таблицы при этом удаляются атрибуты Количество часов и

ФИО преподавателя:

Результаты сессии (ФИО студента, Дисциплина, Семестр, Форма

отчетности, Оценка).

Составной первичный ключ, повторяющийся в обеих таблицах, приводит к

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

целесообразным ввести дополнительный атрибут - № (порядковый номер) – в

таблицу «Учебный план» и использовать именно его в качестве первичного ключа.

Тогда таблицы примут следующий вид:

Учебный план (№ Уч. плана, Дисциплина, Семестр, Форма отчетности,

Кол-во часов, ФИО преподавателя).

Результаты сессии (ФИО студента, №Уч. плана, Оценка).

Такой декомпозиции на самом деле достаточно для того, чтобы преобразовать

исходную таблицу к совокупности нормализованных таблиц (обе полученные

таблицы приведены к НФБК), однако полученный проект может быть улучшен

путем введения дополнительных таблиц «Дисциплины», «Студенты» и

«Преподаватели». Далее мы приведем пример проектирования БД не на основе

декомпозиции универсального отношения, а путем построения инфологической

модели предметной области и преобразования ее на логическом уровне в

реляционную модель данных.

4.5. ПРИМЕР ПРОЕКТИРОВАНИЯ РЕЛЯЦИОННЫЙ БД

Рассмотрим следующую задачу: пусть необходимо обеспечить сбор и

обработку данных по результатам сдачи экзаменов и зачетов студентами

факультета. Организация данных должна поддерживать:

1.Выполнение текущего учебного плана;

2.Формирование ведомостей по отдельным дисциплинам для групп студентов;

3.Формирование листов зачетных книжек студентов;

4.Формирование сводной ведомости курса;

5.Расчет среднего бала по дисциплинами т. п.;

Приведем этапы построения инфологической и даталогической моделей (ER-

диаграммы и реляционной схемы) для решения такой задачи.

4.5.1. Построение ER-диаграммы

Представим предметную область как взаимодействие двух сущностей –

«Дисциплина учебного плана» и «Студент»: каждый студент сдает экзамен или

зачет по некоторой дисциплине учебного плана и получает оценку, которая должна

быть зафиксирована в модели данных.

« Дисциплина учебного плана» с точки зрения решаемой задачи должна быть

представлена группой свойств, позволяющих характеризовать дисциплину в рамках

каждого отдельного семестра: наименование дисциплины, семестр, количество

часов, форма отчетности (экзамен или зачет) и данные о преподаватели, читающим

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

стороны, задачей организации хранения результатов сдачи экзаменов и зачетов

(наименование дисциплины, семестр и форма отчетности), и с другой стороны –

задачей формирования листов зачетных книжек (количество часов и данные о

преподавателе). Отдельный экземпляр такой сущности однозначно

идентифицируется тройкой свойств – наименование дисциплины, семестр и форма

отчетности.

Сущность «Студент» для обеспечения выполнения объявленных функций

должна характеризоваться следующими свойствами: фамилия, имя , отчество и

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

не может однозначно характеризовать экземпляр сущности, так как можно

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

идентификации отдельного экземпляра сущности необходимо ввести

дополнительное (ключевое) свойство – идентификационный номер студента.

Определим для сущности «Студент» еще два дополнительных свойства,

которые не будут непосредственно обеспечивать решение поставленный задачи, но

могут служить для реализации дополнительных (сервисных) функций (например,

организации почтовой или телефонной связи): домашний адрес и номер телефона.

Свойство «Домашний адрес», являясь по сути составным, будет на самом деле

рассматриваться в контексте решаемых задач как простое, а свойство «Номер

телефона» - как условное.

Взаимодействие сущностей реализуется связью «Сводная ведомость», т.е.

Студент сдает экзамен (зачет) по Дисциплине учебного плана. Мощность связи –

«многие ко многим» (М: М). Для идентификации связи отдельных экземпляров

сущностей в этом случае необходимо наличие у связи следующих дополнительных

свойств: оценка и дата сдачи экзамена (зачета).

ER-диаграмма рассматриваемой задачи представлена на рис.16.

Построенная ER-диаграмма находится в первой нормальной форме, так как

сущности не имеют повторяющихся групп свойств. Однако при рассмотрении

свойств сущности «Дисциплина учебного плана» можно заметить, что свойство

«Преподаватель» зависит только от части ключевых свойств, - а именно от свойств

«Наименование дисциплины» и, возможно, «Форма отчетности». Следовательно,

для того чтобы привести ER-диаграмму ко второй нормальной форме, необходимо

выделить свойство «Преподаватель» в отдельную сущность.

Новая сущность «Преподаватель» характеризуется группой основных

свойств – фамилия, имя, отчество, домашний адрес и телефон. Так же, как и для

сущности «Студент», для сущности «Преподаватель» необходимо ввести

дополнительное (ключевое) свойство – идентификационный номер преподавателя.

Взаимодействие новой сущности с сущностью «Дисциплина учебного

плана» осуществляется посредством новой связи «Читает». Мощность связи –

«Многие к одному» (М:1), т.е. несколько дисциплин учебного плана может читать

один преподаватель.

Рис. 16. ER-диаграмма рассматриваемой задачи.

Идентификационный

номер

Фамилия

Семестр

Форма отчетности

Преподаватель

Имя

Количес

тво часов

Отчество

Номер группы

Домашний адрес

Телефон

Наименование

Дисциплина

учебного

Студент

Сводная

ведомост

Оценка

Дата

Измененная ER-диаграмма представлена на рис.17. Новый вариант ER-

диаграммы находится в третьей нормальной форме, так как сущности не имеют

свойств, зависящих от неключевых.

Рис.17.Нормализованная ER – диаграмма.

Идентификационный номер

Фамилия

Семестр

Форма отчетности

Имя

Количество часов

Отчество

Номер группы

Домашний адрес

Телефон

Наименование дисциплины

Дисциплина

учебного класса

Студент

Сводная

ведомость

Читает

Преподаватель

Отчество

Кафедра

Должность

Домашний

адрес

Телефон

Имя

Фамилия

Идентификацио

нный номер

Оценка

Дата сдачи

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

Следующий этап проектирования – построение датологической модели. В

рассматриваемом случае задача этого этапа – преобразование ER-диаграммы в

реляционную схему.

Реляционный подход, в основе которого лежит принцип разделения данных и

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

простые способы хранения и обновления.

Первые шаги преобразования состоят в превращении каждой сущности в

отношение (таблицу). Связь типа М: М, которую называют «сущность – связь»,

тоже превращается в отдельное отношение. Каждое свойство становится атрибутом

– столбцом соответствующей таблицы. После реализации этих шагов получаем

реляционную схему, изображенную на рис. 18, где представлены таблицы

«Студенты», «Сводная ведомость», «Учебный план» и «Кадровый состав»,

отображающие соответственно сущности «Студент», «Сводная ведомость»,

«Дисциплина учебного плана» и «Преподаватель».

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

Далее необходимо преобразовать связи во внешние ключи. Связь «Многие ко

многим», реализуемая отношением «Сводная ведомость», должна содержать

уникальнве идентификаторы сущностей – участников связи. При этом, если для

однозначной идентификации студента достаточно добавить в таблицу столбец

ID_Студент, то однозначная идентификация дисциплины потребует добавления в

таблицу столбцов Наименование, Семестр и Форма отчетности. Хранение всей

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

противоречивости (например, если при переносе дисциплины на другой семестр

обновить только строку таблицы «Учебный план», то содержимое таблицы

«Сводная ведомость» станет неактуальным).

Для ликвидации избыточности и потенциальной противоречивости данных

добавим в таблицу «Учебный план» столбец ID_План, содержимое которого будет

однозначно идентифицировать каждую строку таблицы. Теперь этот новый столбец

Оценка

Дата сдачи

Наименование

Семестр

Количество часов

Форма отчетности

ID_Студент

Фамилия

Отчество

Имя

Номер группы

Телефон

Адрес

Студенты

Сводная ведомость

Учебный план

ID_Преподаватель

Фамилия

Имя

Отчество

Кафедра

Должность

Адрес

Телефон

Кадровый состав

станет первичным ключом, и одноименный столбец должен быть добавлен в

таблицу «Сводная ведомость».

Связь «Читает» предполагает добавление в таблицу «Учебный план»

столбца ID_Преподаватель. Реляционная схема со связями представлена на рис.19.

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

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

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

каждый столбец таблицы неделим и в рамках одной таблицы нет столбцов с

одинаковыми по смыслу значениями.

Таблица «Сводная ведомость» через столбцы ID_Студент и ID_План

связывает информацию о студенте с информацией, о конкретной дисциплине и

фиксирует оценку, полученную студентом. Оценка и дата сдачи экзамена (зачета)

однозначно зависят то содержимого столбцов ID_Студент и ID_План, которые

представляют собой составной первичный ключ. Таким образом, все таблицы имеют

первичные ключи, которые однозначно определяют строки и неизбыточны, и можно

говорить о том, что таблицы находятся во второй нормальной форме.

Рассмотрим подробнее таблицу «Учебный план», которая содержит перечень

дисциплин текущего учебного плана. Первичным ключом таблицы служит столбец

ID_Студент

Фамилия

Отчество

Имя

Номер группы

Телефон

Адрес

Студенты

Сводная ведомость

Оценка

Дата сдачи

ID_Студент (FK)

ID_План (FK)

ID_Преподаватель

Фамилия

Имя

Отчество

Кафедра

Должность

Адрес

Телефон

Кадровый состав

ID_План

Наименование

Семестр

Количество часов

Форма отчетности

ID_Преподаватель (FK)

Учебный план

ID_План, который характеризует каждую дисциплину учебного плана с точностью

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

семестра, в таблице будет отведено несколько строк, сколько семестров длиться

изучение дисциплины. Тогда хранение наименований дисциплин в таблице

«Учебный_план» становится избыточным: например, если изучение английского

языка длится шесть семестров, то наименование «Английский язык» будет

повторно в шести записях и есть вероятность сделать шесть различных ошибок при

вводе одного и того же наименования.

Чтобы избежать этого, приведем декомпозицию отношения «Учебный план»,

выделив наименования дисциплин в отдельное отношение. В результате получим

дополнительную таблицу «Дисциплины» со столбцами ID_Дисциплина и

Наименование, а столбец Наименование в таблице «Учебный план» заменим

столбцом ID_Дисциплина, сформировав тем самым вторичный ключ, связывающий

новую таблицу с таблицей «Учебный план».

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

представлена следующими пятью таблицами:

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

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

отдельного семестра;

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

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

сдачи отдельным студентом отдельной дисциплины;

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

преподавателей.

6.В графической форме (рис. 20) изображены перечисленные таблицы, их

столбцы, первичные и внешние ключи. Задание первичных и внешних ключей

сопровождается построением дополнительных структур – индексов,

обеспечивающих быстрый доступ к данным через значение ключа.

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

8.Каждый столбец таблицы неделим, и в рамках одной таблицы нет столбцов с

одинаковыми по смыслу значениями (1НФ).

9.Первичные ключи однозначно определяют запись и неизбыточны, все поля

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

10.Значение любого поля, не входящего в первичный ключ, не зависит от

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

Следующий этап проектирования – определение доменов (типов) данных,

хранящихся в столбцах таблиц. Параллельно с заданием типа необходимо

сформулировать ограничения целостности связанные с типом, - перечень

допустимых значений типа.

Исходя из особенностей данных и их функционального назначения, требуется

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

столбцов таблиц. При этом необходимо ответить на вопрос: данные каких типов

должны храниться в столбцах и какова их максимальная длина (например, если в

столбце предполагается, хранит процентные значения, то достаточно будет целого

типа данных длиной 1 байт, так как диапазон возможных значений – от 0 до 255;

если для данных столбца выбирается тип «строка символов», то желательно указать

максимальный размер данных столбца и т.п.)

Далее, в каждой таблице должны быть выделены столбцы, которые

обязательно должны быть заполнены при создании отдельной строки таблицы.

Задание такого ограничения целостности не позволит, например, ввести в таблицу

«Студенты» строку, в которой не указан номер группы. Если подобные

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

которые не будут учтены при выполнении функций по обработке данных: появление

в таблице «Студенты» строки без номера группы приведет к ошибке при

форматировании ведомости.

Следующий важный момент – задание для столбцов значений по умолчанию.

Значение по умолчанию впоследствии будет автоматически, вводится в указанный

столбец для каждой строки таблицы. Например, в столбец Дата_сдачи таблицы

«Сводная ведомость» при заполнении очередной строки может автоматически

заноситься текущая дата.

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

данных столбцов и предлагаемыми ограничениями целостности.

Все примеры использования языка SQL (Structured Query Language),

рассматриваемые в следующей главе, будут построены на основе этой учебной базы

данных «Сессия»

Таблица. 7

”Студенты”

Наименование

столбца

Тип данных Ограничения

ID_Студент Целое число Значение уникально

Фамилия

Строка символов размером 30

Значение не должно быть пустым

Имя Строка символов размером 15 Значение не должно быть пустым

Отчество

Строка символов размером 20

Значение не должно быть пустым

Номер группы

Целое число

Значение не должно быть пустым

Адрес Строка символов размером 30

Телефон

Строка символов размером 8

Таблица. 8

”Дисциплины”

Наименование

столбца

Тип данных Ограничения

_Дисциплина

Целое число

Значение уникально

Наименование ID_Дисциплина Значение уникально

Таблица. 9

”Кадровый состав”

Наименование столбца

Тип данных

Ограничения

ID_Преподаватель Целое число Значение уникально

Фамилия Строка символов размером 30 Значение не должно быть пустым

Имя Строка символов размером 15 Значение не должно быть пустым

Отчество

Строка символов размером 20

Значение не должно быть пустым

Должность Строка символов размером 20 Значение не должно быть пустым

Кафедра

Строка символов размером 3

Значение не должно быть пустым

Адрес Строка символов размером 30

Телефон

Строка символов размером 8

Таблица. 10

”Учебный план”

Наименование столбца

Тип данных

Ограничения

ID_План Целое число Значение уникально

_Дисциплина

Целое число

Значение не должно быть пустым

Семестр Целое число Значение не должно быть пустым и

должно находиться в интервале от 1 до 10

Количество часов Целое число

ID_Преподаватель Целое число

Таблица. 11

Сводная ведомость”

Наименование

столбца

Тип данных Ограничения

ID_Студент Целое число Значение не должно быть пустым

_План

Целое число

Значение не должно быть пустым

Оценка Целое число Значение не должно быть пустым и должно

находиться в интервале от 0 до 5

Дата сдачи Дата - время Значение не должно быть пустым, по умолчанию

–

текущая дата.

Рис. 20.Таблицы базы данных ”Сессия”

5. ДОСТУП К ЛОКАЛЬНЫМ БАЗАМ ДАННЫХ, ТАБЛИЦАМ,

ЗАПИСЯМ

5.1. ОРГАНИЗАЦИЯ СВЯЗИ С БАЗАМИ ДАННЫХ В DELPHI.

Основной работы Delphi с базами данных является Borland Database Engine

(BDE) - процессор баз данных фирмы Borland. BDE служит посредником между

приложением и базами данных. Он предоставляет пользователю единый интерфейс

для работы, развязывающий пользователя от конкретной реализации базы данных и

берёт на себя подавляющее большинство стандартных низкоуровневых операций

работы с базами данных. Благодаря этому не надо менять приложение при смене

реализации базы данных. Приложение Delphi никогда не обращается

непосредственно к базе данных, а только к BDE. Таким образом, общение с базами

данных соответствует схеме, приведенной на рис.21.

Приложение Delphi, когда ему нужно связаться с базой данных, обращается к

BDE и сообщает обычно псевдоним базы данных и необходимую таблицу в ней.

BDE реализован в виде динамически присоединяемых библиотек к DLL (файлы

IDAPI01.DLL, IDAPI32.DLL). Они, как и любые библиотеки, снабжены API

(Application Program Interfase - интерфейсом прикладных программ), названным

IDAPI (Integrated Database Application Program Interfase). Это список процедур и

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

BDE по псевдониму находит подходящий для указанной базы данных драйвер.

Драйвер - это вспомогательная программа, которая понимает, как общаться с базами

данных определенного типа. Если в BDE имеется собственный драйвер

соответствующий СУБД, то BDE связывается через него с базой данных и с нужной

таблицей в ней, обрабатывает запрос пользователя и возвращает в приложение

результаты обработки. BDE поддерживает естественный доступ к таким базам

данных, как Microsoft Access, FoxPro, Paradox и dBase.

Если собственного драйвера нужной СУБД в BDE нет, то используется

драйвер ODBC. ODBC (Open Database Connectivity) – DLL, аналогичная функциям

BDE, но разработанная фирмой Microsoft. Она хранится в файле ODBC. DLL.

Поскольку Microsoft включила поддержку ODBC в свои офисные продукты и для

ODBC созданы драйверы практически к любым СУБД, фирма Borland включила в

BDE драйвер, позволяющий использовать ODBC.

BDE поддерживает SQL- стандартизованный язык запросов, позволяющий

обмениваться данными с SQL – серверами, такими, как Sybase, Microsoft SQL,

Oracle, Interbase. Эта возможность используется особенно широко при работе на

платформе клиент/ сервер.