Описание информационных объектов
Преобразуем построенную концептуальную модель в реляционную схему данных. При этом каждому информационному объекту ставится в соответствие реляционная таблица, при определении состава атрибутов которой учитываются связи данного объекта с другими объектами.
Вначале преобразуем объект Марка, поскольку он участвует в единственной связи типа «один ко многим», причем на стороне «один». Атрибут Марка этого объекта является уникальным. Название марки (точнее, модели) автомобиля может выражаться достаточно длинной строкой, поэтому в качестве первичного ключа реляционной таблицы более целесообразно использовать не саму эту строку, а некоторое кодовое значение. Введем такое кодовое поле в состав таблицы и сделаем его первичным ключом этой таблицы:
МАРКА (КодМарки, Марка, Мест, Грузоподъемность, Кузов, Расход, ПериодТО)
Для организации связи между таблицами Марка и Автомобиль в состав последней следует включить первичный ключ таблицы Марка, который будет играть роль ключа связи. Такое поле в составе таблицы Автомобиль называется внешним ключом:
АВТОМОБИЛЬ (Гос№, КодМарки, Год, Цвет, Капремонт, Техосмотр)
Внешний ключ КодМарки ссылается на МАРКА
Аналогичным образом реализуется связь и между таблицами Водитель и Автомобиль: в состав таблицы Водитель вводится первичный ключ таблицы Автомобиль. Первичным ключом самой таблицы Водитель сделаем кодовое поле (как при создании таблицы Марка), поскольку поле ФИО, во-первых, теоретически допускает повторы (два разных водителя, являющиеся полными тезками), а во-вторых — слишком длинное для ключа:
ВОДИТЕЛЬ (КодВодителя, ФИО, ДатаРожд, Категория, НачСтажа, Гос№)
Внешний ключ Гос№ ссылается на АВТОМОБИЛЬ
В таблицу «Путевой лист», в соответствии с методикой преобразования, следует ввести первичные ключи таблиц Автомобиль и Водитель. В результате получим следующую схему:
ПУТЕВОЙ_ЛИСТ (№, Дата, КодВодителя, Гос№, Выезд, Возвращение, СпидВыезд, СпидВозвр, Расход, Выручка)
Внешние ключи: КодВодителя ссылается на ВОДИТЕЛЬ, Гос№ ссылается на АВТОМОБИЛЬ
Итак, мы получили реляционную схему данных рассматриваемой предметно области. Метод, использованный нами для преобразования концептуальной модели в реляционную, гарантирует, что все реляционные таблицы будут соответствовать третьей нормальной форме.
Объекты базы данных Microsoft Access
Таблицы
Средствами Microsoft Access созданы следующие таблицы.
Таблица: Автомобиль
Имя Тип Размер
ГосНомер Текстовый 8
Марка Длинное целое 4
Год Целое 2
Цвет Текстовый 20
Капремонт Дата/время 8
Техосмотр Дата/время 8
Таблица: Водитель
Имя Тип Размер
КодВодителя Длинное целое 4
ФИО Текстовый 50
ДатаРожд Дата/время 8
Категория Текстовый 1
НачСтажа Целое 2
Машина Текстовый 50
Таблица: Марка
Имя Тип Размер
КодМарки Длинное целое 4
Марка Текстовый 50
Мест Байт 1
Грузоподъемность Целое 2
Кузов Текстовый 20
Расход Одинарное с плавающей 4
ПериодТО Длинное целое 4
Таблица: Путевой лист
Имя Тип Размер
№ Длинное целое 4
Дата Дата/время 8
Водитель Длинное целое 4
Машина Текстовый 50
Выезд Дата/время 8
Возвращение Дата/время 8
СпидВыезд Длинное целое 4
СпидВозвр Длинное целое 4
Расход Одинарное с плавающей 4
Выручка Денежный 8
Схема связи таблиц показана на рис. 2.
Рис. 2. Схема связи таблиц
Содержание таблиц, использованных в контрольном примере, приводится ниже.
| Автомобиль | |||||
| Гос. № | Марка | Год выпуска | Цвет | Последний капремонт | Последний техосмотр |
| З837ОТ77 | ГАЗ-2404 | белый | 29.11.08 | ||
| М093РТ77 | ГАЗ-2404 | белый | 03.11.08 | ||
| М398ГН77 | ГАЗ-3110 | бежевый | 16.10.08 | ||
| О386УГ77 | ГАЗ-2410 | бежевый | 23.09.08 | ||
| П865ВГ77 | ГАЗ-2410 | белый | 03.12.08 | ||
| Р874ИТ77 | ГАЗ-2410 | голубой | 31.10.08 | ||
| Р983ПО77 | ГАЗ-2404 | белый | 17.11.08 | ||
| Т341ГШ77 | ГАЗ-3110 | белый | 27.02.08 | 03.10.08 | |
| Т712КД77 | ГАЗ-2404 | бежевый | 03.08.08 | 04.11.08 | |
| У402ОЕ77 | ГАЗ-2410 | белый | 17.10.08 | ||
| У876ВП77 | ГАЗ-3110 | белый | 25.09.05 | 27.10.08 |
| Водитель | |||||
| КодВодителя | ФИО | Дата рожд. | Категория | Год начала стажа | Машина |
| Макаренко Евгений Игоревич | 13.02.65 | B | У876ВП77 | ||
| Тарасов Иван Григорьевич | 12.11.55 | C | О386УГ77 | ||
| Коваль Владимир Владимирович | 03.02.72 | B | П865ВГ77 | ||
| Емельяненко Игорь Александрович | 03.06.68 | C | З837ОТ77 | ||
| Панченко Сергей Алексеевич | 13.02.82 | B | Р983ПО77 | ||
| Уборевич Кирилл Александрович | 21.10.78 | C | Р874ИТ77 | ||
| Панкратов Юрий Иванович | 12.04.59 | C | Р874ИТ77 | ||
| Игнатович Александр Михайлович | 07.08.78 | B | У402ОЕ77 | ||
| Костюк Евгений Алексеевич | 04.11.64 | D | Т712КД77 | ||
| Шпак Антон Семенович | 05.08.82 | B | М093РТ77 | ||
| Филимонов Олег Константинович | 17.04.56 | D | Т341ГШ77 | ||
| Самойленко Станислав Васильевич | 23.07.81 | B | М398ГН77 | ||
| Семашко Юрий Львович | 30.03.67 | C | У876ВП77 | ||
| Коршунов Игорь Васильевич | 25.08.63 | C | М398ГН77 | ||
| Чибис Василий Анатольевич | 19.03.77 | B | З837ОТ77 |
| Марка | ||||||
| КодМарки | Марка | Мест | Грузоподъемность | Тип кузова | Расход топлива на 100 км | Периодичность техосмотров |
| ГАЗ-3110 | седан | 10,2 | ||||
| ГАЗ-2410 | седан | 9,6 | ||||
| ГАЗ-2404 | фургон | 10,7 |
| Путевой лист | |||||||||
| № | Дата | Водитель | Машина | Время выезда | Время возвращения | Спидометр: выезд | возвращение | Расход топлива | Выручка |
| 04.12.08 | Макаренко Евгений Игоревич | У876ВП77 | 6:30 | 13:50 | 750,00р. | ||||
| 04.12.08 | Тарасов Иван Григорьевич | О386УГ77 | 6:55 | 14:25 | 10,2 | 630,00р. | |||
| 04.12.08 | Емельяненко Игорь Александрович | З837ОТ77 | 7:20 | 16:10 | 9,5 | 588,00р. | |||
| 04.12.08 | Семашко Юрий Львович | У876ВП77 | 14:20 | 22:55 | 930,00р. | ||||
| 04.12.08 | Чибис Василий Анатольевич | З837ОТ77 | 16:50 | 23:45 | 810,00р. | ||||
| 04.12.08 | Игнатович Александр Михайлович | У402ОЕ77 | 8:15 | 15:55 | 588,00р. | ||||
| 05.12.08 | Костюк Евгений Алексеевич | Т712КД77 | 7:30 | 14:45 | 9,8 | 720,00р. | |||
| 05.12.08 | Панченко Сергей Алексеевич | Р983ПО77 | 7:35 | 14:55 | 990,00р. | ||||
| 05.12.08 | Шпак Антон Семенович | М093РТ77 | 8:12 | 15:54 | 11,8 | 888,00р. | |||
| 05.12.08 | Коваль Владимир Владимирович | П865ВГ77 | 9:16 | 17:00 | 900,00р. | ||||
| 05.12.08 | Макаренко Евгений Игоревич | У876ВП77 | 7:25 | 15:10 | 990,00р. | ||||
| 05.12.08 | Чибис Василий Анатольевич | З837ОТ77 | 12:40 | 20:45 | 1 050,00р. | ||||
| 14.12.08 | Уборевич Кирилл Александрович | Р874ИТ77 | 8:10 | 16:45 | 870,00р. | ||||
| 14.12.08 | Панкратов Юрий Иванович | Р874ИТ77 | 8:25 | 17:10 | 1 050,00р. | ||||
| 14.12.08 | Филимонов Олег Константинович | Т341ГШ77 | 10:40 | 18:55 | 1 104,00р. | ||||
| 14.12.08 | Самойленко Станислав Васильевич | М398ГН77 | 11:00 | 19:30 | 1 200,00р. | ||||
| 14.12.08 | Коршунов Игорь Васильевич | М398ГН77 | 20:00 | 5:00 | 630,00р. | ||||
| 15.12.08 | Емельяненко Игорь Александрович | З837ОТ77 | 7:30 | 15:20 | 570,00р. | ||||
| 15.12.08 | Игнатович Александр Михайлович | У402ОЕ77 | 7:55 | 16:00 | 870,00р. | ||||
| 15.12.08 | Коваль Владимир Владимирович | П865ВГ77 | 7:57 | 16:00 | 750,00р. | ||||
| 15.12.08 | Коршунов Игорь Васильевич | М398ГН77 | 8:00 | 16:10 | 1 050,00р. | ||||
| 15.12.08 | Костюк Евгений Алексеевич | Т712КД77 | 8:05 | 15:55 | 9,8 | 720,00р. | |||
| 15.12.08 | Макаренко Евгений Игоревич | У876ВП77 | 8:05 | 15:45 | 16,5 | 1 050,00р. | |||
| 15.12.08 | Панкратов Юрий Иванович | Р874ИТ77 | 8:05 | 15:50 | 14,5 | 1 080,00р. | |||
| 15.12.08 | Панченко Сергей Алексеевич | Р983ПО77 | 8:10 | 16:30 | 1 260,00р. | ||||
| 15.12.08 | Самойленко Станислав Васильевич | М398ГН77 | 16:45 | 23:55 | 1 170,00р. | ||||
| 15.12.08 | Семашко Юрий Львович | У876ВП77 | 16:35 | 0:40 | 1 260,00р. | ||||
| 15.12.08 | Тарасов Иван Григорьевич | О386УГ77 | 16:35 | 0:35 | 11,7 | 810,00р. | |||
| 15.12.08 | Уборевич Кирилл Александрович | Р874ИТ77 | 16:40 | 0:55 | 1 110,00р. | ||||
| 15.12.08 | Филимонов Олег Константинович | Т341ГШ77 | 16:40 | 1:20 | 1 380,00р. | ||||
| 15.12.08 | Чибис Василий Анатольевич | З837ОТ77 | 16:45 | 1:20 | 15,6 | 1 050,00р. | |||
| 15.12.08 | Шпак Антон Семенович | М093РТ77 | 17:00 | 1:50 | 1 230,00р. |
Запросы
Для формирования отчетных документов, а также отбора необходимой информации в ходе формирования путевых листов, разработан ряд запросов.
Запрос «График техосмотров» определяет для каждой машины срок очередного техосмотра (на основании данных о периодичности техосмотров для данной марки) и располагает полученные записи в хронологическом порядке очередных техосмотров. Текст запроса на языке SQL:
SELECT Автомобиль.ГосНомер, Марка.Марка, Автомобиль.Год, Автомобиль.Техосмотр, Автомобиль!Техосмотр+Марка!ПериодТО AS [Следующий техосмотр]
FROM Марка INNER JOIN Автомобиль ON Марка.КодМарки = Автомобиль.Марка
ORDER BY Автомобиль!Техосмотр+Марка!ПериодТО;
Рис. 3. Запрос «График техосмотров» в режиме конструктора
Результаты запроса:
| График техосмотров | ||||
| Гос. № | Марка | Год выпуска | Последний техосмотр | Следующий техосмотр |
| О386УГ77 | ГАЗ-2410 | 23.09.08 | 22.12.08 | |
| Т341ГШ77 | ГАЗ-3110 | 03.10.08 | 01.01.09 | |
| М398ГН77 | ГАЗ-3110 | 16.10.08 | 14.01.09 | |
| У402ОЕ77 | ГАЗ-2410 | 17.10.08 | 15.01.09 | |
| У876ВП77 | ГАЗ-3110 | 27.10.08 | 25.01.09 | |
| Р874ИТ77 | ГАЗ-2410 | 31.10.08 | 29.01.09 | |
| М093РТ77 | ГАЗ-2404 | 03.11.08 | 01.02.09 | |
| Т712КД77 | ГАЗ-2404 | 04.11.08 | 02.02.09 | |
| Р983ПО77 | ГАЗ-2404 | 17.11.08 | 15.02.09 | |
| З837ОТ77 | ГАЗ-2404 | 29.11.08 | 27.02.09 | |
| П865ВГ77 | ГАЗ-2410 | 03.12.08 | 03.03.09 |
Запрос «Закрепленная машина» используется в ходе ввода данных в путевой лист. После выбора водителя, фигурирующего в путевом листе, этот запрос определяет автомобиль, за которым закреплен данный водитель, а также последнее (то есть максимальное) показание спидометра при возвращении этой машины в таксопарк, которое вносится в очередной путевой лист как значение по умолчанию. Текст запроса на языке SQL:
SELECT Водитель.Машина, Max([Путевой лист].СпидВозвр) AS Спидометр
FROM (Автомобиль INNER JOIN Водитель ON Автомобиль.ГосНомер = Водитель.Машина) LEFT JOIN [Путевой лист] ON Автомобиль.ГосНомер = [Путевой лист].Машина
WHERE (((Водитель.КодВодителя)=[Forms]![Путевой лист]![Водитель]))
GROUP BY Водитель.Машина;
Рис. 4. Запрос «Закрепленная машина» в режиме конструктора
Поскольку результаты запроса зависят от того, какой водитель внесен в путевой лист, они не приводятся в данной записке.
Следующий запрос — «Пробег машины» — по назначению аналогичен предыдущему: поскольку водитель может отправиться в рейс не на той машине, за которой он закреплен, данные о машине в путевом листе могут быть введены отдельно. Рассматриваемый запрос как раз и учитывает такую ситуацию, вычисляя последнее зарегистрированное показание спидометра данной машины. Текст запроса на языке SQL:
SELECT Max([Путевой лист].СпидВозвр) AS Спидометр
FROM [Путевой лист]
WHERE ((([Путевой лист].Машина)=[Forms]![Путевой лист]![Машина]));
Рис. 5. Запрос «Пробег машины» в режиме конструктора
Поскольку результаты запроса зависят от того, какая машина внесена в путевой лист, они не приводятся в данной записке.
Запрос «Средняя выручка по водителям» готовит данные для соответствующего выходного документа. Текст запроса на языке SQL:
SELECT Водитель.ФИО, Водитель.Категория, Avg([Путевой лист].Выручка) AS [Средняя выручка]
FROM Водитель INNER JOIN [Путевой лист] ON Водитель.КодВодителя = [Путевой лист].Водитель
GROUP BY Водитель.ФИО, Водитель.Категория
ORDER BY Avg([Путевой лист].Выручка) DESC;
Рис. 6. Запрос «Средняя выручка по водителям» в режиме конструктора
Результаты запроса:
| Средняя выручка по водителям | ||
| ФИО | Категория | Средняя выручка |
| Филимонов Олег Константинович | D | 1 242,00р. |
| Самойленко Станислав Васильевич | B | 1 185,00р. |
| Панченко Сергей Алексеевич | B | 1 125,00р. |
| Семашко Юрий Львович | C | 1 095,00р. |
| Панкратов Юрий Иванович | C | 1 065,00р. |
| Шпак Антон Семенович | B | 1 059,00р. |
| Уборевич Кирилл Александрович | C | 990,00р. |
| Чибис Василий Анатольевич | B | 970,00р. |
| Макаренко Евгений Игоревич | B | 930,00р. |
| Коршунов Игорь Васильевич | C | 840,00р. |
| Коваль Владимир Владимирович | B | 825,00р. |
| Игнатович Александр Михайлович | B | 729,00р. |
| Тарасов Иван Григорьевич | C | 720,00р. |
| Костюк Евгений Алексеевич | D | 720,00р. |
| Емельяненко Игорь Александрович | C | 579,00р. |
Остальные запросы готовят данные для отчетов за заданный период. Период указывается пользователем в форме «Отчетный период»; по умолчанию в форму вносится период, соответствующий текущему месяцу.
Запрос «Статистика по автомобилям» вычисляет для каждой автомашины количество рейсов, суммарный пробег, фактический и нормативный расход топлива, и на основании последних данных — перерасход. Текст запроса на языке SQL:
SELECT Автомобиль.ГосНомер, Марка.Марка, Count([Путевой лист].№) AS Рейсов, Sum([Путевой лист]!СпидВозвр-[Путевой лист]!СпидВыезд) AS Километров, Sum([Путевой лист].Расход) AS [Расход топлива], Sum(([Путевой лист]!СпидВозвр-[Путевой лист]!СпидВыезд)*Марка!Расход/100) AS [Нормативный расход], [Расход топлива]-[Нормативный расход] AS Перерасход
FROM Марка INNER JOIN (Автомобиль INNER JOIN [Путевой лист] ON Автомобиль.ГосНомер = [Путевой лист].Машина) ON Марка.КодМарки = Автомобиль.Марка
WHERE ((([Путевой лист].Дата)>=[Forms]![Отчетный период]![Начало] And ([Путевой лист].Дата)<=[Forms]![Отчетный период]![Конец]))
GROUP BY Автомобиль.ГосНомер, Марка.Марка;
Результаты запроса:
| Статистика по автомобилям | ||||||
| Гос. № | Марка | Рейсов | Километров | Расход топлива | Нормативный расход | Перерасход |
| З837ОТ77 | ГАЗ-2404 | 63,1000003814697 | 69,0149987697601 | -5,9149983882904 | ||
| М093РТ77 | ГАЗ-2404 | 28,8000001907349 | 29,9599994659424 | -1,15999927520752 | ||
| М398ГН77 | ГАЗ-3110 | 47,9399991035461 | 22,0600008964539 | |||
| О386УГ77 | ГАЗ-2410 | 21,8999996185303 | 18,2400007247925 | 3,65999889373779 | ||
| П865ВГ77 | ГАЗ-2410 | 22,3680008888245 | 3,63199911117554 | |||
| Р874ИТ77 | ГАЗ-2410 | 56,5 | 51,8400020599365 | 4,65999794006348 | ||
| Р983ПО77 | ГАЗ-2404 | 29,5319994735718 | 0,46800052642822 | |||
| Т341ГШ77 | ГАЗ-3110 | 27,5399994850159 | 13,4600005149841 | |||
| Т712КД77 | ГАЗ-2404 | 19,6000003814697 | 21,7209996128082 | -2,1209992313385 | ||
| У402ОЕ77 | ГАЗ-2410 | 26,7840010643005 | 1,21599893569946 | |||
| У876ВП77 | ГАЗ-3110 | 81,5 | 67,8299987316132 | 13,6700012683868 |
Рис. 7. Запрос «Статистика по автомобилям» в режиме конструктора
Запрос «Статистика по водителям» вычисляет количество рейсов, километраж, расход топлива и объем выручки для каждого водителя за заданный период. Текст запроса на языке SQL:
Рис. 8. Запрос «Статистика по водителям» в режиме конструктора
SELECT Водитель.ФИО, Count([Путевой лист].№) AS Рейсов, Sum([Путевой лист]!СпидВозвр-[Путевой лист]!СпидВыезд) AS Километров, Sum([Путевой лист].Расход) AS [Расход топлива], Sum([Путевой лист].Выручка) AS [Объем выручки]
FROM Водитель INNER JOIN [Путевой лист] ON Водитель.КодВодителя = [Путевой лист].Водитель
WHERE ((([Путевой лист].Дата)>=[Forms]![Отчетный период]![Начало] And ([Путевой лист].Дата)<=[Forms]![Отчетный период]![Конец]))
GROUP BY Водитель.ФИО
ORDER BY Водитель.ФИО;
Результаты запроса:
| Статистика по водителям | ||||
| ФИО | Рейсов | Километров | Расход топлива | Объем выручки |
| Емельяненко Игорь Александрович | 20,5 | 1 158,00р. | ||
| Игнатович Александр Михайлович | 1 458,00р. | |||
| Коваль Владимир Владимирович | 1 650,00р. | |||
| Коршунов Игорь Васильевич | 1 680,00р. | |||
| Костюк Евгений Алексеевич | 19,6000003814697 | 1 440,00р. | ||
| Макаренко Евгений Игоревич | 50,5 | 2 790,00р. | ||
| Панкратов Юрий Иванович | 30,5 | 2 130,00р. | ||
| Панченко Сергей Алексеевич | 2 250,00р. | |||
| Самойленко Станислав Васильевич | 2 370,00р. | |||
| Семашко Юрий Львович | 2 190,00р. | |||
| Тарасов Иван Григорьевич | 21,8999996185303 | 1 440,00р. | ||
| Уборевич Кирилл Александрович | 1 980,00р. | |||
| Филимонов Олег Константинович | 2 484,00р. | |||
| Чибис Василий Анатольевич | 42,6000003814697 | 2 910,00р. | ||
| Шпак Антон Семенович | 28,8000001907349 | 2 118,00р. |
Запрос «Статистика по маркам» вычисляет количество автомобилей данной марки, использовавшихся в заданном периоде, нормативный расход топлива на 100 км для автомобилей заданной марки, суммарный пробег автомобилей этой марки в отчетном периоде и фактический расход топлива (суммарный и на 100 км пробега). Текст запроса на языке SQL:
SELECT Марка.Марка, Count(Автомобиль.ГосНомер) AS Автомобилей, Марка.Расход, Sum([Путевой лист]!СпидВозвр-[Путевой лист]!СпидВыезд) AS Километров, Sum([Путевой лист].Расход) AS [Расход топлива], [Расход топлива]/[Километров]*100 AS [Фактический расход]
FROM Марка INNER JOIN (Автомобиль INNER JOIN [Путевой лист] ON Автомобиль.ГосНомер = [Путевой лист].Машина) ON Марка.КодМарки = Автомобиль.Марка
WHERE ((([Путевой лист].Дата)>=[Forms]![Отчетный период]![Начало] And ([Путевой лист].Дата)<=[Forms]![Отчетный период]![Конец]))
GROUP BY Марка.Марка, Марка.Расход;
Рис. 9. Запрос «Статистика по маркам» в режиме конструктора
Результаты запроса: