Tag: How to find 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.

 

Also Read-How To Find Candidate Keys?

 

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.