This preview shows pages 1–5. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: 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 Exercise 1 (Concept) [20 points] 1. Consider the relation R(A,B,C) shown in Table1 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 Table1 (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, where A is part of some key implies that A is a key. By the definition of an FD, if X is known, then A is known. This means that if X is known, we know a key for the relation, so X must be a superkey. This satisfies all of the properties of BCNF. 5. Given a functional dependency X Z (where X, Z R). (a) How can we check whether X Z F+ without having to compute F+? [2 points] (b) Write down an algorithm that implements the method you propose. [7 points] Answer: By calculating the closure of the attribute X (i.e., X + ) w.r.t F. If Y X + holds then X Y F + holds. Exercise 2 (Functional Dependencies) [20 points] 1. Consider the relation instance given in Table2 below....
View
Full
Document
This note was uploaded on 09/10/2008 for the course CIS 4301 taught by Professor Schneider during the Spring '08 term at University of Florida.
 Spring '08
 Schneider
 Databases

Click to edit the document details