Relational data modeling
The goal of this tutorial is to practice the design of a relational database.
The following topics are covered:
- Conceptual schema of a relational database.
- Logical schema of a relational database.
- Primary keys and foreign keys.
1 Application scenario
We intend to manage the data of a travel reservation system with clients all over the world. Upon registration, customers are automatically given a numeric identifier and they are asked to indicate their first and family names, their gender, date of birth, a phone number, an email address and their country of residence.
Any customer can book a trip that includes the reservation of one or more flights and, possibly, one or more hotels.
Example
Alice wants to fly from Paris, France to New York City (NYC), USA and she intends to stay in NYC for 10 days. Her trip includes two flights: an outbound flight from Paris to NYC and an inbound flight from NYC to Paris; and an hotel in NYC.
A flight is operated by an airline company, of which the system keeps its name (e.g., British Airways), the country where the airline is incorporated and, when available, its IATA code (e.g., BA, a two-letter code identifying the airline), its ICAO code (e.g., BAW, a three-letter code identifying the airline) and alternate name or alias (e.g., British).
A flight connects two airports, each having a name (e.g., London Heathrow Airport), and, possibly, a IATA (e.g., LHR) and ICAO code (e.g., EGLL); an airport serves a specific location (e.g., London, UK) and its precise position is given by its geographic coordinates (latitude and longitude).
A flight connecting two airports at specific departure and arrival times is identified by a flight number. Two flights operated by two different airline companies cannot have the same flight number, but the same flight number can denote two flights operated by the same airline company on different days.
Example
Emirates flight EK074 leaves Paris, France at 10 AM and arrives at Dubai, UAE at 7:40 PM (regardless of the departure day).
For each flight booked by a customer, the system keeps the seat number, the travel class (e.g., economy or business), the price and the date of the flight. Usually, airlines include details on the type of aircraft they plan to use on their flight schedules; these details include the name of the aircraft (e.g., Boeing 787-8) and, when available, the IATA code (e.g., 788, a unique three-letter identifier for the aircraft) and the ICAO code (e.g., B788, a unique four-letter identifier for the aircraft).
The system maintains a list of hotels, with their names and addresses. Customers can write a review for an hotel; in which case the system stores the text of the review, the date and its author. When a customer books an hotel, the system keeps the price paid, the check-in and check-out dates and whether the breakfast is included.
2 Conceptual schema
The conceptual schema of the database is an abstract representation of the entities of the application and their relationships. The conceptual schema does not include any implementation details, such as the definition of primary keys or foreign keys, nor does it include any information about the attribute types. The conceptual schema is usually represented through an Entity-Relationship (ER) diagram.
Exercise
Exercise 2.1 Propose an Entity-Relationship (ER) diagram describing the conceptual model of a relational database for the given application context.
Specify all the attributes for each entity and relation.
For each entity, underline the attributes composing the primary key.
For each relation, clearly indicate the minimum and maximum cardinality.
Solution
3 Logical schema
The logical schema of a relational database is the collection of tables that implement the conceptual schema. The logical schema includes the definition of primary keys and foreign keys, but it does not include any information about the column types.
Entity-Relationship (ER) diagram.
Exercise
Exercise 3.1 Give the logical schema of the database. For each table, underline the primary key and specify the foreign keys.
Solution
customer (cust_code, first_name, last_name, gender, birth_date, phone_number, email_address, cust_country)
trip (trip_code, cust_code)
flight (flight_id, flight_number, flight_date, departure_time, arrival_time, departure_airport, arrival_airport, airline_name, aircraft_name)
hotel (hotel_id, hotel_name, hotel_address)
hotel_reservation (trip_code, hotel_id, price, checkin_date, checkout_date, breakfast)
flight_booking (trip_code, flight_id, seat_number , travel_class, booking_date, price)
review (hotel_id, cust_code, content, date)
airline (airline_name, airline_country, airline_iata, airline_icao, airline_alias)
aircraft (aircraft_name, aircraft_iata, aircraft_icao)
airport (airport_id, airport_name, airport_iata, airport_icao, city, country, latitude, longitude)
Foreign keys:
trip(cust_code) → customer(cust_code)
flight(departure_airport) → airport(airport_id)
flight(arrival_airport) → airport(airport_id)
flight(airline_name) → airline(airline_name)
flight(aircraft_name) → aircraft(aircraft_name)
hotel_reservation(hotel_id) → hotel(hotel_id)
flight_booking(trip_code) → trip(trip_code)
flight_booking(flight_id) → flight(flight_id)
review(cust_code) → customer(cust_code)
review(hotel_id) → hotel(hotel_id)
In table flight we introduced a surrogate primary key flight_id to uniquely identify each flight instance. However, the table is not in third normal form (3NF), because the departure_airport and arrival_airport deped on flight_number.