For an instructor lead, in-depth look at learning SQL click below.
An efficient flight booking system is crucial for any airline’s online services. Through the combination of SQL (Structured Query Language) and sound database design, we can create an effective and reliable system. This article aims to guide you through the process, providing examples of SQL code to illustrate each point.
1. Creating the Database
The first step in building our flight booking system is creating a database. We will name it ‘FlightBooking’.
|
1 2 3 |
CREATE DATABASE FlightBooking; |
2. Designing Tables
Our database will need a few core tables: Flights, Passengers, and Bookings. Let’s start with the ‘Flights’ table.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Flights ( FlightID INT PRIMARY KEY, DepartureLocation VARCHAR(100), ArrivalLocation VARCHAR(100), DepartureTime DATETIME, ArrivalTime DATETIME ); |
Next we will create the ‘Passengers’ table.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Passengers ( PassengerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100) ); |
Lastly, we create the ‘Bookings’ table.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Bookings ( BookingID INT PRIMARY KEY, FlightID INT, PassengerID INT, DateOfBooking DATETIME, FOREIGN KEY (FlightID) REFERENCES Flights(FlightID), FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID) ); |
3. SQL Queries for Operations
Bits of SQL code for common operations in our flight booking system:
Booking a Flight
We can insert a booking by adding a new record to the ‘Bookings’ table.
|
1 2 3 4 |
INSERT INTO Bookings (FlightID, PassengerID, DateOfBooking) VALUES (1, 123, GETDATE()); |
Fetching Passenger’s Bookings
We can fetch a specific passenger’s bookings using a SELECT statement.
|
1 2 3 4 |
SELECT * FROM Bookings WHERE PassengerID = 123; |
Joining Tables
Here we join the ‘Flights’ and ‘Bookings’ tables to get more detailed booking information.
|
1 2 3 4 5 6 |
SELECT Flights.*, Bookings.DateOfBooking FROM Bookings JOIN Flights ON Flights.FlightID = Bookings.FlightID WHERE Bookings.PassengerID = 123; |
This query will return all flights that the passenger with id ‘123’ has booked, along with the booking dates.
With these basics, we can build a functional flight booking system using SQL. Further enhancements can include a ‘Staff’ table for managing tickets, additional booking details, or more complicated queries for advanced functionalities.