Explain closure of attributes in DBMS

DBMSDatabaseBig Data Analytics

Closure of an attribute x is the set of all attributes that are functional dependencies on X with respect to F. It is denoted by X+ which means what X can determine.

Algorithm

Let’s see the algorithm to compute X+

  • Step 1 − X+ =X
  • Step 2 − repeat until X+ does not change
    • For each FD Y->Z in F
      • If Y ⊆ X+ then X+ = X+ U Z

Example 1

Consider a relation R(A,B,C,D,E,F)

F: E->A, E->D, A->C, A->D, AE->F, AG->K.

Find the closure of E or E+

Solution

The closure of E or E+ is as follows −

  E+ = E
    =EA       {for E->A add A}
    =EAD      {for E->D add D}
    =EADC     {for A->C add C}
    =EADC     {for A->D D already added}
    =EADCF    {for AE->F add F}
    =EADCF    {for AG->K don’t add k AG ⊄ D+)

Example 2

Let the relation R(A,B,C,D,E,F)

F: B->C, BC->AD, D->E, CF->B. Find the closure of B.

Solution

The closure for B is as follows −

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

Closure is used to find the candidate keys of R and compute F+

Candidate key of R: X is a candidate keys of R if X->{R}

For example,

R(A,B,C,D,E,F) WHERE F:A->BC, B->D, C->DE, BC->F. Then, find the candidate keys of R.

Solution

A+= {A,B,C,D,E,F}={R}=>A is a candidate key

B+= {B,D} => B is not a candidate key

C+= {C,D,E} => C is not a candidate key

BC+= {B,C,D,E,F} => BC is not a candidate key

Closure of F (F+): F+ is the set of all FDs that can be inferred/ derived from F. Using Armstrong Axioms repeatedly on F, we can compute all the FDs.

Example

R(A,B,C,D,E) AND F: A->B,B->C, C->D, A->E. Find the closure of F

Solution

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

B+= {B,C,D}

C+= {C,D}

F+= {A->A, A->B, A->C, A->D, A->E, B->B, B->C, B->D, C->C, C->D}

raja
Published on 03-Jul-2021 09:16:07
Advertisements