Problem Statement:

Design a database for an airline ,the database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routine of future flights. Your design should include an E-R diagram ,a set of relational schemas, and a list of constraints, including primary key and foreign key constraints.

Assumptions:

Passenger cannot make cancellations.

Entities:

Passenger: This entity includes passenger details P-id, name, age, address, e-mail.

Airport: This entity includes Airport details like Airport code, name, city and country.

Booking Office: It includes Office _Id and Office_name.

Airplane: This entity includes Reg_No, Flight type, and no of seats.

Airlines: This entity represents the Airline Name and its owner.

Flight: This entity includes details about the flights which comprises of Flight, Class Eg: Business class, Economy class etc., source and destination airports of flights, its departure date and time, arrival date and time.

Relationships:

Books Ticket: It is a relationship between the Passenger and Booking office.

Lands On: Its a relationship between the Airplane and Airport, It depicts which plane lands on which airport.

Owned By: Its a relationship which shows which relationship between the Airplane and Airlines. ie., Which airplane belongs to which Airlines.

Flies: is a relationship that tells which Flight flies under which Airline.

Travels on: Its a relationship that tells us which flight travels on which routes. ie., It shows the relationship between Flight and Route.

Charges: Its a relationship between the Route and Fare entities.