CS 235: Assignment 2

PDA: Part 2

(a)

Please attach a copy of your E/R diagram from part one. 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, underlining key attributes in your relations.

(c)

For each relation in your schema, specify a set of completely nontrivial functional dependencies that hold for this relation based on your real-world scenario. Some of your relations may have no nontrivial functional dependencies.

(d)

For each relation in your schema, check if it is in Boyce-Codd Normal Form (BCNF). If not, decompose the relation into BCNF. Some PDA schemas may not have any BCNF violations

(e)

Are there any nontrivial multivalued dependencies, which are not also functional, that hold on any of the relations in your schema? If so, decompose the relations into 4NF.

(f)

Can you combine any relations in your schema without introducing redundancy (i.e., without creating BCNF or 4NF violations)? If so, combine them.

(g)

At this point, if there is something you'd like to change in your database schema (such as an attribute name, a relation structure, etc.), you can do that. Make sure that you don't reintroduce any redundancies.

Problem Set

1. Problem 3.2.2 from the textbook.
   
   
2. Problem 3.4.6 from the textbook.
   
   
3. Problem 3.5.10 from the textbook.
   
   
4. Problem 3.6.4 from the textbook.
   
   
5. Problem 3.7.8 from the textbook.