Unformatted text preview: Background: Functional Dependencies
We are always talking about a relation R,
with a xed schema set of attributes and a
varying instance set of tuples.
Conventions: A; B; : : : are attributes; : : :; Y; Z
are sets of attributes. Concatenation means
FD is X ! Y , where X and Y are sets
of attributes. Two tuples that agree in all
attributes of X must agree in all attributes
of Y .
Implication: FD X ! Y follows from F i all
relation instances that satisfy F also satisfy
X !Y. Three Ways to Reason About FD's
1. Semantic : FD X ! Y is the set of relation instances that satisfy it.
3 Say F j= X ! Y if every instance that
satis es all FD's in F also satisfy X ! Y .
3 All approaches assume there is a xed
relation scheme R to which the FD's
2. Algorithmic : Give an algorithm that tells us,
given F and X ! Y , whether F j= X ! Y .
3. Logical : Give a reasoning system that lets us
deduce an FD like X ! Y exactly when F j=
3 Deduction indicated by F ` X ! Y . Closure Test for Implication Algorithmic
Start with X + = X . Adjoin V to X + if U ! V is
in F , and U X + . At end, test if Y X + .
Proof that if Y X + , then F j= X ! Y :
Easy induction on the number of additions to
X + that X ! A for all A in X + .
Proof that if F implies X ! Y , then Y X + :
Prove the contrapositive; assume Y is not a
subset of X + and prove F does not imply
X ! Y . Construct relation R with two tuples
that agree on X + and disagree elsewhere.
1 Armstrong's Axioms Logical Approach A sound what may be deduced is correct in the j=
sense and complete what is true in the j= sense
can be deduced axiomatization of FD's.
A1: Trivial FD's or Re exivity. X ! Y always
holds if Y X .
A2: Augmentation. If X ! Y , then XZ ! Y Z for
any set of attributes Z .
A3: Transitivity. If X ! Y and Y ! Z , then
X ! Z. Deductive Proofs A series of lines." Each line is either:
1. A given statement FD in the given set F for
deductions about FD's, or
2. A statement that follows from previous lines
by applying an axiom. Example
Given fAB ! C; CD ! E g, deduce ABD ! E .
AB ! C Given
ABD ! CD A2
CD ! E Given
ABD ! E A3
Proof of Soundness Easy observations about relations. Proof of Completeness
1. Given F , show that if A is in X + , then
X ! A follows from F by AA's.
2. Show that if X ! A1 ; : : :; X ! An follow
from AA's, then so does X ! A1 An .
3. Complete the proof by observing that
X ! A1 An follows from F i all of
A1 ; : : :; An are in X + , and therefore i
X ! A1 An follows by AA's. Proof 2 Induction on i that X ! A1 Ai follows.
Basis: i = 1, given.
2 Induction: Assume X ! A1 Ai,1 .
3 X ! XA1 Ai,1 A2 by X applied to
X ! A1 Ai,1 .
3 XA1 Ai,1 ! Ai A2 by A1 Ai,1
applied to X ! Ai .
3 X ! A1 Ai A3 applied to previous
two FD's. Proof 1 Induction on the number of steps used to add A to
Basis: 0 steps. Then A is in X , and X ! A
Induction: Assume B1 Bk ! A in F used
to add A to X + .
3 By the inductive hypothesis, X ! Bj
follows from F for all 1 j k.
3 By part 2 of the theorem,
X ! B1 Bk follows from F .
3 By A3, X ! A follows. Background: Normal Forms
A key for R is a minimal set of attributes
such that X ! R note: I use R as both the
instance and schema of a relation | shame on
me. A superkey is any superset of a key.
BCNF: If X ! Y holds for R and is
nontrivial, then X is a superkey.
3NF: If X ! Y holds for R and is nontrivial,
then either X is a superkey or Y contains a
prime attribute member of some key.
Decomposition : We can decompose R into
schemas S1 ; : : :; Sn if S1 Sn = R. The
instance for Si is S R. The FD's that hold
for Si are those X ! Y such that XY Si
and X ! Y follows from the given FD's for R.
i Covers of Sets of FD's
Sets F 1 and F 2 of FD's are equivalent if each
implies the other.
3 I.e., exactly the same relation instances
3 Any set of FD's equivalent to F is a cover for
A cover is minimal if:
1. No right side has more than one attribute.
2. We cannot delete any FD from the cover and
have an equivalent set of FD's.
3. We cannot delete any attribute from any left
side and have an equivalent set of FD's. Example Relation CTHRSG represents courses, teachers,
hours, rooms, students, and grades. The FD's:
C ! T ; HR ! C ; HT ! R; HS ! R; CS ! G;
CH ! R.
We can eliminate CH ! R.
3 Proof: Using the other 5 FD's, CH + =
Having done so, we cannot eliminate any
attribute from any left side.
3 Sample proof: Suppose we tried to
eliminate T from HT ! R. We would
need that C ! T , HT ! R, HR ! C ,
HS ! R, and CS ! G imply H ! R.
But H + = H with respect to these 5
Thus the remaining ve are a minimal cover of
the original six.
Note: minimal cover need not be unique, or
even have the same number of FD's. Lossless Join The decomposition of R into S1 ; : : :; Sn has a
lossless join with respect to some constraints
on R if for any instance r of R that satis es the
constraints: S1 r . . S r = r
n Motivation: We can replace R by S and
T , knowing that the instance of R can be
recovered from the instances of S and T . Theorem A decomposition of R into S and T has a lossless
join wrt FD's F if and only if S T ! S or
S T ! T.
First note that r S r . T r always.
Assume S T ! S and show that S r . T r r
Suppose s is in S r and t is in T r,
and s and t join i.e., they agree in
S T .
3 Then t is the projection of a tuple t0 of r
that agrees with s on S .
3 So t0 agrees with t on T and with s on S ,
so t0 is s . t; i.e., s . t is in r.
Similarly if S T ! T .
3 Now assume neither FD follows from F .
3 Then there is an instance r consisting of
two tuples that agree on S T + and
disagree elsewhere. This instance satis es
3 Neither S nor T is contained in S T +
or else one of the FD's in question would
3 Thus, r projected and rejoined yields four
distinct tuples, and cannot be r. Theorem We can always decompose a relation R with FD's
F into BCNF relations with a lossless join. Proof
We decompose when we nd a BCNF
violation X ! Y , into X Y and R , Y
But R , Y X X Y = X . Thus
the intersection of the schemas functionally
determines one of them, X Y .
To complete the proof, we need to show that
when we decompose further, the resulting n
relations have a lossless join, but that is an
easy induction on n.
5 Dependency Preservation When we decompose R with FD's F , will F be
equivalent to the union of its projections onto the
One way to guarantee dependency
preservation is to use a minimal cover, and
convert each FD in the cover X ! A into the
3 But if there are some attributes not
mentioned in any FD, make them a
schema by themselves. Theorem A minimal cover F yields 3NF relations. Proof Suppose XA the relation from X ! A is not in
3NF, because Y ! B is a 3NF violation.
We know Y is not a superkey, and B is not
Case 1: A = B . Then Y X . Since Y ! B
follows from F , and X ! A surely follows
from Y ! B , we know F is equivalent to
F , fX ! Ag fY ! B g.
3 Then F was not a minimal cover.
Case 2: A 6= B . Then B is in X .
3 X is surely a superkey for XA, and since
B is not prime, B must not be in any key
3 Then X , B ! A can replace X ! A in
F , showing F is again not minimal. Decomposition With 3NF, Dependency
Preservation and Lossless Join To the schema from a minimal cover, add a key for
the original relation if there is not already some
relation schema that is a superkey. Example R = ABCD; F = fA ! B; C ! Dg.
Start with AB and CD from the FD's.
Only key for R is AC .
Thus, DP, LJ, 3NF decomposition is
fAB; AC; CDg.
6 Proof that the decomposition is always LJ will
have to wait for the theory of generalized
3 This decomposition is obviously LJ, since
AB . AC is lossless because A ! B , and
then ABC . CD is lossless because of
C ! D. 7 ...
View Full Document