База данных "Междугородние пассажирские перевозки"

Задание: Рассмотрим автовокзал, который занимается обслуживанием и учетом пассажиров на междугородных автобусных маршрутах. На автовокзале имеется расписание движения автобусов, содержащее информацию о маршрутах и рейсах.
Необходимо построить такую базу данных, в которой хранится информация как о характеристиках маршрутов, содержащаяся в расписании, так и информация о наличии мест на рейсы, и информация о пассажирах, купивших билеты на определенный рейс.
Так же необходимым условием является наличие 7-ми таблиц (без учёта таблиц связи).
Есть определённые наброски, а также имеются сомнения в их правильности. Поскольку для работы с таблицами нужно будет сделать приложение, то первое с чего мы начинаем это Логин и Пароль из таблицы User(чтобы получить соответствующий уровень доступа: Админ, диспетчер, кассир, водитель)(Сначала таблица была переделана под ключевое поле Login, но затем пришлось вернуть ID_User чтоб не палить не хэшированный логин). В ней же содержатся другие данные о сотруднике. Так как водитель автобуса должен иметь ВУ и может не быть сотрудником Автовокзала для него создана отдельная таблица Driver. Также эта таблица связана с перевозчиком (таблица Carrier), в которой для удобства работы добавлены все возможные поля. Таблица маршрут(Route) по сути дела является ориентировочным расписанием по которому диспетчер составляет таблицу Рейс(Trip), так как по концепции записи из расписания могут удаляться при его изменении (это единственный случай в нашей бд когда предусмотрено удаление), то в таблице Trip данные заносятся в более полном виде без каких либо связей с таблицей Route (диспетчер может создавать практически любые рейсы в зависимости от ситуации). Таблицы Trip и Ticket наиболее связанные. В запись о конкретном рейсе(trip) подтягиваются данные о водителе, а из него данные о перевозчике; номер автобуса (также можно проверить что водитель и автобус от одного перевозчика); количество проданных билетов увеличивается с каждой продажей билета (должно соответствовать Ticket по идее, можно конечно получить кол-во запросом в Ticket по ID_trip, но в реальном применении это будет самая разросшаяся таблица. я не знаю насколько быстро это будет происходить); дата и время отправления и прибытия по умолчанию являются комбинацией дня недели из Route с конкретной неделей в календаре, соответственно время добавляется к этой дате (диспетчер может ввести вообще любые данные по ситуации); цена билета копируется из Route программой, но может быть актуализирована диспетчером; поле отменён чтобы не удалять запись из таблицы; остальные поля - см. цена билета. Таблица Ticket: ID_Passenger связывается с таблицей Passenger; конкретный рейс на который продан билет, ID_cashier связано с ID_user для фио кассира; таблица Льготы(Benefits) связана с полем Льготы. Схема бд во вложении.
Не до конца понимаю сколько у меня таблиц связи. Правильно ли составлена схема бд? На сколько правильно делать связи без каскадного правила обновления и удаления если в бх в принципе не предполагается удаление строк? Для сохранения совместимости концепции не удаления было добавлено начало окончание доступа юзера а также архивный бит в bus. на сколько правильна такая концепция? не копировать же вообще всё в trip... Надо ли хэшировать логин (уже предусмотрено хэширование пароля)?

Миниатюры

 

0

ChatGPT в соседнем разделе!

0

немного аналитики - мои добрые советы

можно выделить таблицу UserType - Тип пользователя (админ, кассир...)
пункты отправления и назначения и остановки тоже хорошо бы вынести в отдельную таблицу - Town
TripStops (id_trip, id_town)
позже в приложении можно будет организовать по ним поиск
марку и модель автобуса вынести в отдельную таблицу - BusModel
день недели - можно прошить номер дня недели и конвертировать в название или в отдельную таблицу
фамилии, имена и отчества разделять - чтобы легче было организовать поиск
адреса тоже можно разделить на город и адрес внутри города делить не рекомендую.
тадлицы Route и Bus Связать таблицей RouteBus (id_route, id_bus)

1

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

0

Лучше, наверное, сделать таблицу MiddleRouteStops (id_route, id_town, stop_order).
Тогда для каждого маршрута получится список остановок.
stop_order указывает порядок сортировки остановок.
начало и конец маршрута лучше, конечно, помеcтить в таблицу Route.
Из таблицы Trip выкинуть пункт отправления и пункт назначения - они уже есть в таблице Route.
Разберётесь.
А Вы маркиза?

1

Поправка:
Из таблицы Trip выкинуть, но в таблицу Ticket добавить пункт отправления и пункт назначения - если пассажир входит или выходит на промежуточной остановке. В одной поездке разные пассажиры могут ехать до разных пунктов назначения. А для Trip отправление и назначение брать из таблицы Route.
Надо ещё предусмотреть возможность смны цены билета. Сделать таблицу Route_Prices с полями id_route, actual_date, цена билета. Выборку для цены билета брать для маршрута по последней дате.
Или даже ещё можно ввести цены для промежуточных остановок.

1