CS101b - Assignment 6

Normal Forms and Functional Dependencies

Please keep track of the total time you spend on this assignment, and make a note of that on what you turn in. This way we can tune the assignments for future classes.

Suggested Reading

This week's assignment draws from the majority of Chapter 7. The reading sections for Lectures 19 through 22 indicate the most relevant sections: 7 - 7.8. As usual, reading the entire chapter is recommended. (Note: This chapter has a significant number of errors listed in the errata, so you should definitely refer to the errata as you read the chapter!)

Important Collab-Policy Notes!

Some of these problems draw on material in the "Practice Exercises" section of the book, for which solutions are available at the book's website. DO NOT CONSULT THESE SOLUTIONS UNTIL AFTER YOU HAVE SUBMITTED YOUR FINAL WORK. Failure to do so will be considered a violation of the Honor Code.

Also, you may use computer programs to solve the functional dependency problems, as long as they are programs that you write. You cannot use programs written by other students, or from other sources, to compute the results of the functional dependency problems.

If you write a program to solve any of the functional dependency problems, turn the program in too.

Problems

1. Practice Problem 7.6 (6 points)

   Your answer should cover all dependencies in the closure of F, but don't feel compelled to write out every single functional dependency in the closure; you can also abbreviate or aggregate the dependencies. Just be sure to clearly indicate exactly what you are abbreviating, so that it's clear that your answer includes the entire closure of F.

2. Problem 7.19 (6 points)

3. For the Union rule, the Decomposition rule, and the Pseudotransitivity rule (pg. 280 in the textbook), use Armstrong's axioms to prove that each of these rules is sound. (6 points)

4. Problem 7.25. Also note all functional dependencies not preserved by the decomposition. (3 points)

5. Problem 7.27 (3 points)

6. Problem 7.29 (3 points)

7. Given the relation schema R(A, B, C, D, E), and the set of functional dependencies F = { B → A, C → BD, CD → E, D → B }.

   • Find a candidate key for R, and prove that it is a superkey. (2 points)
   • Decompose R into a BCNF schema. Record all functional dependencies that are not preserved by the decomposition. (2 points)
   • Find if there is a second, different BCNF schema decomposition for R. If there is, write it out, along with all functional dependencies not preserved by this decomposition. (2 points)
   • Decompose R into a 3NF schema. (2 points)

8. Repeat the steps in the previous problem for the relation schema R(A, B, C, D, E), and the set of functional dependencies F = { A → C, B → AD, D → CE, BC → A }. (same points as above)

9. Given the set of functional dependencies F = { A → E, BC → D, C → A, AB → D, D → F, BC → E, D → E, BC → A }, compute a canonical cover of F. Show each step of your work:
   • If you use inference rules on a step then note which ones on that step.
   • If you assert that a particular attribute is extraneous in a dependency, show a proof that this is the case.
   Failure to record the details of each step will result in point deductions. (6 points)

10. You may have noticed from Problems 7 and 8 that a particular schema can be normalized in several different ways. What actually makes the most sense depends on the enterprise that the schema is actually modeling.

    Consider the relation R(course_num, section_num, dept, units, course_level, instructor_id, term, year, mtg_times, room, num_students). This is a schema for a database of courses and sections. The term and year are included, as is the instructor's ID, and the details of when and where each course is held. Of course, the schema is not normalized at all! (You can assume that all attributes have atomic domains, even though some of the attribute names might imply otherwise.)

    These functional dependencies also hold on R :

    • { course_num } → { dept, units, course_level }
    • { course_num, section_num, term, year } → { mtg_times, room, num_students, instructor_id }
    • { room, mtg_times, term, year } → { instructor_id, course_num, section_num }

    Here are the tasks you must perform with this schema:

    1. Find a candidate key for R. Prove that it is a superkey. (2 points)

    2. Suggest a BCNF decomposition of R. If you have multiple choices for decompositions, explain why your choice of decomposition makes the most sense given the enterprise being modeled.

       List any functional dependencies not preserved by this decomposition.

       (4 points)

    3. Suggest a 3NF decomposition of R. Again, if you have multiple choices for the decomposition, explain why your choice makes sense. (4 points)

    4. Given the above decompositions, and SQL's strengths and weaknesses as far as constraint enforcement, suggest which normalized schema would be best for actual use in a project. Briefly explain your rationale.

       You should take the scale of this database into account. Universities don't normally offer millions of courses, so this database would probably never grow beyond a few thousands or tens of thousands of records, overall. Thus, it is not essential to optimize for performance.

       (2 points)