DATABASE MANAGEMENT

Part I: Entity-Relationship Diagram (30 marks) Consider modelling the following simplified description of airline flights. An airline is identified by a code (e.g. BA) and also has a name. Each airline operates a number of flights. Do not worry about so-called codeshare agreements, which allow multiple airlines to operate the same flight, each potentially with a different flight number. We assume that each flight is operated by exactly one airline. Each flight has a unique flight number, an airport it flies from, an airport it flies to, a departure time and an arrival time. All this information must be recorded for each flight. An airport has a unique code (e.g. LHR) and a name (e.g. London Heathrow). There are many occurrences of each flight. For a particular flight, each occurrence is on a different date. Passengers make bookings on particular occurrences of flights. Each such booking has a seat assignment associated with it. Each passenger is identified by their passport number and also has a name recorded. They must be booked on at least one flight occurrence. Draw an entity-relationship diagram that models the above description of flights etc. Make sure that you specify all details in the diagram, including cardinality (mapping) constraints, participation constraints, keys. etc. Please use the ERD notation used in lectures. Submit a file containing your diagram. You do not necessarily have to use a tool or sophisticated drawing program to produce the diagram; even a hand-drawn and scanned diagram is fine if it is neat and legible