Functional Dependancy

1
Functional Dependancy

• Definition
• constraints on relations
• characteristic of an attribute where values are
  determined by another attributes values
• Notation
• a?ß (a determines ß)
• (a?ß may take the form AB?C, A?BC, etc.)

2
Characteristics

• Functional dependency holds if
• t1ß t2ß where t1a t2a
• ß is functionally dependent on a if
• value of a in tuples t1 and t2 determines
• value of ß in tuples t1 and t2.
• Or, for each value of a, there is only 1
  corresponding value of ß.

3
Keys

• Keys
• consist of single or multiple attributes that
  determine the values of non-key attributes.
• Superkey
• A chosen set of attributes that has closure over
  relation R.
• Candidate key
• A possible set of attributes that has closure
  over relation R.

4
Uses for Functional Dependancy

• To determine if a relation is in a Normal Form.
• To specify constraints on the set of legal
  relations (functional dependencies to focus on)
• To determine if a decomposition would cause data
  loss (R decomposed to R1 and R2 but, R1 X R2 ?
  R)

5
Terms

• Trivial
• A?A and AB?A are trivial because
• A is a subset of A and AB
• Satisfy/Hold
• Relation R satisfies functional dependency a?ß if
  t1ß t2ß wherever t1a t2a conversely
  functional dependency a?ß holds on relation R.

6
Functional Dependency Theory Closure

• Definition
• the set of all functional dependencies that
  logically implied by those in a set F.
• Notation
• F (closure in functional dependency set F)
• a (closure of the attribute set a under F
• used to determine if a is a superkey)
• Fc (canonical cover implied by F that excludes
• extraneous attributes)

7
Closure Rules of Inference

• Armstrongs Axioms
• Reflexivity rule
• if a is a set of attributes and ß is contained in
  a then a?ß
• i.e. given AB?C, then A?B
• Augmentation rule
• given a?ß and another set of attributes ?, then
• ?a??ß
• Transitivity rule
• if a?ß and ß?? , then a??

8
Closure Rules of Inference Cont.

• Other Rules
• Union rule
• if a?ß and a?? , then a?ß?
• Decomposition rule
• if a?ß? , then a?ß and a??
• Pseudotransitivity rule
• if a?ß and ?ß?d , then ?a?d

9
Boyce Codd Normal Form

• Eliminates all redundancy based on functional
  dependancy a?ß in closure F.
• Conditions (at least one)
• ß is a subset of a
• a is a superkey for relation R
• (a has closure over R or all attributes of R)

10
Decomposition into BCNF

• If a?ß and relation R is not in BCNF,
• decompose R into two relations
• (a U ß) and (R - (ß - a))
• if decompositions are not in BCNF, repeat

11
Example

• Given relation R(A, B, C)
• AB is the Superkey, B alone is not
• if F (B?C) required to hold, then R is not in
  BCNF R decomposes to (B,C) and (A,B)
• if F (A?B), (B?C) required to hold, then
• F (A?B), (B?C), (A?C), (AB?C), and
• Fc (AB?C) R is in BCNF

12
Third Normal Form

• Preserves functional dependency
• Conditions (at least one)
• In BCNF
• ß is a subset of a
• a is a superkey for relation R
• each attribute in (ß - a) is contained in a
  candidate key

13
Decomposition into 3NF

• Same as with BCNF
• does not require decomposition if each attribute
  in ß, but not in a, is contained in a candidate
  key

14
Example

• Given relation R(A, B, C, D)
• AB is the Superkey, BC is a candidate key
• if F (A?C) required to hold, R is in 3NF
  since C - A exists in BC no decomposition
  required

15
Fourth Normal Form

• Conditions
• In BCNF
• ß is a subset of a
• a is a superkey for relation R
• Decomposition also based on functional
  dependencies involving multivalued attributes

16
Decomposition into 4NF

• Same as with BCNF
• treat dependencies involving multivalued
  attributes as part of the constraints
• (text says the opposite - treat functional
  dependencies as multivalued dependencies, then
  use all multivalued dependencies as contraints)

17
Example

• Given relation R(A, B, C, D)
• AB is the Superkey, D is multivalued and B?D
• if F (A?C) required to hold, then treat as
  F (A?C), (B?D)
• R is not in 4NF R decomposes to (A,C) and
  (A,B,D) (A,B,D) is not in 4NF, so decompose
  again to (B,D) and (A,B).
• Result (A,C), (B,D), (A,B)

18
Resources

• Silberschatz, A. Korth, H. Sudarshan, S.
  (2006). Database System concepts. New York New
  York.