Acknowledgment

We, the members of this project team, would like to express our heartfelt gratitude to
everyone who supported and guided us in completing our project report on the Transport
Booking System.

First and foremost, we extend our sincere thanks to our project guide, Dr. Brojo Kishore
Mishra, for providing invaluable guidance, insightful feedback, and encouragement
throughout the course of this project. Your expertise has been pivotal in shaping the quality
and success of our work.

We are grateful to NIST University and the Department of Computer Applications for
offering the necessary resources and an environment conducive to learning and collaboration.

We also want to acknowledge the support of our classmates and peers for their constructive
suggestions and assistance during various stages of the project. Their insights and inputs
greatly enhanced the outcome of our work.

Lastly, we extend our deepest gratitude to our families and friends for their unwavering
support, patience, and encouragement, which motivated us to stay focused and work
diligently.

This project is a collective effort, and each team member has played a vital role in its
successful completion. We are proud to present the results of our hard work and
collaboration.

Team Members:
P KARTIKA RAO

MAMATA RAUTA

LIPUN NAHAK

ABHILIPSA PRADHAN

B SONALI PRIYADARSANI

SUNIL TAREI

RUDRA NARAYAN PRADHAN

 Master of Computer Applications
NIST University

Abstract
The transportation booking system is a critical solution in today’s fast-paced world, providing
an efficient and reliable platform for managing travel-related operations. This report delves
into the design and implementation of a transportation booking system that handles essential
functionalities such as passenger management, seat reservation, vehicle allocation, and
payment processing.

The project focuses on developing a well-structured database using a schema that ensures
data integrity, scalability, and seamless retrieval of information. It explores the relationships
between various entities, including passengers, vehicles, bookings, seats, and payments,
supported by a detailed Entity-Relationship Diagram (ERD). Key SQL operations, such as
creating tables, inserting records, updating data, altering table structures, and modifying
attributes, are demonstrated to highlight the system's practicality.

Real-world applications of the system include public transportation, ride-hailing services, and
logistics management, emphasizing its adaptability and relevance in various sectors. The
report also addresses the importance of such systems in enhancing operational efficiency,
improving user experience, and supporting decision-making processes.

This project provided an opportunity to apply theoretical knowledge in database management

systems to solve real-world challenges, fostering a deeper understanding of system design
and data handling. Through this report, we aim to showcase the comprehensive approach
undertaken to develop a transportation booking system that is both functional and scalable.
 TABLE OF CONTENTS
CONTENTS PAGE NO
1. Introduction
1.1 1.1 Definition of Transport Booking System
1-4
1.2 1.2 Importance of Transport Booking System

1.3 1.3 Real-World Applications

1.4 1.4 Objective of Study

2. Literature Review
2.1 Overview of Transport Booking System
5-7
2.2 Features of Transport Booking System
2.3 Benefits of Transport Booking System

3. Schema Of The Transport Booking System

2.1 Tables And Attributes 8-12

2.2 Relationships
2.3 Diagram Representation
4. SQL Statements and Operations 13-18

5. Conclusion 19

6. References 20
 INTRODUCTION
1.1 Definition of Transport Booking System

A transport booking system is a software application or platform designed to

facilitate the booking, management, and monitoring of transportation services. It acts as a
bridge between service providers and customers, streamlining the reservation and scheduling
process while offering a seamless experience. This system can cater to various transportation
modes such as taxis, buses, trains, airlines, and even freight or logistics services.

Core components of a transport booking system include user-friendly interfaces,

automated booking and scheduling features, real-time tracking capabilities, and secure
payment integration. These systems are designed to improve operational efficiency, enhance
user convenience, and optimize resource utilization.

1.2 Importance of Transport Booking System

Transport booking systems play a vital role in modern mobility solutions by automating the
reservation process, reducing manual errors, and providing transparency in pricing and
availability. They improve data management, ensure scalability, and enhance overall
customer satisfaction.

 Enhanced Efficiency

A transport booking system automates various aspects of the booking process, such as
reservations, cancellations, and confirmations. This reduces the time and effort
required to manage bookings manually and minimizes the chances of human error.
The system allows for better planning and resource allocation. By analyzing data on
ride or seat availability, peak times, and customer preferences, the system helps
optimize the scheduling of transport services, ensuring maximum utilization of
resources.

 Convenience for Users

Customers can access the booking system at any time, from anywhere, without being
limited by business hours. This level of accessibility offers great convenience for

1
 users, as they can make bookings, track rides, and make payments according to their
schedules.

 Data Management and Analytics

A transportation booking system collects and stores customer information, such as

booking history, preferences, and payment details, enabling businesses to tailor their
services to individual needs. By analyzing data from bookings, cancellations, and
feedback, companies can gain insights into customer behavior, identify trends, and
improve their services. This helps in making data-driven decisions for improving
operational performance.

 Cost Efficiency

Automation and efficient resource management reduce the need for manual
intervention, minimizing labor costs. Additionally, optimized scheduling reduces fuel
consumption and improves fleet utilization, leading to lower operational costs. The
system can handle a growing number of users and bookings without significant
increases in costs, making it scalable as the business expands.

 Improved Customer Satisfaction

With quick and easy booking processes, instant confirmations, secure payment
options, and real-time tracking, customers enjoy a smooth experience from start to
finish. Integrated features such as live chat, FAQs, or help desks ensure that users can
get assistance when needed, enhancing customer support and satisfaction.

 Business Growth and Competitive Advantage

A robust transportation booking system enables businesses to expand their reach to

new customers, geographical locations, and service offerings. With features like
loyalty programs, promotions, and integration with other platforms, businesses can
attract more customers and retain existing ones. Businesses with an efficient, user-
friendly, and reliable booking system can gain a competitive edge in the marketplace.
It helps service providers differentiate themselves and maintain customer loyalty in an
increasingly crowded market.

2
1.3 Real-World Applications

Transportation booking systems have become integral in various sectors, simplifying and
streamlining the way people and goods are transported. These systems are applied in diverse
industries, ranging from public transport to logistics, making them vital for both consumers
and service providers. Below are some real-world applications of transportation booking
systems:

1.3.1 Ride-Hailing Services

Uber, Ola, Lyft, and Other Services: Ride-hailing platforms have revolutionized
personal transportation by allowing users to book rides through a mobile app in real-
time. These services are built on transportation booking systems that handle user
requests, real-time tracking, and payment processing. The systems manage booking
requests, match passengers with available drivers, calculate fares, and provide
estimated times of arrival.

1.3.2 Public Transport Systems

 Bus and Train Ticketing: Public transportation providers, such as buses and trains,
use booking systems to handle ticket reservations, schedules, and real-time updates.
Users can check the availability of tickets, make reservations, and receive
confirmations instantly via mobile apps or websites.

1.3.3 Airlines and Travel Agencies

 Flight Booking Systems: Airlines use transport booking systems to manage

reservations for flights, allowing customers to book, modify, and cancel tickets. These
systems handle flight schedules, seat availability, passenger details, and payment
processing, offering a smooth booking experience.

 Travel Agencies: Travel agencies integrate these systems into their platforms,
enabling customers to book flights, hotels, and car rentals in one place. This
integration helps users plan their entire trip in one go.

1.3.4 Tourism and Package Services

3
  Tour Booking Systems: Travel and tour companies use booking systems to manage
group tours, excursions, and travel packages. These systems allow customers to book
tours, schedule pickups, and make payments online. It also manages details like group
sizes, destinations, itineraries, and pricing.

 Examples: Websites like TripAdvisor or Viator integrate transportation booking

systems with hotel reservations and tour packages to provide a seamless travel
experience for tourists.

1.3.6 Bike and Scooter Rentals

 Shared Mobility Services: Companies providing bike and scooter rentals, such as
Lime and Bird, rely on transport booking systems to allow users to locate and rent
bikes or scooters. These systems track the availability of bikes and handle the rental
process, including billing and returns.

 Examples: Apps like Lime and Spin allow users to rent electric bikes or scooters for
short trips in urban environments, with the system managing reservations and
payments.

1.4 Objectives of the Report

The primary objective of this report is to explore and demonstrate the functionality, design,
and implementation of a Transport Booking System through a detailed examination of its
components, processes, and database management. The report aims to provide an
understanding of how such systems work and the impact they have on improving
transportation services. Specifically, the objectives of this report are as follows:

 Explore the fundamental concepts of a transportation booking system and its components.

 Design and represent the schema of a transport booking database system.

 Define and explain relationships within the system to ensure data integrity and coherence.

 Implement SQL queries to demonstrate the creation, modification, and management of

database records.

 Perform operations to retrieve specific information based on real-world conditions and

scenarios.

 Emphasize the importance of structured data management in optimizing transport services.

4
2 Literature Review
2.1 Overview of Transport Booking System
A transportation booking system is a comprehensive software solution designed to facilitate
the reservation and management of transportation services. It is tailored to meet the needs of
both service providers and customers, streamlining the booking process while improving
overall efficiency.
This system allows users to book various types of transportation services, such as taxis, car
rentals, buses, trains, or freight services, through an intuitive digital platform. The system
typically incorporates features like:
 User-Friendly Interface: Customers can search for available transportation options,
compare prices, and book services seamlessly.
 Real-Time Availability: Provides up-to-date information on vehicle or seat
availability, ensuring accurate bookings.
 Integrated Payment Gateways: Facilitates secure and convenient payment methods,
such as credit/debit cards, digital wallets, and net banking.
 GPS and Tracking: Enables users to track their booked vehicle in real-time, enhancing
transparency and security.
 Route Optimization: Ensures efficient routes are selected, saving time and reducing.
operational costs.
 Admin Dashboard: Allows service providers to manage bookings, monitor vehicles,
analyze performance, and handle customer inquiries efficiently.
 Notifications and Alerts: Sends reminders, updates, and confirmations via email,
SMS, or push notifications.
 Customer Feedback: Collects user reviews and ratings to improve service quality and
customer satisfaction.
This system is widely applicable across various domains, including ride-hailing services,
logistics and delivery operations, corporate travel, and public transportation. By
leveraging technology, transportation booking systems reduce manual intervention,
minimize errors, and enhance the overall user experience, making them an essential tool
in the modern transportation industry.

5
2.2 Features Of Transport Booking System
A transportation booking system offers key features to ensure efficiency and user
convenience. It includes user-friendly registration, service search, and real-time
availability tracking. Integrated GPS allows vehicle tracking and optimized navigation,
while secure payment options streamline transactions. Users receive instant
notifications for booking confirmations and updates, and fare estimates ensure pricing
transparency. The system supports multi-language and multi-currency capabilities for
global reach and includes feedback mechanisms to enhance service quality. For
providers, an admin dashboard enables booking and driver management, performance
monitoring, and analytics. Security features like data encryption and emergency
support ensure user safety and trust.
Developing and implementing a transportation booking system involves overcoming
several technical, operational, and user-centric challenges:
1. Real-Time Availability and Tracking
Integrating accurate GPS and mapping services to track vehicles in real-time.
Ensuring the system reflects up-to-date availability, especially during peak times or
high demand.
2. Scalability and Performance
Handling a large volume of simultaneous bookings during rush hours or special
events.
Ensuring the system remains responsive under high traffic loads.
3. Payment Security and Integration
Providing secure payment gateways to prevent fraud and data breaches.
Supporting multiple payment methods, including digital wallets and international
transactions.
4. User Experience and Accessibility
Designing an intuitive interface for users with varying levels of technical proficiency.
Offering multi-language and multi-currency support to cater to a global audience.
5. Dynamic Pricing and Cost Transparency
Implementing algorithms for surge pricing while maintaining user trust.
Clearly communicating fare details and additional charges to avoid dissatisfaction.
6. Communication and Notifications

6
 Ensuring timely delivery of booking confirmations, reminders, and updates.
Handling cancellations or delays effectively while keeping users informed.
2.3 Benefits of Transport Booking System
A transportation booking system offers numerous benefits by enhancing efficiency and
convenience for users and service providers. It simplifies the booking process with real-time
availability and fare transparency, saving time and effort for customers. Integrated GPS and
tracking improve safety and provide accurate navigation. Secure payment gateways
streamline transactions, while notifications ensure users are informed about bookings and
updates. For providers, the system optimizes fleet and driver management, reducing
operational costs and boosting productivity. It also facilitates better customer engagement
through feedback mechanisms and provides valuable analytics to improve service quality and
business performance.

A transportation booking system provides significant advantages for both users and service
providers, enhancing operational efficiency, customer satisfaction, and business growth. Key
benefits include:

1. Convenience and Accessibility

o Users can book transportation services anytime, anywhere, using web or mobile
applications.
o Simplifies the process of finding and reserving vehicles with a few clicks.

2. Real-Time Updates

o Provides real-time availability of vehicles or services, ensuring accurate bookings.

o Enables GPS tracking, allowing users to monitor the location and status of their
bookings.

3. Time and Cost Efficiency

o Reduces the time spent on manual booking processes.

o Optimized routes and schedules save fuel and reduce operational costs for providers.

4. Secure and Streamlined Payments

o Integrates multiple payment options, including digital wallets and cards.

o Ensures secure transactions with encryption and fraud prevention measures.

7
3. Schema Of The Transport Booking System

The schema of the transport booking system is a blueprint that defines the structure of the
database, outlining the tables, attributes, data types, and relationships between them. This
schema is essential for managing and organizing the data required to handle booking
operations efficiently.

3.1 Tables And Attributes

 Passengers Table
Stores information about the users of the system, including customers and administrators.
Attributes:
o USERID (Primary Key): Unique identifier for each user.
o FULLNAME: Name of the user.
o Email: Email address of the user.
o PHONENUMBER: Contact number of the user.
o DATEOFBIRTH: date of birth of the user
o Gender: Specified the person

 Vehicles Table
Stores details about the vehicles available for booking.
Attributes:
o VEHICLEID (Primary Key): Unique identifier for each vehicle.
o VEHICLETYPE: Type of vehicle (e.g., car, bus, bike).
o Model: Model name of the vehicle.
o REGISTRATIONNUMBER: Vehicle registration number.
o SEATINGCAPACITY: Number of seats available.
o Status: Indicates whether the vehicle is available or not.

 Seats Table:
Tracks all bookings made by users.
Attributes:

8
 o SEATID (Primary Key): Unique identifier for each SEATPAYMENT.
o VEHICLEID (Foreign Key): References the VEHICLEID in the Bookings
table.
o SEATNUMBER: Numbering the seat.
o Availability: Cheak Available for seats.

 Bookings Table
Tracks all bookings made by users.
Attributes:
o BOOKINGID (Primary Key): Unique identifier for each booking.
o USERID (Foreign Key): References the USERID in the Users table.
o VEHICLEID (Foreign Key): References the VEHICLEID in the Vehicles table.
o BOOKINGDATE: Date and time of the booking.
o PICKUPLOCATION: Starting point of the trip.
o DROPLOCATION: Destination of the trip.
o BOOKINGSTATUS: Status of the booking (e.g., confirmed, cancelled,
completed).
o TotalFare: Total cost of the booking.

 Seat Bookings Table

Links booked seats to a specific booking.
Attributes:
o SEATBOOKINGID (Primary Key): Unique identifier for each payment.
o BOOKINGID (Foreign Key): References the BOOKINGID in the Bookings table.
o SEATID: References the SEATID in the SEATSS table.

 Payments Table
Manages payment details for bookings.
Attributes:
o PAYMENTID (Primary Key): Unique identifier for each payment.
o BOOKINGID (Foreign Key): References the BOOKINGID in the Bookings table.
o PAYMENTMETHOD: Mode of payment (e.g., credit card, debit card, UPI).

9
 o PAYMENTDATE: Date and time of the payment.
o AMOUNTPAID: Total amount paid by the user.
o PAYMENTSTATUS: Status of the payment (e.g., successful, failed).

3.2 Relationships Between Tables

Passengers to Bookings:
 A single user can make multiple bookings, but each booking is associated with only one
user.
 Relationship: One-to-Many.
Vehicles to Seats:
 A vehicle can have multiple seats.
 Relationship: One-to-Many.
Bookings to Seat Bookings:
 Each booking can have multiple assigned seats.
 Relationship: One-to-Many.
Bookings to Payments:
 Each booking can have one payment transaction.
 Relationship: One-to-One.
Vehicles to Bookings:
 One-to-Many (1:M): A vehicle can have multiple bookings, but each booking is related to
only one vehicle.
 Relationship: VEHICLEID in Bookings is a foreign key referencing VEHICLEID in
Vehicles.
Seats to SEATBOOKINGS:
 One-to-Many (1:M): A seat can be booked multiple times (for different bookings), but
each seat booking corresponds to one seat.
 Relationship: SEATID in SEATBOOKINGS is a foreign key referencing SEATID in
Seats.

10
3.3 Diagram Representation
Entity-Relationship (ER) Diagram
The Entity-Relationship Diagram (ERD) provides a visual representation of the database structure
for the transportation booking system. It illustrates the entities involved, their attributes, and the
relationships between them, enabling a clear understanding of how the system components interact.
Key Components of the ERD
1. Entities:
o Represented as rectangles, each entity corresponds to a table in the database.
o Examples include Passengers, Vehicles, Bookings, Seats, Seat Bookings, and
Payments.
2. Attributes:
o Attributes of each entity are listed inside the rectangles, representing the fields
or columns of the respective tables.
o Key attributes, such as primary keys and foreign keys, are highlighted to
denote their importance in maintaining data integrity.
3. Relationships:
o Represented using lines and labeled with cardinalities such as One-to-One or
One-to-Many.
o These relationships define how entities interact, e.g., a Passenger can make
multiple Bookings, and a Vehicle can have multiple Seats.
Purpose of the Diagram
The ERD serves as a blueprint for designing the database schema and helps:
 Identify data dependencies.
 Ensure normalization and reduce redundancy.
 Visualize the database structure for easier implementation and communication among
team members.

11
E-R DIAGRAM

12
4. SQL Statements and Operations
-- Passengers Table--
SQL>CREATE TABLE PASSENGERS (
USERID INT AUTO_INCREMENT PRIMARY KEY,
FULLNAME VARCHAR (100) NOT NULL,
EMAIL VARCHAR (100) UNIQUE,
PHONENUMBER VARCHAR (15),
DATEOFBIRTH DATE,
GENDER VARCHAR (15));
 Describe The Table.
SQL>DESC PASSENGERS;
Name Null? Type
USERID NOT NULL INT
FULLNAME VARCHAR (100)
EMAIL VARCHAR (100)
PHONENUMBER VARCHAR (15)
DATEOFBIRTH DATE
GENDER VARCHAR (15)
-- Insert into Passengers--
SQL>INSERT INTO PASSENGERS (FULLNAME, EMAIL, PHONENUMBER,
DATEOFBIRTH, GENDER) VALUES ('JOHN DOE', 'JOHNDOE@GMAIL.COM',
'9876543210', '1990-01-15', 'MALE'), ('JANE SMITH', 'JANESMITH@EXAMPLE.COM',
'9123456789', '1995-05-22', 'FEMALE');
SQL>INSERT INTO PASSENGERS (FULLNAME, EMAIL, PHONENUMBER,
DATEOFBIRTH, GENDER) VALUES ('NIKITA', 'NIKITA@GMAIL.COM',

13
 '8976543210', '1999-02-16', 'FEMALE'), ('KULDIP', 'KULDIP@GMAIL.COM',
'7833456789', '1979-05-19', 'MALE'), ('MANISHA', 'MANISHA@GMAIL.COM',
'9123456789', '1985-09-12', 'FEMALE');
-- UPDATE PASSENGER'S PHONE NUMBER—
UPDATE PASSENGERS SET PHONENUMBER = '9998887776' WHERE
PASSENGERID = 1;

SQL> SELECT * FROM PASSENGERS;

USERI FULLNAM EMAIL PHONENUMB DATEOFBIRT GENDE

D E ER H R
1 JOHN JOHNDOE@GMAIL.COM 9876543210 1990-01-15 MALE
DOE
2 JANE JANESMITH@EXAMPLE.C 9123456789 1995-05-22 FEMAL
SMITH OM E

3 NIKITA NIKITA@GMAIL.COM 8976543210 1999-02-16 FEMAL

E
4 KULDIP KULDIP@GMAIL.COM 7833456789 1979-05-19 MALE
5 MANISH MANISHA@GMAIL.COM 9123456789 1985-09-12 FEMAL
A E

-- Vehicles Table—
SQL>CREATE TABLE VEHICLES (
VEHICLEID INT AUTO_INCREMENT PRIMARY KEY,
VEHICLETYPE VARCHAR (50) NOT NULL,
MODEL VARCHAR (50),
REGISTRATIONNUMBER VARCHAR (50) UNIQUE NOT NULL,
SEATINGCAPACITY INT NOT NULL,
STATUS VARCHAR (15) DEFAULT 'AVAILABLE');
 Describe The Table.
SQL>DESC VEHICLES;
Name Null? Type
VEHICLEID NOT NULL INT

14
 VECHICLETYPE VARCHAR (50)
MODEL VARCHAR (50)
REGISTRATIONNUMBER VARCHAR (50)
SEATINGCAPACITY NOT NULL INT
STATUS VARCHAR (15)
-- INSERT INTO VEHICLES--
SQL>INSERT INTO VEHICLES (VEHICLETYPE, MODEL,
REGISTRATIONNUMBER, SEATINGCAPACITY, STATUS) VALUES ('BUS',
'VOLVO 9700', 'ABC1234', 50, 'AVAILABLE'), ('CAR', 'TOYOTA INNOVA', 'XYZ5678',
7, 'AVAILABLE'), ('TRAIN', 'METRO', 'WISPI987',100, 'AVAILABLE');

-- UPDATE VEHICLE STATUS—

UPDATE VEHICLES SET STATUS = 'UNAVAILABLE' WHERE VEHICLEID = 2;
UPDATE VEHICLES SET SEATINGCAPACITY= 200 WHERE VEHICLEID = 3;
-- DROP A COLUMN FROM THE VEHICLES TABLE--
ALTER TABLE VEHICLES DROP COLUMN MODEL;

SQL> SELECT * FROM VEHICLES;

VEHICLEID VECHICLETYP MODEL REGISTRATIONNUMBER SEATINGCAPACITY STATUS

E

1 BUS VOLVO ABC1234 50 AVAILABLE

2 CAR TOYOTA XYZ5678 7 AVAILABLE
INNOVA
3 TRAIN METRO WISEPI987 100 AVAILABLE

-- SEATS TABLE—
SQL>CREATE TABLE SEATS (
SEATID INT AUTO_INCREMENT PRIMARY KEY,
VEHICLEID INT NOT NULL, SEATNUMBER VARCHAR (10) NOT NULL,
AVAILABILITY VARCHAR (15) DEFAULT 'AVAILABLE',
FOREIGN KEY (VEHICLEID) REFERENCES VEHICLES(VEHICLEID));
 Describe The Table.
SQL>Desc Seats;
15
 Name Null? Type
SEATID NOT NULL INT
VEHICLEID NOT NULL INT
SEATNUMBER NOT NULL VARCHAR (10)
AVAILABILITY VARCHAR (15)

-- INSERT INTO SEATS--

INSERT INTO SEATS (VEHICLEID, SEATNUMBER, AVAILABILITY) VALUES (1,
'1A', 'AVAILABLE'), (1, '1B', 'AVAILABLE'), (2, '1A', 'BOOKED'),
(3,’2C’,’AVAILABLE’);
-- MODIFY A SEAT'S AVAILABILITY --
UPDATE SEATS SET AVAILABILITY = 'AVAILABLE' WHERE SEATID = 3;
SQL> SELECT * FROM SEATS;

SEATID VEHICLEID SEATNUMBER AVAILABILITY

1 1 1A AVAILABLE
2 1 1B AVAILABLE
3 2 1A BOOKED
4 3 2C AVAILABLE

-- BOOKINGS TABLE—
SQL>CREATE TABLE BOOKINGS (
BOOKINGID INT AUTO_INCREMENT PRIMARY KEY,
USERID INT NOT NULL, VEHICLED INT NOT NULL,
BOOKINGDATE DATETIME NOT NULL,
PICKUPLOCATION VARCHAR (255) NOT NULL,
DROPLOCATION VARCHAR (255) NOT NULL,
BOOKINGSTATUS VARCHAR (20) DEFAULT 'CONFIRMED',
TOTALFARE INT NOT NULL,
FOREIGN KEY (USERID) REFERENCES PASSENGERS(USERID),
FOREIGN KEY (VEHICLEID) REFERENCES VEHICLES(VEHICLEID)
);
 Describe The Table.
SQL>Desc Bookings;

16
 Name Null? Type
BOOKINGID NOT NULL INT
USERID NOT NULL INT
VEHICLEID NOT NULL INT
BOOKINGDATE DATETIME
PICKUPLOCATION VARCHAR (255)
DROPLOCATION VARCHAR (255)
BOOKINGSTATUS VARCHAR (20)
TOTALFARE INT

-- INSERT INTO BOOKINGS—

INSERT INTO BOOKINGS (PASSENGERID, VEHICLEID, BOOKINGDATE,
PICKUPLOCATION, DROPLOCATION, BOOKINGSTATUS, TOTALFARE) VALUES
(1, 1, '2024-12-22 10:00:00', 'NEW YORK', 'BOSTON', 'CONFIRMED', 50.00), (2, 2,
'2024-12-22 11:30:00', 'LOS ANGELES', 'SAN DIEGO', 'CONFIRMED', 30.00);
-- MODIFY THE DATA TYPE OF TOTALFARE IN THE BOOKINGS TABLE --
ALTER TABLE BOOKINGS MODIFY COLUMN TOTALFARE DECIMAL (12, 2);
-- MODIFY BOOKING TOTAL FARE --
UPDATE BOOKINGS SET TOTALFARE = 55.00 WHERE BOOKINGID = 1;

--SELECT COMMAND—
SELECT * FROM BOOKINGS;

BOOKINGID USERID VEHICLEID BOOKINGDAT PICKUPLOCATION DROPLOCATION

E

1 1 2024-12-22 10:00:00 NEW YORK BOSTON

2 2 2024-12-22 11:30:00 LOS ANGELES SAN DIEGO
BOOKINGSTATUS TOTALFARE
CONFIRMED 50.00
CONFIRMED 30.00

-- SEATBOOKINGS TABLE—

17
SQL>CREATE TABLE SEATBOOKINGS (
SEATBOOKINGID INT AUTO_INCREMENT PRIMARY KEY,
BOOKINGID INT NOT NULL, SEATID INT NOT NULL,
FOREIGN KEY (BOOKINGID) REFERENCES BOOKINGS(BOOKINGID),
FOREIGN KEY (SEATID) REFERENCES SEATS(SEATID));
 Describe The Table.
SQL>DESC SEATBOOKINGS;
Name Null? Type
SEATBOOKINGID NOT NULL INT
BOOKINGID NOT NULL INT
SEATID NOT NULL INT

-- INSERT INTO SEATBOOKINGS --

INSERT INTO SEATBOOKINGS (BOOKINGID, SEATID) VALUES (1, 1), (2, 3),(1,2);

--SELECT COMMAND—

SQL> SELECT * FROM SEATBOOKINGS;

SEATBOOKINGID BOOKINGID SEATID

1 1 1

2 2 3

3 1 2

-- PAYMENTS TABLE--
SQL>CREATE TABLE PAYMENTS (
PAYMENTID INT AUTO_INCREMENT PRIMARY KEY,
BOOKINGID INT NOT NULL, PAYMENTMETHOD VARCHAR (15),
PAYMENTDATE DATETIME NOT NULL, AMOUNTPAID INT NOT NULL,
PAYMENTSTATUS VARCHAR (25) DEFAULT 'SUCCESSFUL',
FOREIGN KEY (BOOKINGID) REFERENCES BOOKINGS(BOOKINGID));
 Describe The Table.
Desc payments;
Name Null? Type

18
PAYMENTID NOT NULL INT
BOOKINGID NOT NULL INT
PAYMENTMETHOD VARCHAR (15)
PAYMENTDATE DATETIME
AMOUNTPAID NOT NULL INT
PAYMENTSTATUS NOT NULL VARCHAR (25)

-- INSERT INTO PAYMENTS—

INSERT INTO PAYMENTS (BOOKINGID, PAYMENTMETHOD, PAYMENTDATE,
AMOUNTPAID, PAYMENTSTATUS) VALUES (1, 'CREDIT CARD', '2024-12-21
18:00:00', 50.00, 'SUCCESSFUL'), (2, 'UPI', '2024-12-21 19:30:00', 30.00,
'SUCCESSFUL');
Conclusion
The transportation booking system is an essential tool in modern travel and logistics
management. By implementing a robust database design, the system effectively handles
core functionalities such as passenger management, vehicle scheduling, seat reservations,
payment processing, and real-time seat availability. This report explored the fundamental
aspects of such a system, including schema design, relationships, and the execution of
various SQL operations.
The project emphasizes the importance of maintaining data integrity, reducing
redundancy, and ensuring scalability in database management. Through the creation of a
detailed schema and the use of SQL queries, we demonstrated how data can be efficiently
stored, manipulated, and retrieved to meet user requirements.
The real-world applications of this system extend to various domains, including public
transport, ride-hailing services, and logistics companies, showcasing its versatility. This
report highlights the significance of a well-structured database in improving operational
efficiency, enhancing user experience, and supporting business decision-making.
In conclusion, this project provided an opportunity to apply theoretical knowledge to a
practical scenario, enhancing our understanding of database management systems and
their role in developing efficient and scalable applications.

19
 References
1. Books:
o Date, C. J. (2019). An Introduction to Database Systems. Addison-Wesley.
o Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System
Concepts. McGraw-Hill.
2. Online Resources:
o Oracle Documentation: https://www.oracle.com/database
o MySQL Reference Manual: https://dev.mysql.com/doc
o PostgreSQL Official Documentation: https://www.postgresql.org/docs
o W3Schools SQL Tutorial: https://www.w3schools.com/sql
3. Tools Used:
o MySQL Workbench for database design and query execution.
o Draw.io for creating the ERD (Entity-Relationship Diagram).
o Microsoft Word for drafting and formatting the report.
4. Articles and Tutorials:
o “Understanding Database Design,” GeeksforGeeks. Available at:
https://www.geeksforgeeks.org/database-design
o SQL Commands Explained,” TutorialsPoint. Available at:
https://www.tutorialspoint.com/sql
5. Group Contributions:
o Contributions and discussions among the project team members: B Sonali

20
Priyadarsani, P Kartika Rao, Abhilipsa Pradhan, Lipun Nahak, Rudra
Narayana Pradhan, Sunil Tarei, Mamata Rauta .

21