Scribd
Upload
16 views5 pages

SQL Airline Booking System Design

The document outlines an experiment for creating an Airline Booking System using a relational database. It includes the creation of tables for passengers, flights, and bookings, along with sample data insertion and SQL queries to analyze flight and booking data. The objective is to retrieve insights such as flight schedules, frequent flyers, and booking trends.

Uploaded by

Rohit Kumar
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
16 views5 pages

SQL Airline Booking System Design

The document outlines an experiment for creating an Airline Booking System using a relational database. It includes the creation of tables for passengers, flights, and bookings, along with sample data insertion and SQL queries to analyze flight and booking data. The objective is to retrieve insights such as flight schedules, frequent flyers, and booking trends.

Uploaded by

Rohit Kumar
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

DEPARTMENT OF

COMPUTER SCIENCE & ENGINEERING

Experiment 02
Student Name: Samay Sood UID: 23BCS10321
Branch: BE:CSE Section/Group: 704-B
Semester: 4th Date of Performance: 19Feb25
Subject Name: DBMS Subject Code: 23CSH-205

1. Aim: Set A: Airline Booking System


- Create a tables:
• Passengers(Passenger_id, name, email, phone)
• Flights(flight_id, airline, departure, destination, departure_time)
• Booking(booking_id,passenger_id,flight_id,booking_date,seat_number)

- Insert atleast 10 records in each table.

- Perform SQL queries to:


• Retrieve all flight departing in any 2 days.
• Identify passengers who have booked more than 2 flights.
• Find the most frequently booked airline.
• Retrieve passengers who booked flight to at least 3 different destinies.
• Calculate the total number of bookings for the last month.

2. Objective: Airline Booking System

The objective is to design a relational database for an airline booking system,


perform data insertion, and execute SQL queries to analyze flight, passenger,
and booking data. Key operations cover table creation, data population, and
retrieving insights like flights by date, frequent flyers, top airlines, destination
diversity, and recent booking trends. for 6 seconds
Develop an airline booking system in SQL by creating tables for passengers,
flights, and bookings, inserting sample records, and executing queries to
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

analyze flight schedules, frequent flyers, popular airlines, destination diversity,


and recent booking trends.

3. DBMS script/code:

-- ============================================
-- Step 1: Create Tables
-- ============================================
CREATE TABLE Passengers (
Passenger_id INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Phone VARCHAR(15)
);

CREATE TABLE Flights (


Flight_id INT PRIMARY KEY,
Airline VARCHAR(50),
Departure VARCHAR(50),
Destination VARCHAR(50),
Departure_time DATETIME
);

CREATE TABLE Booking (


Booking_id INT PRIMARY KEY,
Passenger_id INT,
Flight_id INT,
Booking_date DATE,
Seat_number VARCHAR(10),
FOREIGN KEY (Passenger_id) REFERENCES Passengers(Passenger_id),
FOREIGN KEY (Flight_id) REFERENCES Flights(Flight_id)
);

-- ============================================
-- Step 2: Insert Sample Data
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

-- ============================================
-- Insert data into Passengers table
INSERT INTO Passengers VALUES
(1, 'Alice Johnson', 'alice@[Link]', '9876543210'),
(2, 'Bob Smith', 'bob@[Link]', '9876543211'),
(3, 'Charlie Brown', 'charlie@[Link]', '9876543212'),
(4, 'David Wilson', 'david@[Link]', '9876543213'),
(5, 'Emma Davis', 'emma@[Link]', '9876543214'),
(6, 'Frank Miller', 'frank@[Link]', '9876543215'),
(7, 'Grace Hall', 'grace@[Link]', '9876543216'),
(8, 'Henry Lee', 'henry@[Link]', '9876543217'),
(9, 'Ivy White', 'ivy@[Link]', '9876543218'),
(10, 'Jack Black', 'jack@[Link]', '9876543219');

-- Insert data into Flights table


INSERT INTO Flights VALUES
(101, 'Air India', 'Delhi', 'Mumbai', '2025-02-20 08:30:00'),
(102, 'IndiGo', 'Mumbai', 'Chennai', '2025-02-21 10:00:00'),
(103, 'SpiceJet', 'Bangalore', 'Hyderabad', '2025-02-22 14:45:00'),
(104, 'Vistara', 'Kolkata', 'Delhi', '2025-02-23 16:15:00'),
(105, 'AirAsia', 'Delhi', 'Bangalore', '2025-02-24 18:00:00'),
(106, 'GoAir', 'Chennai', 'Kolkata', '2025-02-25 20:30:00'),
(107, 'IndiGo', 'Hyderabad', 'Pune', '2025-02-26 07:00:00'),
(108, 'Vistara', 'Pune', 'Delhi', '2025-02-27 09:15:00'),
(109, 'SpiceJet', 'Bangalore', 'Mumbai', '2025-02-28 12:00:00'),
(110, 'Air India', 'Chennai', 'Delhi', '2025-02-29 15:30:00');

-- Insert data into Booking table


INSERT INTO Booking VALUES
(201, 1, 101, '2025-02-10', 'A1'),
(202, 2, 102, '2025-02-11', 'B2'),
(203, 3, 103, '2025-02-12', 'C3'),
(204, 4, 104, '2025-02-13', 'D4'),
(205, 5, 105, '2025-02-14', 'E5'),
(206, 6, 106, '2025-02-15', 'F6'),
(207, 7, 107, '2025-02-16', 'G7'),
(208, 8, 108, '2025-02-17', 'H8'),
(209, 9, 109, '2025-02-18', 'I9'),
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

(210, 10, 110, '2025-02-19', 'J10'),


(211, 1, 102, '2025-02-15', 'A2'),
(212, 1, 103, '2025-02-16', 'A3'),
(213, 2, 104, '2025-02-17', 'B4'),
(214, 2, 105, '2025-02-18', 'B5'),
(215, 2, 106, '2025-02-19', 'B6');

-- ============================================
-- Step 3: Execute Queries
-- ============================================
-- Query 1: Retrieve all flights departing on two specific days (e.g., Feb 20 & Feb 21,
2025)
SELECT * FROM Flights
WHERE DATE(Departure_time) IN ('2025-02-20', '2025-02-21');

-- Query 2: Identify passengers who have booked more than 2 flights


SELECT p.Passenger_id, [Link], COUNT(b.Booking_id) AS Total_Bookings
FROM Booking b
JOIN Passengers p ON b.Passenger_id = p.Passenger_id
GROUP BY p.Passenger_id
HAVING COUNT(b.Booking_id) > 2;

-- Query 3: Find the most frequently booked airline


SELECT [Link], COUNT(b.Booking_id) AS Total_Bookings
FROM Booking b
JOIN Flights f ON b.Flight_id = f.Flight_id
GROUP BY [Link]
ORDER BY Total_Bookings DESC
LIMIT 1;

-- Query 4: Retrieve passengers who booked flights to at least 3 different destinations


SELECT p.Passenger_id, [Link], COUNT(DISTINCT [Link]) AS
Unique_Destinations
FROM Booking b
JOIN Flights f ON b.Flight_id = f.Flight_id
JOIN Passengers p ON b.Passenger_id = p.Passenger_id
GROUP BY p.Passenger_id
HAVING COUNT(DISTINCT [Link]) >= 3;
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

-- Query 5: Calculate the total number of bookings for the last month
SELECT COUNT(*) AS Total_Bookings_Last_Month
FROM Booking
WHERE Booking_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

4. OUTPUT:

Common questions

Powered by AI

The Booking table utilizes foreign keys to reference the Passenger_id from the Passengers table and the Flight_id from the Flights table, ensuring that all entries in the Booking table correspond to valid passengers and flights. This relational structure enforces data integrity by preventing orphan records, maintaining consistent data, and ensuring that every booking is linked to existing passengers and flights .

Calculating the total number of bookings for the last month provides insights into booking trends and demand over time. This information can inform airline decision-makers about peak travel periods, help in resource allocation, and guide strategic planning such as adding or removing flights based on demand. It can also indicate the effectiveness of marketing strategies and offer data for forecasting future booking trends .

Creating a separate table for flights is an application of database normalization, which reduces redundancy and improves data integrity. The Flights table allows each flight to be uniquely identified and managed separately from bookings and passenger information. This structure enables efficient data retrieval and updating, helps in managing operational logistics, and aids in scalability by allowing the system to handle a large volume of flights without data duplication .

The query to find the most frequently booked airline is: SELECT f.Airline, COUNT(b.Booking_id) AS Total_Bookings FROM Booking b JOIN Flights f ON b.Flight_id = f.Flight_id GROUP BY f.Airline ORDER BY Total_Bookings DESC LIMIT 1. If an airline emerges as the most booked, it may suggest popular routes, competitive pricing, or better service quality. Factors influencing preferences may include flight availability, frequency, ticket costs, loyalty programs, and customer service reputation .

To ensure that sample data remains up-to-date, implement automated scripts or triggers that regularly update the dataset based on real-time booking information. This strategy is crucial for ensuring accuracy in data analysis, as outdated data can lead to inaccurate insights, affecting decision-making, forecasting, and strategic planning. Regular updates also enhance the credibility and reliability of any analysis performed on the data .

Relational database systems offer several benefits for managing airline bookings, including data integrity, ease of access, scalability, and the ability to perform complex queries for insights. They allow for clear relationships between tables, such as passengers, flights, and bookings, and ensure consistent data through enforced constraints. However, challenges include the complexity of design and maintenance, potential performance issues with very large datasets, and the need for skilled personnel to manage and optimize the database .

The SQL query used to identify passengers who have booked more than two flights is: SELECT p.Passenger_id, p.Name, COUNT(b.Booking_id) AS Total_Bookings FROM Booking b JOIN Passengers p ON b.Passenger_id = p.Passenger_id GROUP BY p.Passenger_id HAVING COUNT(b.Booking_id) > 2. This query joins the Booking and Passengers tables, groups by passenger ID, and applies a HAVING clause to filter those with more than two bookings. This can indicate that these passengers are frequent flyers or possibly business travelers who travel often .

To modify the existing SQL query to retrieve flights departing on more than two specific days within a week, use: SELECT * FROM Flights WHERE DATE(Departure_time) BETWEEN '2025-02-20' AND '2025-02-26'. This query expands the date range, allowing analysis across a week's span. This adjustment enhances the analysis by providing a broader view of flight schedules, capturing patterns such as peak travel days, and enabling more comprehensive operational planning .

Analyzing recent booking trends using the database system provides actionable data that airlines can use to enhance operational efficiency. By understanding booking patterns, airlines can optimize staffing, adjust flight schedules, and allocate resources where they are needed most. Trend analysis can also help in anticipating peak periods, designing promotions, and planning maintenance schedules to minimize disruptions during high-traffic times .

The SQL query to identify passengers with at least three different flight destinations is: SELECT p.Passenger_id, p.Name, COUNT(DISTINCT f.Destination) AS Unique_Destinations FROM Booking b JOIN Flights f ON b.Flight_id = f.Flight_id JOIN Passengers p ON b.Passenger_id = p.Passenger_id GROUP BY p.Passenger_id HAVING COUNT(DISTINCT f.Destination) >= 3. This analysis is significant as it highlights passengers with diverse travel patterns, possibly indicating business travelers or individuals with varied travel needs. It helps in tailoring marketing strategies or loyalty programs targeting such customer segments .

You might also like