CS 4604: Project Assignment 5

Released on Apr 10. Hardcopy due at the start of the class on Apr 17, 2013.

If we gave you comments, corrections, or suggestions about Assignment 4 of your project, address those modifications first before you proceed with Step 5.

1. (25 points) Use the mechanical process to convert from your E/R diagram to relations, subject to the deviations in item 2. Do not normalize yet. Strictly follow the procedures taught in class and described in the textbook. You are allowed to combine relations (e.g., for an entity set and a many-to-one relationship from the entity set to another one). If you do so, you must write down which parts of the E/R diagram you combined for each relation and provide your rationale for doing so. For the ISA hierarchies in your E/R diagram (you surely have inheritance in your diagram, don't you?), use the technique described in Chapter 4.6.1 of your textbook for conversion. Please do not use the technique of Chapter 4.6.3 (even though it is easier). For your solution, submit a list of relation schemas (i.e., the names of the relations and what their attributes are, and underline primary keys).


2. (15 points) In your conversion, deviate from the procedures discussed in class as described below. These deviations deliberately create a bad relational design so that you can use normalisation techniques to improve the design. The purpose of these deviations is to demonstrate that even if you make a mistake in the conversion from E/R diagrams to relations, normalisation can save the day! Please read them carefully.
   1. Pick one many-many relation (let us call it S) in your E/R diagram between entity sets G and H. Do not create a relation for S. Merge the attributes you would normally have created in the relation for S into the relation for G or for H.
   2. Pick one many-one relationship (let us call it R) in your E/R diagram. Do not create a relation for R. Suppose R is many-one from an entity set E to an entity set F. In class, we learnt how to merge the attributes for R (and for F) into the relation for E. In this step, you should merge the attributes of the relation you would normally have created for R into the relation for F.
      
   3. Describe in words the types of problems you have introduced by deviating as instructed. Explain in terms of the attributes of the relations you have created.
   Include the relations so created in the set of relations that result from the previous step. Be sure to mark which relations you applied the deviations to with text such as "Relation for many-one relationship R resulting from deviation."
   If you do not have a many-one relationship, pick two many-many relationships to apply the deviation to. Similarily, if you do not have a many-many relationship, pick two many-one relationship to apply the deviation to. If all or all but one relationship are one-one, then your E/R diagram has major problems, which we should have identified in the previous step.


3. (15 points) List all completely non-trivial FDs and MDs that apply to your design. Only list FDs that have one attribute on the right hand side. It is enough to list only the FDs in a minimal basis. Of course, you have to perform these steps separately for each relation.


4. (20 points) For each relation, write down if it is in 3NF, BCNF, or 4NF. 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.


5. (10 points) At this stage, if your relations are not in 4NF, normalise them into 4NF by applying the 4NF decomposition algorithm.


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 after solving the previous step, i.e., after bringing all relations to 4NF. Do you notice anything interesting? Describe the differences in terms of relations that are different, relations (from Assignment 2) that are not in 3NF, BCNF, or 4NF, etc. If some relation from Assignment 2 is not 3NF, BCNF, or 4NF, identify the (faulty) design choice you made that caused the relation not to be normalised.

What you should turn in:
Paper copies of these answers. Attach a copy of your E/R diagram (modified and corrected based on our comments, if required) to your answer, so that we can verify that you did the conversion correctly. Identify your group by your project title and the team members.