База данных для информационной системы - Таксопарк
Рисунок 4.1 – Глобальная ER-модель
Перевод целостных и операционных ограничений на уровень глобальной модели
Сущность СОТРУДНИК Атрибут Водительские права => Сущность МАРКА МОДЕЛЬ атрибут Категория ТС
Сущность СОТРУДНИК атрибут Дата выдачи прав – Сущность СОТРУДНИК ат. Дата рождения<= 18 лет
Сущность НОМЕР атрибут Дата выдачи паспорта – Сущность СОТРУДНИК ат. Дата рождения <= 16 лет
Сущность ВЫЗОВ: если ат. флаг VIP клиента=истина, то Сущность ВЫЗОВ ат. номер карты не пуст
Сущность ВЫЗОВ: если ат. флаг почасовой оплаты=истина, то Сущность ВЫЗОВ ат. время движения не пуст
Сущность НАЧАЛО ат.Улица не = Сущность КОНЕЦ ат. Улица
Сущность VIP КЛИЕНТ если ат. Баланс <1000 руб., то ат.Скидка <= 10%
Сущность VIP КАРТА: атрибут Дата Договора <(раньше) Сущность ЗАПИСЬ ат.Дата записи
Сущность МАТЕРИАЛЬНОЕ СРЕДСТВО атрибут Номинальная стоимость < Сущность МАТЕРИАЛЬНОЕ СРЕДСТВО ат. Затраты на Эксплуатацию
Сущность КОНТРАГЕНТ: атрибут Телефон должен соответствовать коду города, который указан в ат. Сущность КОНТРАГЕНТ Юридический адрес
Сущность КОНТРАГЕНТ: атрибут Факс должен соответствовать коду города, который указан в Сущность КОНТРАГЕНТ ат. Юридический адрес
Сущность КОНТРАГЕНТ: атрибут Дата Договора <(раньше) Сущность ЗАПИСЬ ат. Дата записи
4.6 Вывод
В результате проектирования глобальной ER-модели, соответствующей автоматизируемым функциям, получена модель, включающая 7 сущностей, 2 связи типа «многие ко многим» и 8 связей типа «один ко многим».
5 ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННОЙ SQL-МОДЕЛИ
Данный раздел посвящен проектированию реляционной SQL-модели. Здесь выполняется перевод глобальной ER-модели в реляционную форму, специфицируются ограничения и правила поддержания целостности на реляционном уровне, записывается SQL-код для создания реляционной модели.
5.1 Перевод глобальной ER-модели в реляционную форму
Устранение связей типа «один ко многим». Такие связи устраняются путем копирования первичных ключей исходной сущности в множество атрибутов дочерней.
Конкретные данные приведены в таблице 5.1
Таблица 5.1 - «Копирование первичных ключей при устранении связей типа «один ко многим».
| Исходная сущность | Дочерняя сущность | Первичный ключ |
| Сотрудник | Материальное средство | Код сотрудника |
| Сотрудник | Вызов | Код сотрудника |
| Сотрудник | Паспорт | Код сотрудника |
| Материальное средство | Поставщик | Код мат. средства |
| Вызов |
VIP Клиент |
Код вызова |
|
VIP Клиент |
VIP Карта |
Код VIP Клиента |
|
VIP Клиент |
Паспорт |
Код VIP Клиента |
| Вызов | Транспортное средство | Код вызова |
5.2 SQL-код для создания реляционной модели
Create Table Сотрудник (
Код_сотрудника Int Not Null,
Фамилия Char (20),
Имя Char (20),
Отчество Char (20),
Дата_рожд Date,
Адрес Char (20),
Права Char (20),
Должность Char (20),
Образование Char (20),
Серия_Паспорта Int Not Null,
№Паспорта Int Not Null,
№телефона Int Not Null,
№ТС Char (6),
Дата_записи Date,
Primary key (Код_сотрудника) )
Create table Транспортное_Средство(
Код_мат_средства Int Not Null,
№TC Char (6),
Марка Char (20),
Цвет Char (20),
VIN Int,
Категория Char (20),
Дата_Выпуска Date,
№Двигателя Int,
№Кузова Int,
Двигатель Int,
Масса Int,
Дата_записи Date,
Primary Key (Код_мат_средства)
)
Create Table Поставщик(
Код_Поставщика Int Not Null,
Код_Мат_средства Int NOt Null,
Наименование Char (20),
Вид Char (20),
ИНН Int,
ОКОНХ Int,
Юр_адрес Char (20),
Факс Int,
Договор Char (20),
Дата_договора Date,
Коментарий Char (20),
Дата_записи Date,
Primary Key (Код_Поставщика)
)
Create Table Паспорт (
Серия_паспорта Char (20),
№Паспорта Int Not Null,
Кем_Выдан Char (20),
Код_подразделения Char (20),
Адрес_регистрации Char (20),
Дата_регистрации Date,
Дата_записи Date,
Primary Key (Серия_паспорта)
)
Create Table Материальное_Средство (
Код_мат_средства Int Not Null,
Код_отв_сотрудника Int,
Стоимость Int,
Описание Char (20),
Назначение Char (20),
Эксплуатация Char (20),
Доп_инфо Char (20),
Дата_записи Date,
Primary Key (Код_мат_средства)
)
Create Table Вызов (
Код_Вызова Int Not Null,
Влаг_VIP_Клиента Char (20),
№VIP_Карты Int,
Время_заказа Date,
Флаг_почасовой_оплаты Char (20),
Время_жвижения Date,
Расстояние Int,
Стоимость_р_ч Int,
Стоимость_р_км Int,
Стоимость_заказа Int,
Флаг_состояния Char (20),
Код_сотрудника Int Not Null,
№ТС Char (6),
Дата_записи Date,
Primary Key (Код_Вызова)
)
Create Table VIP_Клиент (Код_VIP_Клиента Int Not Null,
№VIP_Карты Int,
Фамилия Char (20),
Имя Char (20),
Отчество Char (20),
Скидка Int,
Баланс Int,
Адрес Char (20),
Серия_Паспорта Char (20),
№Паспорта Int,
Дата_записи Date,
Primary Key (Код_VIP_Клиента)
)
Create Table VIP_Карта (№VIP_Карты Int Not Null,
№Договора Int,
Дата_Договора Date,
Дата_записи Date,
Primary Key (№VIP_Карты))
6 ПРОЕКТИРОВАНИЕ ПРЕДСТАВЛЕНИЙ
ДЛЯ АВТОМАТИЗИРУЕМЫХ ФУНКЦИЙ
Данный раздел посвящен проектированию представлений для автоматизируемых функций. Здесь выполняется определение путей доступа к данным для автоматизируемых функций, записывается SQL-код локальных просмотров для автоматизируемых функций.
Определение способа и формы представления
Для 5 функций был выбран способ реализации представления в виде запроса (Select), форма представления была выбрана в виде иерархии таблиц.
6.2 SQL – код для реализации выборки.
VIP_Клиент
SELECT [VIP КЛИЕНТ].№VIP_Карты,
[VIP КЛИЕНТ].Фамилия,
[VIP КАРТА].№Договора,
ПАСПОРТ.Адрес_регистр
FROM ПАСПОРТ INNER JOIN ([VIP КАРТА] RIGHT JOIN [VIP КЛИЕНТ] ON [VIP КАРТА].№VIP_Карты = [VIP КЛИЕНТ].№VIP_Карты) ON (ПАСПОРТ.№Паспорта = [VIP КЛИЕНТ].№Паспорта) AND (ПАСПОРТ.Серия_Паспорта = [VIP КЛИЕНТ].Серия_Паспорта);
Вызов
SELECT ВЫЗОВ.Время_заказа,
[ТРАНСПОРТНОЕ СРЕДСТВО].Цвет,
СОТРУДНИК.Фамилия, [VIP КЛИЕНТ].№VIP_Карты
FROM [VIP КЛИЕНТ] INNER JOIN (СОТРУДНИК RIGHT JOIN ([ТРАНСПОРТНОЕ СРЕДСТВО] INNER JOIN ВЫЗОВ ON [ТРАНСПОРТНОЕ СРЕДСТВО].№ТС = ВЫЗОВ.№ТС) ON СОТРУДНИК.Код_сотрудника = ВЫЗОВ.Код_сотрудника) ON [VIP КЛИЕНТ].№VIP_Карты = ВЫЗОВ.№VIP_Карты;
Поставщик
SELECT [МАТЕРИАЛЬОЕ СРЕДСТВО].Код_мат_ср, ПОСТАВЩИК.Наименование,
ПОСТАВЩИК.Факс,
ПОСТАВЩИК.ИНН,
СОТРУДНИК.Фамилия,
СОТРУДНИК.Должность
FROM СОТРУДНИК INNER JOIN ([МАТЕРИАЛЬОЕ СРЕДСТВО] INNER JOIN ПОСТАВЩИК ON [МАТЕРИАЛЬОЕ СРЕДСТВО].Код_мат_ср = ПОСТАВЩИК.Код_мат_средства) ON СОТРУДНИК.Код_сотрудника = [МАТЕРИАЛЬОЕ СРЕДСТВО].Код_отв_сотр;
Сотр_мат_Средство
SELECT СОТРУДНИК.Фамилия,
ПАСПОРТ.Адрес_регистр,
СОТРУДНИК.Адрес,
[МАТЕРИАЛЬОЕ СРЕДСТВО].Описание,
[МАТЕРИАЛЬОЕ СРЕДСТВО].Стоимость
FROM (ПАСПОРТ INNER JOIN СОТРУДНИК ON (ПАСПОРТ.№Паспорта = СОТРУДНИК.№Паспорта) AND (ПАСПОРТ.Серия_Паспорта = СОТРУДНИК.Серия_Паспорта)) INNER JOIN [МАТЕРИАЛЬОЕ СРЕДСТВО] ON СОТРУДНИК.Код_сотрудника = [МАТЕРИАЛЬОЕ СРЕДСТВО].Код_отв_сотр;
Сотрудник
SELECT СОТРУДНИК.Фамилия,
[ТРАНСПОРТНОЕ СРЕДСТВО].№ТС,
ПАСПОРТ.Адрес_регистр,
[МАТЕРИАЛЬОЕ СРЕДСТВО].Код_мат_ср,
[МАТЕРИАЛЬОЕ СРЕДСТВО].Описание
FROM ПАСПОРТ INNER JOIN ((СОТРУДНИК INNER JOIN [ТРАНСПОРТНОЕ СРЕДСТВО] ON СОТРУДНИК.№ТС = [ТРАНСПОРТНОЕ СРЕДСТВО].№ТС) LEFT JOIN [МАТЕРИАЛЬОЕ СРЕДСТВО] ON СОТРУДНИК.Код_сотрудника = [МАТЕРИАЛЬОЕ СРЕДСТВО].Код_отв_сотр) ON (ПАСПОРТ.№Паспорта = СОТРУДНИК.№Паспорта) AND (ПАСПОРТ.Серия_Паспорта = СОТРУДНИК.Серия_Паспорта);
Заключение
В результате выполнения этого курсового проекта я выработала умения и навыки проектирования структуры базы данных, предназначенной для функционирования автоматизированной информационной системы.
В разделе 1 я выбрала автоматизированные функции и информационное обеспечение. Дала краткое описание предметной области, произвела выбор и описание автоматизируемых функций, выполнила первичное описание информационного обеспе6чения.
Во 2 разделе я выявили ограничения и правила поддержания целостности данных, которые будут размещаться в базе данных. Рассмотрела ограничения и правила для отдельных атрибутов, кортежей, множеств кортежей и базы данных в целом.
В 3 разделе я спроектировала локальные ER-модели, соответствующие отдельным автоматизируемым функциям. Произвела нормализацию локальных ER-моделей, разработала спецификации огрничений и правил поддержания целостности для локальных ER-моделей.
В 4 разделе спроектировала глобальную ER-модель. Произвела выявление и устранение эквивалентных сущностей, выявила категории и синтез обобщающих сущностей, выявила и устранила дублирование атрибутов и связей.
В разделе 5 записала SQL-код для создания реляционной модели
В разделе 6 записала SQL-код локальных запросов для автоматизируемых функций.