ER Diagram Example: Flight Database

The flight database stores details about an airline’s fleet, flights, and seat bookings. Again, it’s a hugely simplified version of what a real airline would use, but the principles are the same.

Consider the following requirements list:

• The airline has one or more airplanes.

• An airplane has a model number, a unique registration number, and the capacity to take one or more passengers.

• An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time.

• Each flight is carried out by a single airplane.

• A passenger has given names, a surname, and a unique email address.

• A passenger can book a seat on a flight.

The ER diagram derived from our requirements is shown in Figure.

• An Airplane is uniquely identified by its RegistrationNumber, so we use this as the primary key.

• A Flight is uniquely identified by its FlightNumber, so we use the flight number as the primary key. The departure and destination airports are captured in the From and To attributes, and we have separate attributes for the departure and arrival date and time.

• Because no two passengers will share an email address, we can use the EmailAddress as the primary key for the Passenger entity.

• An airplane can be involved in any number of flights, while each flight uses exactly one airplane, so the Flies relationship between the Airplane and Flight relationships has cardinality 1:N; because a flight cannot exist without an airplane, the Flight entity participates totally in this relationship.

• A passenger can book any number of flights, while a flight can be booked by any number of passengers. As discussed earlier in Intermediate Entities,” we could specify an M:N Books relationship between the Passenger and Flight relationship, but considering the issue more carefully shows that there is a hidden entity here: the booking itself. We capture this by creating the intermediate entity Booking and 1:N relationships between it and the Passenger and Flight entities. Identifying such entities allows us to get a better picture of the requirements. Note that even if we didn’t notice this hidden entity, it would come out as part of the ER-to-tables mapping process we’ll describe next in Using the Entity Relationship Model.”

Source: oreilly

The Tech Platform

www.thetechplatform.com