Database Keys in DBMS

CODD Rule:

No two tuples of the table should be the same, means one tuple should differentiate from other tuple using same attribute set.

(How we can get this?? — By Key

What is a Key ?

The minimum no. of attributes used to differentiate all the tuples of the relation is called a key. For example in Table 1

Table 1
SID	CID	Sname	Marks	Class	City
S1	C3	Ankit	98	12^th	Bhiwani
S2	C4	Pooja	89	10^th	Hansi
S3	C5	Komal	89	6^th	Bhiwani
S4	C1	Ankit	78	6^th	Ujjain

{SID}      : key in Table 1 ⇒ Because It recognize the tuple uniquely.
{SID, CID} : can be the key but SID is only able to differentiate that is
             is a key. So, no need of CID.

Different Types of Database Keys in DBMS –

Simple Key
Compound Key/Composite Key/Concatenated Key
Primary Key
Super Key
Candidate Key
Alternate Key/Secondary Key
Foreign Key

Let us consider another example to illustrate the concept of different types of database keys in DBMS. Let the Table 2 be a relation of EMPLOYEE, which consists of Employee ID (empID), Employee Name(Ename), Department Number in which the Employee is working(D.No), Date of Birth (DOB), and the Employee Father’s Name (F.Name).

Table 2
empID	Ename	D.No	Passport_No.	DOB	F.Name
S1	Sunny	D1	P-45896	10/90	X
S2	Mohit	D3	P-78952	12/91	Y
S1	Sunny	D2	P-63589	11/92	Y
S3	Ajit	D1	P-98723	10/90	Z

Simple key

If key consists of single attributes. For example,

{SID} : Simple Key ⇒ Because SID is only able to recognize the tuple 
        uniquely.

Compound key/Composite Key/Concatenated Key

If key consists of more than one attribute. In Table 2, No single attribute is able to recognize the tuple uniquely as if empID is choosen as key, then we found duplicate records for S1. So, a compound key will be choosen to identify a tuple/record uniquely of an employee.

{empID,D.NO}   : Compound key/Composite Key/Concatenated Key in Table 2
              ⇒ Because the key consists of two attributes empID and D.NO
                 that recognize the tuple uniquely. Any attribute alone
                 cannot recognize the tuple uniquely.
{empID, Ename} : cannot be the key, because anyone key alone cannot be
                 used to differentiate.
{DOB, F.Name}  : can be the key, but, any one key alone cannot be used to
                 differentiate.
{Passport_No}  : can be the key. Because each employee has its own
                 Passport Number.

Primary key

One of the candidate key becomes primary key that should satisfy some properties. The properties are – .

Primary key do not allow null values.
Values must be distinct.

{SID}          : Primary Key in Table 1.
{empID,D.NO}   : Primary Key in Table 2.

Selection of Primary Key is a part of database optimization. Let us discuss some constraints to select the Primary Key –

Candidate key with no null values should be choosen as Primary Key.
Assume SID and Pno -> not null DBMS provides default index on the primary key.
```
Select *
from emp
where pno=x;
```
if we get results 10% only by using SID as key, and 90% from Pno, then make key which is used to access data more frequency is better choice.
Key with numerical values is better choice rather than choosing Key having character values.
Key with less number of attributes is a better choice. For example, A- NOT NULL 50% chances, integer (1 attribute, only A) BC- NOT NULL 50% chances, integer (2 attribute B & C) choose ‘A’ as primary key as it has only 1 attribute.
Key which is most frequently used to access the Database should be choosen.

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. It is any combination of fields within a table that uniquely identifies each record within that table. Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key

A minimal super key is called a candidate key. There may be more than one keys in a relation such that they recognize a tuple uniquely. (i.e. each of the key have the property of the primary key). But the least combination of fields that uniquely identifies each record in the table is a Candidate Key. The least combination of fields distinguishes a candidate key from a super key. There can be multiple Candidate Keys in one table.

{SID}          : Candidate Key in Table 1
{CID}          : Candidate Key in Table 1
{empID,D.NO}   : Candidate key in Table 2
{Passport_No}  : Candidate Key in Table 2
{empID,F.Name} : Candidate Key in Table 2

Alternate key (Secondary key)

All candidate keys except primary key are secondary key. For example,

CID            : Alternate Key in Table 1
{Passport_No}  : Alternate Key in Table 2
{empID,F.Name} : Alternate Key in Table 2

So, From the above understanding, the SUPER-KEYS will be –

{SID}          : Super Key in Table 1.
{CID}          : Super Key in Table 1.
{empID,D.NO}   : Super Key in Table 2.
{Passport_No}  : Super Key in Table 2.
{empID,F.Name} : Super Key in Table 2.

Foreign Key

A foreign Key is an attribute or (combination of more than one attribute) of a relation (Table) that is the primary key of another relation (Table ). In other words, If we had a table A with a primary key P that linked to a table B where P was a field in B, then X would be a foreign key in B. So, Foreign key is the set of attribute used to reference primary key and alternate key of the same table or some other table. For example,

Table 3
D.NO	D.Location
D1	Rohtak
D2	Bhiwani
D3	Karnal

{D.NO} : Primary Key of Table 3

Consider the Table 3,

D.No is a foreign key in Table 2 since D.NO is a Primary Key in Table 3.

Some Important Points about Super Key

 • Minimal super key is the candidate key.
 • Every minimal key is super key.

Difference between primary key and alternate key-

Primary Key	Alternate Key
Null values not allowed	Null values Allows
Atmost 1 primary key possible	More than 1 alternate keys are possible

Questions On Super Keys – Click Here ]]>