TOPIC: IMPLEMENTATION OF A SIMPLE ER DIAGRAM.

QUESTION:

Design an ER diagram for an airline reservation system.

  • The database represents each Airport, keeping its unique Airport Code, the Airport Name, and the City and State in which the Airport is located.
  • Each airline flight has a unique number, the Airline for the flight, and the Weekdays on which the flight is scheduled.
  • A flight is composed of one or more flight legs. Each flight leg has a leg number, Departure airport and Scheduled Departure Time, and an arrival airport and Scheduled Arrival Time.
  • A leg instance is an instance of a flight leg on a specific date. The Number of available seats and the airplane used in the leg instance are also kept.
  • The customer reservations on each leg instance include the customer name, phone, and seat numbers for each reservation.
  • Information on Airplanes and Airplane Types are also kept. For each Airplane type the Type Name, manufacturing company, and maximum number of seats are kept. The Airports in which planes of this type Can Land are kept in the database. For each Airplane, the Airplane Id, Total number of seats, and Type are kept.

SOLUTION:

 

  • Entities

 

  • Airport
  • Airplane Type
  • Airplane
  • Flight leg
  • Flight
  • Leg Instance

  • Relationships

  • It is a 1-N relationship between the airport and flight leg based on departure airport.
  • It is a 1-N relationship between Airport and Flight Leg based on arrival airport.
  • It is a M-N relationship between Airport and Airplane Type based on landing.
  • It is a 1-N relationship between Airplane Type and Airplane based on the type.
  • It is a 1-N relationship between Airplane and Leg instance based on the assignment.
  • It is a 1-N relationship between Leg instance and Seat based on reservation.
  • It is an N-1 relationship between Flight Leg and Flight based on the legs.
  • It is a 1-N relationship between Flight and Fare based on the fares.
  • It is a 1-N relation between Airport and Leg instance based on to where it departs.
  • It is a 1-N relationship between Airport and Leg instance based on the arrival.
  • It is a 1-N relationship between Flight Leg and Leg instance based on instance.relation1relation2
  • Key attributes

    • Airport- Airport code
    • Airplane Type- TypeName
    • Airplane- AirplaneId
    • Leg Instance- Date
    • Seat- SeatNo
    • Fare- Code
    • Flight Leg- LegNo
    • Flight- Number

  • Other relevant attributes

    • Airport- Name, City, State
    • Airplane Type- Max-seats, Company
    • Airplane- Total-no-of-seats
    • Leg Instance- No-of-avail seats
    • Fare- Amount, Restrictions
    • Flight- Weekdays, Airline
  • ER Diagram
  • erdiag
Advertisements