Project Requirements
The class will be divided up to into groups of up to 2 persons
each. You must inform me of your group
name and members by Wednesday August 31. Each group will design and develop your
own substantial database application.
You will begin with E/R diagrams, convert them into relations, and
ultimately create database schemas through SQL.
Once your database is designed you will populate your database with a
significant amount of data and formulate several ‘useful’ queries. Finally you will assemble several web-pages
that provide an easy to use GUI to manipulate the database.
Part 1: Database Design (25%)
The first part of the project is for you to identify the domain of data
you would like to manage with your database and to construct an E/R diagram
that models the data. By default, you
should pick one of the database projects described in chapter 1 of the textbook
and work on it for the whole semester. If you strongly prefer a database
project of your own design, you should lay out your ideas on paper and talk
with me as soon as possible. The ideal database project for this course should
be relatively substantial, but not too enormous. Your E/R diagram design should have in the range
of 5-10 entity sets and a similar number of relationships. You should include different kinds of
relationships (many-many, many-one) and different kinds of data (string,
integer) but your application is not required to use such features as subclassing, multi-way relationships, or weak entity sets
if they are not appropriate for your application.
(a) Describe the
database application you propose to work with through the course. Your description should be brief and
relatively informal. If there are any
unique or particularly difficult aspects please point them out. Your description will be graded on clarity
and conciseness.
(b) Specify an E/R
diagram for your proposed database.
Don’t forget to underline key attributes and include arrows and rounded
arrows indicating the multiplicity of relationships. Weak entity sets must also be indicated with
double lines.
(c) Use the method for
translating E/R diagrams into relations to produce a set of relations. Specify your relational schema using the notation
described in lecture (as well as the textbook) and be sure to underline key
attributes.
(d) List all functional
dependencies for all relations. Make
sure there are no flaws in your schemas.
Do you have non-BCNF relation schemas?
If so, do you want to decompose them?
Part 2: SQL (35%)
(a) Write an SQL
database schema using the CREATE TABLE commands. Pick suitable data types for each
attribute. You need to produce a file
with the commands you use to create your database schema.
(b) Develop a
substantial amount of data for your database and load it into your relations
using the related commands. To create
data you can write a program in any programming language that creates large
files of records in a format acceptable to the MySQL
data loader. You need large amounts of
data so that you can experiment with a database of realistic size.
At least two relations
should have a couple thousand tuples each and at
least one additional relation with several hundred tuples.
(c) Write 5 queries that
exhibit some interesting feature of SQL (queries over multiple relations,
aggregations, etc). You must turn in a
copy of your SQL queries along with a script illustrating their execution (but
don’t turn in thousands of lines worth of tuples).
(d) Write 5 data
modification commands. These must be
interesting modifications (updating several tuples at
once, deleting a proper subset of tuples in a given
relation, etc).
Part 3: Web-Interface (40%)
Design several “user-friendly” web pages to provide an interactive
application program front end to your database.
(a) Revise any schemas
to include proper foreign-key constraints and add at least two attributes based
constraints. Show the revised schema,
its successful declaration, and the response to violations of the constraints.
(b)
Your interactive web application should consist of a continuous loop in which:
1. A list of at least
five alternative options is offered with an additional quit option.
2. The user selects an
alternative.
3. The system prompts
the user for appropriate input values (if any).
4. The system accesses
the database to perform the appropriate queries / modifications.
5. Data or an
appropriate acknowledgment is returned to the user.
(c) All projects will be DEMO-ed at
the end of the course.