База данных системы «Туристическое бюро» предназначена для хранения информации
о маршрутах, автобусах, экипажах, членах экипажей, выполненных рейсах и начислениях экипажам.
Для обеспечения целостности данных используются первичные и внешние ключи,
ограничения и триггеры.
Основные таблицы
Route
Хранит сведения об экскурсионных маршрутах.
route_id— идентификатор маршрутаroute_name— название маршрутаstart_point— начальный пунктend_point— конечный пунктdistance_km— протяженность маршрута
Bus
Хранит сведения об автобусах.
bus_id— идентификатор автобусаbus_number— номер автобусаbus_name— название автобусаtotal_mileage_km— общий пробег автобуса
Crew
Хранит сведения об экипажах.
crew_id— идентификатор экипажаbus_id— идентификатор автобуса, за которым закреплен экипаж
CrewMember
Хранит сведения о членах экипажа.
member_id— идентификатор члена экипажаsurname— фамилияpersonnel_number— табельный номерexperience_years— стажcategory— категорияaddress— адресbirth_year— год рожденияcrew_id— идентификатор экипажа
Trip
Хранит сведения о выполненных рейсах.
trip_id— идентификатор рейсаdeparture_date— дата отбытияarrival_date— дата прибытияpassenger_count— количество пассажировticket_price— стоимость билетаroute_id— идентификатор маршрутаbus_id— идентификатор автобуса
CrewPayments
Хранит информацию о начислениях экипажам за определённый период.
payment_id— идентификатор записиcrew_id— идентификатор экипажаperiod_start— начало периодаperiod_end— конец периодаpercent_rate— процент начисленияtotal_amount— сумма начисленийcalc_date— дата расчёта
User
Хранит данные пользователей системы.
user_id— идентификатор пользователяlogin— логинpassword— парольrole— роль пользователяcrew_member_id— ссылка на члена экипажа
Связи между таблицами
- Один маршрут может использоваться во многих рейсах.
- Один автобус может участвовать во многих рейсах.
- Один экипаж закреплен за одним автобусом.
- Один экипаж состоит не более чем из 3 членов экипажа.
- Один рейс связан с одним маршрутом и одним автобусом.
- Один пользователь с ролью
crewсвязан с одним членом экипажа.
Ограничения целостности данных
- Первичные ключи
PRIMARY KEYдля уникальной идентификации записей. - Внешние ключи
FOREIGN KEYдля обеспечения связей между таблицами. - Ограничения
UNIQUEдля предотвращения дублирования данных. - Ограничения
CHECKдля проверки корректности значений.
Дополнительно используются триггеры:
check_crew_member_limit— ограничивает количество членов экипажа.validate_trip_before_insert— проверяет корректность данных при добавлении рейса.
Реализация бизнес-логики
Так как в SQLite отсутствует поддержка хранимых процедур, необходимая функциональность реализована через триггеры и SQL-запросы.
- Проверка данных при добавлении рейсов — с помощью триггеров.
- Расчёт начислений экипажам — с использованием
INSERT INTO ... SELECT ...с сохранением результата в таблицуcrew_payments.
Схема базы данных
Файлы базы данных
Вывод
Разработанная схема базы данных отражает структуру предметной области,
обеспечивает целостность данных и позволяет выполнять аналитические запросы,
включая расчёт доходов и формирование отчётов.