Проектирование структуры базы данных

 

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

Имеются три сущности предметной области: Корпуса, Аудитории и Сотрудники. Определим связи между этими двумя сущностями. Связь «Корпуса – Аудитории» имеет тип «один ко многим». Аудитория идентифицируется номером аудитории и названием корпуса. Так как номер уникален только в пределах одного корпуса, то сочетание значений названия корпуса и номера аудитории в этом корпусе уникально для каждой аудитории. Следовательно, в одном корпусе может быть несколько аудиторий, но одна конкретная аудитория находится только в одном корпусе.

Связь «Аудитории – Сотрудники» имеет тип «многие к одному», так как к каждой аудитории прикреплен один сотрудник, ответственный за пожарную безопасность, но один сотрудник может быть ответственным за несколько аудиторий.

Диаграмма связей между сущностями представлена на рисунке 2.15.3

 

Рис. 2.15.3. Диаграмма связей между сущностями

 

Произведём идентификацию атрибутов и определим, какие из них будут входить в уникальные идентификаторы сущностей.

Корпус имеет название корпуса и адрес. Так как каждый корпус имеет уникальное название, то атрибут «Название корпуса» будет являться уникальным идентификатором (первичным ключом), значение которого однозначно идентифицирует каждый экземпляр сущности «Корпус».

Каждая аудитория характеризуется числом посадочных мест, типом, также к каждой аудитории прикреплён один сотрудник, ответственный за пожарную безопасность. Для однозначной идентификации этой сущности достаточно совокупности значений атрибутов «Название корпуса», к которому принадлежит эта аудитория и «Номер аудитории». Первый атрибут является также и внешним ключом, так как реализует связь с сущностью «Корпуса».

Сущность «Сотрудники» имеет атрибуты: «Табельный номер сотрудника», «ФИО», «Должность», «Служебный телефон». Уникальным идентификатором этой сущности является атрибут «Табельный номер сотрудника». Атрибуты «ФИО» и «Должность» для этой цели не годятся, так как могут быть не уникальны даже в совокупности. Атрибут служебный телефон тоже не подходит, ибо может содержать неопределённые значения (NULL), что недопустимо для первичного ключа.

Структура спроектированной базы данных приведена на рисунке 2.15.4

 

Рис. 2.15.4. Структура БД

 

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

Уникальный идентификатор сущности «Аудитории» состоит из двух атрибутов «Название корпуса» и «Номер аудитории». Ни число посадочных мест, ни тип, ни табельный номер сотрудника не зависят только от атрибута «Название корпуса» или атрибута «Номер аудитории». Например, в одном и том же корпусе может быть несколько аудиторий, имеющих одно и то же число посадочных мест, значит атрибут «Число посадочных мест» не зависит функционально от атрибута «Название корпуса». Также может быть, что в разных корпусах под одним номером могут оказаться аудитории, имеющие одно и то же число посадочных мест. Опять же один номер может идентифицировать несколько сущностей, а не одну. Для функциональной зависимости необходимо, чтобы каждому значению зависимого атрибута соответствовало только одно значение зависящего от него атрибута. Аналогично, не зависят от части уникального идентификатора и значения атрибутов «тип» и «Табельный номер сотрудника», так как они не уникальны и могут повторяться для разных аудиторий, то есть один и тот же тип может быть у различных аудиторий и один сотрудник может быть ответственным за несколько аудиторий.

Покажем, что в схеме БД нет атрибутов, зависящих от неключевых.

Для сущности «Сотрудник» атрибут «Должность» не зависит от атрибута «ФИО», ибо значения атрибута «ФИО» не уникальны, а значит, для одного и того же значения атрибута «ФИО» может быть несколько значений атрибута «Должность». Обратная зависимость тоже отсутствует, ибо значение атрибута «Должность» не уникально. Атрибут «Служебный телефон» мог бы рассматриваться как альтернативный ключ, если бы не мог содержать неопределенных значений.

У сущности «Корпуса» имеется только один неключевой атрибут «Адрес».

У сущности «Аудитории» имеются три неключевых атрибута: «Табельный номер сотрудника», «Число посадочных мест» и «Тип». Покажем, что между ними нет функциональных зависимостей. Один сотрудник может быть ответственным за несколько аудиторий, среди которых могут оказаться и аудитории с разным числом посадочных мест, значит значения атрибута «Число посадочных мест» не зависят функционально от значений атрибута «Табельный номер сотрудника». Для разных аудиторий, характеризующихся одним и тем же числом посадочных мест, могут быть разные сотрудники, значит значения атрибута «Табельный номер сотрудника» не зависят функционально от значений атрибута «Число посадочных мест». Значения атрибута «Тип» не уникальны, то есть один и тот же тип может быть у различных аудиторий, среди которых могут оказаться как несколько аудиторий, к которым прикреплены разные сотрудники, так и несколько аудиторий, характеризующихся разным числом посадочных мест. Значит ни значения атрибута «Табельный номер сотрудника», ни атрибута «Число посадочных мест» функционально не зависят от значений атрибута «Тип». Аналогично можно показать, что атрибут «Тип» не зависит ни от одного из атрибутов «Табельный номер сотрудника» и «Число посадочных мест», ибо один сотрудник может быть ответственным за несколько аудиторий, среди которых могут оказаться несколько аудиторий, имеющих разный тип, также как и среди нескольких аудиторий с одним и тем же числом посадочных мест могут оказаться несколько аудиторий, имеющих разный тип.

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

 

Таблица Аудитории (Auditoriums)

Наименование поля	Тип данных	Ограничения
Название корпуса (name_build)	Строка (20)	Входит в состав первичного ключа
Номер аудитории (number_aud)	Целое число	Входит в состав первичного ключа
Табельный номер сотрудника (number_emp)	Целое число	NOT NULL
Число посадочных мест (number_pla)	Целое число	NOT NULL
Тип (type_)	Строка (20)	NOT NULL

 

Таблица Корпуса (Buildings)

Наименование поля	Тип данных	Ограничения
Название корпуса (Name_build)	Строка (20)	Первичный ключ
Адрес (Address)	Строка (20)	NOT NULL

 

Таблица Сотрудники (Employees)

Наименование поля	Тип данных	Ограничения
Табельный номер сотрудника (number_emp)	Целое число	Первичный ключ
ФИО (FIO)	Строка (30)	NOT NULL
Должность (Jobtitle)	Строка (20)	NOT NULL
Служебный телефон (telephone)	Строка (10)