Super Key (SK):
A super key is a set of columns that uniquely defines a row.
Let’s consider below table,
From above table we can see,
Consider one column :
At column “BookID” all fields are unique, So BookID is a SK. [BookID]
At column “Name” all fields are not unique (XYZ, ABC are duplicate). So this is not SK.
At column “Author” all fields are not unique (A1, A2, A3 are duplicate). So this is not SK.
Consider tow Columns :
“BookID” and “Name” the each row’s value is unique. So [BookID, Name] is SK.
“BookID” and “Author” the each row’s value is unique. So [BookID, Author] is SK.
“Name” and “Author” the each row’s value is unique. So [Name, Author] is SK.
N.B : Lets add a new row under Book_Table which value is B7, ABC, A1
If so that [Name, Author] is not SK. Because we get duplicate rows which value is ABC, A1 (At row 2 and 7).
Consider three Columns :
“BookID”, “Name” and “Author” the each row’s value is unique. So [BookID, Name, Author] is SK.
N.B : We know that BookID is unique, so in [BookID, Name, Author] this relation we can easily say that whatever the value of Name and author is [BookID, Name, Author] is always unique because of BookID. So in this relation Name and Author are redundant attributes.
Candidate Key :
Candidate Keys are super keys for which no proper subset is a super key. In other words candidate keys are minimal super keys.
1. A superkey without redundancy.
2. Not reducible.
3. More than one possible candidate key we may found in one solution.
R (A, B, C, D, E, F)
A => C [A determines C], C => D [C determines D], D => B [D determines B], E => F [E determines F]
Here candidate key is AE. Because by using AE we can take all the tuples or columns or items of R.
AE => ACDBEF
So AE is candidate key.
N.B: Candidate keys are always one type of super key.
Primary Key :
When we design a database system than we should take candidate key which is known as primary key.
But if we get 2 candidate key (AB and AC) and if database designer chooses a candidate (lets consider he choose AC) for design purpose than AC will consider as primary key.
N.B : Primary is one of a candidate key that is used for next implementation by database designer.