Functional Dependencies [239]

Let R be a relation schema. A functional dependency over R is an expression X $\rightarrow$ Y where X $\subseteq$ R, Y $\subseteq$ R. A state d(R) of R satisfies an FD X $\rightarrow$ Y iff whenever two tuples in d(R) agree on X, they also agree on Y.

S# $\rightarrow$ SCITY

A database with schema R satisfies X $\rightarrow$ Y iff every valid state of the database satisfies X $\rightarrow$ Y.

If A $\rightarrow$ B and B $\rightarrow$ C hold in a relation, then A $\rightarrow$ C also holds (they logically imply it).

Implies A set of FD's F over R implies an FD X $\rightarrow$ Y over R iff each relation d(R) which satisfies F must also satisfy X $\rightarrow Y$.

$F \models X \rightarrow Y$

Closure of F = $F^{+} = \{X \rightarrow Y \vert F \models X \rightarrow Y \}$

Design: Example of Closure [240]

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

$F^{+} =$

$A \rightarrow B,B \rightarrow C, A \rightarrow C, AB \rightarrow B$

$AC \rightarrow B, ABC \rightarrow B, AB \rightarrow C, BC \rightarrow C$

$ABC \rightarrow C,AC \rightarrow C, A \rightarrow AB, A \rightarrow AC$

etc.

Trivial FD's always hold: $A \rightarrow A, AB \rightarrow B$

Design: Armstrong's Axioms [241]

1. Reflexivity. If $X \subseteq Y \subseteq R$ then $Y \rightarrow X$ holds trivially.

2. Augmentation. If $X \rightarrow Y$ holds and $Z \subseteq R$ then $XZ \rightarrow YZ$ holds.

3. Transitivity. If $X \rightarrow Y$ holds and $Y \rightarrow Z$ holds then $X \rightarrow Z$ holds.

Design: Closure of a Set of Attributes [242]

Let $X \subseteq R$, then $X^{+}$ wrt a set of FD's F =

{ $A \vert X \rightarrow A$ can be inferred from F}

Example: R = ABC, $F = \{A \rightarrow B, B \rightarrow C\}$

$A^{+} = \{ABC\}$

$B^{+} = \{BC\}$

$C^{+} = \{C\}$

$(AB)^{+} = \{ABC\}$

Example: R = ABCDEF, $F = \{A \rightarrow C, BC \rightarrow D, AD \rightarrow E\}$

X = AB

$X^{0} = AB$

$X^{1} = ABC$

$X^{2} = ABCD$

$X^{3} = ABCDE$

$X^{4} = X^{3}$ saturates

Must always halt since R is finite.

$X^{+} = ABCDE$

Design: Keys [243]

Key - If R is a relation schema with attributes A1..An and FD's F, and X is a subset of A1..An, then X is a key of R if

1. $X \rightarrow A1..An$ is in $F^{+}$

2. for no proper subset $Y \subset X$ is $Y \rightarrow A1..An$ in $F^{+}$

First Example: A is a key for ABC

Second Example: AB is not a key for ABCDEF, but ABF is a key

Suppliers: S#P# is a key

If more than one candidate key, designate one as primary and the others as alternate keys. A superkey is any superset of a key. A prime is any attribute which is a member of a key.

Example: R(CITY,ST,ZIP) with FD's F:

$CITY ST \rightarrow ZIP$

$ZIP \rightarrow CITY$

$(CS)^{+} = CSZ$ so CITY ST is a key.

$(SZ)^{+} = CSZ$ so ST ZIP is a key.

Decomposing Relation Schemas [244]

Let R be a relation schema. A decomposition of R is a set {R1..Rk} of relation schemas such that

1. $R_{i} \subseteq R$

2. $R = \cup R_{i}$

Example of {S,P,SP} is a decomposition of BAD.

Conditions for a `reasonable' decomposition

• do S,P,SP contain the same data as BAD?

  $BAD = \pi_{S}(BAD) \Join \pi_{P}(BAD) \Join \pi_{SP}(BAD)$

  This is a lossless join.

• can the dependencies for BAD be enforced by `local' dependencies on S, P, SP?

  Let F be the set of FD's for BAD and

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

  Is $\pi_{S}(F) \cup \pi_{P}(F) \cup \pi_{SP}(F)$ equivalent to F (is it a cover)?

Lossless Join [245]

Project and Join are approximate inverses

A	B
a1	b1
a2	b2
a3	b3

$\Join$

B	C
b1	c1
b2	c2
b2	c3

=

A	B	C
a1	b1	c1
a2	b2	c2
a2	b2	c3

Does $\pi_{AB}$ yield the first relation?

$\pi_{AB}(r \Join s) \subseteq r$

r

A	B	C
a1	b1	c1
a2	b1	c2

$\pi_{AB}(r) \Join \pi_{BC}(r)$

A	B	C
a1	b1	c1
a1	b1	c2
a2	b1	c1
a2	b1	c2

$\pi_{AB}(r) \Join \pi_{BC}(r) \supseteq r$

If EQUALITY then r has lossless join wrt AB,BC.

Design: Lossless Join [246]

Let R be a relation schema and F a set of dependencies over R. A decomposition D={R1..Rk} of R has lossless join wrt D iff, for every relation r(R) satisfying F,

$r = \pi_{R1}(r) \Join \pi_{R2}(r) \Join .. \Join \pi_{Rk}(r)$

In general $r \subseteq$ the above.

Example: Shipments

S	P	Q	N	C	X	Y
S1	P1	30	s	l	n	l
S1	P2	20	s	l	b	p
S1	P3	40	s	l	s	r
S2	P1	30	j	p	n	l
S2	P2	40	j	p	b	p
S2	P3	20	j	p	s	r

Decomposition D = {SNC,PXY,SPQ}

Design: Lossless Join [247]

$\pi_{SNC}$

S	N	C
S1	s	l
S2	j	p

$\pi_{PXY}$

P	X	Y
P1	n	l
P2	b	p
P3	s	r

$\pi_{SPQ}$

S	P	Q
S1	P1	30
S1	P2	20
S1	P3	40
S2	P1	30
S2	P2	40
S2	P3	20

r = $\pi_{SNC}(r) \Join \pi_{PXY}(r) \Join \pi_{SPQ}(r)$

What if you always decomposed by every attribute?

• redundancy?
• preservation of FD's?
• lossless join?

Test for Lossless Join: Chase FD's [248]

Input:

• relation schema R = (A1..An)
• set of FD's F
• decomposition D = (R1..Rk)

Output: YES if the decomposition has the lossless join property.

Method:

• construct a tableau with A1..An
• add a row for each element R1..Rk
• use a distinguished symbol if Ai is in Rj
• else use non-distinguished symbol
• use FD's to force non-d's to dist.
• halt with YES if entire row is dist.
• halt with NO if no more changes

Lossless Join: Example [249]

Supplier decomposition has lossless join

R = SNCPXYQ, D = (SNC,PXY,SPQ)

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

Key = SP since $(SP)^+ = SNCPXYQ$

S	P	Q	N	C	X	Y
s	p1	q1	n	c	x1	y1
s1	p	q2	n1	c1	x	y
s	p	q	n2	c2	x2	y2

Use: $S \rightarrow NC, P \rightarrow XY$

S	P	Q	N	C	X	Y
s	p1	q1	n	c	x1	y1
s1	p	q2	n1	c1	x	y
s	p	q	n	c	x	y

The last row is all distinguised. Output YES.

This row is the `glue' that can put together the original.

Lossless Join: Example [250]

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

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

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

A	B	C	D	E
a	b	c	d1	e1
a1	b	c	d	e2
a	b1	c1	d	e

Use $BC \rightarrow D, AD \rightarrow E$

A	B	C	D	E
a	b	c	d	e
a1	b	c	d	e2
a	b1	c1	d	e

Row 1 is all distinguished, output YES.