FLIGHT MANAGEMENT SYSTEM
1. Flight Table
SQL Queries:
CREATE TABLE Flight (
Flight_ID INT PRIMARY KEY,
Flight_Number VARCHAR(10),
Source VARCHAR(30),
Destination VARCHAR(30),
Departure_Time TIME,
Arrival_Time TIME,
Status VARCHAR(15)
);
INSERT INTO Flight VALUES
(1,'AI101','Delhi','Mumbai','10:00','12:00','On Time');
SELECT * FROM Flight;
Output:
Flight_ID Flight_Number Source Destination Departure_Time Arrival_Time Status
1 AI101 Delhi Mumbai 10:00 12:00 On Time
2. Aircraft Table
SQL Queries:
CREATE TABLE Aircraft (
Aircraft_ID INT PRIMARY KEY,
Model VARCHAR(30),
Capacity INT,
Manufacturer VARCHAR(30),
Maintenance_Status VARCHAR(20)
);
INSERT INTO Aircraft VALUES
(101,'A320',180,'Airbus','Good');
SELECT * FROM Aircraft;
Output:
Aircraft_ID Model Capacity Manufacturer Maintenance_Status
101 A320 180 Airbus Good
3. Airport Table
SQL Queries:
CREATE TABLE Airport (
Airport_ID INT PRIMARY KEY,
Airport_Name VARCHAR(50),
City VARCHAR(30),
Country VARCHAR(30),
Terminal_Count INT
);
INSERT INTO Airport VALUES
(1,'IGI Airport','Delhi','India',3);
SELECT * FROM Airport;
Output:
Airport_ID Airport_Name City Country Terminal_Count
1 IGI Airport Delhi India 3
4. Passenger Table
SQL Queries:
CREATE TABLE Passenger (
Passenger_ID INT PRIMARY KEY,
Passenger_Name VARCHAR(30),
Age INT,
Gender VARCHAR(10),
Passport_Number VARCHAR(20),
Contact_Number VARCHAR(15)
);
INSERT INTO Passenger VALUES
(1,'Aamir Khan',18,'Male','P123456','9876543210');
SELECT * FROM Passenger;
Output:
Passenger_ID Passenger_Name Age Gender Passport_Number Contact_Number
1 Aamir Khan 18 Male P123456 9876543210
5. Reservation Table
SQL Queries:
CREATE TABLE Reservation (
Reservation_ID INT PRIMARY KEY,
Passenger_ID INT,
Flight_ID INT,
Seat_Number VARCHAR(5),
Reservation_Date DATE
);
INSERT INTO Reservation VALUES
(1,1,1,'12A','2025-01-10');
SELECT * FROM Reservation;
Output:
Reservation_ID Passenger_ID Flight_ID Seat_Number Reservation_Date
1 1 1 12A 2025-01-10
6. Ticket Table
SQL Queries:
CREATE TABLE Ticket (
Ticket_ID INT PRIMARY KEY,
Reservation_ID INT,
Travel_Class VARCHAR(15),
Fare INT,
Ticket_Status VARCHAR(15)
);
INSERT INTO Ticket VALUES
(101,1,'Economy',5500,'Confirmed');
SELECT * FROM Ticket;
Output:
Ticket_ID Reservation_ID Travel_Class Fare Ticket_Status
101 1 Economy 5500 Confirmed
7. Payment Table
SQL Queries:
CREATE TABLE Payment (
Payment_ID INT PRIMARY KEY,
Ticket_ID INT,
Amount INT,
Payment_Mode VARCHAR(20),
Payment_Status VARCHAR(15)
);
INSERT INTO Payment VALUES
(1,101,5500,'UPI','Successful');
SELECT * FROM Payment;
Output:
Payment_ID Ticket_ID Amount Payment_Mode Payment_Status
1 101 5500 UPI Successful
8. Crew Table
SQL Queries:
CREATE TABLE Crew (
Crew_ID INT PRIMARY KEY,
Crew_Name VARCHAR(30),
Designation VARCHAR(20),
Experience INT,
Assigned_Flight INT
);
INSERT INTO Crew VALUES
(1,'Rohit Sharma','Pilot',10,1);
SELECT * FROM Crew;
Output:
Crew_ID Crew_Name Designation Experience Assigned_Flight
1 Rohit Sharma Pilot 10 1
9. Baggage Table
SQL Queries:
CREATE TABLE Baggage (
Baggage_ID INT PRIMARY KEY,
Passenger_ID INT,
Weight INT,
Baggage_Status VARCHAR(20)
);
INSERT INTO Baggage VALUES
(1,1,18,'Checked-In');
SELECT * FROM Baggage;
Output:
Baggage_ID Passenger_ID Weight Baggage_Status
1 1 18 Checked-In