Super Key, Candidate Key and Primary Key

Super Key:

An Attribute or the combination of attributes of the relation is called super key if and only if they derive all the attributes of the relation.

Note:

Super key can be either simple or composite.
A relation can have more than one super key.

in the below example all possible super keys are : A, AB, AC and ABC

Example:

A	B	C
1	2	3
2	2	3
3	2	3
4	3	2
5	2	3
6	3	2

All possible FD’s of the above relation:

A->A : Valid (Determinant A’s values are unique)
A->B : Valid (Determinant A’s values are unique)
A->C : Valid (Determinant A’s values are unique)
B->A : Not Valid (for same determinant B’s value ‘2’, dependent A’s value ‘1’,’2′ is different)
B->B : Valid (Trivial Dependency always valid according to Reflexive Axiom)
B->C : Valid (for same determinant B value, same dependent C value, for different B value C value could be anything hence it is valid)
C->A : Not Valid (for same determinant C value 3 there are 5 different dependent A values hence not valid)
C->B : Valid (for same determinant C value that is 3, same dependent B value that is 2, for different C value B value could be anything hence it is valid)
C->C : Valid (Trivial Dependency always valid according to Reflexive Axiom)
AB->A : Valid (A’s values are unique hence determinant AB’s values will also be unique, hence dependent A’s value could be anything, AB->any attribute of above relation will be valid)
AB->B : Valid (A’s values are unique hence determinant AB’s values will also be unique, hence dependent B’s value could be anything, AB->any attribute of above relation will be valid)
AB->C : Valid (A’s values are unique hence determinant AB’s values will also be unique, hence dependent C’s value could be anything, AB->any attribute or attributes combination of above relation will be valid)
BC->A : Not Valid (for same value {2,3} of determinant BC there are two values {1},{2}of dependent A, hence not valid)
BC->B : Valid (for same value {2,3} o determinant BC, same value {2} of dependent B exist. this holds for any combination of determinant to dependent. hence it is valid)
BC->C : Valid (for same value {2,3} o determinant BC, same value {3} of dependent C exist. this holds for any combination of determinant to dependent. hence it is valid)
CA->A : Valid (A’s values are unique hence determinant AC’s or CA’s values will also be unique, hence dependent A’s value could be anything, CA->any attribute or attributes combination of above relation will be valid)
CA->B : Valid (A’s values are unique hence determinant AC’s or CA’s values will also be unique, hence dependent B’s value could be anything, CA->any attribute or attributes combination of above relation will be valid)
CA->C : Valid (A’s values are unique hence determinant AC’s or CA’s values will also be unique, hence dependent C’s value could be anything, CA->any attribute or attributes combination of above relation will be valid)
ABC->A : Valid (A’s values are unique hence determinant ABC’s values will also be unique, hence dependent C’s value could be anything, ABC->any attribute or attributes combination of above relation will be valid)
ABC->B : Valid (A’s values are unique hence determinant ABC’s values will also be unique, hence dependent B’s value could be anything, ABC->any attribute or attributes combination of above relation will be valid)
ABC->C : Valid (A’s values are unique hence determinant ABC’s values will also be unique, hence dependent C’s value could be anything, ABC->any attribute or attributes combination of above relation will be valid)

Candidate Key:

An attribute or the combination of attributes is called candidate key if and only if:

They derive all the attributes of the relation.
They are the minimal subset of the super key.

Note:

Candidate keys can be either simple or composite.
Minimal subset is not with respect to the no of attributes however it always refer to the minimal level of subset which does not have any proper subsets that derives all the attributes of the relation.
A relation can have more than one candidate key.

Example:

From above example if we take all the super keys then

Super Key: ABC’s proper subsets are Φ, A, B, C, AB, BC, AC, ABC in which A, AB, AC derives all the attributes of the relation hence ABC will not be a candidate key.

Super Key: AB’s proper subsets are Φ, A, B, AB in which A derives all the attributes of the relation hence AB will also not be a candidate key.

Super Key: AC’s proper subsets are Φ, A, C, AC in which A derives all the attributes of the relation hence AC will also not be a candidate key.

Super Key: A’s proper subsets are Φ, A where no proper subset derive all the attributes of the relation. Hence A is the candidate key of the relation.

Candidate Key VS Primary Key:

Candidate Key	Primary Key
Candidate Keys can either be simple or composite	Primary keys can also be simple or composite
There can be many candidate keys possible in a relation	Out of all the candidate keys only one and only one will be primary key
Candidate key attribute or in case of composite candidate key attributes should be unique and not null	Column selected for primary key should also be unique and not null
No Index would get created implicitly on the candidate key column	Once the primary key is declared, a clustered Index will get created automatically with the sorted data by default

Note:

In case of the none of the determinants of the given functional dependencies, derives all the attributes, then we shall choose the best closure based on more no of attributes derived and then combine the missing attribute to the best closure and make sure it is minimal subset before it gets declared as candidate key.

Recommended Articles: Prime and Non Prime Attributes