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

**sections.**

*blurred***to view the full version.**

*Sign up*
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,