Solutions for Exercise no. 3

1. Design an ER-Schema that actually reflects the requirements

Answer:
An ER-diagram for the airline reservation system is shown in the figure below.

2. Sketch how the ER-Diagram can be mapped to a set of tables.
Answer:
The ER-diagram above could be mapped to the following set of tables.

Airport (code, name, city, state)
AirplanType (type_name, max_seats, company)
Airplane (id, no_seats, type_name)
Flight (number, weekdays, airline)

Flightleg (flight_number, leg_number, departure_airport_code, departure_time,
                arrival_airport_code, arrival_time)
Fare (flight_number, code_number, amount, restrictions)
LegInstance (airplane_id, name, date, flight_number, leg_number,
                     departure_airport_code, departure_time,
                     arrival_airport_code, arrival_time)
Seat (airplane_id, name, seat_no, customer_name, phone)

CanLand (airport_code, airplane_type)