Computer Science 317 Database Management

1
Computer Science 317Database Management

• Functional Dependencies

2
Functional Dependency - definition

• Consider a relational schema R(A1,, An). A
  functional dependency X ? Ywhere X, Y are
  sets of attributes of R, is a constraint that
  says that if two tuples agree on the attributes
  of X, then they must agree on the attributes of
  Y.
• Note If X is a candidate key, then X ? Yholds
  for all sets Y.

3
FDs - Running Example (bad)

• For the time being, well assume that Sections is
  a strong entity set with primary key RNum. Assume
  that we are working with Smajor(ID, LName,
  FName, Address, RNum, Cnum, CourseTitle)
• Some likely FDs would be ID?LName,
  ID?LName,FDame,, RNum?CNum, CNum?CourseTitle

4
Consequences of set of FDs

• Sometimes there are implicit constraints which
  are implied by the explicit constraints.
• Suppose F is a set of FDs for schema R. We say
  that F implies X ? Y if for every possible
  instance r of R where F holds, we must have X ? Y
  holding.
• In this case we write F X ? Y .
• We let F denote the set of all FDs that are
  implied by F. This is called the closure of F.

5
FDs - Inference Rules of FDs

• 1. (Reflexive) If X ? Y then X ? Y always holds.
  So F X ? Y for any set of FDs, F.
• 2. (Augmentation) X ? Y XZ ? YZ
• 3. (Transitive) X ? Y, Y ? Z X ? Z
• 4. (Decomposition or Projection) X ? YZ X
  ? Y

6
FD Inference Theorem

• Theorem. By continually applying 1-3, we obtain
  all of the consequences of F.
• Let F0 F ? X ? Y X ? Y Fi1 Fi ? XZ ?
  YZ X ? Y ? Fi ? X ? Z X ? Y, Y ? Z ?
  Fi F ? Fk
• The theorem states that F F

7
Example of computing F

• Consider Smajors(Id, Lname, Rnum, Cnum, Title)
• Let F C ? T, I ? L, R ? C. Then
• F0 C?T,I?L,R?C,C?C,T?T,, IL ?I, IL ?L,
• F1 F0 ? IC ?IT, IRC ?IRT,... ? R ?T,...
• ...

8
Closure of a set of attributes

• By looking at closures of sets of attributes
  rather than sets of FDs, we get a better
  organizational scheme.
• Assuming we are working with a fixed schema, R,
  and a fixed set of FDs, F, if X is a set of
  attributes of R, we define X A X ? A is
  in F A F X ? A
• X is called the closure of X.

9
Algorithm to compute X

• Set X to XRepeat Set Old X to X
  For each Y ? Z in F If Y? X then Set X
  X ? Zuntil Old X X

10
Examples computing X

• Let FAB ? C, C ? A, BC ? D, ACD ? B, D ?
  EG, BE ?C, CG ? BD, CE ? AG
• Let XBD
• X(0) BD X(1) BDEG X(2) BDEGC X(3)
  BDEGCA (all attributes)
• X BDEGCA
• Let XAD
• X(0) AD X(1) ADEG X(2) ADEG
• X ADEG

11
Lattices (not from text)

• A lattice is a partially ordered set (has ??
  relationship) in which every two elements has a
  least upper bound and a greatest lower bound.
  That is, for any elements a and b, there is a
  smallest element c and a largest element d such
  that a?c, b?c, d?a, d?b.
• Lattices can be drawn graphically giving a clear
  picture of the structure and relationships
  present.

12
Lattice of Closed Sets of Attributes

• For fixed schema R(A1,, An) and fixed set of
  FDs, F, we say that a set of attributes X is
  closed if X X.
• The set of closed sets form a lattice. For closed
  sets X and Y, the least upper bound is (X ? Y)
  and the greatest lower bound is X?Y.
• This lattice gives a clear and concise picture of
  the FDs present, both explicit and implicit.
• Note The empty set, ?, is always a closed set.

13
Lattice of Closed Sets - Example

• R(ABCDE), FA?C, B?C, C?D, DE? C, CE ?
  A
• A ACD AB ABCD AE ACDE ABE ABCDE
• B BCD BE BCDEA
• C CD CE CDEA
• D D DE DECA
• E E

14
Lattice of Closed Sets - Example (cont.)

• The closed sets were ?, D, E, CD, ACD, BCD, ABCD,
  ACDE, and ABCDE.

15
Gleaning information from the lattice

• To find X, look at lattice and find smallest
  closed set containing X
• AACD
• CEACDE
• To see if X?Y is in F, see if Y? X.
• DE ? A? Yes!
• DE ? B? No!