CSE3330/5330 Project #1

Thursday February 18, before midnight
Worth 5% of the final grade


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:

Each project is worth 5% of the final score, so that the total weight of the project is 25%.


Best Travel is a new travel agency that is being set up with the objective of providing the best services to its customers by keeping at the leading edge of technology. Thus, they would not only like to provide the traditional travel agency services, but they also keep detailed records on their customers and their preferences in order to customize their services to the clients' needs and preferences. You have been hired to set-up an information system to assist Best Travel achieve its goals. Note that Figure 3.20 on Page 94 in the textbook captures a similar application. You can get ideas from this diagram but you should not copy it blindly. Some of the requirements listed below are slightly different from those used in this ER diagram.

Requirements Analysis

A requirements analysis that was conducted has identified a number things about the operations and goals of Best Travel. You, as the systems analyst/designer, should feel free to add to these requirements in order to achieve a richer design. The travel agency keeps records about a number of items. First and foremost, it keeps track of the airports in the system which are uniquely identified by their international codes (for example, for Dallas/Fort Worth airport the code is DFW). The name of the airport, its location (city and country) and its phone number are other essential information. The travel agency is thinking about including other information such as the available facilities so that this information may be provided to customers who may be connecting at a particular airport. There is information about the airlines (also called carriers in the business) that are also kept by the travel agency. The more important information about the airlines that are stored are their unique names, their addresses, the contact phone number.

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.

What to Submit

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.

Submit Project #1:

Last modified: 02/10/10 by Leonidas Fegaras