Closure Of Functional Dependency

DBMS Tutorial


[fblike]

Closure Of Functional Dependency : Introduction

  • The Closure Of Functional Dependency means the complete set of all possible attributes that can be functionally derived from given functional dependency using the inference rules known as Armstrong’s Rules.
  • If “F” is a functional dependency then closure of functional dependency can be denoted using “{F}+”.
  • There are three steps to calculate closure of functional dependency. These are:

Step-1 : Add the attributes which are present on Left Hand Side in the original functional dependency.

Step-2 : Now, add the attributes present on the Right Hand Side of the functional dependency.

Step-3 : With the help of attributes present on Right Hand Side, check the other attributes that can be derived from the other given functional dependencies. Repeat this process until all the possible attributes which can be derived are added in the closure.

  • Seems difficult? Check out the example explained below and it will surely clear your doubt on how to calculate closure of functional dependency.

 

Closure Of Functional Dependency : Examples

Example-1 : Consider the table student_details having (Roll_No, Name,Marks, Location) as the attributes and having two functional dependencies.

FD1 : Roll_No  Name, Marks

FD2 : Name  Marks, Location

 

Now, We will calculate the closure of all the attributes present in the relation using the three steps mentioned below.

Step-1 : Add attributes present  on the LHS of the first functional dependency to the closure.

{Roll_no}+ = {Roll_No}

Step-2 : Add attributes present on the RHS of the original functional dependency to the closure.

{Roll_no}+ = {Roll_No, Marks}

Step-3 : Add the other possible attributes which can be derived using attributes present on the RHS of the closure. So Roll_No attribute cannot functionally determine any attribute but Name attribute can determine other attributes such as Marks and Location using 2nd Functional Dependency(Name  Marks, Location).

Therefore, complete closure of Roll_No will be :

{Roll_no}+ = {Roll_No, Marks, Name, Location}

 

Similarly, we can calculate closure for other attributes too i.e “Name”.

Step-1 : Add attributes present on the LHS of the functional dependency to the closure.

{Name}+ = {Name}

Step-2 : Add the attributes present on the RHS of the functional dependency to the closure.

{Name}+ = {Name, Marks, Location}

Step-3 : Since, we don’t have any functional dependency where “Marks or Location” attribute is functionally determining any other attribute , we cannot add more attributes to the closure. Hence complete closure of Name would be :

{Name}+ = {Name, Marks, Location}

NOTE : We don’t have any Functional dependency where marks and location can functionally determine any attribute. Hence, for those attributes we can only add the attributes themselves in their closures. Therefore,

{Marks}+ = {Marks}

and

{Location}+ = { Location}

 

Example-2Consider a relation R(A,B,C,D,E) having below mentioned functional dependencies.

FD1 : A  BC

FD2 : C  B

FD3 : D  E

FD4 : E  D

 

Now, we need to calculate the closure of attributes of the relation R. The closures will be:

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

{B}+ = {B}

{C}+ = {B, C}

{D}+ = {D, E}

{E}+ = {E}

 

Closure Of Functional Dependency : Calculating Candidate Key

  • A Candidate Key of a relation is an attribute or set of attributes that can determine the whole relation or contains all the attributes in its closure."
  • Let’s try to understand how to calculate candidate keys.

Example-1 : Consider the relation R(A,B,C) with given functional dependencies :

FD1 : A B

FD2 : B C

 

Now, calculating the closure of the attributes as :

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

{B}+ = {B, C}

{C}+ = {C}

Clearly, “A” is the candidate key as, its closure contains all the attributes present in the relation “R”.

 

Example-2 : Consider another relation R(A, B, C, D, E) having the Functional dependencies :

FD1 : A BC

FD2 : C B

FD3 : D E

FD4 : E D

 

Now, calculating the closure of the attributes as :

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

{B}+ = {B}

{C}+ = {C, B}

{D}+ = {E, D}

{E}+ = {E, D}

In this case, a single attribute is unable to determine all the attribute on its own like in previous example. Here, we need to combine two or more attributes to determine the candidate keys.

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

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

Hence, "AD" and "AE" are the two possible keys of the given relation “R”. Any other combination other than these two would have acted as extraneous attributes.

 

NOTE : Any relation “R” can have either single or multiple candidate keys.

 

Closure Of Functional Dependency : Key Definitions

  1. Prime Attributes : Attributes which are indispensable part of candidate keys. For example : “A, D, E” attributes are prime attributes in above example-2.
  2. Non-Prime Attributes : Attributes other than prime attributes which does not take part in formation of candidate keys. For example.
  3. Extraneous Attributes : Attributes which does not make any effect on removal from candidate key.

 

For example : Consider the relation R(A, B, C, D) with functional dependencies :

FD1 : A BC

FD2 : B C

FD3 : D C

Here, Candidate key can be “AD” only. Hence,

Prime Attributes : A, D.

Non-Prime Attributes : B, C

Extraneous Attributes : B, C(As if we add any of the to the candidate key, it will remain unaffected). Those attributes, which if removed does not affect closure of that set.