Design Bus Reservation Database System | Database Design and Implementation Help

realcode4you
Jul 1, 2022
3 min read

INTRODUCTION

This system is basically concerned with online reservation of BUS tickets for passengers who are traveling with HARIOM TRAVELLERS.
In this project we are trying to educate how reservation is done.
Keep track of all of its passengers and schedule their journey accordingly.
Maintaining records of passenger travelling in the different bus on different dates reaching different destinations.
Maintaining records of passenger travelling in the different buson different dates reaching different destinations.

SCOPE OF STUDIES

All the manual work should be converted and computerized so that the manual work of employees will be reduced.
The database should be stored in a computer rather than storing all the records manually.
Introducing a new online ticketing system is not only technological innovation but also will improve the BUSservices to a certain extent. It solves the difficult problems of BUSticketing.

FEATURES

Surfing data is easy.
The wait time ofpassengers will be reduced.
Accuracy of the information.
It is a fast process.
Data is efficient

These are the following entities that are related to reservation.

DRIVER
BUS
CUSTOMER
PAYMENTS
RESERVATION
TRANSACTION REPORT

1.DRIVER and itsattributes.

DRIVER_ID INT PRIMARY KEY
DRIVER_NAME VARCHAR
BUS_ID INT FOREIGN KEY

2.BUS and its attributes.

BUS_ID INT PRIMARY KEY
BUS_NO INT
BUS_TYPE VARCHAR
BUS_SEATS INT
DRIVER_ID INT FOREIGN KEY

3.PASSENGER and its attributes.

PASSENGER_ID INT PRIMARY KEY
FNAME VARCHAR
LNAME VARCHAR
GENDER VARCHAR
AGE INT
CONTACT_NO INT

4.PAYMENTS and its attributes.

PAYMENT_ID INT PRIMARY KEY
PASSENGER_ID INT FOREIGN KEY
RESERVATION_ID VARCHAR FOREIGN KEY
PAYMENT_DATE DATE

5.RESERVATION and its attributes.

RESERVATION_ID VARCHAR PRIMARY KEY
PASSENGER_ID INT FOREIGN KEY
BUS_ID INT FOREIGN KEY
DEPARTURE_TIME DATETIME
DESTINATION VARCHAR
RESERVATION_DATE DATE

6.TRANSACTION_REPORT and its attributes.

REPORT_ID INT PRIMARY KEY
PASSENGER_ID INT FOREIGN KEY
RESERVATION_ID VARCHAR FOREIGN KEY
PAYMENT_ID INT FOREIGN KEY
REPORT_DATE DATE

TABLES CREATION

1.CREATE DATABASEBUS_RESERVATION_SYSTEM

2.ADDING TABLE DRIVER TO DATABASE

3.ADDING TABLE BUS TO DATABASE

4.ADDING TABLE PASSENGER TO DATABASE

5.ADDING TABLE PAYMENTS TO DATABASE

6.ADDING TABLE RESERVATION TO DATABASE

7.ADDING TABLE TRANSACTION_REPORT TO DATABASE

8.ADDING TABLE TRANSACTION_REPORT TO DATABASE

INSERTING VALUES INTO TABLES

1.SELECT * FROM DRIVER

2.SELECT * FROM BUS

3.SELECT * FROM PASSENGER

4.SELECT * FROM PAYMENTS

5.SELECT * FROM RESERVATION

6.SELECT * FROM TRANSACTION_REPORT

SUB QUERIES

1. Count the total number of BUSES HAVNG AC BUSTYPE.

2. Display WHOLE DATA of the DRIVERS starting from LETTER R.

3. Display WHOLE DATA of the DRIVER who drive the bus with BUS_ID=101.

4. Display WHOLE DATA of DRIVER_ID 40.

5. Display THE BUSES having SEATS 35.

5. Count the total number of BUSES HAVNG NON AC BUSTYPE.

6.DISPLAY FIRST 5 PASSENGERS DETAILS

7.DISPLAY ONLY MALE PASSENGERS WITH DETAILS

8.DISPLAY PASSENGERS DETAILS IN ASCENDING ORDER

9.DISPLAY ONLY FEMALE PASSENGERS

10.DISPLAY THE RECORD OF PASSENGER HAVING LASTNAME BHENDE

11.CHANGE THE CONTACT NUMBER OF PASSENGER NIRMAL.

12.CHANGE THE DATATYPE OF CONTACT_NO FROM INT TO VARCHAR.

13.DISPLAY DETAILS OF PASSENGER HAVING AGE LESS THAN OR EQUAL TO 30

14.DISPLAY THE TOTAL NUMBERS OF RESERVED PASSENGER AS TOTAL_NO_PASSENGER.

15.DISPLAY THE PAYMENT STATUS HAVING PASSENGER_ID 1 AND PAYMENT_DATE 09/09/2022

16.DISPLAY THE BUS_ID OF HAVING DESTINANTION MUMBAI.

17.DISPLAY PAYMENT_ID OF PASSENGER_ID 1.

JOINS

1.INNER JOINS

DISPLAY THE FIRSTNAME WITH THERE RESPECTIVE RESERVATION DATE.

DISPLAY THE FIRSTNAME,GENDER WITH THERE RESPECTIVE RESERVATION DATE AND

BUS_ID IN EHICH THEY ARE GOING TO BE TRAVEL.

2.OUTER JOIN

RETRIEVE BUS_NO, BUS_TYPE, DRIVER_NAME FROM THE DRIVER AND BUS

TABLES TABLES

RETRIEVE FNAME, PAYMENT_DATE, PAYMENT_ID FROM THE PASSENGER

VIEWS

CREATE VIEW PASSENGER_RESERVATION_DETAILS

Observation or Conclusion :

The main purpose of maintaining this database for the BUS reservation system is to reduce the error involved in booking and cancellation of tickets and make it convenient for customers and to maintain the data about customers and seat available.
By making an online application, many loopholes that exist in manual maintenance of the records can be removed.
The speed of obtaining data will be fast.
To overcome all the problems of the manual reservation system we can implement this database which includes customer details , availability of seats in BUS, number of BUSES and their details.