Key attribute: Minimal no. of the attribute used to differentiate all tuples of the relation (table).
Student:
Note: Assume that no two students with same DOB and Fname.
Because Sid is a Key Attribute Set, the combination of any non-key attribute to any key, also differentiates all tuples like Sid Sname. It also differentiates all tuples but Sid Sname is not a key because it is not minimal.
Note: Key must be minimal.
Candidate Key:
C.K = K.A.S
Candidate key is same as Key Attribute Set i.e. Minimal no. of attributes used to differentiate all tuples in a table.
So, C.K = {Sid, PPNO, LNO, DOBFname}
We got 4 candidate keys here.
Primary Key: We can choose any one candidate key and make it the primary key, (It is always better to choose a single attribute if present).
Let us choose Sid as the primary key.
P.K = {Sid}.
Alternative key:
The rest of the candidate keys except the primary key will be alternative key.
A.K= {PPNO, LNO, DOBFname}
NULL value is not allowed at primary key but at alternate key ‘NULL value’ are allowed.
Super Key: Set of attributes used to differentiate all tuples of the relation.
Note: In this above definition we do not mention the term minimal that means every attribute or set of attributes that uniquely differentiate all tuples is super key.
Student:
So, every candidate key is super key.
And the combination of all non-keys with the key is also super key because super key need not be minimal.
Like Sid → S.K, DOBFname → S.K
But SidSname → S.K (super key) but not candidate key because it is not minimal.
So, SidSname, SidPPNO, SidDOB, Sname DOBFname etc. all are super keys. But Sname, DOB is not super key because it not differentiate all tuples.
Every candidate key is S.K but every super key is not candidate key.
Q: UGC NET 2016, paper-2
Consider the following database table following table having A,B,C and D as its four attributes and four possible candidate keys (I,II,III and IV) for this table:
A | B | C | D |
a1 | b1 | c1 | d1 |
a2 | b3 | c3 | d1 |
a1 | b2 | c1 | d2 |
I. {B} II. {B,C} III. {A,D} IV. {C,D}
If different symbols stand for different values in the table (e.g., d1 is definitely not equal to d2), then which of the above could not be the candidate key for the database table?
A. I and II only
B. III and IV only
C. II only
D. I only
Solution:
From the table we observe,
Attributes A, C, D – all have duplicate values. So, they individually can’t be primary key (or candidate key), as PK only takes unique values.
So, option (c) is the correct answer.
Q: UGC NET- 2017-JAN- paper-III, Q8
Which one is correct w.r.t RDBMS?
A.
B.
C.
D.
Solution:
So, candidate key ⊆ super key.
So, primary key ⊆ candidate key
So, option (b) is correct.
(PK ⊆ CK ⊆ SK)