Finding the candidate keys for Sub relations using Functional Dependencies

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

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 and BC are the candidate key.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.