Flight Booking System - SQL Mini Project
1. Project Overview
This SQL-based Flight Booking System manages flight information, passengers, bookings, and
airports. It allows tracking of flight schedules, bookings, and availability, all using SQL queries and
database design principles.
2. ER Diagram (Text-based)
Entities:
- Airport (AirportID, Name, City, Country)
- Flight (FlightID, FlightNumber, DepartureAirportID, ArrivalAirportID, DepartureTime, ArrivalTime)
- Passenger (PassengerID, Name, Email, Phone)
- Booking (BookingID, PassengerID, FlightID, BookingDate, SeatNumber)
Relationships:
- A flight departs from and arrives at airports.
- A passenger can book many flights.
- A booking links a passenger to a flight.
3. Database Schema (SQL)
CREATE TABLE Airport (
AirportID INT PRIMARY KEY,
Name VARCHAR(100),
City VARCHAR(100),
Country VARCHAR(100)
);
CREATE TABLE Flight (
FlightID INT PRIMARY KEY,
FlightNumber VARCHAR(10),
DepartureAirportID INT,
ArrivalAirportID INT,
DepartureTime DATETIME,
ArrivalTime DATETIME,
FOREIGN KEY (DepartureAirportID) REFERENCES Airport(AirportID),
FOREIGN KEY (ArrivalAirportID) REFERENCES Airport(AirportID)
);
CREATE TABLE Passenger (
PassengerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Phone VARCHAR(15)
);
CREATE TABLE Booking (
BookingID INT PRIMARY KEY,
PassengerID INT,
FlightID INT,
BookingDate DATE,
SeatNumber VARCHAR(10),
FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID),
FOREIGN KEY (FlightID) REFERENCES Flight(FlightID)
);
4. Sample Data (INSERTs)
INSERT INTO Airport VALUES (1, 'JFK International', 'New York', 'USA');
INSERT INTO Airport VALUES (2, 'Heathrow', 'London', 'UK');
INSERT INTO Flight VALUES (101, 'AA101', 1, 2, '2025-05-01 08:00:00', '2025-05-01 20:00:00');
INSERT INTO Passenger VALUES (201, 'Alice Smith', 'alice@example.com', '1234567890');
INSERT INTO Passenger VALUES (202, 'Bob Johnson', 'bob@example.com', '0987654321');
INSERT INTO Booking VALUES (301, 201, 101, '2025-04-20', '12A');
INSERT INTO Booking VALUES (302, 202, 101, '2025-04-21', '12B');
5. Sample Queries
-- List all flights from a specific airport
SELECT * FROM Flight WHERE DepartureAirportID = 1;
-- Show all bookings for a specific flight
SELECT p.Name, b.SeatNumber, b.BookingDate
FROM Booking b
JOIN Passenger p ON b.PassengerID = p.PassengerID
WHERE b.FlightID = 101;
-- Available seats on a flight (assume 150 seats)
SELECT 150 - COUNT(*) AS AvailableSeats FROM Booking WHERE FlightID = 101;
-- List all passengers flying between two cities
SELECT p.Name, f.FlightNumber
FROM Passenger p
JOIN Booking b ON p.PassengerID = b.PassengerID
JOIN Flight f ON b.FlightID = f.FlightID
JOIN Airport a1 ON f.DepartureAirportID = a1.AirportID
JOIN Airport a2 ON f.ArrivalAirportID = a2.AirportID
WHERE a1.City = 'New York' AND a2.City = 'London';
6. Conclusion & Enhancements
This SQL-based Flight Booking System demonstrates effective data modeling and query operations
in a transportation context. Future additions can include flight pricing, seat availability checks, and
stored procedures for complex operations like rescheduling or cancellation.
