This project must be done individually. No copying is permitted. This is the first project from a series of five. You are going to build a database application in five stages:
One of the more important pieces of information that needs to be maintained is that related to flights. There is a need to know the carrier, the flight number, the departure and arrival (destination) airports, the departure and arrival times and the schedule (weekdays) for a given flight. One complication that is noticed during requirements analysis is that different types of airplanes may be assigned to the same flight on different weekdays. Thus, the number of available seats for each flight (both at the business and the economy class) change. Furthermore, the fare seems to change for different days (weekends are cheaper than weekdays) Therefore, there may be a need to keep track of these flight "instances" in addition to the more generic flight information. An airplane may be assigned to several flight instances (of course as long as their times do not conflict, but we are not concerned with this scheduling problem; that is the responsibility of the airlines). A flight instance, on the other hand, uses only one airplane. Needless to say, each flight has only one departure and arrival airport but each airport has many incoming and outgoing flights.
Since different airplanes may be assigned to a given flight on different days, customers have a tendency to ask what type of plane flies at a particular day. So, airplane information is also kept by the travel agency. The typical information that is maintained are the airplane identity (BOEING, AIRBUS, etc.), type, what type of an airplane it is (e.g., jet vs turboprop), the (business and economy class) capacity of the plane, and other physical information such as maximum range, length, etc. that some strange customers may ask for.
An essential piece of the data that Best Travel wants to maintain is data about its customers. This passenger information includes the name, address, telephone number, the set of frequent flyer memberships that the customer has (if any), the preferred payment method (e.g., credit cards and their numbers) and a list of preferences (front/back of the airplane, aisle/window, diet requirements).
When a customer makes a reservation, it should be kept in the company's database. The reservation record should identify the customer, the travel agent who made the reservation, the flights that are part of the same reservation (e.g., a flight from Dallas to Denver and a flight from Denver to San Fransisco), the payment method for this reservation and the status of the reservation. Typically, a reservation is either OK'ed which means it is confirmed and a seat is reserved for the customer, or the reservation is on a waiting list. Note that it is possible for one flight leg on a reservation to be OK'ed and another flight leg to be on the waiting list. For each reservation, the customer's preferences, his/her frequent flyer number (if any) are also recorded since the airline companies work directly from this relation and ticketing information and have no access to the customer information.
You need to draw an E-R diagram for your database design, along with any annotations needed to understand it. It will be graded on whether it is consistent with the directions in homework 1.
You need to submit your diagram online using the following form. Acceptable forms are: Microsoft word/powerpoint document, PDF, and postscript. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated. We do not accept email or hardcopy submissions. Any other form of submission, other than using this web form, gets a zero scrore.
Last modified: 02/10/10 by Leonidas Fegaras