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.

Summary
Closure in DBMS | Steps to Find Closure
Article Name
Closure in DBMS | Steps to Find Closure
Description
Closure in DBMS is a set of attributes that can be functionally determined from an attribute set. How to find closure in DBMS- The given steps are followed to find the closure of an attribute set.
Author
Publisher Name
Gate Vidyalay
Publisher Logo
Liked this article? Share it with your friends and classmates now-