Covers [251]

Let F and G be sets of dependencies.

F and G are equivalent if $F^{+} = G^{+}$.

F = { $EC \rightarrow D, AB \rightarrow E,E \rightarrow AB$}

G = { $ABC \rightarrow D, AB \rightarrow E, E \rightarrow AB$}

1. $F^{+} \subseteq G^{+}$

$E \rightarrow AB$

$EC \rightarrow ABC$

$ABC \rightarrow D$

$EC \rightarrow D$

2. $G^{+} \subseteq F^{+}$

$AB \rightarrow E$

$ABC \rightarrow EC$

$EC \rightarrow D$

$ABC \rightarrow D$

Design: Minimal Cover [252]

If F is equivalent to G, then F covers G and G covers F.

A set F is a minimal cover if

• every rhs is a single attribute
• removal of any FD does NOT yield an equivalent to F
• for no $X \rightarrow A$ is there a proper subset of X which can be used instead and still have an equivalent to F

Design: Minimal Cover Example [253]

$AB \rightarrow C, C \rightarrow A, BC \rightarrow D$

$ACD \rightarrow B, D \rightarrow EG, BE \rightarrow C$

$CG \rightarrow BD, CE \rightarrow AG$

Step 1, make every rhs a single attribute.

$AB \rightarrow C, C \rightarrow A, BC \rightarrow D$

$ACD \rightarrow B, D \rightarrow E, D \rightarrow G, BE \rightarrow C$

$CG \rightarrow B, CG \rightarrow D, CE \rightarrow A, CE \rightarrow G$

Step 2, remove redundant FD's.

$CE \rightarrow A$ is redundant since $C \rightarrow A$

$CG \rightarrow B$ is redundant since if you remove it,

$(CG)^{+} = CGDABE$ and B is still included.

Can not also remove $ACD \rightarrow B$ since

$(ACD)^{+} = ACDEG$ which does not include B.

(But start over and remove $CE \rightarrow A, CG \rightarrow D, ACD \rightarrow B$)

Step 3, $ACD \rightarrow B$ can be replaced by $CD \rightarrow B$ since $C \rightarrow A$ is given.

Design: Third Normal Form [254]

If whenever $X \rightarrow A$ holds in R and A is not in X, then either X is a superkey for R, or A is prime.

A decomposition of R into Third Normal Form implies that each of its subschemas is in Third Normal Form.

Example: R = Shipments = (SPQNCXY)

F = { $S \rightarrow NC,P \rightarrow XY,SP \rightarrow Q$}

Key: SP

R is not in 3NF since first and second FD do not have superkeys on lhs and the rhs is not prime.

The decomposition (SNC,PXY,SPQ) is in 3NF since the keys are S,P,SP.

Design: Third Normal Form Example [255]

R = ABCDE, D = (ABC,BCD,ADE)

F = { $AB \rightarrow C,BC \rightarrow D, AD \rightarrow E$}

Key = AB since $(AB)^{+} = ABCDE$

R is not in 3NF since the second,third FD's violate.

But the decomposition D is in 3NF since the keys are AB,BC,AD.

Both of these examples preserve the original FD's but on a local level (to be proven later).

Both of these examples have been shown to have lossless join. That is because the original key is still a member of one of the decompositions.