Tag: Attribute Closure in DBMS

Closure in DBMS | Steps to Find Closure

Closure of an Attribute Set-

 

  • The set of all those attributes which can be functionally determined from an attribute set is called as a closure of that attribute set.
  • Closure of attribute set {X} is denoted as {X}+.

 

Steps to Find Closure of an Attribute Set-

 

Following steps are followed to find the closure of an attribute set-

 

Step-01:

 

Add the attributes contained in the attribute set for which closure is being calculated to the result set.

 

Step-02:

 

Recursively add the attributes to the result set which can be functionally determined from the attributes already contained in the result set.

 

Example-

 

Consider a relation R ( A , B , C , D , E , F , G ) with the functional dependencies-

A → BC

BC → DE

D → F

CF → G

 

Now, let us find the closure of some attributes and attribute sets-

 

Closure of attribute A-

 

A+   = { A }

= { A , B , C }                          ( Using A → BC )

= { A , B , C , D , E }               ( Using BC → DE )

= { A , B , C , D , E , F }          ( Using D → F )

= { A , B , C , D , E , F , G }    ( Using CF → G )

Thus,

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

 

Closure of attribute D-

 

D+   = { D }

= { D , F }   ( Using D → F )

We can not determine any other attribute using attributes D and F contained in the result set.

Thus,

D+ = { D , F }

 

Closure of attribute set {B, C}-

 

{ B , C }+= { B , C }

= { B , C , D , E }               ( Using BC → DE )

= { B , C , D , E , F }          ( Using D → F )

= { B , C , D , E , F , G }    ( Using CF → G )

Thus,

{ B , C }+ = { B , C , D , E , F , G }

 

Finding the Keys Using Closure-

 

Super Key-

 

  • If the closure result of an attribute set contains all the attributes of the relation, then that attribute set is called as a super key of that relation.
  • Thus, we can say-

“The closure of a super key is the entire relation schema.”

 

Example-

 

In the above example,

  • The closure of attribute A is the entire relation schema.
  • Thus, attribute A is a super key for that relation.

 

Candidate Key-

 

  • If there exists no subset of an attribute set whose closure contains all the attributes of the relation, then that attribute set is called as a candidate key of that relation.

 

Example-

 

In the above example,

  • No subset of attribute A contains all the attributes of the relation.
  • Thus, attribute A is also a candidate key for that relation.

 

PRACTICE PROBLEM BASED ON FINDING CLOSURE OF AN ATTRIBUTE SET-

 

Problem-

 

Consider the given functional dependencies-

AB → CD

AF → D

DE → F

C → G

F → E

G → A

 

Which of the following options is false?

(A) { CF }+ = { A , C , D , E , F , G }

(B) { BG }+ = { A , B , C , D , G }

(C) { AF }+ = { A , C , D , E , F , G }

(D) { AB }+ = { A , C , D , F ,G }

 

Solution-

 

Let us check each option one by one-

 

Option-(A):

 

{ CF }+  = { C , F }

      = { C , F , G }                     ( Using C → G )

      = { C , E , F , G }               ( Using F → E )

      = { A , C , E , E , F }          ( Using G → A )

      = { A , C , D , E , F , G }    ( Using AF → D )

 

Since, our obtained result set is same as the given result set, so, it means it is correctly given.

 

Option-(B):

 

{ BG }+  = { B , G }

      = { A , B , G }                   ( Using G → A )

      = { A , B , C , D , G }        ( Using AB → CD )

 

Since, our obtained result set is same as the given result set, so, it means it is correctly given.

 

Option-(C):

 

{ AF }+  = { A , F }

      = { A , D , F }               ( Using AF → D )

      = { A , D , E , F }          ( Using F → E )

 

Since, our obtained result set is different from the given result set, so,it means it is not correctly given.

 

Option-(D):

 

{ AB }+  = { A , B }

      = { A , B , C , D }            ( Using AB → CD )

      = { A , B , C , D , G }      ( Using C → G )

 

Since, our obtained result set is different from the given result set, so,it means it is not correctly given.

Thus,

Option (C) and Option (D) are correct.

 

Next Article- 10 Different Kinds of Keys in DBMS

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.