Scribd
Upload
59 views26 pages

RedBus Database Management System Project

The RedBus Database Management System project creates a relational database for a bus reservation platform, storing essential data such as users, bus operators, and routes. It includes a Python script for database connectivity and querying, demonstrating how backend databases can support real-world applications. The database design features interconnected tables to manage data efficiently, ensuring consistency and avoiding duplication.

Uploaded by

hshamajain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
59 views26 pages

RedBus Database Management System Project

The RedBus Database Management System project creates a relational database for a bus reservation platform, storing essential data such as users, bus operators, and routes. It includes a Python script for database connectivity and querying, demonstrating how backend databases can support real-world applications. The database design features interconnected tables to manage data efficiently, ensuring consistency and avoiding duplication.

Uploaded by

hshamajain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

REDBUS DATABASE MANAGEMENT SYSTEM

Subject: EBOX

Prepared by:
H SHAMA JAIN (USN: 4AL23CS049)
ANMOL V HADLI (USN: 4AL23CS0017)

Department of Computer Science and


Engineering
Alva’s Institute of Engineering and Technology
Abstract

This project implements a relational database


system for a bus reservation platform (modelled
after RedBus) and includes a simple Python script to
demonstrate database connectivity and querying.
The database stores users, bus operators, bus
types, buses, cities, routes, schedules, passengers,
tickets, and payments. The project code contains a
small Python client that demonstrates connecting to
the database and listing available cities.
Introduction:
The RedBus Database Management System is a
mini-project that represents how an online bus
booking platform like RedBus works.
It focuses on creating a relational database that
stores all important travel-related details such as
users, bus operators, bus types, buses, cities,
routes, schedules, passengers, tickets, and
payments.
This project also includes a Python program that
connects to the MySQL database and displays city
details from the database.
The goal of this project is to understand how
backend databases and simple Python scripts can
work together to form the foundation of a complete
application.

Objectives:
The main objectives of this project are:
1. To design a structured database for a bus
booking system.
2. To link all the entities using primary and foreign
keys.
3. To store, manage, and retrieve data using SQL
queries.
4. To demonstrate a simple Python–MySQL
connection.
5. To understand how databases can be used
in real-world applications like RedBus.

Software Used:
Software Purpose

MySQL / Creating and managing the


phpMyAdmin RedBus database

For connecting to and accessing


Python data from the database

MySQL Connector To establish the link between


Module Python and MySQL

Windows OS Platform for running the project

MySQL Workbench Executing SQL queries easily


Database Design:
The database created in this project is
named redbus_demo.
It contains multiple tables that are linked to each
other using foreign keys.
Each table represents one part of the bus booking
system.

Tables in the Database:


1. users – stores user details like name, email,
phone, and password.
2. bus_operators – contains details of bus service
providers.
3. bus_types – defines the type of buses (AC, Non-
AC, Sleeper, etc.).
4. buses – stores each bus’s number, capacity,
type, and operator.
5. city – list of cities connected by routes.
6. routes – defines the source and destination
cities with distance and time.
7. daily_schedules – shows the schedule, fare,
and timings for each bus.
8. passengers – stores passenger information.
9. tickets – records the booking details.
10. payments – stores payment information
related to each ticket.
All these tables are interconnected through primary
and foreign keys so that the data remains
consistent.

Relationships Between Tables


• A bus operator can have many buses.
• A bus belongs to one bus type and
one operator.
• A route connects two cities — source and
destination.
• A schedule is linked to a bus and a route.
• A passenger books one or more tickets.
• Each ticket has one payment record.
This design helps to manage data efficiently without
any duplication.
Full SQL Schema ([Link]):

CREATE DATABASE IF NOT EXISTS redbus_demo;


USE redbus_demo;

CREATE TABLE users (


user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15),
created_at DATETIME DEFAULT
CURRENT_TIMESTAMP
);

CREATE TABLE bus_operators (


operator_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
contact_number VARCHAR(20),
email VARCHAR(100),
address VARCHAR(255)
);

CREATE TABLE bus_types (


bus_type_id INT AUTO_INCREMENT PRIMARY KEY,
type_name VARCHAR(50) NOT NULL,
description VARCHAR(200)
);

CREATE TABLE buses (


bus_id INT AUTO_INCREMENT PRIMARY KEY,
operator_id INT NOT NULL,
bus_type_id INT NOT NULL,
bus_number VARCHAR(20) UNIQUE NOT NULL,
capacity INT NOT NULL,
FOREIGN KEY (operator_id) REFERENCES
bus_operators(operator_id),
FOREIGN KEY (bus_type_id) REFERENCES
bus_types(bus_type_id)
);
CREATE TABLE city (
city_id INT AUTO_INCREMENT PRIMARY KEY,
city_name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE routes (


route_id INT AUTO_INCREMENT PRIMARY KEY,
source_city_id INT NOT NULL,
destination_city_id INT NOT NULL,
distance_km INT,
duration VARCHAR(20),
FOREIGN KEY (source_city_id) REFERENCES
city(city_id),
FOREIGN KEY (destination_city_id) REFERENCES
city(city_id)
);

CREATE TABLE daily_schedules (


schedule_id INT AUTO_INCREMENT PRIMARY KEY,
bus_id INT NOT NULL,
route_id INT NOT NULL,
travel_date DATE NOT NULL,
departure_time TIME NOT NULL,
arrival_time TIME NOT NULL,
fare DECIMAL(10,2) NOT NULL,
FOREIGN KEY (bus_id) REFERENCES
buses(bus_id),
FOREIGN KEY (route_id) REFERENCES
routes(route_id)
);

CREATE TABLE passengers (


passenger_id INT AUTO_INCREMENT PRIMARY
KEY,
user_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
age INT,
gender ENUM('Male', 'Female', 'Other'),
FOREIGN KEY (user_id) REFERENCES
users(user_id)
);
CREATE TABLE tickets (
ticket_id INT AUTO_INCREMENT PRIMARY KEY,
schedule_id INT NOT NULL,
passenger_id INT NOT NULL,
seat_number VARCHAR(10) NOT NULL,
booking_time DATETIME DEFAULT
CURRENT_TIMESTAMP,
status ENUM('BOOKED', 'CANCELLED',
'COMPLETED') DEFAULT 'BOOKED',
FOREIGN KEY (schedule_id) REFERENCES
daily_schedules(schedule_id),
FOREIGN KEY (passenger_id) REFERENCES
passengers(passenger_id)
);

CREATE TABLE payments (


payment_id INT AUTO_INCREMENT PRIMARY KEY,
ticket_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_method VARCHAR(50),
payment_status ENUM('SUCCESS', 'FAILED',
'PENDING') DEFAULT 'PENDING',
transaction_time DATETIME DEFAULT
CURRENT_TIMESTAMP,
FOREIGN KEY (ticket_id) REFERENCES
tickets(ticket_id)
);

INSERT INTO users (username, password, email,


phone) VALUES
('arun_k', 'pass123', 'arun@[Link]',
'9876543210'),
('meena_p', 'pass456', 'meena@[Link]',
'9123456789'),
('vijay_r', 'pass789', 'vijay@[Link]',
'9000012345'),
('kavya_s', 'abc123', 'kavya@[Link]',
'9012345678'),
('ramesh_t', 'xyz456', 'ramesh@[Link]',
'9112233445'),
('divya_m', 'hello123', 'divya@[Link]',
'9321456789'),
('suresh_n', 'test321', 'suresh@[Link]',
'9456123456'),
('lavanya_d', 'qwerty', 'lavanya@[Link]',
'9789654321'),
('rahul_p', 'admin@1', 'rahul@[Link]',
'9090909090'),
('nithya_v', 'pw12345', 'nithya@[Link]',
'9345678912'),
('aravind_j', 'mypw12', 'aravind@[Link]',
'9789012345'),
('hema_r', 'sun123', 'hema@[Link]',
'9345678901'),
('vignesh_b', 'moon456', 'vignesh@[Link]',
'9445566778'),
('keerthi_k', 'pass432', 'keerthi@[Link]',
'9223344556'),
('mani_d', 'secure1', 'mani@[Link]',
'9667788990');

INSERT INTO bus_operators (name,


contact_number, email, address) VALUES
('KPN Travels', '0422-223344', 'info@[Link]',
'Coimbatore'),
('SRS Travels', '080-22335566', 'info@[Link]',
'Bengaluru'),
('Orange Tours', '040-22334455',
'info@[Link]', 'Hyderabad'),
('ABT Travels', '044-24556677', 'support@[Link]',
'Chennai'),
('Parveen Travels', '044-22223333',
'parveen@[Link]', 'Chennai'),
('KSRTC', '080-27000000', 'support@[Link]',
'Bengaluru'),
('TNSTC', '044-27001111', 'help@[Link]', 'Madurai'),
('Kallada Travels', '0484-2223344',
'kallada@[Link]', 'Kochi'),
('RTC Andhra', '0866-2333222', 'info@[Link]',
'Vijayawada'),
('Universal Travels', '0422-334455',
'contact@[Link]', 'Trichy'),
('SRM Travels', '044-2345678', 'help@[Link]',
'Chennai'),
('Rathimeena Travels', '0452-2456222',
'info@[Link]', 'Madurai'),
('Jabbar Travels', '040-22331122',
'info@[Link]', 'Hyderabad'),
('Kaveri Travels', '040-22006677', 'contact@[Link]',
'Hyderabad'),
('Vivegam Travels', '0422-334455',
'support@[Link]', 'Coimbatore');

INSERT INTO bus_types (type_name, description)


VALUES
('AC Sleeper', 'Air-conditioned sleeper coach'),
('Non-AC Seater', 'Basic non-AC seating bus'),
('Volvo Multi Axle', 'Luxury Volvo with suspension'),
('AC Semi Sleeper', 'Comfortable semi-sleeper AC'),
('Non-AC Sleeper', 'Sleeper without air-
conditioning'),
('Mini Bus', 'Small capacity bus'),
('Scania AC', 'Luxury Scania multi-axle'),
('Luxury Sleeper', 'Top-end sleeper comfort'),
('Deluxe', 'Comfort deluxe seating'),
('Super Deluxe', 'Super comfort deluxe'),
('AC Chair Car', 'Short route AC chair bus'),
('Express', 'High-speed express bus'),
('Super Fast', 'Express speed service'),
('Ordinary', 'Low fare city service'),
('Ultra Deluxe', 'State transport ultra deluxe');

INSERT INTO buses (operator_id, bus_type_id,


bus_number, capacity) VALUES
(1, 1, 'TN66A1001', 40),
(2, 2, 'KA09B2002', 45),
(3, 3, 'TS07C3003', 50),
(4, 4, 'TN10D4004', 42),
(5, 5, 'TN09E5005', 40),
(6, 6, 'KA12F6006', 30),
(7, 7, 'TN58G7007', 48),
(8, 8, 'KL07H8008', 44),
(9, 9, 'AP16J9009', 50),
(10, 10, 'TN45K1010', 40),
(11, 11, 'TN20L1111', 46),
(12, 12, 'TN58M1212', 48),
(13, 13, 'TS09N1313', 49),
(14, 14, 'TS08P1414', 38),
(15, 15, 'KL10Q1515', 42);
INSERT INTO city (city_name) VALUES
('Chennai'),
('Coimbatore'),
('Madurai'),
('Trichy'),
('Bengaluru'),
('Mysuru'),
('Hyderabad'),
('Vijayawada'),
('Kochi'),
('Thiruvananthapuram'),
('Salem'),
('Erode'),
('Tirunelveli'),
('Warangal'),
('Visakhapatnam');

INSERT INTO routes (source_city_id,


destination_city_id, distance_km, duration) VALUES
(1,5,350,'6h 30m'),
(1,2,500,'8h 00m'),
(2,3,215,'4h 00m'),
(3,4,135,'3h 00m'),
(4,1,330,'6h 00m'),
(5,7,570,'9h 00m'),
(7,8,280,'6h 00m'),
(8,15,400,'7h 00m'),
(9,10,210,'5h 00m'),
(10,9,220,'5h 00m'),
(11,2,170,'3h 30m'),
(2,12,60,'1h 30m'),
(13,1,650,'10h 00m'),
(14,7,150,'3h 00m'),
(5,6,140,'3h 00m');

INSERT INTO daily_schedules (bus_id, route_id,


travel_date, departure_time, arrival_time, fare)
VALUES
(1,1,'2025-10-15','22:00:00','04:30:00',800.00),
(2,2,'2025-10-16','21:30:00','05:30:00',850.00),
(3,3,'2025-10-17','06:00:00','10:00:00',500.00),
(4,4,'2025-10-18','07:00:00','10:00:00',400.00),
(5,5,'2025-10-19','23:00:00','05:00:00',750.00),
(6,6,'2025-10-20','20:00:00','05:00:00',950.00),
(7,7,'2025-10-21','21:00:00','03:00:00',900.00),
(8,8,'2025-10-22','05:00:00','12:00:00',700.00),
(9,9,'2025-10-23','06:00:00','11:00:00',600.00),
(10,10,'2025-10-23','16:00:00','21:00:00',580.00),
(11,11,'2025-10-24','08:00:00','11:30:00',350.00),
(12,12,'2025-10-25','09:00:00','10:30:00',200.00),
(13,13,'2025-10-25','18:00:00','04:00:00',1000.00),
(14,14,'2025-10-26','07:30:00','10:30:00',450.00),
(15,15,'2025-10-27','06:30:00','09:30:00',420.00);

INSERT INTO passengers (user_id, name, age,


gender) VALUES
(1,'Arun Kumar',28,'Male'),
(2,'Meena Priya',25,'Female'),
(3,'Vijay Raj',32,'Male'),
(4,'Kavya S',27,'Female'),
(5,'Ramesh T',35,'Male'),
(6,'Divya M',23,'Female'),
(7,'Suresh N',30,'Male'),
(8,'Lavanya D',29,'Female'),
(9,'Rahul P',33,'Male'),
(10,'Nithya V',26,'Female'),
(11,'Aravind J',31,'Male'),
(12,'Hema R',34,'Female'),
(13,'Vignesh B',28,'Male'),
(14,'Keerthi K',24,'Female'),
(15,'Mani D',29,'Male');

INSERT INTO tickets (schedule_id, passenger_id,


seat_number, status) VALUES
(1,1,'A1','BOOKED'),
(1,2,'A2','BOOKED'),
(2,3,'B1','BOOKED'),
(3,4,'B2','BOOKED'),
(4,5,'C1','BOOKED'),
(5,6,'C2','BOOKED'),
(6,7,'D1','BOOKED'),
(7,8,'D2','BOOKED'),
(8,9,'E1','BOOKED'),
(9,10,'E2','BOOKED'),
(10,11,'F1','BOOKED'),
(11,12,'F2','BOOKED'),
(12,13,'G1','BOOKED'),
(13,14,'G2','BOOKED'),
(14,15,'H1','BOOKED');

INSERT INTO payments (ticket_id, amount,


payment_method, payment_status) VALUES
(1,800.00,'UPI','SUCCESS'),
(2,800.00,'CreditCard','SUCCESS'),
(3,850.00,'DebitCard','SUCCESS'),
(4,500.00,'NetBanking','SUCCESS'),
(5,400.00,'UPI','SUCCESS'),
(6,750.00,'UPI','SUCCESS'),
(7,950.00,'CreditCard','SUCCESS'),
(8,900.00,'DebitCard','SUCCESS'),
(9,700.00,'UPI','SUCCESS'),
(10,600.00,'NetBanking','SUCCESS'),
(11,580.00,'UPI','SUCCESS'),
(12,350.00,'DebitCard','SUCCESS'),
(13,200.00,'CreditCard','SUCCESS'),
(14,1000.00,'UPI','SUCCESS'),
(15,450.00,'NetBanking','SUCCESS');

Python Integration:
To demonstrate database connectivity, a Python
script is included in the project inside
the RedBusfile. The file name is [Link].
The purpose of this code is to connect Python with
the MySQL database and fetch all the city names
stored in the database.

Python Code:
import [Link]
from [Link] import Error

def get_db_connection():
return [Link](
host="localhost",
user="root",
password="alvas@1234",
database="redbus_demo"
)

try:
conn = get_db_connection()
print(conn)
except Error as e:
print("Error while connecting to MySQL")

conn = get_db_connection()
cursor = [Link](dictionary=True)
[Link]("SELECT * FROM city ORDER BY
city_id;")
rows = [Link]()
for row in rows:
city_id = row["city_id"]
city_name = row["city_name"]
print(city_id, "\t", city_name)

Explanation of the Code:


1. The [Link] module is used to
connect Python with the MySQL database.
2. The function get_db_connection() returns a
connection to the redbus_demo database.
3. The connection details like host, username, and
password are mentioned in the code.
4. A cursor is created to execute SQL commands.
5. The query SELECT * FROM city; retrieves all the
city details.
6. The program then prints the list of all cities
stored in the database.
This code shows how the Python program and
MySQL database can work together.

Steps to Run the Project:


1. Install MySQL and Python on your system.
2. Create the database by running
the [Link] file in MySQL Workbench or
phpMyAdmin.
3. Open the [Link] file in VS Code or any Python
editor.
4. Make sure you have installed the library using
the command:
pip install mysql-connector-python
5. Run the [Link] file.
6. It will show the connection status and display
the list of cities from the database.

Advantages :
• Helps understand the structure of a real-world
booking system.
• Shows practical use of SQL with multiple
related tables.
• Demonstrates Python and MySQL integration.
• Encourages learning about database
connectivity and queries.
• Can be easily extended into a full bus booking
web application.
Conclusion:
The RedBus Database Management System is a
simple yet effective model that represents how
online bus booking platforms store and manage
their data.
By combining MySQL for data storage
and Python for database connectivity, this project
gives a clear understanding of how backend systems
work in real life.
The system can later be developed into a full web
application with more advanced features like login
systems, real-time seat selection, and payment
gateways.

Common questions

Powered by AI

Storing bus schedules and routes as separate tables in the RedBus database system enhances optimization and flexibility. The schedules table includes details like travel dates and fare, while the routes table specifies the source, destination, and travel duration . Separating these ensures that changes to schedule details, such as timings or fares, or route changes, can be managed independently without affecting other related data. This separation contributes to efficient query processing by isolating specific aspects of travel data for targeted operations, improving overall system performance and adaptability to changes like new route additions .

The RedBus Database schema facilitates scalability through its modular table structure and use of relationships defined by foreign keys, which ensure data consistency across entities like users, buses, and routes . This modular design allows for easy expansion; for instance, new features can be integrated by adding new tables or modifying existing ones without disrupting the overall system. Similarly, the use of primary and foreign keys enables maintaining data integrity even as the number of users or records increases, which is crucial for scaling efficiently . Additionally, using MySQL, a highly scalable database system, ensures that the database can handle increased loads with optimized query performance .

The RedBus Database Management System provides several advantages for developing a full bus booking application: it offers a well-structured database reflecting real-world relationships such as those between buses, routes, and schedules, which enhances data integrity . The use of SQL and relational database principles supports efficient data querying and management. Additionally, the integration of Python for database connectivity facilitates the development of interactive applications that can dynamically retrieve and manipulate data, such as listing cities or checking ticket availability . These components not only support the current minimal system but also provide robust groundwork for adding advanced functionalities like live seat selection and secure payment processing .

Expanding the RedBus Database Management System to include international routes and multilingual support poses several challenges. For international routes, the database must accommodate varied formats for time, distance, and currency, necessitating changes to data types and constraints in the relevant tables . A potential solution is to incorporate different schemas or add columns that manage these multiple formats. For multilingual support, implementing Unicode character sets for all text fields would allow data entry in various languages, while using translation tables or dictionaries could help map interface elements for different languages. Additionally, any integration of APIs for real-time language translation could enhance user experience across diverse linguistic backgrounds. These expansions inherently require robust testing and adjustments to ensure seamless integration without compromising existing functionalities .

The Python integration in the RedBus project can be extended beyond simple data retrieval to include functionalities such as user authentication, real-time booking management, and dynamic fare calculations. Python can facilitate user authentication by interacting with the 'users' table, verifying credentials, and managing sessions . Implementing a booking management system can allow users to book or cancel tickets in real-time by updating the 'tickets' and 'payments' tables, enabling instant status changes . Additionally, algorithms written in Python could calculate dynamic fares based on demand and availability, interacting with the 'daily_schedules' table to adjust prices accordingly. These capabilities demonstrate Python's potential in managing complex tasks that require direct database interactions.

The choice of data types in the RedBus SQL schema impacts both performance and data integrity significantly. For instance, using INT for identifiers like 'user_id' and 'operator_id' ensures efficient indexing and fast lookup operations . VARCHAR is used for fields requiring variable-length strings, optimal for storage because it adjusts the space allocation according to the actual string size, thereby optimizing disk space. ENUM data type is used for fields like 'gender' and 'payment_status', ensuring data integrity by restricting values to predefined options, which helps prevent incorrect data entries . Choosing appropriate data types thus supports efficient data access and maintains the integrity of stored data through constraints and efficient space utilization.

The Python script in the RedBus project demonstrates database connectivity by utilizing the mysql.connector module to connect with the MySQL database . It defines a function 'get_db_connection()' which establishes a connection to the 'redbus_demo' database using specified credentials. Once connected, it creates a cursor to execute SQL queries like 'SELECT * FROM city;' This query retrieves all city records, which the script then prints as evidence of successful data retrieval .

Primary and foreign keys are central to maintaining data consistency across the RedBus database tables. Primary keys uniquely identify records within a table, whereas foreign keys establish relationships between different tables. For example, in the RedBus system, the 'buses' table uses 'operator_id' and 'bus_type_id' as foreign keys to reference primary keys in the 'bus_operators' and 'bus_types' tables, respectively . This setup ensures that relationships between related data like buses and their operators are maintained accurately, avoiding duplication and inconsistencies.

In the RedBus system, each ticket has one corresponding payment record, creating a one-to-one relationship between the tickets and payments tables. This relationship is managed through a foreign key 'ticket_id' in the payments table, referencing the primary key 'ticket_id' in the tickets table . This structure aids in efficient data management by ensuring that payment details are directly linked to specific ticket bookings, simplifying financial transaction audits and guaranteeing that each payment is associated with a valid ticket.

In the RedBus Database Management System, a bus operator can have many buses, showcasing a one-to-many relationship. This is effectively managed using a foreign key, where each bus record in the 'buses' table references a single operator in the 'bus_operators' table through the 'operator_id' field . This design helps to efficiently manage and query associations between buses and operators, reducing data redundancy and maintaining consistency.

You might also like