Ch 4: Relational Database Design. 4.1 Features of Good Relational designs. Four Informal measures Semantics of the relation attributes Reducing the redundant values in tuples. Reducing the null values Disallowing the possibility of generating spurious(wrong) tuples.
t1 [X] = t2 [X] , they must also have,
t1 [Y] = t2 [Y].
In other words
Whenever two tuples of r agree on their X value, they also agree on their Y value.
bor_loan = (customer_id, loan_number, amount ).
We expect this functional dependency to hold:
but would not expect the following to hold:
SOME ADDITIONAL FD’S ARE
are sound & complete
By sound, we mean that given a set of FD on relation R, any dependency that can infer from F holds in every reln satisfies the dependencies. They do not generate incorrect FD.
By complete, we mean that using 3 FC repeatedly to a complete set of all possible dependencies that can be inferred from F.
and augmenting of CG H to inferCGI HI,
and then transitivity
result := a; while (changes to result) do for each in F do begin if result then result := result end
1. result = AG
2. result = ABCG (A C and A B)
3. result = ABCGH (CG H and CG AGBC)
4. result = ABCGHI (CG I and CG AGBCH)
(1) The right hand side of every FD in S involve just one attribute (i.e., it is a singleton set)
(2) The left hand side of every FD in S is irreducible in turn meaning that no attribute can be discarded from the determinant without changing the CLOSURE S+.
Compute an irreducible set of FD that is equivalent to this given set.
Bring answer in reducible form
(1) The step is to rewrite the FD such that each has a singleton right hand side.
We observe that the FD A B occurs twice. So one occurrence will be eliminated.
(Augmentation: if X Y then XZ YZ)
Thus C on the left hand side is redundant.
3. Next, we observe that the FD AB C can be eliminated, because again we have
By augmentation AB CB
By decomposition AB C AB B
4. Finally, the FD A C is implied by the FD A B and B C, by transitivity so it can be eliminated.
Now we have A B
This set is irreducible.