In this project, you will design and implement a relational database system to support the operations of an online travel reservation system, along the lines of expedia.com, booking.com, and many others. You will use HTML for the user interface, MySQL or SQL Server for the database server, and Java, Javascript, and JDBC for connectivity between the user interface and database server. Please install MySQL or SQL server on your laptop if you have not already done so.
You are to work in teams of three. Many of you will form
teams of three on your own. If not, do not worry. Please just be sure to
attend class on a regular basis, and I will make sure that every student
is placed in a team of three.
As you probably know, there are a multitude of online travel reservation systems on the web. Two popular ones are expedia.com and orbitz.com. I suggest that you visit these web site to get an understanding of the look-and-feel of a travel web site and how such a system is supposed to function.
I will also place a Powerpoint tutorial on blackboard on how to connect to MySQL using Java and on how to develop the UI (User Interface) for your project. You will be able to find the tutorial in the Project Assignments subfolder of the Assignments folder. (You will also be able to find the tutorial via the link associated with the announcement I will post on blackboard about the tutorial.)
To further help you with the project, a former TA has developed a prototypical database system for a University Registration system that closely resembles the student registration system used as a running example in the course textbook. You should consult this demo system as a guide for your project development. You can access the demo system, and the underlying source code, by following the links I placed in an announcement I recently posted on blackboard. As you experiment with the demo system, you should also start to become familiar with Java, Javascript, HTML, and JDBC on your own. I will lecture about JDBC (Chapter 8 of the course textbook) after the midterm. You might also want to buy the books about these languages recommended on the course homepage.
It is recommended that you regularly check the blackboard Project
Assignments folder and the
PROJECT
ASSIGNMENTS web page for information on the course project. This page will
be updated from time to time with important information on how to proceed with
the project.
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 and "approximate" time.
Your system should also support reverse auctions, in which individuals specify the price they are willing to pay for a seat and the airlines either agree to sell it at that price or not. Reverse auction sites include priceline.com and expedia.com, a Microsoft-owned travel site that has a feature enabling customers to name their price.
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 consider flight reservations only this semester.
Your database system must be based on the specifications and requirements that follow.
The users of your system will be the customers (pasengers) that use your system to make a flight reservation, customer representatives who provide customer-related services, and the site's manager. You should assume that the computer knowledge of the users is limited (say, that of a typical AOL subscriber), and thus your system must be easy to access and operate.
The data items required for the travel reservation database can be classified into six categories: airlines, airports, flights, reservations, customers and employees.
The above classification does not imply any particular table arrangement. You are responsible for arranging the data items into tables, determining the relationships among tables and identifying the key attributes. In addition, you should include indices in your tables to speed up query processing. You should base your choice of indices on the type and expected frequency of the queries outlined in Section 3. Finally, you should specify and enforce integrity constraints on the data, including referential integrity constraints.
You will first create an E-R diagram of your online travel reservation system before developing your relational model. Details of this assignment will be forthcoming.
This category of data should include the following items:
Each airline has an two-letter ID. For example, the ID for American Airlines is AA, and the ID for United Airlines is UA.
This category of data should include the following items:
Each airport has a three-letter ID. For example, ISP, LGA, and JFK are well known local airport codes.
This category of data should include the following items:
A flight is operated by an airline, has a flight number (unique only within that airline), makes a sequence of airport stops (with each stop having local arrival and departure times), and operates on a given set of days of the week. For example, UA Flight # 1 flies from Chicago O'Hare on Mondays and Thursdays, departing at 5pm and arrives into Paris at 6am the next day. It then departs Paris at 10am local time to arrive in Rome at 12pm, and so on. A flight also has an associated fare and fare restrictions, which include Advance Purchase (deeply discounted fares may require advance purchases of a 3, 7, 14, or 21 days) and Length of Stay (some round-trip fares may have minimum or maximum stay requirements). A flight may also have a hidden fare, which is the lowest bid the airline is willing to accept in a reverse auction.
This category of data should include the following items:
A reservation has a unique number and is either for a single passenger or a set (maybe a group/family) of passengers. Each reservation is associated with a sequence of direct-flight segments called the legs of the reservation. Each leg has an associated from-airport, to-airport, flight number (along with its airline), departure date and time, special meal ordered, seat number, and class (economy/business/first). A reservation also has the following attributes: total fare, fare restrictions, and date when reservation made. For example, Mr. John Smith makes a reservation on 1/1/2020. He flies from Chicago on 1/10/2020 on flight UA #1 to Paris. After staying for a couple of days in Paris, Mr. Smith departs on 1/12/2020 from Paris on flight UA #3 to London, and so on.
A reservation also has an associated booking fee, which is how your company makes money, and an associated customer representative.
The items required for this category include:
A customer may partake in any number of flight reservation transactions, and may have one or more accounts from which to purchase tickets. Associated with each account is a reservation portfolio, indicating which reservations are held in that account. Customer preferences include aisle or window seat, preferred meal, etc. The customer's rating should reflect how active a ticket buyer he or she is.
This category of data should include the following:
A database transaction can be viewed as a small program (written in the DML) that either updates or queries the database. Transactions that change the contents of the database must do so in a consistent manner. Moreover, transactions should not interfere with one another when running concurrently.
What follows is a breakdown of the user-level transactions that your database system should support. To make sure transactions maintain the integrity of the database, you must write them using the SQL transaction structuring capabilities (i.e., begin transaction, commit transaction, etc).
The manager should be able to:
Customer Representatives should be thought of as reservation agents and should be able to:
Customers should be thought of as online airline ticket buyers and should be able to easily browse your online travel reservation system on the web and make flight reservations. In particular, they should be able to make the following types of reservations:
A customer should also be able to cancel an existing reservation and partake in reverse auctions. While they will not be permitted to access the database directly, they should be able to retrieve the following information:
Your database system should provide controlled access to the data by distinguishing between the different types of users: manager, customer representatives, and customers.
In addition to the transactions described above, the system should provide facilities for:
HTML and its successors provide facilities for creating pop-up and pull-down menus, value lists, input/output forms, labels and customized reports. You should make use of all of these capabilities, and in the process come up with a system that caters to users with only limited computer knowledge. The information you provide to customers should look professional and inviting.
You will be required to supplement your completed database implementation with a design document that contains information concerning your design criteria and decisions. The following is a list of some of the information you should include:
You will also be required to submit a Users Guide that carefully explains how to use all aspects of the system. It should be understandable by non-computer experts. Be sure that the user interface (screen design, menu structure, etc.) is clearly explained.
You will be given three assignments: 1) produce an E-R and relational model of your system; 2) implement (in SQL) and execute all transactions described in the above project specification; and 3) implement (using Java, Javascript and JDBC) the final interactive system to support your online movie rental system. The due date for the first assignment will be announced shortly; as for the the other two assignments, the due dates for all three assignments will be spaced roughly three weeks apart.
All documentation should be available on-line. You may also be asked to hand-in hardcopies when assignments are due.
As stated above, you will be working on the course project in teams of three. A rough three-way division of labor is as follows: