Functional Dependency Preservation [260]

Projection of FD's over a Subschema [260]

If F is a set of FD's over R and X $\subseteq$ R then

$\pi_{X}(F) = \{V \rightarrow W \in F^{+} \vert VW \subseteq X \}$

Example: $A \rightarrow B, B \rightarrow C$

$\pi_{A}(F) = empty$

$\pi_{AB}(F) = \{A \rightarrow B\}$

$\pi_{BC}(F) = \{B \rightarrow C\}$

$\pi_{ABC}(F) = \{A \rightarrow B, B \rightarrow C\}$

A decomposition (R1..Rk) preserves F iff $\cup \pi_{Ri}(F)$ is a cover for F.

Design: Preservation [261]

R = (CITY,ZIP,ST)

F = { $CS \rightarrow Z, Z \rightarrow C$}

D = (CZ,SZ), Keys: CS,SZ

D has a lossless join wrt F

C	Z	S
c	z	s1
c1	z	s

$\Rightarrow$

C	Z	S
c	z	s1
c	z	s

However, D does not preserve F:

$\pi_{CZ}(F) = \{Z \rightarrow C\}$

$\pi_{SZ}(F) =$empty

$CS \rightarrow Z \not \in (\pi_{CZ}(F) \cup \pi_{SZ}(F))^{+}$

Design: Preservation [262]

However, the 3NF decomposition (CSZ,ZC) has the keys already in it so it preserves dependencies and has lossless join.

The CSZ adds a completely distinguished row to the tableau so it has a lossless join.

Dependency Preservation:

$\pi_{CSZ}(F) = \{CS \rightarrow Z, Z \rightarrow C\}$

$\pi_{ZC}(F) = \{Z \rightarrow C\}$

the union trivially covers F

Design: Preservation [263]

A decomposition can preserve F but not have a lossless join wrt F:

R = ABCD, D = (AB,CD), F = { $A \rightarrow B, C \rightarrow D$}

Key: AC

D preserves F trivially

Lossless Join fails:

A	B	C	D
a	b	c1	d1
a1	b1	c	d

There is no `glue' to put back R.

If we add the key AC to the decomposition

(AB,CD,AC) then its a 3NF decomposition which

1. preserves the FD's obviously

2. Lossless Join

A	B	C	D
a	b	c1	d1
a1	b1	c	d
a	b2	c	d2

$\Rightarrow$

A	B	C	D
a	b	c1	d1
a1	b1	c	d
a	b	c	d

Test for Preservation of Dependencies [264]

Input: A decomposition D = (R1..Rk) of relation R with FD's F.

Output: YES iff D preserves F

Method:

for each $X \rightarrow Y$ in F do {

Z := X

while (changes occur in $Z$) and ( $Y \not \subset Z$) do

/* compute the closure of projections */

for i := 1 to k do $Z := Z \cup ((Z \cap Ri)^{+} \cap Ri)$

if $Y \not \subset Z$ output NO and stop

}

output YES

Algorithm Example [265]

R = (ABCD), D = (AB,BC,CD)

$F = \{A \rightarrow B, B \rightarrow C, C \rightarrow D, D \rightarrow A\}$

Does D preserve F? Obviously, D preserves the first three.

Does D preserve $D \rightarrow A$?

$Z := \{D\}$

$Z := \{D\} \cup ((\{D\} \cap \{CD\})^{+} \cap \{CD\}) = \{CD\}$

$Z := \{CD\} \cup ((\{CD\} \cap \{BC\})^{+} \cap \{BC\}) = \{BCD\}$

$Z := \{BCD\} \cup ((\{BCD\} \cap \{AB\})^{+} \cap \{AB\}) = \{ABCD\}$

$A \subseteq \{ABCD\}$ STOP and output YES