FOR FREE MATERIALS

Super Key, Candidate Key, Primary Key:

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:

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.

• Only attribute B has unique values b1, b3 & b2. So, B will be the candidate key, also it is super key.
• BC can’t be CK, as B is already a candidate key i.e., minimal super key. BC can be SK but not CK.
• AD and CD are candidate keys as they have distinct tuples.
• So, only BC can’t become CK

So, option (c) is the correct answer.

Q: UGC NET- 2017-JAN- paper-III, Q8

Which one is correct w.r.t RDBMS?

A. $primary key \subseteq super key \subseteq candidate key$

B. $primary key \subseteq candidate key \subseteq super key$

C. $super key \subseteq candidate key\subseteq primary key$ 

D. $super key \subseteq primary key\subseteq candidate key$ 

Solution:

• Super Key is the set of attributes used to uniquely identify each tuple of a relational schema.
• Candidate key is minimal super key, i.e., a minimal set of attributes uniquely identify each type of R.

So, candidate key ⊆ super key.

• One of the candidate keys are arbitrarily chosen as a primary key, rest of the candidate keys, who are not selected as primary key, are known as an alternate key.

So, primary key ⊆ candidate key

So, option (b) is correct.

(PK ⊆ CK ⊆ SK)