Functional Dependencies (FDs)

1
Functional Dependencies (FDs)
Let r(R) be a relation and let t ? r, then the
restriction of t to X ? R, written tX, is the
projection of t onto X.
BC ? ABC tBC lt2, 5gt (B2), (C5)
R(A B C) 1 2 3 2 2 5 2 3
5 4 4 5
r
t
Let R be a relational schema and let X ? R and Y
? R. X ? Y is a functional dependency or FD. A
relation r(R) satisfies the FD X ? Y (or X ? Y
holds) if for any two tuples t1 and t2 in r,
t1X t2X ? t1Y t2Y alternatively
(and equivalently) if for every (sub)tuple s in
?Xr, ?Xs?XYr 1.
A ? C B ? C AB ? C AC ? B
2
FD Implication
Let r(R) and let F be a set of FDs over R. Then
r satisfies F if each FD in F holds for r.
F may imply that other FDs also hold. F implies
X ? Y if X ? Y holds for every relation that
satisfies F.
F A ? B, B ? C r satisfies F. F implies A ? C.
r A B C D 1 2 3 4 5 6 7
8 5 6 7 9 0 1 2 3
Proof 1. Let sA tA 2. sA tA ? sB
tB given, A ? B 3. sB tB
1 2, modus ponens 4. sB tB ? sC
tC given, B ? C 5. sC tC
3 4, modus ponens
3
F
Let S be a set of attributes. If F is a set of
FDs over S, the set of all FDs implied by F is
called the closure of F, denoted F. When we
assert an FD X ? Y, we mean X ? Y ? F.
Rules for computing F (trivial implication)
Y ? X ? X ? Y e.g., Name City ? Name
(accumulation) X ? Y, W ? Z, W ? Y ? X ?
YZ e.g., GuestNr ? Name City, Name ? RoomNr
? GuestNr ? Name City RoomNr (projection)
X ? Y, Z ? Y ? X ? Z e.g., GuestNr ?
Name City RoomNr ? GuestNr ? RoomNr We
compute F by a least-fixed-point process.
4
Sound and Complete Rules
The implication rules for F are sound the
derived FDs hold for any relation satisfying F
complete repeated application of the rules
derives all implied FDs.
Proof of Soundness. (Y ? X ? X ? Y) Let sX
tX. Then since Y ? X, sY tY. (X ?
Y, W ? Z, W ? Y ? X ? YZ) Let sX tX. Then
since X ? Y, sY tY. Then since W ?
Y, sW tW and since W ? Z, sZ tZ.
Now, since sY tY and sZ tZ,
sYZ tYZ. (X ? Y, Z ? Y ? X ? Z) Let
sX tX. Then since X ? Y, sY tY, and
since Z ? Y, sZ tZ. Proof of
Completeness (basic idea) Assume an FD X ? Y
holds but is not in F. We can show, however,
that X ? Y can be computed by trivial
implication, accumulation, and projection and
thus contradict our assumption.
5
Example of F
If the set of attributes is ABC and the set of
FDs F A ? B, B ? C, then F A ? B, B ? C
A ? A, B ? B, C ? C, AB ? AB, AC ? AC, BC ?
BC, ABC ? ABC, AB ? A, AB ? B, AC ? A, AC ?
C, BC ? B, BC ? C, ABC ? A, ABC ? B, ABC ? C,
ABC ? AB, ABC ? AC, ABC ? BC, A ? BC, A ?
C, A ? AB, A ? AC, A ? ABC, B ? BC, AB ? C, AB
? AC, AB ? BC, AB ? ABC, AC ? B, AC ? AB, AC ?
BC, AC ? ABC
6
Additional FD-Implication Rules
(augmentation) X ? Y ? XZ ? YZ RoomNr ?
Cost ? RoomNr NrDays ? Cost NrDays
(transitivity) X ? Y, Y ? Z ? X ? Z
GuestNr ? Name, Name ? RoomNr ? GuestNr ?
RoomNr (union) X ? Y, X ? Z ? X ?
YZ RoomNr ? NrBeds, RoomNr ? Cost ? RoomNr
? NrBeds Cost
7
Checking for X ? Y ? F

• Generate F and see if X ? Y is present
  (expensive)
• Derive X ? Y from F, or determine that its not
  derivable
• Derivation sequence for X ? Y sequence of FDs
• Each FD is given in F or follows by a sound
  derivation rule
• X ? Y is the last FD in the sequence
• Examples

R ABCD, F A ? B, B ? C, AD ? C ? F?, BC ?
A ? F?
1. A ? B given 2. B ? C given 3. A ?
C transitivity, 1 2 4. AD ? CD
augmentation, 3 5. AD ? C projection, 4
1. BC ? B trivial implication 2. B ? C
given 3. BC ? C transitivity, 1 2 . .
. How do we know we cannot derive BC ? A?
8
TAP Derivation Sequence

• A particular derivation sequence always works!
• List the given FDs
• T Trivial Implication
• A Accumulation (repeated zero or more times with
  FDs in F)
• P Projection (if needed)
• Examples

R ABCD, F A ? B, B ? C, AD ? C ? F?, BC ?
A ? F?
1. A ? B given 2. B ? C given 3. BC ?
BC T How do we know we cannot derive BC ?
A? Accumulation yields nothing more,
and projection cannot yield A on the rhs, and BC
? A ? F iff there is a TAP derivation sequence
for BC ? A.
1. A ? B given 2. B ? C
given 3. AD ? AD T 4. AD ? ADB A 5. AD
? ADBC A 6. AD ? C P
9
X Closure of a Set of Attributes

• X maximal accumulation in a TAP derivation
  sequence starting with X.
• Algorithm for X given a set of FDs F
• 1. Start with X X.
• 2. If Y ? Z ? F and Y ? X, X becomes XZ.
• 3. Repeat 2 until no more changes to X (least
  fixed point).
• Examples

R ABCD, F A ? B, B ? C AD ABCD
BC BC BD BCD D D A
ABC
10
X ? Y ? F iff Y ? X

• Significant observation!
• X ? Y ? F looks like a problem requiring
  exponential time
• BUT has a polynomial-time solution (linear with
  well-chosen data structures)
• This is an example of the essence of good
  computer science.

11
X and Hypergraph Reachability
To test X ? Y ? F, mark the vertices in X and
see if the vertices in Y are reachable following
directed edges.
A ? D ? F? Yes A ? G ? F? No E
? CD ? F? Yes A ? BH ? F? No AE ? HG
? F? Yes . . .
12
FD Equivalence

• Two sets of FDs F G are equivalent, written F ?
  G, if F implies each FD in G and conversely.
• If F ? G, then F G.

F A ? B, AB ? C, C ? D ? G A ? BC, C ? D,
A ? D In F, A ABCD ? A ? BC A ? D and C
CD ? C ? D. In G, A ABCD ? A ? B, AB ABCD
? AB ? C, and C CD ? C ? D.
F
G
F
G
F
G
F
G
F ? G G ?
F (G ? F ? F ? G) ? F G
13
Keys and FDs
Let U be a set of attributes, and let F be a set
of FDs over U. Let R ? U be a relational schema.
A subset K of R (K need not be a proper subset
of R) is a superkey of R if K ? R ? F and is a
candidate key (minimal key) of R if there does
not exist a proper subset K? of K such that K? ?
R ? F.
Example U ABCDE and F A ? B, B ? A, AB ? C,
D ? BC.
Schema Candidate Keys AB
A, B CE CE ABCD
D ABCDE DE