Project Information

CMP 420: Database Systems
Lehman College, City University of New York
Spring 2003

General Information

This course is required to have a project implemented in a database query language (see the syllabus for course description). The projects for this semester will be implemented in a combination of SQL and Java. The project is worth 15% of your final grade.

As described in Lab 1, you will be building a substantial database application for a real-world scenario of your choosing. You will design a relational schema for the database, and you will create an actual database using a relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database, and develop user-friendly tools for manipulating the database. For ideas on possible databases, see the book's webpage.

Your first step is to identify the domain you would like to manage with your database, and to construct an entity-relationship diagram for the data. Pick an application that you will enjoy working with, since you'll be stuck with it for the rest of the semester.

Try to pick an application that is relatively substantial, but not too enormous. For example, when expressed in the entity-relationship model, you might want your design to have in the range of five or so entity sets, and a similar number of relationships. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application is not required to use advanced features, such as subclassing, multiway relationships, or weak entity sets, if they are not appropriate for your application.

Initial Design

Target Date: 20 February in lab.

Please use complete sentences, and type (or write neatly) the following:

Describe the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded only on suitability and conciseness.
Specify an entity-relationship diagram for your proposed database. Don't forget to underline key attributes and include arrowheads indicating the multiplicity of relationships. If there are weak entity sets, indicate them by double lines, as described in class.

Design Specification

Target Date: 6 March in lab.

For this part of the project, you need:

An E/R diagram describing your database application. Include the one from the first part of the project, addressing any changes suggested. If there's significant changes from your initial E/R diagram, include a redrawn version. Rembember, you should have at least 5 entity sets and 5 relations. Make sure to mark any one-one and many-one relations, as well as weak entity sets, subclasses, and keys. Include as notes on your diagram any assumptions made for your database.
Conversion to Relations: Convert your E/R diagram to a relational schema. Include keys for each relation and a list of functional dependencies for each of your relations.
Normal Forms: Are your relations in normal form for BCNF, 3NF or 4NF? Include a brief explanation about which normal forms your relations obey. If they violate some normal form, explain why (remember, sometimes there's cases where being in normal form might not be best for the design).

Initial Webpage

Target Date: 13 March in lab.

For this part of the project, you need to design the initial webpage for your project. In the future, the webpage will be used to access your database. For now, your webpage should have:

The name of your project,
your name, and
a short description of the project (2-3 sentences that describe your database-- these can be taken from your original design).

You should title the file index.html and post your webpage in your public_html directory on comet.

SQL Schema and Database Creation

Target Date: 20 March in lab.

You must turn in, on paper, the SQL commands used (as well as any programs used). You also must demonstrate that your database has the properties listed below, during lab.

For this part of the project, you need an SQL schema that:

creates the relations in your database, and
defines primary keys and references (can be done in the creation statements above or as separate commands).

You also need to add several tuples to the database and demonstrate that the information is loaded properly. You do not have to worry about implementing all constraints at this point (since how to do this in SQL has not been covered yet), you only need to define keys.

Populating your Database

Target Date: 10 April in lab.

You must turn in, on paper, the SQL commands used (as well as any programs used). You also must demonstrate that your database has the properties listed below, during lab.

For this part of the project, you need to populate your database with data. To do this, you may take data from webpages, as in Lab 8 (include references to any webpages you use) or you may create the data, as in Lab 5. The number of tuples per table will depend on your design, but you must have at least 500 tuples of data.

Simple Graphical User Interface

Target Date: 1 May in lab.

You also must demonstrate that your database has the properties listed below, during lab. The program and webpage used for this simple GUI will be used in later parts of the project.

For this part of the project, you need a webpage with a java applet. The applet should have several buttons and a text window for returning messages. For this first version, when a user pushes one of the buttons, a message should appear in the text window. A different message should appear for each button. (In future versions, each button will be linked to an SQL query, and the messages will be the response from the SQL server to the query.)

Views

Target Date: 8 May in lab.

Create two natural views for your database. These should incorporate some common query a user would ask of your database. For example, if your database is for employees, you might want a view that consists of the names and addresses of all employees that live in the Bronx.

Java Interface

Due 15 May in lab.

Write a java interface for a user to interact with your database. A user should be able to click on a button that executes one of your queries from above and displays the results to the screen (using a scrolling text subwindow would be one way to do this). You should have a second button that displays one of your views.

Extra credit points (5% each) will be given, if in addition to above, your interface:

allows the user to insert a tuple into one of your tables, using button, radio buttons, and text windows (i.e. the user should do the minimal amount of typing). For example, if your database is for a university, you should allow a user to enroll in a class, by typing their social security number, and then choosing the department and course number from menus or buttons.
allows the user to delete a tuple from your database by specifying only the key. For example, if your database is an employee database, include the ability for the user to delete employees by typing only their employee id number.

Turning in the Final Project

During the last lab, you need to show Prof. St. John how each part of your lab works. Once you have done that, you should collect all the files for the lab into a bundle (called a tarball) and mail it to stjohn@lehman.cuny.edu.

To make bundle a directory, called myproject, you type at the command line:

    tar cfv myproject.tar myproject

This will create a file called myproject.tar that consists of all the files in the directory myproject. To mail it, you can use Netscape mail, or you type at the command line:

    mail stjohn < myproject.tar

Since all accounts will disappear after classes end, you should transfer any files you wish to keep to another machine. To do this, make a tarball of the directories and then mail it (or you can use the file transfer program, ftp) to another machine.