CS 387 Database Design Project
Prof. Sung-Hyuk Cha
Due: Feb 15th, Mar 22nd, & May 3rd
In this project you will implement a database application of your choice
using a DBMS (either Oracle, MicroSoft Access or MySQL) and application
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
Finally, (see section 4) you will have to implement your system.
2 Design - Requirements of your application
DUE DATE: Feb 15th
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
You need to hand in the followings:
- 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.
- A detailed description of your application.
The description should include the following:
- The main goal of your application
- The potential user(s), e.g. if you wish to support views explain why and what kind of
views might be involved.
- 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?
- 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.
- Finally state any extensions you could have done should more time and resources were available.
3 The Relational Model
DUE DATE: Mar 22nd
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.
DUE DATE: May 3rd
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
MySQL and PHP.
be required to incorporate most of the following features:
Always have in mind the end-user(s) of your application and ask yourselves questions such as:
- loops (simple, nested and quantification).
- some complex SQL queries involving aggregate operators, group-by etc.
- user friendly interface (windows, special keys, help keys, forms)
- parameter passing among frames
- integrity constraints (type checking, value range checking, referential integrity,
Boyce-Codd Normal Form (in applicable))
- views (if applicable)
- Examples of entry, exit, and select processing
Finally, you are required to demonstrate your database application together with possible extensions
and improvements of your final product.
- what if the user types in an erroneous character or datum?
- how easy is it for her to acquire information and to navigate through the different windows?
- what should I do to enforce privacy? How do I protect different views from undesired users (if applicable)?
- which steps or side effects are involved as a result of an insertion? deletion? update?
How do I treat these cases?
You need to hand in the followings:
- hand-in a hard copy of your code files.
- be prepared for the presentation slides.