The procedure for turning in this assignment is the same as for prior assignments, and please remember the strictly enforced late policy.

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

In this second part of the project, you will produce a relational schema from the entity-relationship diagram you came up with in PDA Part 1. In the following project part, you will create an actual database from this schema using the Oracle DBMS.

(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 TA 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 the next part of this assignment.

(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.

(e) Are there any non-trivial 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.

(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.