HW4 Answer Key
1. Exercise 7.2: List all functional dependencies satisfied by the relation of Figure 7.18.
Answer:
Non-trivial functional dependencies:
A -> B
C -> B
2. Exrercise 7.6: Compute the closure of the following set F of functional dependencies for relation schema R = {A, B, C, D, E}.
A -> BC
CD -> E
B -> D
E -> A
List the candidate keys for R.
Answer:
A -> BC, B -> D so A -> D so A -> DC -> E
therefore A -> ABCDE
E -> A, A -> ABCDE, so E -> ABCDE
CD -> E, so CD -> ABCDE
B -> D, BC -> CD, so BC -> ABCDE
Attribute closure:
A -> ABCDE
B -> BD
C -> C
D -> D
E -> ABCDE
AB -> ABCDE
AC -> ABCDE
AD -> ABCDE
AE -> ABCDE
BC -> ABCDE
BD -> BD
BE -> ABCDE
CD -> ABCDE
CE -> ABCDE
DE -> ABCDE
ABC -> ABCDE
ABD -> ABCDE
ABE -> ABCDE
ACD -> ABCDE
ACE -> ABCDE
ADE -> ABCDE
BCD -> ABCDE
BDE -> ABCDE
CDE -> ABCDE
ABCD -> ABCDE
ABCE -> ABCDE
ABDE -> ABCDE
ACDE -> ABCDE
BCDE -> ABCDE
The candidate keys are A, E, CD, and BC
Any combination of attributes that includes those is a superkey.
3. Exercise 7.18: Why are certain functional dependencies called trivial functional dependencies?
Answer:
Because the right hand side is a subset of the left hand side. Therefore it is obvious that the right hand side is dependent on the left hand side.
More opaque book definition: An FD is trivial if it is satisfied by all instances of a relation.
4. Exercise 7.17: Explain what is meant by repetition of information and inability to represent information. Explain why each of these properties may indicate a bad relational database design.
Answer:
- Repetition of Information is a condition in a relational database where the values of one attribute are determined by the values of another attribute in the same relation, and both values are repeated throughout the relation. This is a bad relational database design because it increases the storage required for the relation and it makes updating the relation more difficult.
- Inability to represent information is a condition where a relationship exists amoung only a proper subset of the attributes in a relation. This is bad relational database design because all the unrelated attributes must be filled with null values otherwise a tuple without the unrelated information cannot be inserted into the relation.
5. Consider a relation R(A,B,C,D,E) with the following dependencies:
{AB-> C, CD -> E, DE -> B}
Is AB a candidate key of this relation? If not, is ABD? Explain your answer.
No. The closure of AB does not give you all of the attributes of the relation.
If not, is ABD? Explain your answer.
A -> A
B -> B
C -> C
D -> D
E -> E
AB -> ABC
AC -> AC
AD -> AD
AE -> AE
BC -> BC
BD -> BD
BE -> BE
CD -> BCDE
CE -> CE
DE -> BDE
ABD -> ABCDE
Yes, ABD is a candidate key. No subset of its attributes is a key.
6. Consider a relation with schema R(A,B,C,D) and FDs {AB -> C, C -> D, D -> A}.
a. What are some of the nontrivial FDs that can be inferred from the given FDs?
Some examples:
C -> ACD
D -> AD
AB -> ABCD
AC -> ACD
BC -> ABCD
BD -> ABCD
CD -> ACD
ABC -> ABCD
ABD -> ABCD
BCD -> ABCD
b. What are all candidate keys of R?
By calculating an attribute closure we can see the candidate keys are:
AB, BC, and BD
Attribute closure:
A -> A
B -> B
C -> ACD
D -> AD
AB -> ABCD
AC -> ACD
AD -> AD
BC -> ABCD
BD -> ABCD
CD -> ACD
ABC -> ABCD
ABD -> ABCD
ACD -> ACD
BCD -> ABCD
c. Indicate all BCNF violations for R.
C->D and D->A
d. Decompose the relations into collections of relations that are in BCNF.
(ABCD)
| \
C->D |
| |
(CD) (ABC)
| \
C->A |
| |
(CA) (AC)
So you get: R1(CD), R2(AC), and R3(BC)
If we split on D->A
(ABCD)
| \
D->A |
| |
(AD) (BCD)
| \
C->D |
| |
(CD) (BC)
So you get: R1(AD), R2(CD), and R3(BC)
e. Indicate which dependencies if any are not preserved by the BCNF decomposition.
If we start to decompose on C->D then D->A and AB->C
If we start to decompose on D->A then AB->C
7. Consider a relation R(A,B,C,D,E) with FDs {AB -> C, DE ->C, and B -> D}
a. Indicate all BCNF violations for R.
Logically, since C and D are the only attributes that can be determined via other attributes, we can deduce that the keys will contain the other attributes, thus we prefrom a smaller attribute closure:
ABE -> ABCDE
ABCD -> ABCDE
ABCE -> ABCDE
Candidate keys: ABE
Violations:
B->D, AB->C, DE->C
b. Decompose the relations into collections of relations that are in BCNF.
(ABCDE)
Break down (ABCDE) with AB->C
(ABC) and (ABDE)
Break down (ABDE) with B->D
(BD) And (ABE)
So we get R1(ABC), R2(BD) and R3(ABE)
c. Indicate which dependencies if any are not preserved by the BCNF decomposition.
DE->C
8. Prove or disprove the following inference rules for functional dependencies.
Note: Read "|=" as implies
a. {X->Y, Z->W} |= XZ ->YW
XZ -> XZ
XZ -> XW (Z -> W)
XZ -> W (decomposition rule)
XZ -> XZ
XZ -> YZ (X -> Y)
XZ -> Y (decomposition rule)
XZ -> YW (union rule)
b. {X->Y, XY -> Z} |= X -> Z
Y -> Z (pseudotransitivity rule)
X -> Z (transitivity)
c. {XY -> Z, Y->W} |= XW->Z
W -> W
X -> X
Y -> YW
Z -> Z
WX -> WX
WY -> WY
WZ -> WZ
XY -> WXYZ
XZ -> XZ
YZ -> WYZ
Therefore WX -> Z is not true
You can also find the attribute closure for WX and show that closure set does not contain Z.
Use Armstrong's Axioms or Attribute closure to prove or disprove.
9. Consider a relation R(A,B,C,D) with FDs {A ->B, B ->C, C-> D}
a. Indicate all BCNF violations for R.
Logically, since B, C, and D are the only attributes that can be determined via other attributes, we can deduce that the keys will contain the other attributes, thus we prefrom a smaller attribute closure:
A -> ABCD
AB -> ABCD
AC -> ABCD
AD -> ABCD
ABC -> ABCD
ABD -> ABCD
ACD -> ABCD
Violations:
B->C, C->D
b. Decompose the relations into collections of relations that are in BCNF.
Breakdown based on B->C
(BC), (ABD)
Breakdown based on B->D
(AB), (BD)
So we get R1(BC), R2(AB), R3(BD)
c. Indicate which dependencies if any are not preserved by the BCNF decomposition.
C->D
10. For the same example relation R with the two tuples as in the notes above, decompose it as R1(A,B) and R2(A,C). Try and merge them back using natural join and see if the resulting relation is the same as R. Do you think this decomposition is a lossless join decomposition?
Suppose R contains two tuples (1, 2, 3) and (2, 2, 4)
R1 contains (1, 2), (2, 2)
R2 contains (1, 3), (2, 4)
Natural Join on A and we have:
(1, 2, 3), (2, 2, 4)
As you can see, we have gotten the original relations back.
Yes, it is lossless because the dependency A->B is not broken.