Information and Database Management Systems I (CIS 4301) (Spring 2008) Instructor: Dr. Markus Schneider TA: Ganesh Viswanathan Wenjie YuanHomework 5 Solution Key Name: UFID: Email Address: Pledge (Must be signed according to UF Honor Code) On my honor, I have neither given nor received unauthorized aid in doing this assignment. _______________________________________________Signature For scoring use only: Maximum Received Exercise 1 20 Exercise 2 20 Exercise 3 20 Exercise 4 20 Exercise 5 20 Total 100
has intentionally blurred sections.
Sign up to view the full version.
Exercise 1 (Concept) [20 points] 1. Consider the relation R(A,B,C) shown in Table-1 below: ABCa1b1c1a1b1c2a2b1c1a2b1c3Table-1 (a) List all functional dependencies that R satisfies. [2 points] (b) Assume that the value of attribute C of the last record in the relation is changed from c3 to c2.Now list all the functional dependencies that R satisfies. [1 points] Answer:(a) The following FDs hold over R: C→B, A→B, AC→B (b) The FD set remains the same. 2. Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB, under which R is in 2NF but not in 3NF. [3 points] Answer:Consider the set of FD: AB →CD and C →D. AB is obviously a key for this relation since AB →CD implies AB →ABCD. It is a primary key since there are no smaller subsets of keys that hold over R(A,B,C,D). The FD: C →D violates 3NF but not 2NF since: D ∈C is false; that is, it is not a trivial FD C is not a superkey D is not part of some key for R 3. Consider the relation schema R(A,B,C), which has the FD B →C. If A is a candidate key for R, is it possible for R to be in BCNF? If yes, under what conditions? If not, explain why not. [2 points] Answer:The only way R could be in BCNF is if B includes a key, i.e. B is a key for R. 4. Prove that, if R is in 3NF and every key is simple, then R is in BCNF. [3 points] Answer:Since every key is simple, then we know that for any FD that satisfies X →A,