Functional Dependencies

1
Functional Dependencies
2
Reading and Exercises

• Database Systems- The Complete Book Chapter 3.4,
  3.5
• Self testing exercises
• 3.4.1, 3.5.1
• Any of the remaining exercises in the textbook
• Following lecture slides are modified from Jeff
  Ullmans slides for Fall 2002 -- Stanford

3
Database Design

• Goal
• Represent domain information
• Avoid anomalies
• Avoid redundancy
• Anomalies
• Update not all occurrences of a fact are changed
• Deletion valid fact is lost when tuple is deleted

4
Functional Dependencies

• FD X ? A for relation R
• X functional determines A, i.e., if any two
  tuples in R agree on attributes X, they must also
  agree on attribute A.
• X set of attributes
• A single attribute
• If t1 and t2 are two tuples of r over R and
  t1X t2X then t1A t2A

5
Functional Dependency Example

• Owner(Name, Phone)
• FD Name ? Phone
• Dog(Name, Breed, Age, Weight)
• FD Name, Breed ? Age
• FD Name, Breed ? Weight

6
Example - FD
Dog-Kennels(Name,Breed,Age,Weight,Date,Kennel)
Name,Breed ? Age Name,Breed ? Weight
7
FD with Multiple Attributes

• Right side can be more than 1 attribute
  splitting/combining rule
• E.g., FD Name, Breed ? Age
• FD Name, Breed ? Weight
• combine into
• FD Name, Breed ? Age,Weight
• Left side cannot be decomposed!

8
FD Equivalence

• Let S and T denote two sets of FDs.
• S and T are equivalent if the set of relation
  instances satisfying S is exactly the same as the
  set of instances satisfying T.
• A set of FDs S follows from a set of FDs T if
  every relation instance that satisfies all FDs in
  T also satisfies all FDs in S.
• Two sets of FDs S and T are equivalent if S
  follows from T and T follows from S.

9
Trivial FD

• Given FD of the form A1,A2,,An?B1,B2,,Bk
• FD is
• Trivial if the Bs are subset of As
• Nontrivial if at least one of the Ba is not
  among As
• Completely nontrivial if none of the Bs is in
  As.

10
Keys and FD

• K is a (primary) key for a relation R if
• K functionally determines all attributes in R
• 1 does not hold for any proper subset of K
• Superkey 1 holds, 2 does not hold

11
Example

• Dog-Kennels(Name,Breed,Age,Weight,Date,Kennel)
• Name,Breed,Date is a key
• KName,Breed,Date functionally determines all
  other attributes
• The above does not hold for any proper subset of
  K
• What are?
• Name,Breed,Kennel
• Name,Breed,Date,Kennel
• Name,Breed,Age
• Name,Breed,Age,Date

12
Where do Keys Come From?

• Assert a key K, then only FDs are
• K ? A for all attributes A (K is the only key
  from FDs)
• Assert FDs and deduce the keys
• E/R gives FDs from entity set keys and many-one
  relationships

13
E/R and Relational Keys

• E/R keys properties of entities
• Relation keys properties of tuples
• Usually one tuple corresponds to one entity
• Poor relational design one entity becomes
  several tuples

14
Closure of Attributes

• Let A1,A2,,An be a set of attributes and S a set
  of FDs. The closure of A1,A2,,An under S is the
  set of attributes B such that every relation that
  satisfies S also satisfies A1,A2,,An ? B.
• Closure of attributes A1,A2,,An is denoted as
  A1,A2,,An

15
Algorithm Attribute Closure

• Let X A1,A2,,An
• Find B1,B2,,Bk ? C such that B1,B2,,Bk all in X
  but C is not in X
• Add C to X
• Repeat until no more attribute can be added to X
• X A1,A2,,An

16
Closures and Keys

• A1,A2,,An is a set of all attributes of a
  relation if and only if A1,A2,,An is a superkey
  for the relation.

17
Projecting FDs

• Some FD are physical laws
• E.g., no two courses can meet in the same room at
  the same time
• A professor cannot be at two places at the same
  time.
• How to determine what FDs hold on a projection of
  a relation?

18
FD on Relation

• Relation schema design which FDs hold on
  relation
• Given X1 ? A1, X2 ? A2, , Xn ? An whether Y ? B
  must hold on relations satisfying X1 ? A1, X2 ?
  A2, , Xn ? An
• Example A ? B and B ? C, then A ?C must also hold

19
Inference Test

• Test whether Y ? B
• Assume two tuples agree on attributes Y
• Use FDs to infer these tuples also agree on other
  attributes
• If B is one of the other attributes, then Y ? B
  holds.

20
Armstrong Axioms

• Reflexivity
• If A1,A2,,Am superset of B1,B2,,Bn then
  A1,A2,,Am ? B1,B2,,Bn
• Augmentation
• If A1,A2,,Am ? B1,B2,,Bn then
  A1,A2,,Am,C1,,Ck ? B1,B2,,Bn,C1,,Ck
• Transitivity
• If A1,A2,,Am ? B1,B2,,Bn and B1,B2,,B ?
  C1,C2,,Ck then A1,A2,,Am ? C1,C2,,Ck

21
FD Closure

• Compute the closure of Y, denoted as Y
• Basis Y Y
• Induction look FD, where left side X is subset
  of Y . If FD is X ? A then add A to Y .

22
Finding All FDs

• Normalization break a relation schema into two
  or more schemas
• Example
• R(A,B,C,D)
• FD AB ?C, C ?D, D ? A
• Decompose into (A,B,C), (A,D)
• FDs of (ABC) A,B ?C and C ? A

23
Basic Idea

• What FDs hold on a projections
• Start with FDs
• Find all FDs that follow from given ones
• Restrict to FDs that involve only attributes from
  a schema

24
Algorithm

• For each X compute X
• Add X ? A for all A in X - X
• Drop XY ? A if X ? A
• Use FD of projected attributes

25
Tricks

• Do not compute closure of empty set of the set of
  all attributes
• If X all attributes, do not compute closure of
  the superset of X

26
Example

• ABC with A ? B, B ? C and projection on AC
• A ABC, yields A ? B and A ? C
• B BC, yields B ? C
• C C, yields nothing
• BC BC, yields nothing
• Resulting FDs A?B, A?C, B?C
• Projection AC A ? C