{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

HW5 Solution

# HW5 Solution - Information and Database Management Systems...

This preview shows pages 1–5. Sign up to view the full content.

Information and Database Management Systems I (CIS 4301) (Spring 2008) Instructor: Dr. Markus Schneider TA: Ganesh Viswanathan Wenjie Yuan Homework 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

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Exercise 1 (Concept) [20 points] 1. Consider the relation R(A,B,C) shown in Table-1 below: A B C a 1 b 1 c 1 a 1 b 1 c 2 a 2 b 1 c 1 a 2 b 1 c 3 Table-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 c 3 to c 2. 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,