CPS 430/542 Lecture notes: Attribute and FD Closure Algorithms and Canonical Cover

---

Closure of a set of attributes

---

• closure of a set of attributes A under the set of FD's S is the set of attributes B such that every relation which satisfies all of the FD's in S also satisfies A → B [FCDB]
• in other words, A → B `follows from' S (we can also say S |= A → B)
• closure of a set of attributes {A₁, A₂, ..., A_n} is denoted {A₁, A₂, ..., A_n}⁺
• X⁺_F = {A | X → A ∈ F⁺}
• X⁺_F = {A | F⁺ |= X → A}
• algorithm (courtesy [DBSC] Fig. 7.9 [p. 281])
  
  F given
  
  result = A
  
  while (changes to result)
  for each FD X → Y ∈ F do
  if X ⊆ result
  result = result ∪ Y
• another approach
  1. start with initial set of attributes X
  2. identify FD's A → B where A ∈ X, but B ∉ X
  3. add B to X
  4. repeat until no more attributes can be added to the closure, or, in other words, when you reach a fixpoint
• what is the running time of the attribute closure algorithm?

---

Simple exercise

---

• R(A, B, C, D, E, F), S = {A B → C, B C → A D, D → E, C F → B}
• compute {AB}⁺
• {AB}⁺ = {ABCDE}
• means S |= AB → CDE

---

[DBAA] example 6.4.2 (p. 204)

---

---

Basic properties

---

• when is {A₁, A₂, ..., A₂}⁺ the set of all attributes in a relation?
• a set of attributes A is a superkey for R iff {A}⁺ = R
• a set of attributes A is a key for R iff {A}⁺ = R and no subset X of A exists where X⁺ = R

---

Uses of the attribute closure algorithm?

---

• determine if a set of attributes X is a superkey; X⁺ = {all attributes in R}
• can check if an FD A → B holds in a relation (i.e., S |= A → B)?
  1. compute {A}⁺
  2. check if B ∈ {A}⁺
  3. if so, A → B
  • examples
    • [DBAA] example 6.4.3 (pp. 205-206)
    • does A B → D follow from S? approach, compute {AB}⁺
    • does D → E follow from S? approach, compute {D}⁺
• can infer all FD's which follow from a given set of FD's; an alternative to F⁺
  for each Δ ⊆ R
  compute Δ⁺
  for each S ⊆ of Δ⁺
  output FD Δ → S
  
  
  • this is an exponential algorithm (in the number of attributes)
  • is it NP-complete?
  • optimizations
    • the empty set and the set containing all attributes will never lead to any nontrivial FD's
    • once we determine that a set of attributes S is a superkey, we need not compute the closure of any supersets of S because they will never lead to any new nontrivial FD's

---

Example

---

• consider the relation R(A, B, C, D)
• derive all FD's which follow from S = {A B → C, C → D, D → A}
• look at all subsets of {A,B,C,D} and see which lead to new FD's
• all singletons

  {A}+ = {A}
  {B}+ = {B}
  {C}+ = {A,C,D}
  {D}+ = {A,D}
  

  new FD: C → A
• all pairs

  {A,B}+ = {A,B,C,D}
  {A,C}+ = {A,C,D}
  {A,D}+ = {A,D}
  {B,C}+ = {A,B,C,D}
  {B,D}+ = {A,B,C,D}
  {C,D}+ = {A,C,D}
  

  new FD's: A B → D, B C → A B D → C
• all triples

  {A,B,C}+ = {A,B,C,D}
  {A,B,D}+ = {A,B,C,D}
  {A,C,D}+ = {A,C,D}
  {B,C,D}+ = {A,B,C,D}
  

  no new FD's?
• all subsets of size 4: no need to look at them
• so the complete set of completely nontrivial FD's is {A B → C, C → D, D → A, C → A, A B → D, B C → A, B D → C}
• we need not have computed the closure of sets {ABC}, {ABD}, and {BCD}

---

Basis set of FD's

---

• a set of FD's F is a basis set of FD's iff F⁺ = {all FD's of the relation}
• a basis set of FD's f is a minimal basis iff no proper subset of it is also a basis
• a relation may have several minimal bases

---

Why compute a minimal basis?

---

---

What is a minimal basis (canonical cover) F_c?

---

---

Concept of extraneousness

---

two ways to be extraneous
• extraneous FD, e.g., A → C is an extraneous FD in the set {A → B, B → C, A → C}
• extraneous attribute (on either side of an FD)

AB → C, A → C (B is extraneous in the first FD)
AB → CD, A → C (is any attribute extraneous?)

given X → Y ∈ F

A is extraneous if A ∈ X and F |= (F-(X → Y)) ∪ {(X-A) → Y}

I=X-A

check if F |= I → Y

if Y ⊆ I_F⁺, then A is extraneous

or

A is extraneous if A ∈ Y and (F-(X → Y)) ∪ {(X → (Y-A))} |= F

F^' = (F-(X → Y)) ∪ {X → (Y-A)}

F^' |= X → A

if A ⊆ X_F'⁺, then A is extraneous

example: F = {AB → CD, A → E, E → C}, is C extraneous in the first FD?

ABF'+ = {AB → D, A → E, E → C} = {ABCDE}

The answer is yes.

---

Canonical cover algorithm

---

F_c = F
repeat
use join rule to combine X → Y, X → Z to X → YZ

find an FD in F_c with an extraneous attribute in either X or Y and delete it from X → Y
until you reach a fixpoint for F_c

---

Canonical cover example

---

A → BC
B → C
A → B
AB → C

(C is extraneous in the first FD and A is extraneous in the last FD)

minimal basis: {A → B, B → C}

a minimal basis does not always have the smallest number of FD's, e.g., which of the following two sets of FD's is a minimal basis? {A → B, B → C} or {A → C}

remember a minimal basis must be a basis first

---

Soundness and completeness of algorithms

---

• sound: finds no false positives (returns no wrong answers)
• complete: finds all true positives (returns all right answers)
• ideally we want both
• means Armstrong's axioms are sound and complete

---

FD's in projected relations

---

• what is projection?
• what FD's hold in the projected relation?
• example 5 (courtesy [FCDB] example 3.23, pp. 99-100): R(A, B, C, D) with S = {A → B, B → C, C → D}, project to S(A, C, D), take closure of all subsets, add FD X → E for each attribute E that is in X⁺ and S, but not R

---

References

[DBAA]	M. Kifer, A. Bernstein, and P. M. Lewis. Database Systems: An Application-Oriented Approach. Addison-Wesley, Boston, MA, Second edition, 2006.
[DBSC]	A. Silberschatz, H.F. Korth, and S. Sudarshan. Database Systems Concepts. McGraw Hill, Boston, MA, Fifth edition, 2006.
[FCDB]	J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.