ISE 503 -- Data Management

Fall 2024

Requirements Specification for the Database Programming Project

Introduction

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.

For these project, I suggest you use MySQL Server or SQL Server.

For MySQL, you can download a copy of MySQL server from the MySQL web site. MySQL is a good RDBMS for learning SQL, has very low resource requirements, is open-source, and has JDBC drivers. Also, it is SQL-99 compliant, so it will be more in tune with ANSI SQL standards as opposed to SQL Server, which has some proprietary semantics embedded over the standard query language.

SQL server is a relational database server produced by Microsoft, based on the ANSI SQL-92 standard. You can get a free copy of the SQL Server Developer Edition, for 2005 and 2008, from the Stony Brook DreamSpark (formerly known as MSDNAA) web site.

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.

Getting Started

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.

Project Specification

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.

1 System Users

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.

2 Required Data

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.

2.1 Airlines Data

This category of data should include the following items:

1. Airline ID
2. Airline Name

Each airline has an two-letter ID. For example, the ID for American Airlines is AA, and the ID for United Airlines is UA.

2.1 Airport Data

This category of data should include the following items:

1. Airport ID
2. Airport Name
3. City
4. Country

Each airport has a three-letter ID. For example, ISP, LGA, and JFK are well known local airport codes.

2.3 Flight Data

This category of data should include the following items:

1. Flight Number
2. Airline
3. Number of Seats
4. Stops
5. Days of Week
6. Fare

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.

2.4 Reservation Data

This category of data should include the following items:

1. Reservation Number
2. Date
3. Passenger(s)
4. Legs
5. Total Fare
6. Fare Restrictions
7. Booking Fee
8. Customer Representative

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.

2.5 Customer Data

The items required for this category include:

1. Last Name
2. First Name
3. Address
4. City
5. State
6. Zip Code
7. Telephone
8. E-mail Address
9. Account Number
10. Account Creation Date
11. Credit Card Number
12. Preferences
13. Rating

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 they are.

2.6 Employee Data

This category of data should include the following:

1. Social Security #
2. Last Name
3. First Name
4. Address
5. City
6. State
7. Zip Code
8. Telephone
9. Start Date
10. Hourly Rate

3 User-Level Transactions

  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).

3.1 Manager-Level Transactions

The manager should be able to:

• Add, Edit and Delete information for an employee
• Obtain a sales report for a particular month
• Produce a comprehensive listing of all flights
• Produce a list of reservations by flight number or by customer name
• Produce a summary listing of revenue generated by a particular flight, destination city, or customer
• Determine which customer representative generated most total revenue
• Determine which customer generated most total revenue
• Produce a list of most active flights
• Produce a list of all customers who have seats reserved on a given flight
• Produce a list of all flights for a given airport
• Produce a list of all flights whose arrival and departure times are on-time/delayed

3.2 Customer-Representative-Level Transactions

Customer Representatives should be thought of as reservation agents and should be able to:

• Record a reservation
• Add, Edit and Delete information for a customer
• Produce customer mailing lists
• Produce a list of flight suggestions for a given customer (based on that customer's past reservations)

3.3 Customer-Level Transactions

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:

• One-Way
• Round-Trip
• Multi-City
• Domestic or International
• Flexible Date/time

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:

• A customer's current reservations
• Travel itinerary for a given reservation
• A customer's current bid on a given reverse auction
• The bid history for a given reverse auction
• A history of all current and past reservations a customer has made
• Best-Seller list of flights
• Personalized flight suggestion list

  7 Documentation

  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:

  • Entity-Relationship (E-R) Diagram of the complete database scheme
  • Lucid description of the relational database scheme, including a discussion of the reasoning behind your design decisions. Make clear how your design supports efficient query processing.
  • A list of all functional dependencies in the relational database scheme
  • Description of integrity constraints including referential integrity

  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.

  8 Grading

  You will be given two assignments: 1) produce an E-R and relational model of your system; and 2) implement (in SQL) and execute all transactions described in the above project specification. The due date for the first assignment will be announced shortly.

  All documentation should be available on-line. You may also be asked to hand-in hardcopies when assignments are due.

  9 Collaboration Plan

  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:

  • Teammate 1 will focus on all aspects of flight reservations.
  • Teammate 2 will focus on all aspects related to querying the database for available flights, most active flights, site revenue, etc.
  • Teammate 3 will focus on all aspects of reverse auctions.

  About this document ...