Логическое проектирование БД

4. Логическое проектирование БД

Логическое проектирование начинается с построения универсальной таблицы (реляционного отношения), которая удовлетворяет требованию первой нормальной формы (1НФ), т.е. в универсальной таблице имеется закономерное «один факт в одном месте». Построение универсальной таблицы ведётся исходя из проведённого анализа предметной области. Универсальная таблица для проектируемой базы данных автовокзала приведена в приложении Б.

Как видно из приложения таблица обладает избыточностью. Данные практически всех столбцов многократно повторяются, в таблице существует потенциальная противоречивость, существует аномалия включения: например в такую БД не может быть записан (внесён) новый шофер, который ещё ни разу не делал маршрут по любому рейсу. В такой универсальной таблице существует и аномалия удаления.

Для приведения универсальной таблицы ко второй нормальной форме (2НФ) необходимо чтобы все поля каждого реляционного отношения не входящие в первичный ключ соответствующего отношения, были связаны полной функциональной зависимостью с первичным ключом. Для этого проведём дополнительный анализ, выделив составной первичный ключ универсальной таблицы.

Предположим, что каждый рейс имеет уникальный номер, относящийся к единственному месту отправления и единственному месту назначения, времени в пути, расстоянию, промежуточным остановкам. Следовательно, номер рейса однозначно определяет указанные атрибуты.

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

Марка автобуса однозначно описывает его технические характеристики код автобуса, такие как количество мест, дальность пути, марка топлива, объём топливного бака.

Номер экипажа уникален для группы допуска, медицинского заключения о здоровье всего экипажа перед выездом.

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

Код маршрута уникален для даты отправления, времени отправления, количество проданных билетов. Маршрут делает нумерованный регистрационный знак (автобус) который является уникальным для марки автобуса и названия автокомпании.

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

Код маршрута, № рейса, № экипажа, Код состава экипажа, Регистрационный знак, Марка автобуса, Название автокомпании.

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

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

Полученные в приложении В таблицы являются полной декомпозицией универсальной таблицы. В каждой из полученных таблиц отсутствуют нетривиальные многозначные зависимости, а следовательно все отношения приложения В находятся и в четвёртой нормальной форме (4НФ).

Преобразуем ER- диаграмму в схему базы данных. Данное преобразование представлено в приложении Г.

4.1 Ограничения целостности

Опишем проектируемую базу данных на языке ЯИМ с указание ключей и других ограничений целостности.

ТАБЛИЦА Автокомпании (Обозначающая сущность, обозначает Автобусы)

ПЕРВИЧНЫЙ КЛЮЧ (Автокомпания)

ПОЛЯ (Автокомпания – Текст 50, Номер лицензии – Длинное целое, Адрес офиса – Текст 50, Тел. гл менеджера – Текст 50)

ОГРАНИЧЕНИЯ (Значения поля Автокомпания должны быть уникальны, при нарушении вывод сообщения «Такая автокомпания уже есть »)

ТАБЛИЦА Марки автобусов (Обозначающая сущность, обозначает автобусы)

ПЕРВИЧНЫЙ КЛЮЧ (Марка автобусов)

ПОЛЯ (Марка автобусов – Текст 50, Количество мест – Длинное целое, Дальность маршрута – Текст 50, Марка топлива – Текст 50, Объём топливного бака – Длинное целое, Группа допуска – Длинное целое)

ОГРАНИЧЕНИЯ (Значения поля Марка автобуса должны быть уникальны, при нарушении вывод сообщения «Такая марка автобуса уже есть»)

ТАБЛИЦА Автобусы (Стержневая сущность)

ПЕРВИЧНЫЙ КЛЮЧ (Регистрационный знак)

ВНЕШНИЙ КЛЮЧ (Марка автобусов ИЗ Марки автобусов

NULL – значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Марки автобусов КАСКАДИРУЮТСЯ

ОБНОВЛЕНИЯ Марки автобусов. Марка автобуса КАСКАДИРУЮТСЯ

ВНЕШНИЙ КЛЮЧ (Автокомпания ИЗ Автокомпании

NULL – значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Автокомпании КАСКАДИРУЕТСЯ

ОБНОВЛЕНИЯ Автокомпании. Автокомпания КАСКАДИРУЮТСЯ

ПОЛЯ (Регистрационный знак – Длинное целое, Марка автобуса – Текст 50, Автокомпания – Текст 50)

ОГРАНИЧЕНИЯ (1.Значения поля Регистрационного знака должны быть уникальны, при нарушении вывод сообщения «Такой регистрационный номер уже есть»

2. Значения полей Марка автобуса и Автокомпания должны принадлежать набору значений из соответствующих полей таблиц Марки автобусов и Автокомпании)

ТАБЛИЦА Экипажи (Стержневая сущность)

ПЕРВИЧНЫЙ КЛЮЧ (№ экипажа)

ПОЛЯ (№ экипажа – Длинное целое, Группа допуска – Длинное целое, Медицинское заключение – Текст 50)

ОГРАНИЧЕНИЯ (Значения поля № экипажа должны быть уникальны, при нарушении вывод сообщения «Такой № экипажа уже есть»)

ТАБЛИЦА Состав экипажа(Характеризующая сущность, характеризует Экипажи)

ПЕРВИЧНЫЙ КЛЮЧ (Код состава экипажа)

ВНЕШНИЙ КЛЮЧ (№ экипажа ИЗ Экипажи

NULL – значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Экипажи КАСКАДИРУЕТСЯ

ОБНОВЛЕНИЯ Экипажи. № экипажа КАСКАДИРУЕТСЯ

ПОЛЯ (Код состава экипажа – Счётчик, Фамилия – Текст 50, Имя – Текст 50, Отчество - Текст 50, № экипажа – Длинное целое)

ОГРАНИЧЕНИЯ (Значения поля № экипажа должны принадлежать набору значений из соответствующего поля таблицы Экипажи)

ТАБЛИЦА Рейсы (Стержневая сущность)

ПЕРВИЧНЫЙ КЛЮЧ (№ рейса)

ПОЛЯ (№ рейса – Длинное целое, Место отправления – Текст 50, Место назначения – Текст -50, Время в пути – Время, Расстояние – Длинное целое, Промежуточные остановки – Текст -50)

ОГРАНИЧЕНИЯ (Значения поля № рейса должны быть уникальны, при нарушении вывод сообщения «Такой № рейса уже есть»)

ТАБЛИЦА Вылеты (Ассоциативная сущность, связывает Рейсы, Автобусы, Экипажи)

ПЕРВИЧНЫЙ КЛЮЧ (Код Маршрута)

ВНЕШНИЙ КЛЮЧ (№ рейса ИЗ Рейсы

NULL – значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Рейсы КАСКАДИРУЕТСЯ

ОБНОВЛЕНИЯ Рейсы. № рейса КАСКАДИРУЕТСЯ

ВНЕШНИЙ КЛЮЧ (Регистрационный знак ИЗ Автобусы

NULL – значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Автобусы КАСКАДИРУЕТСЯ

ОБНОВЛЕНИЯ Автобусы. Регистрационный знак КАСКАДИРУЕТСЯ

ВНЕШНИЙ КЛЮЧ (№ экипажа ИЗ Экипажи

NULL – значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Экипажи КАСКАДИРУЕТСЯ

ОБНОВЛЕНИЯ Экипажи. № экипажа КАСКАДИРУЕТСЯ

ПОЛЯ (Код вылета – Счётчик, № рейса – Длинное целое, Дата отправления – Дата, Время отправления – Время, № экипажа – Длинное целое, Количество проданных билетов – Длинное целое)

ОГРАНИЧЕНИЯ (Значения полей № рейса, Регистрационный номер, № экипажа должны принадлежать набору значений из соответствующих полей таблиц Рейсы, Автобусы, Экипажи).