Super Key Candidate Key Primary Key in Database Explained

After reading this article, there will be no confusion in this. This topic will be crystal clear.

Super Key: Super key is the set attributes which enables you to uniquely identify a record in the database.

Let us have a look at this relation.

Example 1:

R(ABCD)
A -> BC 

So what is the super key in this table ?

Actually there is none ! If we figure out the closure of A here it is as follows:

(A)+ = BC

So there is no combination of attributes in this table which lets you to uniquely identify a record. Therefore, there is no super key in this table.

Example 2 :

R(ABCD)
ABC -> D
AB  -> CD 
A   ->  BCD

Finding the closures for ABC, AB and A :

ABC+ = ABCD 
AB+  = ABCD 
A+   = ABCD

In this example, ABC, BC and A -all three are super keys.

Candidate Key: Candidate keys are “such super keys which does not have proper subset which is also a super key”.

In Example 2,

- ABC is not a candidate key because its proper subset AB and A are super keys
-AB is not a candidate key because its proper subset A is a super key
- A is a candidate key because it has no proper subset which is a super key.

Lets see another example:

Example 3:
R(ABCD)
 
B-> ACD
ACD -> B 

Here, B and ACD both are super keys.

What are the candidate keys?

Have a look at the definition of cadidate key once again !

“such super keys which does not have proper subset which is also a super key”

Therefore, both B and ACD are super keys !

Example 4:
R(ABCD)
AB->C
C->BD 
D ->A

Lets find out the super keys and candidate keys for this example.

AB+ = ABCD
C+ = ABCD 

Both AB and C are super keys as well as candidate keys.

Primary key: Primary key is the candidate key chosen by database administrator.

In the previous example, there are two candidate keys : AB and C. The database administrator can choose any of them; the chosen key will be called primary key.

There are many factors to make the choice of primary key. Imagine a scenario where there are two candidate keys in the database: ‘A’ and ‘BCD’. ‘A’ refers to transaction ID and ‘B,C,D’ refers to name, order date and price respectively. A customer may forget the transaction ID(‘A’) but he may be able to remember BCD. In this scenario, BCD can be used as a candidate key.