CS 33 (Spring 2011): Homework 3: Database and Basic Application Design

Deadline: 10:00pm on April 18, 2011 (Free extension until 10:00pm on April 19, 2011)

Unlike the previous two homework sets, this one is fairly open-ended, with details left up to you (and your imagination). There isn't any one right answer for these problems.

Part 1: E-R Diagrams and Database Design

1. Solve Problem 7.23 from the textbook. Note that this is really a two-part problem. Start by drawing an E-R diagram for your design.
   [15 points]


2. Finish the above problem by designing relational schemas and constraints, as asked for in the textbook. Put these schemas and constraints into a MySQL script which, when run, will create the required tables and load them with data, similar to what you see in ~cs33/data/university.sql. Make sure you load up a significant amount of data. Rough guideline: 20 cities, 50 to 70 flights (multiple flights per day between some pairs of cities), at least 10 seats per flight, at least 200 customers (you can borrow a list of names from anywhere you like, if you credit your source).
   [20 points]

Part 2: Web Interface to a Database

Implement some simple web-based query and update functionality for the database you designed above. Specifically, implement at least the following features.

3. From a customer's viewpoint, the ability to book or cancel a flight, and to retrieve a list of past and upcoming reservations. The system should assign the customer a seat on a flight when he/she books it, and should disallow the booking if the flight is full.
   [20 points]


4. From the airline's viewpoint, the ability to retrieve the passenger list of a past or upcoming flight, and to gather statistics on popular routes flown within a given date range. For definiteness's sake, let us interpret the second task as follows: given a date range, display the 5 most used flights (city-to-city) within that range, where the usage of a flight is the number of passengers who were on that flight.
   [25 points]

You need not implement any authorization, i.e., do not bother creating separate accounts with passwords for each customer, etc. Simply have a starting webpage from which the user can either perform a customer's actions, or the airline company's actions. Use a menu of some sort to pick an action. In order for your PHP code to access your database on sunapee.cs.dartmouth.edu, you need your database password. Don't put this password in your code; instead, have the user enter the password somewhere on the webpage.

For extra credit, you can add functionality to your system, e.g., letting a passenger select or change a specific seat, adding pricing information to the flights, etc. However, I strongly recommend that you first finish the required part of the homework before going for this.