Download Report

Database Management Systems (CIS 4301)
(Fall 2014)
Instructor: Dr. Markus Schneider
TAs: Babak Alipour
Clay Brooks
Homework 4 Solutions
Name
UF Id
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:
Exercise 1
Exercise 2
Exercise 3
Total
Maximum
30
35
35
100
Received
Exercise 1 (Knowledge Questions)
[30 points]
1. Explain functional dependencies. What is “functional” about functional dependencies? (4
points)
Solution: A1, A2, …An -> B is a functional dependency because in principle there is a
function that takes a list of values, one for each of the attributes A1, A2, …An and
produces a unique value (or no value at all) for B. This function is only “computed” by
look-up in the relation. The functional dependency is a property of the semantics or
meaning of the attributes.
2. List the six inference rules and a brief description of each? (4 points)
reflexivity rule: Let B ⊆ A. Then always A → B (special case: A → A) holds.
augmentation rule: If A → B holds, then also A ∪ C → B ∪ C holds.
transitivity rule: If A → B and B → C holds, then also A → C holds.
union rule: If A → B and A → C holds, then also A → B ∪ C holds.
decomposition rule: If A → B ∪ C holds, then also A → B and A → C holds.
pseudotransitivity rule: If A → B and B ∪ C → D holds, then also A ∪ C → D
holds.
3. What is a closure and a canonical cover of a given set F of FDs? (4 points)
Answer: refer to class notes.
4. Please give the definition of the following normal forms (8 points):
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Answer: refer to class notes.
5. Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB under which
R is in 1NF but not in 2NF. (5 points)
Answer: Consider the set of FD: AB->CD and B->C. AB is obviously a superkey 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: B->C violates 2NF since:
1) C ∈ B is false, that is, it is not a trivial FD
2) B is not a superkey
3) C is not part of some key for R
4) B is a proper subset of the key AB (transitive dependency)
6. 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. (5 points)
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:
1)D ∈ C is false; that is, it is not a trivial FD
2)C is not a superkey
3)D is not part of some key for R
Exercise 2 (Functional Dependencies)
[35 points]
1. Let Q be the set of functional dependencies listed below. Show that and exactly how A →
FG is implied. [10 points]
Q = {AB→CD, B→DE, C→F, E→G, A→B}
Answer:
A→B
A→AB
AB→AC
A→AC
B→DE
A→DE
A→ACD
ACD→CDE
A→CDE
A→CE
C→F
CE→FE
E→G
FE→FG
CE→FG
A→FG
given
augmentation
given
transitivity
given
transitivity
augmentation
augmentation, twice
transitivity
trivial dependency
given
augmentation
given
augmentation
transitivity
transitivity
2. 2. Given R = ABCDEGH and F = {A → DE, C → ADH, BH → GE, ABH →C, BGH → C}.
Compute X+ for sets of attributes X such that X appears on the left hand side of a FD in F
[9 points]
Solution:
– A+ = {ADE}
– C+ = {ACDEH}
– BH+ = {BCEGH}
– ABH+ = {ABCDEGH}
– BGH+ = {ABCDEGH}
3. Consider the relation schema R(A,B,C,D) with FD’s A → C and B→D. Prove whether or
not {A,B} is a key for R using Armstrong’s Axioms.. If it is a key, what type of key is it?
[8 points]
A→C implies AB→ABC
B→D implies ABC→ABCD
AB→ABCD
AB is a super key for relation R. AB is also a candidate key and the only candidate key of R,
and therefore the only possible primary key of R, since A+  ABCD and B+  ABCD.
4. Consider a schema R={S,T,V,C,P,D} and F= {S → T, V → SC, SD → P}. Find all candidate
keys of R and clearly describe the way how you find them. [8 points]
Solution
V and D do not appear on the right side, they must be in the key.
VD+ = {STVCPD}. So VD is the only key.
Proof for claim above
V → SC # given
VD → SCD # aug. (1)
VD → VSCD # aug. (2)
S → T # given
SC → TC # aug. (4)
V → TC # (1), (5)
VD → TCD # (6) aug.
SD → P # given
VD → SD # decomp. (2)
VD → P # trans. (8), (9)
VD → STVCPD # union (3),(7),(10)
Alternatively, one can apply the algorithm to compute the closure VD+.
Exercise 3 (Normal Forms)
[35 points]
1. R = (A, B, C), F = (A→B, B→C). Prove that this relation is not in BCNF form. [5 points]
A→B,
A→R (A is a key)
A→C,
B→C,
B-/-> R (B is not a superkey)
Therefore, R is not in BCNF.
2. Consider the following collection of relations and dependencies. For each relation, please (a)
determine the candidate keys, and (b) if a relation is not in BCNF then decompose it into a
collection of BCNF relations.
a. R(A,C,B,D,E), A → B, C→ D
[5 points]
Compute the keys for R1. The attributes A, C, E do not appear on right hand side of any
functional dependency therefore they must be part of a key. So we start from {A, C, E}
and find out that this set can determine all features. So the key is {A, C, E}
We have dependencies A → B and C → D so the table is not BCNF. Applying the BCNF
decomposition algorithm, the non-BCNF dependency is A → B, therefore create two
relations (A, C, D, E) and (A, B). The first relation is still not in BCNF since we have a
non-BCNF dependency C→ D. Therefore decompose further into (A, C, E) and (C, D).
Now all relations are in BCNF and the final BCNF scheme is (A, C, E), (C, D), (A, B).
b. R(A,B,C,D,E) with functional dependencies D → B, CE → A. [5 points]
The key for R5 is {CDE} since we have {CDE}+ = {ABCDE}. R5 is not in BCNF and
not in 3NF. Applying the BCNF decomposition, we pick D → B which is not BCNF and
decompose into (D, B) and (ACDE). But (ACDE) is still not in BCNF since CE→ A is
not BCNF. Therefore decompose into (D, B), (A,C,E) and (D,C,E) which is now in
BCNF.
c. R(A,B,C,D,E) with functional dependencies A→ E, BC→ A, DE→ B. [5 points]
Since D and C do not appear on right hand side, all keys must contain C and D. The set
{C,D} by itself is not a key since {C,D}+= {C,D}. However, all three element sets
containing {C,D} are keys – i.e., {A,C,D} is a key, {B,C,D} is a key, and {C,D,E} is a
key. So all attributes are prime attributes. Therefore the schema is in 3NF. However, the
left hand side of the FDs is not a key therefore it is not in BCNF. One decomposition will
start by removing A → E to form (A,E) and (A,B,C,D). But (A,B,C,D) is not in BCNF
since BC is not the key. Therefore decompose further to get (A,E), (BCA) and (BCD).
This is in BCNF since the only dependency in (BCD) is the trivial dependency BCD →
BCD.
3. Given the relational schema R(A,B,C,D,E,F,G,H,I,J) and FDs: B → E, E → FH, BCD → G,
CD → A, A → J, I → BCDE, H → I.
A).Does the functional dependency B → J hold? If yes, prove your answer. [5 points]
B).List the candidate keys of R and describe how you determine them. [5 points]
C).Normalize R into BCNF. Make sure to underline primary key fields. [5 points]
(a) The functional dependency B → J holds as J is in the closure of B
B+ = {A,B,C,D,E,F,G,H,I,J}.
(b) The candidate keys of R: {B}, {E}, {H}, {I}.
(c) Since there are so many keys of R, there is not much normalization to be done. The only
two FDs that violate BCNF are: C,D → A and A → J. Final normalized relations are:
R1(A,J), R2(C,D,A) and R3(B,C,D,E,F,G,H,I). Note that we could choose any of the
candidate keys as the primary key.