CS125 Project, Part 3

Due Friday, October 8, 2004

For this assignment, everyone needs their PostgreSQL account. Your account name will be your Westmont email address.  Your password will be the final six (6) digits of your student ID number. 

Project 3 Deliverable

  1. Please attach a copy of your E/R schema from the Part 1 of your project. If you have modified your design because of my feedback (or any other reason), please hand in the modified design instead; the new design will not be graded but will be compared with your relational design.
  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.
  4. Login to your PostgreSQL database using your favorite SQL client.  Try some simple commands, such as help or table (relation) creation. It is important that people try logging in as soon as possible.

Don't forget to save a copy of your work for reference as you do Part 4 of the Database Project.

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