Exercise 3

1. ER-modeling and from an ER-model to Tables

Propose an ER diagram that models an airline reservation system with the following requirements.

For each airplane we keep track of its identity, number of seats, and type. Its type is defined by the name, the maximum number of seats, and the company that produces it. Specific airplane types can land at specific airports. For each airport we need to know the name, the city, the state, and the code of it. Each flight has information about the airline, the number and the weekdays it operates. It also has fares, with code number, amount, and restrictions. A flight has several flight legs (a flight leg is for example Aalborg-Copenhagen, and Copenhagen-New York, when you are flying from Aalborg to New York via Copenhagen), from and to an airport, with scheduled departure and arrival time respectively. Each airplane is assigned to a leg instance, which is a particular occurrence of a flight leg on a particular date, while a leg is a non-stop portion of a flight. Each leg instance arrives to and departs from airport at specific arrival and departure times. Finally, for each leg instance we can make reservations of seats, based on the customer name and customer phone number.

  1. Design an ER schema that accurately reflects the requirements.
  2. Sketch how the ER diagram can be mapped to a set of tables.

2. From Tables to an ER-Model

You may find yourself in the situation where you have to work with a database schema that is not well documented. In this particular case we will assume that the documentation does not contain an ER-model. To get an overview of the database the first thing you do is to make an ER-model from the table definitions. In this exercise we use the tables from exercise 1, your job is to make an ER-model over these 5 tables. In particular you have to document primary keys, foreign keys, and cardinalities.

Best regards,
Kristian Torp