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
- 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
- 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).
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
- 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.
- 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.
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
What to turn in
- Prepare a PDF file (extension .pdf) with your E-R diagram for
- Prepare an SQL script file (extension .sql) for Problem 2.
for Problems 3 and 4. The starting webpage should be named
index.html or index.php. Put all of these files
into a directory (folder) called AirlineDB.
- Prepare a zip (or tar/gzip) archive containing the PDF file,
the SQL script, and the AirlineDB directory above. Submit
this zip file using the homework