Functional Dependencies

1
Functional Dependencies
Lecture Notes
2
Outline of Lecture

• Definition
• Functional Dependencies and Keys
• Rules about Functional Dependencies
• Armstrongs axioms
• Closure of attribute set
• Closure of set of functional dependencies

3
Functional Dependencies

• Most important kind of constraint in the
  relational model
• Unique value constraint generalization of the
  key
• Constraint on the possible tuples that can form a
  relation state r of R
• Knowledge about functional dependencies is vital
  for redesign of database schemas to eliminate
  anomalies redundancies

4
Definition Functional Dependency (FD)

• Formally A1, A2, , An ? B (commas usually
  omitted)
• Read A1, A2, , An functionally determine B
• If two tuples of r(R) agree on attributes A1, A2,
  , An of R, then they must also agree in another
  attribute B
• i.e., the tuples have the same values in their
  respective components for each of these
  attributes
• Also, if
• A1 A2 An ? B1
• A1 A2 An ? B2
• A1 A2 An ? Bm
• then
• A1 A2 An ? B1 B2 Bm

5
Pictorially Speaking...

• Assume A ? B

R
As
Bs
t
u
If t and u agree here,
then they must agree here

• A FD tells us about any two tuples t and u in
  relation R

6
Example
Movies
title
year
length
filmType
studioName
starName
Star Wars
1977
124
color
Fox
Carrie Fisher
Star Wars
1977
124
color
Fox
Mark Hamill
Star Wars
1977
124
color
Fox
Harrison Ford
Mighty Ducks
1991
104
color
Disney
Emilio Estevez
Waynes World
1992
95
color
Paramount
Dana Carvey
Waynes World
1992
95
color
Paramount
Mike Myers

• Can assert FDs
• title year ? length
• title year ? filmType
• title year ? studioName

• But not
• title year ? starName

7
FDs and Schema

• A FD is an assertion about the schema of a
  relation, NOT about a particular instance
• Cannot tell for certain what FDs are by simply
  looking at schema (based on our knowledge of real
  world)
• FDs are a property of the semantics of the
  attributes
• All data must conform
• Relation extensions r(R) that satisfy the FD
  constraints are called legal extensions (legal
  relation states)

8
FDs and Keys

• New, more general definition of candidate key,
  consistent with functional dependency theory
• KA1,A2,,An is a candidate key for relation R
  if
• 1. K functionally determines ALL other attributes
  of R
• i.e., impossible for two distinct tuples, t and u
  to agree on all of A1, A2, , An
• AND
• 2. No proper subset of K functionally determines
  all other attributes of R
• i.e., K must be minimal

9
Trivial Dependencies

• A functional dependency A1A2An ? B is said to
  be trivial if B is one of the As
• Ex. title year ? title
• Every trivial dependency holds in every relation
• i.e., tuples that agree in all of A1,A2, , An
  agree in one of them
• We may assume trivial dependencies without having
  to justify them
• trivial Bs subset of As
• nontrivial at least one of the Bs not among As
• completely nontrivial none of the Bs part of
  As

10
Rules About Functional Dependencies

• Let F be set of FDs specified on R
• Must be able to reason about FDs in F
• Designer usually explicitly states only FDs
  which are obvious
• Without knowing exactly what all tuples are, must
  be able to deduce other/all FDs that hold on R
• Essential when we discuss design of good
  relational schemas

11
Rules About Functional Dependencies

• SPLITTING RULE
• If AA ? B1, B2, ..., Bn then AA ? B1, AA ? B2,
  ..., AA ? Bn
• Q Can we also split the left-hand side?
• COMBINING RULE
• If AA ? B1, AA ? B2, ..., AA ? Bn then AA ? B1,
  B2, ..., Bn
• TRIVIAL DEPENDENCY RULES
• If AA ? BB then AA ? (BB - AA)
• If AA ? BB then AA ? (BB ? AA)
• TRANSITIVE RULE
• If AA ? BB and BB ? CC then AA ? CC

12
Example Transitivity Rule

• R(A,B,C)
• F A?B, B ? C
• Show A ?C holds?
• Show that two tuples in r(R) that agree on A also
  agree on C
• Step 1 t(a,b1,c1), u(a,b2,c2)
• Step 2 Since A ?B, and tuples agree on A, they
  must also agree on B
• ? b1 b2
• ? t (a,b,c1), u (a,b,c2)

13
Example Contd

• Step 3 Since B ? C and tuples agree on B, must
  also agree on C
• ? c1 c2
• ? t (a,b,c), u (a,b,c)
• Step 4
• ? tuples that agree on A also agree on C
• ? A ? C

14
Set of Inference Rules

• Reflexive rule
• Augmentation rule
• Transitivity rule
• Decomposition rule
• Union rule
• Pseudotransitive rule

Armstrongs axioms sound and complete
15
Closure of Attributes

• General principle from which all rules follow
• Given a relation R, a set of FD's for R, and a
  set of attributes A1, A2, ..., Am of R
• Find all attributes B in R such that A1, A2,
  ..., Am ? B
• This set of attributes is called the "closure"
  and is denoted A1, A2, ..., Am

16
Algorithm for Computing Closure

• Start with A1, A2, ..., Am
• repeat until no change
• if current set of attributes includes LHS of a
  dependency,
• add RHS attributes to the set
• Effectively applies combining and transitive
  rules until there's no more change

17
Calculating the Closure

• R(A,B,C,D,E,F)
• F AB ? C, BC ? AD, D ? E, CE ? B
• How to compute closure of A,B, i.e., A,B?
• 1. Start with XA,B
• 2. Add C to X due to AB ? C XA,B,C
• 3. Add A,D to X due to BC ? AD XA,B,C,D
• 4. Add E to X due to D ? E XA,B,C,D,E
• 5. No more attributes can be added to X
• 6. A,B A,B,C,D,E

18
So What!

• If we can compute closure of any set of
  attributes, we can test whether any given
  functional dependency A1A2An?B follows from set
  of dependencies F
• Compute closure of A1, A2, , An using F
• If B is in A1, A2, , An , then A1A2An?B does
  follow from F
• ALSO We can test if KA1,A2,,An is a key for
  relation R if A1,A2,,An is the set of all
  attributes in R and if K is minimal

19
Specifying FD's for a Relation

• Let F be set of FDs specified on R
• Must be able to reason about FDs in F
• Designer usually explicitly states only FDs
  which are obvious
• Without knowing exactly what all tuples are, must
  be able to deduce other/all FDs that hold on R
• Essential when we discuss design of good
  relational schemas
• How can we tell if one FD follows from others?
• Use Armstrongs axioms and reason it out, OR
• Attribute closure algorithm always works!
• Set of ALL FDs that hold on a schema is called
  closure of F , F

20
Canonical Cover

• A set S2 of FD's "follows" from another set S1 of
  FD's if all relation instances that satisfy S1
  also satisfy S2
• When specifying FD's for a relation, we would
  like to specify a minimal set of completely
  nontrivial FD's such that all FD's that hold on
  the relation follow from the dependencies in this
  set (Why minimal set?)
• Canonical Cover FC