Functional Dependencies

1
Functional Dependencies

• Meaning of FDs
• Keys and Superkeys
• Inferring FDs

2
Functional Dependencies

• X -gt A is an assertion about a relation R that
  whenever two tuples of R agree on all the
  attributes of X, then they must also agree on the
  attribute A.
• Say X -gt A holds in R.
• Convention , X, Y, Z represent sets of
  attributes A, B, C, represent single
  attributes.
• Convention no set formers in sets of attributes,
  just ABC, rather than A,B,C .

3
Example

• Drinkers(name, addr, beersLiked, manf, favBeer)
• Reasonable FDs to assert
• name -gt addr
• name -gt favBeer
• beersLiked -gt manf

4
Example Data
name addr beersLiked manf favBeer Janeway
Voyager Bud A.B. WickedAle Janeway Voyager
WickedAle Petes WickedAle Spock Enterprise
Bud A.B. Bud
5
FDs With Multiple Attributes

• No need for FDs with gt 1 attribute on right.
• But sometimes convenient to combine FDs as a
  shorthand.
• Example name -gt addr and name -gt
  favBeer become name -gt addr
  favBeer
• gt 1 attribute on left may be essential.
• Example bar beer -gt price

6
Keys of Relations

• K is a superkey for relation R if K
  functionally determines all of R.
• K is a key for R if K is a superkey, but no
  proper subset of K is a superkey.

7
Example

• Drinkers(name, addr, beersLiked, manf, favBeer)
• name, beersLiked is a superkey because
  together these attributes determine all the other
  attributes.
• name -gt addr favBeer
• beersLiked -gt manf

8
Example, Cont.

• name, beersLiked is a key because neither
  name nor beersLiked is a superkey.
• name doesnt -gt manf beersLiked doesnt -gt addr.
• There are no other keys, but lots of superkeys.
• Any superset of name, beersLiked.

9
E/R and Relational Keys

• Keys in E/R concern entities.
• Keys in relations concern tuples.
• Usually, one tuple corresponds to one entity, so
  the ideas are the same.
• But --- in poor relational designs, one entity
  can become several tuples, so E/R keys and
  Relational keys are different.

10
Example Data
name addr beersLiked manf favBeer Janeway
Voyager Bud A.B. WickedAle Janeway Voyager
WickedAle Petes WickedAle Spock Enterprise
Bud A.B. Bud
Relational key name beersLiked But in E/R,
name is a key for Drinkers, and beersLiked is a
key for Beers. Note 2 tuples for Janeway entity
and 2 tuples for Bud entity.
11
Where Do Keys Come From?

• Just assert a key K.
• The only FDs are K -gt A for all attributes A.
• Assert FDs and deduce the keys by systematic
  exploration.
• E/R model gives us FDs from entity-set keys and
  from many-one relationships.

12
More FDs From Physics

• Example no two courses can meet in the same
  room at the same time tells us hour room -gt
  course.

13
Inferring FDs

• We are given FDs X1 -gt A1, X2 -gt A2,, Xn -gt An
  , and we want to know whether an FD Y -gt B must
  hold in any relation that satisfies the given
  FDs.
• Example If A -gt B and B -gt C hold, surely A -gt
  C holds, even if we dont say so.
• Important for design of good relation schemas.

14
Inference Test

• To test if Y -gt B, start by assuming two tuples
  agree in all attributes of Y.
• Y
• 0000000. . . 0
• 00000?? . . . ?

15
Inference Test (2)

• Use the given FDs to infer that these tuples
  must also agree in certain other attributes.
• If B is one of these attributes, then Y -gt B is
  true.
• Otherwise, the two tuples, with any forced
  equalities, form a two-tuple relation that proves
  Y -gt B does not follow from the given FDs.

16
Closure Test

• An easier way to test is to compute the closure
  of Y, denoted Y .
• Basis Y Y.
• Induction Look for an FDs left side X that is a
  subset of the current Y . If the FD is X -gt A,
  add A to Y .

17
Y
18
Finding All Implied FDs

• Motivation normalization, the process where we
  break a relation schema into two or more schemas.
• Example ABCD with FDs AB -gtC, C -gtD,
  and D -gtA.
• Decompose into ABC, AD. What FDs hold in ABC ?
• Not only AB -gtC, but also C -gtA !

19
Why?
ABCD
ABC
a1b1c
a2b2c
Thus, tuples in the projection with equal Cs
have equal As C -gt A.
20
Basic Idea

• Start with given FDs and find all nontrivial
  FDs that follow from the given FDs.
• Nontrivial left and right sides disjoint.
• Restrict to those FDs that involve only
  attributes of the projected schema.

21
Simple, Exponential Algorithm

• For each set of attributes X, compute X .
• Add X -gtA for all A in X - X.
• However, drop XY -gtA whenever we discover X -gtA
  for the minimal basis
• Because XY -gtA follows from X -gtA in any
  projection.
• Need to include it for a complete set
• Finally, use only FDs involving projected
  attributes.

22
A Few Tricks

• No need to compute the closure of the empty set
  or of the set of all attributes.
• If we find X all attributes, so is the
  closure of any superset of X.

23
Example

• ABC with FDs A -gtB and B -gtC. Find
  non-trivial FDs when projected onto AC.
• A ABC yields A -gtB, A -gtC.
• We do not need to compute AB or AC .
• B BC yields B -gtC.
• C C yields nothing.
• BC BC yields nothing.

24
Example --- Continued

• Resulting the minimal basis of non-trivial FDs
  A -gtB, A -gtC, and B -gtC.
• Projection onto AC A -gtC.
• Only FD that involves a subset of A,C .
• Key for such AC A
• For complete set of non-trivial FDs in ABC, add
  AB-gtC, AC-gtB

25
More Examples

• ABCDE with FDs AB -gtDE, C-gtE, D-gtC, and E
  -gtA, project onto ABC, please give the
  non-trivial FDs
• Inference the disclosures
• A A, B B, C ACE
• AB ABCDE
• AC ACE
• BC ABCDE
• Ignore D and E, for
• A minimal basis C-gtA, AB-gtC
• A complete set also include BC-gtA

26
A Geometric View of FDs

• Imagine the set of all instances of a particular
  relation.
• That is, all finite sets of tuples that have the
  proper number of components.
• Each instance is a point in this space.

27
Example R(A,B)
(1,2), (3,4)
(5,1)

(1,2), (3,4), (1,3)
28
An FD is a Subset of Instances

• For each FD X -gt A there is a subset of all
  instances that satisfy the FD.
• We can represent an FD by a region in the space.
• Trivial FD an FD that is represented by the
  entire space.
• Example A -gt A.

29
Example A -gt B for R(A,B)
A -gt B
(1,2), (3,4)
(5,1)

(1,2), (3,4), (1,3)
30
Representing Sets of FDs

• If each FD is a set of relation instances, then a
  collection of FDs corresponds to the
  intersection of those sets.
• Intersection all instances that satisfy all of
  the FDs.

31
Example
A-gtB
B-gtC
CD-gtA
32
Implication of FDs

• If an FD Y -gt B follows from FDs X1 -gt
  A1,,Xn -gt An , then the region in the space of
  instances for Y -gt B must include the
  intersection of the regions for the FDs Xi -gt Ai
  .
• That is, every instance satisfying all the FDs
  Xi -gt Ai surely satisfies Y -gt B.
• But an instance could satisfy Y -gt B, yet not be
  in this intersection.

33
Example
B-gtC
A-gtC
A-gtB
34
Backup Slides
35
More Examples

• ABCD with FDs AB -gtC, C-gtD, and D -gtA,
• What are the non-trivial FDs in ABCD ?
• A A, B B, C ACD, D AD, gives C-gtA
• AB ABCD, gives AB-gtD, no need for ABC, or
  ABD
• AC ACD, gives AC-gtD
• AD AD
• BC ABCD, gives BC-gtA, BC-gtD
• BD ABCD, gives BD-gtA, BD-gtC
• CD ACD, gives CD-gtA