Лекции.Орг


Поиск:




Описание информационных объектов




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

Вначале преобразуем объект Марка, поскольку он участвует в единственной связи типа «один ко многим», причем на стороне «один». Атрибут Марка этого объекта является уникальным. Название марки (точнее, модели) автомобиля может выражаться достаточно длинной строкой, поэтому в качестве первичного ключа реляционной таблицы более целесообразно использовать не саму эту строку, а некоторое кодовое значение. Введем такое кодовое поле в состав таблицы и сделаем его первичным ключом этой таблицы:

МАРКА (КодМарки, Марка, Мест, Грузоподъемность, Кузов, Расход, ПериодТО)

Для организации связи между таблицами Марка и Автомобиль в состав последней следует включить первичный ключ таблицы Марка, который будет играть роль ключа связи. Такое поле в составе таблицы Автомобиль называется внешним ключом:

АВТОМОБИЛЬ (Гос№, КодМарки, Год, Цвет, Капремонт, Техосмотр)

Внешний ключ КодМарки ссылается на МАРКА

Аналогичным образом реализуется связь и между таблицами Водитель и Автомобиль: в состав таблицы Водитель вводится первичный ключ таблицы Автомобиль. Первичным ключом самой таблицы Водитель сделаем кодовое поле (как при создании таблицы Марка), поскольку поле ФИО, во-первых, теоретически допускает повторы (два разных водителя, являющиеся полными тезками), а во-вторых — слишком длинное для ключа:

ВОДИТЕЛЬ (КодВодителя, ФИО, ДатаРожд, Категория, НачСтажа, Гос№)

Внешний ключ Гос№ ссылается на АВТОМОБИЛЬ

В таблицу «Путевой лист», в соответствии с методикой преобразования, следует ввести первичные ключи таблиц Автомобиль и Водитель. В результате получим следующую схему:

ПУТЕВОЙ_ЛИСТ (, Дата, КодВодителя, Гос№, Выезд, Возвращение, СпидВыезд, СпидВозвр, Расход, Выручка)

Внешние ключи: КодВодителя ссылается на ВОДИТЕЛЬ, Гос№ ссылается на АВТОМОБИЛЬ

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

Объекты базы данных 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. Запрос «Статистика по маркам» в режиме конструктора

Результаты запроса:

Статистика по маркам
Марка Автомобилей Расход топлива на 100 км Километров Расход топлива Фактический расход
ГАЗ-2404   10,7   141,500000953674 10,0783476462731
ГАЗ-2410   9,6   132,39999961853 10,66022541212
ГАЗ-3110   10,2   192,5 13,7010676156584




Поделиться с друзьями:


Дата добавления: 2017-01-28; Мы поможем в написании ваших работ!; просмотров: 489 | Нарушение авторских прав


Поиск на сайте:

Лучшие изречения:

Слабые люди всю жизнь стараются быть не хуже других. Сильным во что бы то ни стало нужно стать лучше всех. © Борис Акунин
==> читать все изречения...

764 - | 719 -


© 2015-2024 lektsii.org - Контакты - Последнее добавление

Ген: 0.008 с.