CS 4604 Project Assignment 5

Assigned on Thursday, November 06, 2008. Hardcopy due on Thursday, November 13, 2008 at beginning of class. Assignments not handed in-class will be subject to a deduction (10% or higher).

Consider the relation schema that you arrived at in Project Assignment 4. Keep in mind that these you obtained these relations by following the deviations from the normal E/R-to-relations procedure detailed in Project Assignment 4.
  1. (0 points) Write down the relations in this schema. Make sure you address any corrections we suggested in the previous assignment.
  2. (15 points) List all completely non-trivial FDs and MDs that apply to your design. If you had inheritance in your E/R diagram, use any one (but not both) of the two sets of relations you created in part 3 of Project Assignment 4.
  3. (30 points) Are all the relations in your chosen schema in 3NF? Are they in BCNF? Explain each answer. If any of your relations are not in BCNF, normalise them to BCNF. If you choose to normalise your relations only till they are in 3NF, explain your reasons (e.g., the amount of redundancy introduced is limited or some other valid reason). Notice that if you decide to go ahead with normalization, you will have to list the violating FDs for each of your relations and explain why you think they violate 3NF and/or BCNF.
  4. (25 points) At this stage, if your relations are not in 4NF, normalise them into 4NF.
  5. (15 points) Write down the relations you would have obtained if you had converted the E/R diagram from Project Assignment 3 using the method described in class. Keep in mind that this set of relations is different from what you submitted in Assigment 4, since Assigment 4 required you to deviate from the method discussed in class. Compare this set of relations with the relations obtained in the previous part. Do you notice anything interesting?
  6. (15 points) Write down the relations you created in Project Assignment 2. Compare this set of relations with the relations obtained in this assignment. Do you notice anything interesting?
FAQ: The third question seems to imply that we can "get away without doing normalization". Is that true? Why do you have the clause "if you decide to go ahead with normalization"?

Answer: There is no escaping from normalization! We haven't spent so much time on it without requiring you to do it for your project! First realize that you have to bring your relations into 3NF, come what may. The reason we have that clause is that there are some exceptional cases where normalization to BCNF can be skipped and you can actually leave the relation in 3NF. However, such cases are far and few in between. You must have a really sound reason for not doing BCNF normalization (read the textbook for examples).