LOGISTICS AND LATE POLICY REMINDER

• Turn-in: Please submit your project via the MOODLE  for your class. Make sure everyone's name is clearly on the project. Only the team leader for each group should post a project submission.

• Deadline: The on-time deadline for all students is in class on the due date set in MOODLE.

• Late policy: All written work is to be turned in during class on the day that it is due. Written work turned in after the deadline will be accepted but penalized 10% per day for each day it is past due. 

• Please save a copy of your final relational schema for your PDA Project (part (g)), as it will be needed for subsequent project parts.

THE PROJECT

Personal Database Application, Part 2

(a) Please attach a copy of your E/R diagram from PDA Part 1. If you would like to make changes to your original E/R diagram at this point (due to staff feedback or any other reason), you may do so. The new E/R design will not be graded but will be used as a basis for grading part (b).

(b) Using the method for translating an E/R diagram to relations, produce a set of relations for your database design. As usual, please be sure to underline key attributes in your relations.

(c) For each relation in your schema, specify a set of completely nontrivial functional dependencies for the relation. Any functional dependencies that actually hold in the real-world scenario that you're modeling should be specified, or should follow from the specified dependencies. Don't worry if you find that some of your relations have no nontrivial functional dependencies.

(d) Is each relation in your schema in Boyce-Codd Normal Form (BCNF) with respect to the functional dependencies you specified? If not, decompose the relation into smaller relations so that each relation is in BCNF. Be sure to underline key attributes in your new relations. Don't worry if you don't have any BCNF violations - many PDAs will not have any.

(e) Are there any nontrivial multivalued dependencies that hold on any of the relations in your schema? (You needn't consider MVD's that are also functional dependencies.) If so, specify the multivalued dependencies, then decompose the relations into smaller ones so that each one is in Fourth Normal Form (4NF). Be sure to underline key attributes in your new relations. Don't worry if you don't have any 4NF violations - most PDAs will not have any.

(f) Now that you've decomposed your relations as far as possible, are there any relations that could be combined without introducing redundancy (i.e., without creating BCNF or 4NF violations)? If so, combine them.

(g) Is there anything you still don't like about the schema (e.g., attribute names, relation structure, 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 now to make sure you're happy with it. And once again:

• Please save a copy of your final relational schema (part (g)), as it will be needed for subsequent project parts.