The AIRLINE relational database schema shown in the above figure describes a database for airline
flight information. Each FLIGHT is identified by a Flight_number, and consists of one or more
FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. Each FLIGHT_LEG has scheduled arrival and
departure times, airports, and one or more LEG_INSTANCEs—one for each Date on which the flight
travels. FAREs are kept for each FLIGHT. For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept,
as are the AIRPLANE used on the leg and the actual arrival and departure times and airports. An
AIRPLANE is identified by an Airplane_id and is of a particular AIRPLANE_TYPE. CAN_LAND relates
AIRPLANE_TYPEs to the AIRPORTs at which they can land. An AIRPORT is identified by an Airport_code.
2. Extend your design to satisfy the following requirements:
• Separate the CUSTOMER entity from the SEAT_RESERVATION and extend it with the following
attributes; e-mail, adress, country, passport number.
• Create a COMPANY entity for both AIRPLANE and AIRLINE. Use generalization/specialization
hierarchy.
• Create an entity for frequent flyer customer tracking called FFC to keep track of the customers’
flight information. If a customer has checked-in physically a flight create a transaction record
with the mileage information assigned to that flight leg.
Identify all the important concepts represented in EER diagram.
so, what's the problem?
Start by defining your tables.
Look for similar examples online. You will find many.
Do not expect anyone here to undertake such a project for you.
The important concepts represented in the Extended Entity-Relationship (EER) diagram are as follows:
1. Entities:
- AIRLINE: Represents an airline company.
- FLIGHT: Represents a flight.
- FLIGHT_LEG: Represents a leg of a flight.
- LEG_INSTANCE: Represents an instance of a flight leg on a specific date.
- FARE: Represents the fare for a flight.
- SEAT_RESERVATION: Represents a seat reservation for a flight leg.
- AIRPLANE: Represents an airplane used on a flight leg.
- AIRPLANE_TYPE: Represents the type of an airplane.
- CAN_LAND: Relates airplane types to the airports where they can land.
- AIRPORT: Represents an airport.
- CUSTOMER: Represents a customer of the airline.
- COMPANY: Represents a company for both airplanes and airlines.
- FFC (Frequent Flyer Customer): Represents frequent flyer customer tracking.
2. Relationships:
- FLIGHT_LEG belongs to FLIGHT: Defines the relationship between flight legs and flights.
- LEG_INSTANCE belongs to FLIGHT_LEG: Defines the relationship between leg instances and flight legs.
- FARE belongs to FLIGHT: Defines the relationship between fares and flights.
- SEAT_RESERVATION belongs to FLIGHT_LEG_INSTANCE: Defines the relationship between seat reservations and leg instances.
- AIRPLANE belongs to FLIGHT_LEG: Defines the relationship between airplanes and flight legs.
- AIRPLANE_TYPE belongs to AIRPLANE: Defines the relationship between airplane types and airplanes.
- CAN_LAND relates AIRPLANE_TYPE and AIRPORT: Defines the relationship between airplane types and airports.
- CUSTOMER has SEAT_RESERVATION: Defines the relationship between customers and seat reservations.
- CUSTOMER has FFC: Defines the relationship between customers and frequent flyer customer tracking.
- FFC has FLIGHT_LEG_INSTANCE: Defines the relationship between frequent flyer customer tracking and leg instances.
- FFC has MILEAGE_TRANSACTION: Defines the relationship between frequent flyer customer tracking and mileage transactions.
3. Attributes:
- FLIGHT_NUMBER: Represents the unique number assigned to a flight.
- LEG_NUMBER: Represents the number assigned to a flight leg.
- SCHEDULED_ARRIVAL_TIME and SCHEDULED_DEPARTURE_TIME: Represents the scheduled arrival and departure times for a flight leg.
- ACTUAL_ARRIVAL_TIME and ACTUAL_DEPARTURE_TIME: Represents the actual arrival and departure times for a flight leg.
- AIRPORT_CODE: Represents the unique code assigned to an airport.
- EMAIL, ADDRESS, COUNTRY, PASSPORT_NUMBER: Represents the additional attributes added to the CUSTOMER entity.
- AIRPLANE_ID: Represents the unique identifier for an airplane.
- MILEAGE_INFORMATION: Represents the information about the mileage assigned to a flight leg.
These concepts form the foundation of the database schema for airline flight information management.