Topic: Database Revision
Related Reading:
Due: 8:00pm, Monday 7 November 2005

Please make sure you adhere to the policies on academic integrity.

This assignment is a followup to the earlier assignment in which you developed your own database schema and implementation. Presumably, you will be working in the same group for this assignment as you did for that earlier assignment. However if there is some reason why the previous group dynamics no longer works, please speak to me as soon as possible so that we can devise a plan for this assignment.

Requirements for Revision

For this assignment, you are to take the feedback which you have received from the original work, and to perfect your design, your implementation and your demonstration of the database. Problems which were clearly addressed in the feedback to the original assignment should be remedied in your revision. Furthermore, we are asking everyone for additional work which was not in the original assignment. The precise efforts for each group will vary somewhat depending on the state of the original work, but the following are the major themes for the new assignment

• Accurate ER model
  You must submit a new version of your ER model, which is accurate in the following ways:

  • If there were major design problems with your original model, those should be corrected.

  • You should use the precise diagramming conventions as used in our class and text, including annotations for the cardinality constraints on the participating entities for each relationship.

  • The ER model on paper should match as closely as possible with the actual SQL implementation you are giving. This means that the names of the entities, relationships and attributes should be the same, when appropriate.

    However please note that the ER model is not the same as the SQL implementation. There may be times where an entity and relationship is the ER diagram are combined into a single table in the relational schema. Similarly, the foreign keys in the relational schema which represent participating entities for a relationship are not explicitly diagramed as attributes in the ER model (but rather are implicit in the semantics).

• Accurate SQL scripts
  We will be testing your revised SQL scripts on turing. You are to ensure their correct operation. Furthermore, we ask that you explicitly break your previous SQL script(s) into distinct pieces as follows:

  • An SQL script titled schema.sql, which defines all aspects of your database schema (i.e., the tables, constraints, and any associated domains). This script should be written so that we can recreate your database schema from scratch.

  • An SQL script titled populate.sql, which populates your database instance (this will be called once, after schema.sql has been executed)

  • An SQL script titled cleanup.sql, which can be used to drop all parts of your database, getting us back to a clean slate.

• New SQL Queries
  To see your database in action, you are to write queries to answer five natural questions for your database domain. In particular, five specific questions have been suggested for your project, as part of the feedback to the original assignment. You must write queries for those five questions based on your (redesigned) SQL schema. You should create a separate script query.sql with those queries, and you should save the output of those queries on your populated database in a separate text file results.

• Populate your database with a richer instance
  In generally, the original assignments were quite weak in the area of populating the database. Though it is not necessary to have thousands of entries, it is important to have enough data entered to demonstrate the aspects of the database. In particular

  • Every table which is defined should have a bare minimum of two entries.
  • If a relationship schema is many-to-one, then there should be data demonstrating such a many-to-one relationship
  • If a relationship schema is many-to-many, then there should be data demonstrating such a many-to-many relationship.
  In focusing your efforts, consider populating your database more fully in areas which involve the five sample queries. Make sure that the results of those queries are interesting, both for the data which is included as well as the data which is excluded.

• Demonstrate the integrity constraints
  Presumably, all of the insertions in the populate.sql script should succeed. However it should be possible to demonstrate the enforcement of the integrity constraints (e.g. primary key, unique, and foreign key constraints) by attempting other data manipulations which violated such constraints. To do this, provide a separate script, failure.sql, which gives a series of commands which trigger the failure of the various constraints in your schema.

Submitting your Assignment

Based on the above description, each group must submit the following documents:

• A revised version of your ER diagram.

• Five SQL scripts, schema.sql, populate.sql, query.sql, failure.sql, cleanup.sql.

• A text file, results, giving the output of the executed queries on your populated database.

Grading Standards

We will attempt to base our evaluation of this assignment as fairly as possible upon the work which has been done during the revision (as opposed to the quality of the original assignment).