CS125 Project, Part 2

Due Wednesday, September 27, 2006

Part 2 of Your Database Project

Having submitted your project domain proposal (and received approval), your first step is to construct an entity-relationship diagram for the data.   As suggested in Project 1 assignment, your entity-relationship diagram should reflect a moderate number of entity sets and relationships -- in the 8-14 range.  You should have already been thinking about the types of queries you want to run.  In other words, what is the intended purpose of the database?  The answer to this question should guide your modeling process.

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 relationsships, or weak entity sets, if they are not appropriate for your application.

Project 2 Deliverable

1.  Create an entity-relationship diagram for your proposed database. As always, don't forget to underline key attributes and include arrowheads and rounded arrows indicating the multiplicity of relationships. If there are weak entity sets, indicate them by double lines, as described in class.  I suggest you use a drawing tool for this part of the assignment.

2.  Use the method for translating an E/R diagram to relations described in class and the text to produce a set of relations from your E/R design. Specify your relational schema using the notation of Section 3.1.2, and please be sure to underline key attributes.

3.  Are there any flaws in the relational database schema you get from step 2? Are there opportunities to combine relations without introducing redundancy? If so, indicate which, and if not, indicate there are none. Are there examples of non-BCNF relation schemas? If so, do you want to decompose them? For each opportunity to combine or decompose relations, decide whether or not to do so, and explain your reasoning briefly (e.g., explain what queries you expect will be typical for your database, and tell how the design you pick facilitates them). Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer. You will be working with this schema quite a bit, so it's worth spending some time to make sure you're happy with it.

Also, don't forget to save a copy of your E/R diagram for later reference as you work on subsequent parts of the PDA.

Acknowledgements:  Most of the material for the project descriptions and assignments has been taken from Professor Ullman's website.