Open In App

Finding the candidate keys for Sub relations using Functional Dependencies

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

We know how to find candidate keys for a relation already, you can check here Finding Attribute Closure and Candidate Keys using Functional Dependencies. But here we will learn to find out the candidate keys for a sub relation which is a little bit different from that of finding out candidate keys for a relation. Let see how to find it. 

What is Functional dependency? 
A functional dependency is simply a constraint between two sets of attributes from the database. A functional dependency is used in normalization. A functional dependency is denoted by an arrow → . The functional dependency of A on B is represented by A → B. Functional Dependency plays a vital role in finding the difference between good and bad database design. 

 

A→B

Above Functional dependency is pronounced as : 
 

  1. A determines B 
     
  2. A functionally determines B 
     
  3. B is functionally dependent on A 
     
  4. For a given value of A, we can functionally determine the value of B. 
     

A and B can be a set of attributes, they need not be single attributes always. 
 

Example : 
AB→B,  A→ ACB etc. 

With a given relation, we are also given a set of Functional dependencies most of the time according to the semantics of the database design. 

Finding the candidate keys for Sub relations using Functional Dependencies: 
To find out the candidate keys for a sub relation is a little bit different from that of finding out candidate keys for a relation. Let see how to find it. 

In this problem, we generally have a table(universal table) or database and have some functional dependencies applied to the relation. 

And we will break the table into smaller tables, and we need to find the candidate keys for the newly decomposed smaller tables. 

Example:. 
A relation R(ABCD) is given with functional dependencies F: { AB→CD, D→A }. Find out the candidate keys of the sub relation R'(BCD). 

Step-1: Finding the closure of one valued attribute in sub relation: 
{B}+ = B, cant derive all the attributes present in the sub relation i.e BCD, so its not a candidate key. 
{C}+ = C, cant derive all the attributes present in the sub relation i.e BCD, so its not a candidate key. 
{D}+ = D A, cant derive all the attributes present in the sub relation i.e BCD, so its not a candidate key. 

Step-2: Finding the closure of two-valued attribute in sub relation: 
{BC}+ = B C, cant derive all the attributes present in the sub relation i.e BCD, so its not a candidate key. 
{BD}+ = B D A C, can derive all the attributes present in the sub relation i.e BCD, so its a candidate key. 
{CD}+ = C D A, cant derive all the attributes present in the sub relation i.e BCD, so its not a candidate key. 

Step-3: Finding the closure of three-valued attribute in sub relation: 
{BCD}+ =, Not possible, since it’ll become the super key of BD. 

Therefore, BD hast= the capacity to uniquely derive all the attributes present in the sub relation i.e BCD. 
Therefore BD is the candidate key. 

Example-2: 
A relation R(ABCDE) is given with functional dependencies F: { A→BC, CD→E, B→D, E→A }. Find out the candidate keys of the sub relation R'(ABCE). 

Step-1: Finding the closure of one valued attribute in sub relation: 
{A}+ = A B C D E, can derive all the attributes present in the sub relation i.e ABCE, so its a candidate key 
{B}+ = B D, cant derive all the attributes present in the sub relation i.e ABCE, so its not a candidate key 
{C}+ = C, cant derive all the attributes present in the sub relation i.e ABCE, so its not a candidate key 
{E}+ = A B C D E, can derive all the attributes present in the sub relation i.e ABCE, so its a candidate key 

Step-2: Finding the closure of two valued attribute in sub relation: 
A and E attribute can be include in 2 valued attribute, since their inclusion will make it super key. 

{BC}+ = B C D E A 

{CD}+ = A B C D E

Step-3: Finding the closure of three valued attribute in sub relation: 
Not possible, since it’ll become super key. 

Therefore, A, E and BC has the capacity to uniquely derive all the attributes present in the sub relation i.e ABCE. 
Therefore A, E, CD and BC are the candidate key.
 


Last Updated : 25 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads