Functional Dependencies

1
Functional Dependencies

• What you will learn from this lecture
• Database Design
• Definitions of functional dependencies (FDs)
• Closure of FDs
• Algorithm for calculating closure
• Irreducibility

2
Database Design

• Give some body of data to be represented in a
  database, how do we decide on a suitable logical
  structure for that data?
• We are concerned here with logical (or
  conceptual) design only, not physical design
• Database design is still very much an art, not a
  science
• Database design is not just a question of getting
  the data structure right data integrity is a
  (perhaps the) key ingredient too
• We will be concerned for the most part with what
  might be termed application independent design

3
Functional Dependencies

• Informal definition
• A many-to-one relationship between one set of
  attributes A and
• and another set of attributes B in a given
  relation R
• i.e. for many values of the set A, there is only
  one value in set B
• functional dependencies (FDs) tell us the meaning
  of data
• (e.g. every supplier is located in only one
  city )
• FDs represent integrity constraints.
• FDs are checked by the database management system
  (DBMS) at every update.
• So we are interested in finding the smallest set
  of FDs that capture the intended meaning of the
  data.

4
Definitions

• A more formal definition
• Given R, an instance of a relation, and X and Y,
  arbitrary attribute subsets of R, then Y is
  functionally dependent on X
• X ? Y
• if and only if each X-value in R is associated
  with precisely one Y-value in R

5
Example
Consider an instance of the revised shipments
relation, called SCP
S
CITY
P
QTY
S1 S1 S2 S3 S4
London London Paris Paris London
P1 P2 P1 P2 P2
100 100 200 300 400
S, P ? QTY S, P ? CITY S, P ?
CITY, QTY S, P ? S S, P ? S, P,
CITY, QTY S ? QTY QTY ? S
Some FDs satisfied by SCP
6
Definitions(Cont.)

• We are usually interested in the FDs that hold
  for all possible values of that relvar
• A definition for relation variables
• Given R, a relation (variable), and X and Y,
  arbitrary subsets of the set of attributes of R,
  then Y is functionally dependent on X if and only
  if for every possible value of R, each X-value in
  R is associated with precisely one Y-value in R

7
Definitions(Cont.)

• Here then are some (time-independent) FDs that
  apply to relvar SCP
• S, P ? QTY
• S, P ? CITY
• S, P ? CITY, QTY
• S, P ? S
• S, P ? S, P, CITY, QTY
• S ? CITY

8
Use of Functional Dependencies

• We use functional dependencies to
• test relations to see if they are legal under a
  given set of functional dependencies. If a
  relation r is legal under a set S of functional
  dependencies, we say that r satisfies S.
• specify constraints on the set of legal
  relations we say that S holds on R if all legal
  relations on R satisfy the set of functional
  dependencies S.
• Note A specific instance of a relation schema
  may satisfy a functional dependency even if the
  functional dependency does not hold on all legal
  instances.

9
Observation on Definitions

• Even in SCP there is a large number of FDs
• We need a small number of FDs, since they have to
  be checked at every update by the DBMS
• For a given set S of FDs, find a set T of FDs for
  which it holds that
• T is smaller than S
• every FD in S is implied by the FDs in T
• And so the DBMS can just check the FDs in T
• Partial answer eliminate trivial dependencies,
  e.g. SCP
• Is there a method for finding T?

10
Trivial and Nontrivial Dependencies

• One obvious way to reduce the size of FDs we need
  to deal with is to eliminate the trivial
  dependencies. A dependency is trivial if it
  cannot possibly not be satisfied.
• An FD is trivial if and only if the right-hand
  side is a subset (not necessarily a proper
  subset) of the left-hand side
• As the name implies, trivial dependencies are not
  very interesting in practice

11
Closure of FDs

• Some FDs imply others
• S, P ? CITY, QTY implies
• both S, P ? CITY and S, P ? QTY
• The set of all FDs implied by a given set S of
  FDs is the closure
• We denote the closure of S by S
• There are different ways of inferring FDs
• Can use these to show if an FD is a member of the
  closure of some given set of FDs

12
A Set of Inference Rules

• We can find all of S by applying Armstrongs
  Axioms
• Reflexivity if B ? A, then A ? B (trivial)
  ???
• Augmentation if A ? B , then AC ? BC
  ???
• Transitivity if A ? B and B ? C, then A ? C
  ???
• These rules are complete and sound.(???,???).
• Complete given a set S of FDs, all FDs implied
  by S can be derived from S using the rules.
• Sound no additional FDs can be so derived.
• The rules can be used to derive precisely the
  closure S.

13
Additional Rules

• We can further simplify computation of S by
  using the following additional rules.
• Self-determination A ? A ??
• Decomposition If A ? BC, then A ? B and A ? C
  ??
• Union, If A ? B and A ? C , then A ? BC ??
• Composition If A ? B and C ? D, then AC ? BD ??
• The above rules can be inferred from Armstrongs
  axioms.

14
Example

• R (A, B, C, G, H, I)
• S A? B
• A? C
• CG ? H CG ? I B ? H
• some members of S
• A ? H
• AG ? I
• CG ? HI

15
Closure of Attribute Sets

• First, we need to know how to calculate closure
  Z of a set of attributes Z, under a set of FDs S
• Given Z and S, determine the set of attributes A
  which are functionally dependent on Z (i.e. the
  closure Z of Z under S)
• Algorithm to compute Z, the closure of Z under S
• result Z while (changes to result) do
  for each X ? Y in S do begin if X ?
  result then result result ? Y end

16
Example

• Given R with attributes A, B, C, D, E, F and S
  as
• A ? BC, E ? CF, B ? E, CD ? EF
• calculate the closure to A, B of the set A,
  B under S
• 1) initialise the closure to A, B
• 2) for every FD X ? Y in S, if X is a subset of
  the closure, then closure closure union Y
• A, B A,B,C,E,F

17
More Example

• R (A, B, C, G, H, I)
• S A? B
• A ? C
• CG ? H CG ? I B ? H
• (AG) ABCGHI
• Is AG a candidate key?

18
Important Corollaries

• By this algorithm, we can tell whether an FD X ?
  Y follows from S
• The FD will follow if and only if Y is a subset
  of the closure X of X under S
• Giving a way of determining whether a given FD is
  in the
• closure S of S, without actually having to
  computer that closure S.
• K is a superkey if and only if the closure K of
  K is precisely the set of all attributes

19
The Cover and Equivalent of FDs

• Given two sets of FDs S1 and S2, if every FD
  implied by S1 is implied by the FDs in S2 (i.e.
  if S1 is a subset of S2) then S2 is a cover for
  S1 (i.e. the DBMS only has to check S2)
• If the DBMS enforce the FDs in S2, then it will
  automatically be enforcing the FDs in S1.
• If S2 is a cover for S1 and S1 is a cover for S2,
• i.e., if S1S2, we say S1 and S2 are
  equivalent.
• If S1 and S2 are equivalent, then if the DBMS
  enforce the FDs in S2, it will automatically be
  enforcing the FDs in S1, and vice versa.

20
Irreducibility

• A set of FDs S is irreducible if and only if
• 1) the right -hand side of each FD in S involves
  one attribute
• 2) No attribute in the left-hand side of each FD
  in S can be
• removed without changing S
• 3) no FD in S can be removed without changing S
• forms basis of method for calculating the
  irreducible form of a set of FDs.
• For every set of FDs, there exists at least one
  equivalent set that is irreducible.

21
Irreducible Equivalent

• A set I of FDs that is irreducible and equivalent
  to some other set S of FDs is said to be an
  irreducible equivalent to S.
• Given some particular set S of FDs that need to
  be enforced, it is sufficient for the system to
  find and enforce the FDs in an irreducible
  equivalent I instead.
• A given set of FDs does not necessarily have a
  unique irreducible equivalent.

22
Example

• R (A, B, C, D)
• S A ? BC
• B? C
• A ? B
• AB ? C
• AC ? D
• the irreducible set
• A ? B
• B ? C
• A ? D

23
Example(1)

• R (A, B, C, D)
• S O
• the candidate key?
• AB?AC, B?C?
• e.g. (class,name)?(class,dept)
• R(A,B,C)
• SA?B, B?C
• S?

/
24
Example(2)

• R(A, B, C, D, E)
• S AB?C, CD?A,BC?D,D?B,EA?C
• ABE?
• R(A, B, C, D, E)
• S A?B, B?D,CE?A,CD?E
• AC?BE ?
• the candidate key?

25
Example(3)

• S AB?C, D?EG, C?A,
• BE?C, BC?D, CG?BD,
• CD?B,CE?A, CE?G
• the irreducible set?

26
Reading
Chapter 10 (Dates book)