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.