Case Study: Public Bus Ticket Management System

Executive Summary

This paper presents the design and development of a comprehensive Public Bus Ticket Management System, aimed at addressing the operational inefficiencies experienced by bus operators, drivers, passengers, and bus owners. The system integrates key entities such as Buses, Drivers, Passengers, Tickets, Routes, Bus Owners, and Insurance Details. By digitizing the ticketing process and centralizing information, the system enhances the efficiency, accuracy, and reliability of managing bus operations. This document outlines the design process, including the creation of an Entity$-$Relationship Diagram $($ERD$),$ data normalization, and the integration of SQL queries, ultimately leading to a robust and scalable database system.

Introduction

Public transportation, particularly bus services, faces challenges in managing and coordinating daily operations, including ticket sales, passenger records, and vehicle management. Traditional manual systems often result in inefficiencies such as booking errors, revenue discrepancies, and inadequate communication between drivers, bus owners, and passengers. This case study explores the development of a Public Bus Ticket Management System designed to streamline these processes by integrating various operational aspects into a single digital platform. The system manages entities such as Drivers, Buses, Passengers, Tickets, Routes, and Insurance, ensuring seamless coordination between all stakeholders.

Assumptions

$1.$ The system requires a unified digital platform to manage tickets, buses, drivers, and passengers, accessible only by authorized users.

$2.$ Consistency in data format across all entities $($e$.$g$.,$ ticket numbers, passenger details, bus information$)$ is crucial to avoid errors and ensure accurate records.

$3.$ The system should be extensible to accommodate additional features, such as multi$-$modal transport options, without compromising performance or data integrity.

$4.$ Strict access control must be enforced to prevent unauthorized access to sensitive information, such as financial data and personal passenger details.

$5.$ The system must adhere to data integrity rules, including the use of appropriate data types for primary keys and enforcing referential integrity through foreign keys.

$6.$ The system should be compatible with other tools, such as accounting software and mobile applications for ticket bookings.

$7.$ Regular backups must be scheduled to prevent data loss and ensure quick recovery in case of system failures.

Entity$-$Relationship Diagram $($ERD$)$

Relational Schema

Entities and Attributes

$1.$ Buses

o Attributes: BusID $($PK$),$ BusNumber, Capacity, BusOwnerID $($FK$),$ InsuranceID $($FK$)$

$2.$ Drivers

o Attributes: DriverID $($PK$),$ Name, LicenseNumber, Phone

$3.$ Passengers

o Attributes: PassengerID $($PK$),$ Name, Email, Phone

$4.$ Tickets

o Attributes: TicketID $($PK$),$ PassengerID $($FK$),$ BusID $($FK$),$ RouteID $($FK$),$ BookingDate, SeatNumber, Fare

$5.$ Routes

o Attributes: RouteID $($PK$),$ RouteName, StartPoint, EndPoint, Distance

$6.$ BusOwners

o Attributes: BusOwnerID $($PK$),$ Name, Phone, Email, Address

$7.$ InsuranceDetails

o Attributes: InsuranceID $($PK$),$ BusID $($FK$),$ PolicyNumber, InsuranceCompany, ValidFrom, ValidTo

Entity Relationships

$1.$ Buses and Drivers

o One$-$to$-$Many: A bus can have multiple drivers, but a driver is assigned to one bus at a time.

o Relationship: BusID $->$ Drivers $($BusID$)$

$2.$ Buses and Tickets

o One$-$to$-$Many: A bus can have many tickets issued, but each ticket is for a single bus.

o Relationship: BusID $->$ Tickets $($BusID$)$

$3.$ Passengers and Tickets

o One$-$to$-$Many: A passenger can book multiple tickets, but each ticket is associated with one passenger.

o Relationship: PassengerID $->$ Tickets $($PassengerID$)$

$4.$ Routes and Tickets

o One$-$to$-$Many: A route can have multiple tickets issued, but each ticket is associated with one route.

o Relationship: RouteID $->$ Tickets $($RouteID$)$

$5.$ Buses and InsuranceDetails

o One$-$to$-$One: Each bus has one insurance detail.

o Relationship: BusID $->$ InsuranceDetails $($BusID$)$

$6.$ BusOwners and Buses

o One$-$to$-$Many: A bus owner can own multiple buses, but each bus is owned by one owner.

o Relationship: BusOwnerID $->$ Buses $($BusOwnerID$)$

Business Rules

$1.$ Bus Management

o A bus must be associated with a bus owner and have valid insurance details.

o Each bus must have a unique BusID.

$2.$ Driver Management

o Each driver must have a unique DriverID and be licensed to operate a bus.

o A driver can be assigned to one bus at a time, but may drive different buses over time.

$3.$ Passenger Management

o Each passenger must have a unique PassengerID.

o Passengers can book multiple tickets, but each ticket must be associated with one route and one bus.

$4.$ Ticket Management

o A ticket must be linked to a bus, a route, and a passenger.

o Each ticket must have a unique TicketID and must include seat number and fare details.

$5.$ Route Management

o Each route must have a unique RouteI make it very professional