As you probably know, there is a multitude of online travel reservation systems on the web. Some
popular ones are expedia, skyscanner and kayak. I suggest that you visit these web sites to get an
understanding of the look-and-feel of a travel web site and how such a system is supposed to
The basic idea behind your on-line travel reservation system is that it will allow customers to use
the web to browse/search the contents of your database (at least that part you want the customer to
see) and to make flight reservations over the web. Your web site should allow users to make both
domestic and international reservations. It should also allow users to query the database for
available flights (direct or indirect) between a pair of cities for a given date.
Actual travel sites allow you to do a lot more than simply make flight reservations. For example,
you can book a rental car or a hotel room. Due to time limitations, we will stick to flight
You will first create an E-R diagram of your online travel reservation system before developing
your relational model according to the functional requirements described below.
You are responsible for arranging the data items into tables, determining the relationships among
tables and identifying the key attributes. Finally, you should specify and enforce integrity
constraints on the data, including referential integrity constraints.
1. Functional Requirements
The users of your system will be the customers (passengers) that use your system to make a flight
reservation, customer representatives who provide customer-related services, and the site’s admin.
You should assume that the computer knowledge of the users is limited, and thus your system must
be easy to access and operate.
Every airline company owns a number of aircrafts and it is associated with a number of airports
from where it operates. Each airline has a two-letter ID from which it is being identified uniquely.
For example, the ID for American Airlines is AA, and the ID for United Airlines is UA. Similarly,
each airport has a three-letter ID. For example, EWR, LGA, and JFK are well known local airport
A flight is operated by an airline and a specific aircraft that has specific number of seats, and
operates on a given set of days of the week (e.g. every Monday, Wednesday). Flights can either be
domestic or international. For every flight, it must be recorded its flight number (unique only
within that airline), the departure and destination airports, as well as the departure and arrival time.
When accessing your online reservation system, customers should be able to search for specific
flights by providing information about the departure and arrival airport as well as the date they
wish to fly. They should also be able to search for one-way or round-trip flights, and they should
be able to set if they are flexible about flight dates (+/- 3 days).
Customers should then be able to make flight reservations (buy flight tickets) for the flights they
are interested in. Once they buy it, they should be able to see all the information for their flight
ticket. If there are not available seats for a specific flight, they should be able to get into the flight’s
waiting list. A flight ticket has a unique number and is for just a single passenger. Each ticket is
associated with a sequence of flights. For example a ticket might be associated with just one flight
if it is one-way and direct or with 2 flights if it is direct and round-trip or more than 2 if it has stops
(either for one-way or round-trip). Each ticket must include all the associated flights and include:
from-airport, to-airport, flight numbers (along with its airline), departure date and time, seat
number, and class (economy/business/first). It also has the following attributes: first and last name
of the passenger, ID number, total fare, and date and time when ticket was purchased. In case the
class of the ticket is economy, the customer should not be able to change/cancel their ticket unless
a fee is paid. For business/first class, customers should be able to change their ticket with no fee.
Finally, a flight ticket also has an associated booking fee, which is how your company makes