Лидеры рейтинга

ID: 226425

Konstantin Shvetski

Мастер-Эксперт

944

Россия, Северодвинск


ID: 259041

Алексеев Владимир Николаевич

Мастер-Эксперт

596

Россия, пос. Теплоозёрск, ЕАО


ID: 401284

Михаил Александров

Академик

352

Россия, Санкт-Петербург


ID: 137394

Megaloman

Мастер-Эксперт

308

Беларусь, Гомель


ID: 400669

epimkin

Профессионал

292


ID: 400484

solowey

Профессор

95


ID: 401888

puporev

Профессор

52

Россия, Пермский край


8.1.6

02.01.2021

JS: 2.2.2
CSS: 4.2.0
jQuery: 3.5.1


 

Установка и настройка серверов баз данных, таблицы и запросы.

Администратор раздела: Коцюрбенко Алексей Владимирович (Старший модератор)


Коцюрбенко Алексей Владимирович
Статус: Старший модератор
Рейтинг: 2132
solowey
Статус: Профессор
Рейтинг: 95
Alexander Babich
Статус: Профессор
Рейтинг: 0
 

Перейти к консультации №:
 

Консультация онлайн # 187740
Раздел: • Базы данных
Автор вопроса: Aleksandrkib (Посетитель)
Дата: 11.02.2014, 16:02
Поступило ответов: 1

Здравствуйте! У меня возникли сложности с таким вопросом:

Разработать структуру БД "Эксплуатация машин" (в MS Access) для первых пяти дней недели работы автопредприятия.
1. Самостоятельно определить:
- количество таблиц в БД;
- типы данных из предлагаемого генерального списка полей: Марка машины, Изображение машины, Номер, Суточный пробег, Дата выезда, Время выезда, Время возвращения, Старший машины, Расход топлива на 100 км.
- ключевое поле (при необходимости создать искусственно).
2. Установить межтабличные связи.
3. Заполнить БД, указав не менее 4 автомобилей, выезжающих в один день (хотя бы поля: марка машины, суточный пробег, расход топлива на 100 км).

Заполнить готовую БД теоретически несложно, но вот практически... Машинами я не занимаюсь и имею весьма расплывчатое представление про марки машин, их суточный пробег, расход топлива на 100 км.

Последнее редактирование 11.02.2014, 16:10 Андреенков Владимир (Академик)

Состояние: Консультация закрыта

Ответ # 272662 от Филатов Евгений Геннадьевич

Здравствуйте, Александр!

Создана база со следующими таблицами:
Tip справочник видов автомобилей
Kod счетчик, ключевое поле
Tip строка ( 20 ) 1-легковой, 2-грузовой

SprAvto Справочник наименований и характеристик автомобилей
Kod счетчик, ключевое поле
Name строка ( 50 ) марка автомобиля
Gruz целое грузоподъемность, кг ( только для грузовых )
Vid = Tip.Kod

Avto Справочник автомобилей организации
Kod счетчик, ключевое поле
Avto = SrAvto.Kod
GosNom строка ( 12 ) госномер
NomKuz строка ( 12 ) номер кузова
NomDvig строка ( 12 ) номер двигателя
NomShas строка ( 12 ) номер шасси
Photo строка ( 150 ) путь к файлу фото автомобиля ( не используется в запросах )
Rash целое расход, л/100км
Otv строка ( 50 ) ФИО ответственного

Smena таблица выездов автомобилей
Kod счетчик, ключевое поле
Mash = Avto.Kod
Dat краткий формат даты дата смены
Beg краткий формат времени время выезда
Kon краткий формат времени время заезда
NacProb целое показания пробега при выезде
KonProb целое показания пробега при въезде
В течение одного дня один автомобиль совершает один выезд. Без перехода смены на следующий день.

Базы справочников заполнены, база движений заполнена с 3.2.2014 по 7.2.2014 – рабочая неделя. Поэтому при отборе дат вводится всего один параметр – текущая дата. Отчеты за период, для которых нужно вводить два параметра – начальная и конечная дата отчета, не создавались.

Было создано несколько вспомогательных запросов

1. Список дат
SELECT [dat] FROM Smena GROUP BY [dat];

2. Количество выездов
SELECT [dat], count([kod]) AS viezd FROM Smena GROUP BY [dat];
Суммирует количество выездов по датам

3. Пробег и время работы
SELECT ([konprob]-[nacprob]) AS probeg, datediff('n',[beg],[kon]) AS rabota, *
FROM Smena ORDER BY [dat], [mash];
Основной запрос, по данным которого создаются все остальные. Выдает значения суточного пробега для каждого автомобиля и время рабочей смены в минутах.

4. Расход топлива
SELECT [probeg]/100*[rash] AS rashod, a.kod AS kod_avto, *
FROM [Пробег и время работы] AS pr INNER JOIN Avto AS a ON pr.mash=a.Kod;
Определяется расход топлива на основании суточного пробега и нормы расхода.

5. Расход по возрастанию
SELECT [dat], [rashod], [mash], * FROM [Расход топлива] ORDER BY [dat], [rashod], [mash];

6. Расход по убыванию
SELECT [dat], [rashod], [mash], * FROM [Расход топлива] ORDER BY [dat], [rashod] DESC , [mash];

7. Пробег по возрастанию
SELECT [dat], [probeg], [mash], * FROM [Расход топлива] ORDER BY [dat], [probeg], [mash];

8. Пробег по убыванию
SELECT [dat], [probeg], [mash], * FROM [Расход топлива] ORDER BY [dat], [probeg] DESC , [mash];

Запросы 5-8 используются для формирования статистических отчетов за смену.

9. Минимальный пробег
SELECT s.dat,
(select top 1 probeg from [Пробег по возрастанию] ss1 where ss1.dat=s.dat) AS prob,
(select top 1 mash from [Пробег по возрастанию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;

10. Максимальный пробег
SELECT s.dat,
(select top 1 probeg from [Пробег по убыванию] ss1 where ss1.dat=s.dat) AS prob,
(select top 1 mash from [Пробег по убыванию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;

11. Минимальный расход
SELECT s.dat,
(select top 1 rashod from [Расход по возрастанию] ss1 where ss1.dat=s.dat) AS ras,
(select top 1 mash from [Расход по возрастанию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;

12. Максимальный расход
SELECT s.dat,
(select top 1 rashod from [Расход по убыванию] ss1 where ss1.dat=s.dat) AS ras,
(select top 1 mash from [Расход по убыванию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;

В запросах 9-12 используются вложенные подзапросы. Разберем их подробнее на примере
(select top 1 probeg from [Пробег по возрастанию] ss1 where ss1.dat=s.dat)
Данные берутся из запроса [Пробег по возрастанию] , в котором данные отсортированы в порядке возрастания расхода за каждую дату. Для начала отбираются только те данные, которые удовлетворяют условию нужной даты - where ss1.dat=s.dat . Т.к. за эту дату может быть несколько записей, а нам нужно выбрать минимальную, то возвращаем только первую строку - top 1 . Поскольку во вложенном запросе может возвращаться только одно значение, то указываем его – используем поле probeg. Именно поэтому приходится использовать два запроса для вывода данных о минимальном пробеге за этот день и код автомобиля, у которого это значение. Если не нужно знать, у какого автомобиля минимальный пробег за этот день, то можно не использовать второй вложенный запрос.

Вот и добрались до запросов для задания.

13. 1_Расход топлива с параметром даты и нужными полями вывода
SELECT [SprAvto].[name],
[Расход топлива].[gosnom], [Расход топлива].[otv],
[Расход топлива].[beg], [Расход топлива].[kon],
[Расход топлива].[probeg], [Расход топлива].[rashod]
FROM [Расход топлива] INNER JOIN SprAvto ON [Расход топлива].[avto]=[SprAvto].[Kod]
WHERE ((([Расход топлива].[Dat])=[ДатаОтчета]));
Используем соединение INNER JOIN для получения данных о марке авто из справочника.

14. 2_Расход топлива в течение недели автомобилями с нужными полями
SELECT [Расход топлива].[dat], [SprAvto].[name],
[Расход топлива].[gosnom], [Расход топлива].[otv],
[Расход топлива].[beg], [Расход топлива].[kon],
[Расход топлива].[probeg], [Расход топлива].[rashod]
FROM [Расход топлива] INNER JOIN SprAvto ON [Расход топлива].[avto]=[SprAvto].[Kod]
ORDER BY [SprAvto].[name], [Расход топлива].[gosnom], [Расход топлива].[dat];
Сортировка производится по наименованию автомобиля, его госномеру и дате смены.

15. 3_Статистика по дням с нужными полями
SELECT [Количество выездов].[dat],
[Количество выездов].[viezd],
[Минимальный пробег].[prob],
[Максимальный пробег].[prob]
FROM ([Минимальный пробег] INNER JOIN [Максимальный пробег] ON [Минимальный пробег].[dat]=[Максимальный пробег].[dat]) INNER JOIN [Количество выездов] ON [Максимальный пробег].[dat]=[Количество выездов].[dat];
В этом запросе соединено три отчета со статистикой. Т.к. во всех используются одни и те же исходные данные, то за конкретную дату будут данные либо во всех таблицах ( если был хотя бы один выезд в этот день ) , либо данных не будет и эта дата выводиться не будет ( если выездов не было ) .

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

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

Базу ( немного подкорректировал ) отправлю на почтовый ящик.

С уважением.


Консультировал: Филатов Евгений Геннадьевич
Дата отправки: 14.02.2014, 20:22

Рейтинг ответа:

0

[подробно]

Сообщение
модераторам

Отправлять сообщения
модераторам могут
только участники портала.
ВОЙТИ НА ПОРТАЛ »
регистрация »

Мини-форум консультации № 187740
Филатов Евгений Геннадьевич

1

= общий = |  11.02.2014, 19:07

могу для начала предложить черновик состава таблиц БД

SprAvto сравочник наименований автомобилей
Kod счетчик
Name текст ( 50 ) марка

Avto справочник автомобилей
Kod счетчик
Tip = SprAvto.Kod ссылка на код марки
GosNom текст ( 12 ) регистрационый номер
NomKuz текст ( 12 ) номер кузова
NomDvig текст ( 12 ) номер двигателя
NomShas текст ( 12 ) номер шасси
Photo текст ( 50 ) имя файла с фото ( фотографии в БД хранятся по-другому )

SprRash
Avto = Avto.Kod целое ссылка на код марки
Dat дата с какого момента действует эта норма расхода
Rash целое значение расхода на 100 км

SprStar
Avto = Avto.Kod целое ссылка на код марки
Dat дата с какого момента этот ответственный
Otv текст ( 50 ) ФИО

Smena
Avto = Avto.Kod целое ссылка на код марки
NacDat дата начало выхода в рейс
NacProb целое начальное значение счетчика пробега
KonDat дата конец рейса
KonProb целое конечное значение счетчика пробега

Таблицы SprRash и SprStar можно не создавать – хранить необходимые значения в Avto

Филатов Евгений Геннадьевич

2

= общий = |  13.02.2014, 15:13

Что-то нет ответа от автора вопроса. Сегодня обдумаю и напишу ответ на вопрос. Без таблиц SprRash и SprStar .

Aleksandrkib

Посетитель

ID: 317729

3

= общий = |  13.02.2014, 15:30 |  цитировать |  профиль |  личное сообщение

Здравствуйте, Евгений Геннадьевич! Задание необходимо выполнить в программе MS Access (желательно 2007 или 2010), поля называть русскими буквами и отправить mdb-файл. Мне ну хотя бы 2 задания (сам "скелет" БД). Заполнить БД могу и сам, но не знаю, чем. В машинах я особо не разбираюсь. Если заполните поля "Марка машины", "Суточный пробег", "Расход топлива" или дадите ссылку, где такую информацию можно почерпнуть, буду очень признателен. Но на заполнении БД я не настаиваю (т.к. не знаю, позволяет ли это сделать стоимость вопроса).
Размещаю вопрос по просьбе другого человека. Пусть потом сам и заполняет.

Филатов Евгений Геннадьевич

4

= общий = |  13.02.2014, 16:02
Aleksandrkib:

Добрый день.
К сожалению, у меня только Access2000. Но там можно сделать преобразование к текущей версии. Я создам таблицы, настрою связи, заполню данными и добавлю несколько запросов для вывода сводных данных. Часа через 2.

Aleksandrkib

Посетитель

ID: 317729

5

= общий = |  13.02.2014, 16:25 |  цитировать |  профиль |  личное сообщение

Ничего, пусть будет Access2000.
Насчёт запросов. Если, конечно, Вас не затруднит и позволит стоимость вопроса, к этой работе как раз есть задания на составление запроса:
- запрос с параметром, в котором при вводе в окно параметра Дата выезда на экран будут выводиться данные о марке машины, её номере, старшем машины, времени выезда и возвращения;
- запрос с вычисляемым полем, с помощью которого можно получить информацию о расходе топлива каждой автомашиной в течение недели;
- итоговый запрос о количестве автомашин, выезжающих в каждый день с указанием минимального и максимального суточного пробега.

Филатов Евгений Геннадьевич

6

= общий = |  13.02.2014, 17:20
Aleksandrkib:

С запросами почти понятно.
1. Поездка осуществляется в течение дня? Т.е. выезд, например, утром, а заезд вечером. Или смена может начаться вечером, а закончиться утром? Для упрощения желательно, чтобы смена а проходила в течение одного дня раз в сутки для одного автомобиля. Тогда будет удобно отбирать по дате.
2. В расходе топлива хранится усредненное значение для простоты расчетов. В теории расход немного больше, т.к. зависит от времени года ( зимой прогрев автомобиля, но расход на километраж меньше ) , маршруте движения - городской или пригородный и т.п. Поэтому упрощаем и берем среднее значение, зависящее от километража.
3. Для чего указать максимальный и минимальный суточный пробег? Отдельно для каждого автомобиля на период запроса, или указать для каждого дня марку автомобилей, у которых был минимальный и максимальный расход в этот день и их значения? Можно просто два отдельных запроса.
4. Скорее всего, в каждом запросе необходим ввод параметров - текущая дата для запроса 1 или начальная и конечная дата для остальных. Тогда не нужно определять первый и последний день недели, а пользователь будет сам вводить даты.
Как писал ранее, черновик базы будет через 2-3 часа. полный ответ ночью или завтра к обеду. Оплаты хватит.

Филатов Евгений Геннадьевич

7

= общий = |  13.02.2014, 20:52
Aleksandrkib:

Вот черновик базы. Много запросов пока не сделал. И не заполнил некоторые необязательные значения типа госномера и проч.
база

Филатов Евгений Геннадьевич

8

= общий = |  13.02.2014, 23:56
Aleksandrkib:

блин. что-то снова с хранилищем файлов. сделал все и заполнил данными, а загрузить файл не могу. попробую чуть позже.

Aleksandrkib

Посетитель

ID: 317729

9

= общий = |  14.02.2014, 03:33 |  цитировать |  профиль |  личное сообщение

Да, с хранилищем проблемы. Я разместил ещё один вопрос 187739. Там я замучился с загрузкой файлов. Файлы загрузились в "Мои файлы", всё нормально, а при скачивании либо открывается пустой файл, либо вообще загрузки не происходит.
В итоге я закачал файлы на DepositFiles и дал ссылки.
Наверное, в данном случае лучше использовать любой файлообменник.
С запросами сделайте так, как Вам удобно.

Филатов Евгений Геннадьевич

10

= общий = |  14.02.2014, 09:27
Aleksandrkib:

снова не записывает. есть ящик? мой senoj@mail.ru . напиши - отправлю базу.

Aleksandrkib

Посетитель

ID: 317729

11

= общий = |  15.02.2014, 09:28 |  цитировать |  профиль |  личное сообщение

Огромное спасибо Вам за решение!!!

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