project

CMSC 661 Database Systems

Project Description

You are required to build a database application for a business of your choice. The project will consist of

developing a schema on paper,
creating the actual database using the Oracle database system from OIT, (accounts on Oracle server will be provided soon)
populating the database and testing with interesting queries,
modifying the database and creating programs to manipulate the database,
implementing constraints and triggers,
developing a Web front-end for the database application.

You will work in groups of 2 to 3. The project will proceed in three phases as described below. Each phase has a set of deliverables and deadline associated with it. At the end of the semester, each team is required to demo their project. A schedule of the demo will be made known to the class towards the end of the semester.

Phase I -- Design

After identifying the business application, your first step is to construct an E-R diagram for the database application.
The requirements for the size of the problem domain: at least 5 entity sets and about the same number of relationship sets; different cardinalities of relationships -- many-to-one, many-to-many, one-to-one; different kinds of data(strings, integers, and so on), at least one weak entity set. Identify referential integrity constraints between different entity sets -- referencing and referenced entity sets. Identify key attributes for the entity sets.

Deliverables: Due September 26
1. A short description of the database application
2. E/R diagram for your database application. Follow all notational conventions.
3. Team members. Note: if you have problems finding a team to work with, contact Dr. Mundur immediately.

Go or No Go/Modify decision from Dr. Mundur on October 3 (It is in your interest to submit a well-thought out design of the complexity expected -- otherwise modifying it to fit the minimum requirements will cut into your Phase II and III time).

Phase II -- Relations and Implementation Plan

Translate your E/R diagram into a set of relations. Eliminate or combine relations after assessing the necessity of having each relation. Underline all key attributes in your relations.
Specify all nontrivial functional dependencies for each relation.
Provide a critique of your design and verify the normal form, BCNF or 3NF that each relation in your schema is in. Decompose the relations appropriately so that each relation is in BCNFor 3NF.
In parallel, start putting together an implementation plan -- play around with Oracle and Java and develop a plan for implementing your application.

Deliverables: Due Oct 12
All previous deliverables with modifications if any
1. Relations corresponding to the E/R diagram.
2. Nontrivial Functional dependencies for each relation.
3. Normal form that the relation is in.
4. Decompose if necessary so that each relation is in BCNF or 3NF.
5. As part of your implementation plan, identify all system functions -- DBA and non-DBA.

Phase III -- Implementation, Test, and Demo
This will be the final phase of the project where you will be required to create, populate, and use your database using Oracle. A Web front-end is also required. You will also be required to implement appropriate constraints (at least 4) and triggers (at least 1). Develop and test several interesting SQL queries -- data retrieval as well data manipulation statements (your test queries will
be part of project grade).

A few pointers for populating your database: generate your fake data carefully, for instance, do not generate duplicate values for key attributes. For finding answers to interesting queries, it is expected that two or more relations will join. Be sure to generate values that match with values from other relations. Generating values for multiple relations at the same time will help.

Deliverables: Due Dec 13-14 (SEE DEMO GUIDELINES)
All previous deliverables with modifications if any.
1. A sample program code for creating the database.
2. Any scripts for generating and loading the data into your database.
3. Sample code for constraints and triggers (using PL/SQL).
4. SQL queries and other statements used in testing and test results .
5. Contribution log for the project. While you cannot precisely put a number on how much work each person in your team contributed, try to estimate. If you do not have any problems acknowledging equal responsibility, indicate the same.
6. A 15-30 minute demo. The schedule will be made available.

Tentative grading Policy:
(each category will be further broken down as we progress through the semester)

Design 30%
Implementation 60%
Demo & Presentation 10%