CS 387 Database Design Project

Prof. Sung-Hyuk Cha
Fall 2003
Due: Sept 18th, Oct 16th, & Dec 2nd

1 Introduction

In this project you will implement a database application of your choice using a DBMS (either Oracle, MicroSoft Access or MySQL) and application system. The project consists of three parts. Each part has a separate due data. In the first part (see section 2), you will represent the requirements and the design of the application you wish to implement. In the second part (see section 3), you will implement the relational model based on the ER-diagram of the first part. Finally, (see section 4) you will have to implement your system.

2 Design - Requirements of your application

DUE DATE: Sept 18th

You will form groups of three people. In all the documents you hand in, clearly mark the names of the people in the group. Also, mention any specific contributions or work of each member. Each group can choose a non-trivial application (e.g., an enterprise involving stocks, orders, invoices, etc., hospital, insurance company, theater or playhouse reservation or anything you might find interesting, useful and perhaps original).

You need to hand in the followings:

  1. ER-diagram of your application.
    The minimum requirements in terms of the overall complexity are: at least 6 entities and 5 relationships (among which at least 1 many-to-many and 2 one-to-many relationships). Describe the necessity of each entity/relationship, argue about the selection of primary keys, state clearly your assumptions about mapping cardinalities.
  2. A detailed description of your application.
    The description should include the following:
    1. The main goal of your application
    2. The potential user(s), e.g. if you wish to support views explain why and what kind of views might be involved.
    3. The overall flow of information. How will the final product be like? How does a user navigate through the main functional parts of your design? What are these parts? How is the data acquisition phase going to be like in terms of user friendliness, errors etc?
    4. Most importantly give a collection of sample queries you would like your application to be able to execute. Try to think which queries will be executed more often (i.e., popular queries): you might want to 'tune-up' your design accordingly.
    5. Finally state any extensions you could have done should more time and resources were available.

3 The Relational Model

DUE DATE: Oct 16th

Based on your design with possible modifications and corrections, create all the necessary tables (they should be at least 10) and justify why you might choose not to create separate tables for one-to-many or one-to-one relationships (think of the complications arising from such a decision). For example, NULL values and how you expect to treat them. Finally, primary keys should be clearly stated as well as the type of all attributes involved.

4 Implementation

DUE DATE: Dec 2nd

You are allowed to do last minute changes and to reconsider part of your initial design, but be prepared to be able to justify all these changes.
In terms of implementation, there will be an introductory session on Perl and Oracle or MySQL and PHP. You will be required to incorporate most of the following features:

  1. loops (simple, nested and quantification).
  2. some complex SQL queries involving aggregate operators, group-by etc.
  3. user friendly interface (windows, special keys, help keys, forms)
  4. parameter passing among frames
  5. integrity constraints (type checking, value range checking, referential integrity, Boyce-Codd Normal Form (in applicable))
  6. views (if applicable)
  7. Examples of entry, exit, and select processing
Always have in mind the end-user(s) of your application and ask yourselves questions such as: Finally, you are required to demonstrate your database application together with possible extensions and improvements of your final product.

You need to hand in the followings:

  1. hand-in a hard copy of your code files.
  2. be prepared for the presentation slides.