1. Создайте новую базу AutoPark.mdb, сохраните файл базы данных в своей папке. В данной базе создайте таблицы: «Автобусы», «Водители», «Маршруты», «Топливо», «Путёвки» и «Марки»

Таблица Автобусы
Структура
Поле Тип Размер поля/Примечание
КодАвтобуса Счетчик -
ГосНомер Текстовый 8
Марка Числовой Длинное целое
ДатаВыпуска Дата/Время Краткий формат даты
ДатаКапремонта Дата/Время Краткий формат даты
Пробег Числовой Длинное целое

Первичный ключ таблицы - поле КодАвтобуса

Данные
КодАвтобуса ГосНомер Марка ДатаВыпуска ДатаКапремонта Пробег
1 НС 57-85 2 07.11.1998 14.04.2003 1164000
2 МЕ 99-18 3 20.12.1991 23.04.1996 1767000
3 НО 50-10 2 10.11.1994 14.11.1999 1424000
4 РЗ 68-49 2 06.10.1980 26.04.1985 1340000
5 НО 57-93 4 01.09.1996 11.08.2001 1344000
6 МЕ 38-60 5 18.12.1996 15.02.2001 1302000
7 РЗ 21-49 6 14.11.1986 07.08.1991 1232000
8 НФ 04-57 3 20.12.1981 11.06.1986 1813000
9 МЕ 84-07 4 06.09.2008   154000
10 НО 51-02 1 11.12.1991 28.11.1996 1691000
11 НС 94-82 4 22.06.2004 14.02.2009 1691000
12 ПО 62-20 4 02.09.2001 04.08.2006 528000
13 НП 64-49 6 26.05.2008   684000
14 НС 18-79 1 05.10.2000 02.07.2005 160000
15 НП 51-19 1 18.01.2001 20.04.2006 930000
16 НФ 68-34 1 15.10.1984 10.06.1989 747000
17 НФ 57-03 6 23.12.2007   234000
18 НС 80-05 5 08.07.1996 27.01.2001 1400000
19 НФ 80-27 1 07.03.2008   188000
20 НФ 03-88 2 28.04.1983 12.07.1988 1241000



Таблица Водители
Структура
Поле Тип Размер поля/Примечание
Код_Водителя Счетчик -
Фамилия Текстовый 15
Имя Текстовый 10
Отчество Текстовый 15
ДатаРожд Дата/Время Краткий формат даты
ТрудСтаж Числовой -

Первичный ключ таблицы - поле Код_Водителя

Данные
Код_Водителя Фамилия Имя Отчество ДатаРожд ТрудСтаж
1 Беспалов Борис Петрович 09.09.1981 8
2 Богданов Степан Николаевич 05.03.1965 25
3 Воропанов Владимир Иванович 11.02.1956 34
4 Забалуев Дмитрий Владимирович 20.11.1975 13
5 Киль Сергей Афанасьевич 04.07.1972 19
6 Иванцов Иван Владимирович 02.03.1959 29
7 Бармасов Александр Степанович 14.04.1951 38
8 Кабанов Михаил Васильевич 16.03.1964 25
9 Барсуков Анатолий Степанович 26.05.1952 37
10 Кардашов Петр Борисович 23.12.1962 26
11 Климов Федор Алексеевич 09.01.1962 30
12 Бондаренко Валентин Николаевич 21.01.1965 26
13 Вершинин Валерий Ипатьевич 08.04.1971 21
14 Викторов Владимир Ильич 20.11.1974 17
15 Бельков Борис Семенович 20.04.1967 25
16 Гавриленко Владимир Иванович 22.01.1968 20
17 Горбачев Владимир Дмитриевич 08.12.1983 5
18 Гурьев Вячеслав Глебович 24.06.1981 9
19 Жалин Джон Георгиевич 10.02.1979 9
20 Ахатов Аким Федорович 25.06.1954 35
21 Карапетян Николай Васильевич 25.08.1974 18
22 Изергин Леонид Владимирович 17.04.1951 38
23 Киселев Сергей Анатольевич 25.11.1967 25
24 Капитоненко Михаил Васильевич 20.09.1955 35
25 Сидоров Порфирий Ахатович 21.04.1981 8



Таблица Маршруты
Структура
Поле Тип
Маршрут Числовой
Километраж Числовой

Первичный ключ таблицы - поле Маршрут

Данные
Маршрут Километраж
21 25
22 22
23 19
24 19
25 18
26 25
27 24
28 16
29 19
30 21
31 13
32 19
33 25
34 29



Таблица Путёвки (главная таблица)
Структура
Поле Тип Размер поля/Примечание
Дата Дата/Время Краткий формат даты
Автобус Числовой Длинное целое
Маршрут Числовой Длинное целое
Водитель Числовой Длинное целое
Выручка Числовой Длинное целое

Индексных полей (первичного ключа) в таблице нет. Внешние ключи - поля Автобус, Маршрут, Водитель

Данные
Дата Автобус Маршрут Водитель Выручка
08.03.2009 5 21 17 800
24.04.2009 6 21 3 120
21.05.2009 14 21 2 560
05.02.2009 16 22 10 540
08.03.2009 7 22 20 260
25.03.2009 10 22 10 540
17.10.2009 15 22 3 660
27.05.2009 5 23 15 860
22.09.2009 20 24 5 420
01.07.2009 8 25 7 380
15.11.2009 5 25 10 760
22.04.2009 18 26 7 160
24.06.2009 3 27 16 400
13.09.2009 15 27 9 480
28.06.2009 4 28 13 580
17.08.2009 6 28 13 240
14.11.2009 17 29 6 280
02.12.2009 9 29 8 760
14.05.2010 11 29 5 720
15.09.2010 12 29 21 920
01.02.2009 13 30 10 380
26.05.2009 6 30 23 180
10.09.2009 17 30 7 360
14.10.2009 13 30 18 380
24.12.2009 4 31 19 400
07.02.2009 16 32 1 660
18.10.2009 17 32 7 400
04.06.2009 1 33 5 400
27.08.2009 6 33 8 200
08.11.2009 2 33 6 480
27.02.2009 17 34 6 300
12.06.2009 15 34 3 600
19.07.2009 8 34 24 360
22.07.2009 19 34 1 520
28.12.2010 14 34 21 520

 

Таблица Марки
Структура
Поле Тип Размер поля/Примечание
Код_Марки Счетчик -
Марка Текстовый 8
Мест Числовой Длинное целое
Топливо Числовой Длинное целое
РасходНа100 Числовой Длинное целое

Первичный ключ таблицы - поле Код_Марки

Данные
Код_Марки Марка Мест Топливо РасходНа100
1 ЛИАЗ 34 1 34
2 ПАЗ 30 1 32
3 КаВЗ 25 1 22
4 Ikarus 46 3 15
5 ГАЗель 16 2 13
6 ЗиЛ 20 3 20



Таблица Топливо
Структура
Поле Тип Размер поля/Примечание
Код_Топл Счетчик -
Наим_Топл Текстовый 5
Цена Денежный Денежный

Первичный ключ таблицы - поле Код_Топл
Данные
Код_Топл Наим_Топл Цена
1 АИ-80 17,50р.
2 АИ-92 21,50р.
3 ДТ 19,50р.



2. Свяжите таблицы по ключевым полям так, как это показано на рисунке:

3. С помощью запросов к базе данных определите:

  1. Все рейсы водителя Кардашова П.Б. (поля Дата, ФИО водителя, ГосНомер, Марка, Маршрут и Километраж.)
    Для создания поля ФИО необходимо воспользоваться Построителем выражений, в котором следует сформировать выражение:
  2. Все маршрутные листы (путевки), выписанные на автобус с государственным регистрационным номером «НП 64-49» (поля те же, что и в предыдущем задании)
  3. Все маршрутные листы (путевки), с километражом, превышающим 25 км (поля те же, что и в предыдущем задании)
  4. Маршруты, на которых задействовано более 2-х автобусов (поля Маршрут и ГосНомер). При выполнении этого задания потребуется использование Групповых операций. Для поля Маршрут групповая операция - Группировка, Для поля ГосНомер групповая операция - Count (количество). Дополнительно для поля ГосНомер нужно задать условие отбора «>2»
  5. Рассчитайте износ каждого автобуса в процентах, исходя из номинального срока службы транспортного средства - 15 лет (Поля: ГосНомер, Марка, ДатаВыпуска, Износ). Поле Износ - вычисляемое поле, в которое нужно ввести с помощью Построителя выражений следующую строку:
    Здесь слово Износ - заголовок поля, выражение Date() получает значение системной даты, а строка Year(Date()) извлекает из системной даты значение текущего года. Аналогично строка Year([ДатаВыпуска]) выделяет из поля ДатаВыпуска значение года выпуска. Иными словами, здесь написано: из текущего года вычесть год выпуска и полученный результат - возраст машины - разделить на нормативный срок службы - 15 лет.
    Для того, чтобы получить проценты, нужно умножить полученное на 100 и в конце добавить символ «%». Но можно вместо этого задать процентный формат поля:


  6. Удельную нагруженность автобусов на маршрутах (отношение километража к количеству автобусов на маршруте). Для это потребуется 4 поля: Маршрут, Километров, Автобусов и Нагрузка. Из перечисленных в наличии имеется только поле Маршрут, остальные поля - вычисляемые.
    Вычисление поля Километров происходит на основе данных из поля Километраж с помощью групповой операции суммирования (Sum).
    Вычисление поля Автобусов происходит на основе данных из поля КодАвтобуса с помощью групповой операции счета (Count).
    После задания этих двух полей запрос следует сохранить.
    Вычисление поля Нагрузка происходит на основе данных из вновь созданных полей Километров и Автобусов с помощью групповой операции Выражение: их нужно просто поделить друг на друга. Если в Построителе выражений этих полей не видно, значит запрос не сохранён.
  7. Маршруты и Фамилия И.О. водителей, возраст которых превышает 50 лет или трудовой стаж которых превышает 25 лет
    Для это потребуется 4 поля: Маршрут, ФИО водителя, Возраст и Стаж(ТрудСтаж). Поле ФИО водителя вычисляется так же, как и аналогичное поле из задания а. Поле Возраст вычисляется аналогично расчету поля Износ из задания е.
  8. Суммарную общую выручку и километраж по каждому автобусу. Для получения запроса потребуются поля ГосНомер, Марка, Выручка и Километраж. Т.к. в последних двух полях будут находиться вычисляемые данные (групповые операции Sum), а Access в таких случаях «любит» переименовывать поля на свое усмотрение (типа: Sum-Выручка или Sum-Километраж), во избежание проблем в дальнейшем для этих двух полей лучше задать их имена явным образом: Выручка:Выручка  и Километраж:Километраж. Данный запрос нужно сохранить под именем Выручка&Километраж, т.к. он еще понадобится.
  9. Создайте еще один запрос на основе предыдущего запроса Выручка&Километраж и двух таблиц: Топливо и Марки. Свяжите таблицу Выручка&Километраж с таблицей Марки по полям Марка - Марка (иначе, если этого не сделать, то количество записей в результирующем запросе очень сильно возрастет). В этом запросе нужно выбрать все поля из таблицы Выручка&Километраж (для этого нужно выбрать поле *), поле РасходНа100, а также создать два вычисляемых поля: Литраж (количество литров топлива) и Расход (денежные затраты на покупку топлива):


    В поле Литраж должно вычисляться количество литров топлива на основе нормативного расхода (поле РасходНа100) и километража. В поле Расход вычисляются расходы на топливо на основе цены и литража.
  10. В этом же запросе самостоятельно создайте еще два вычисляемых поля - Прибыль и Рентабельность. Прибыль - это разность между полями Выручка и Расходы, Рентабельность - это отношение поля Прибыль к полю Расход. Для создания этих полей можно воспользоваться Построителем выражений. Отсортировав таблицу по этим двум полям можно сделать очень важные экономические выводы, например, наибольшая выручка или прибыль еще не означает, что данный автобус самый эффективный.
4. Задание еще не закончено. Продолжение следует