Project
Introduction
The goal of this project is to assess your knowledge of the main notions presented in classroom.
The project must be implemented by students working in groups.
This project consists in designing a relational database for the given application context, importing the data of a given dataset into a PostgreSQL database and querying the data in SQL.
Application context
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, addresses and an average review score, which is a real number denoting the average grade assigned to the hotel by its customers. 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.
Design of a relational database
You’ll now proceed to the definition of a relational database for our travel reservation system. First, you need to define the conceptual schema and then you’ll define the tables that compose the database.
The conceptual schema
Before defining the logical schema of the database, answer the following questions:
Can you use the name of the hotel as a primary key? Justify your answer.
Can you use the flight number as a primary key to identify a flight? Justify your answer and, in case of a negative answer, propose a solution.
Knowing that it is unlikely that two reviews have the same textual content, would you use it as a primary key? Justify your answer.
Knowing that the IATA code uniquely identifies an airport, would you choose it as a primary key for the entity Airport? Justify your answer.
Exercise
Exercise 1 Propose an Entity-Relationship 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.