Keys in DBMS 

Keys in DBMS – CODD Rule:

Before discussing keys in DBMS, you must learn the CODD Rule. CODD Rule says that – 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 in DBMS?

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 12th Bhiwani
S2 C4 Pooja 89 10th Hansi
S3 C5 Komal 89 6th Bhiwani
S4 C1 Ankit 78 6th 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 in DBMS

If key consists of single attributes. For example,

{SID} : Simple Key ⇒ Because SID is only able to recognize the tuple 
        uniquely.
Compound key in DBMS or Composite Key in DBMS or Concatenated Key in DBMS

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. 
Database 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

  1. Candidate key with no null values should be choosen as Primary Key.
  2. 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.

  3. Key with numerical values is better choice rather than choosing Key having character values.
  4. 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.
  5. Key which is most frequently used to access the Database should be choosen.
Super Key in DBMS

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. 
Questions On Super Keys – Click Here

Candidate Key in DBMS

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 in DBMS or Secondary key  in DBMS

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.
Database 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 or secondary key-

Primary Key Alternate Key or Secondary 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


Previous Home Next
Relational Model Types of Constraints in DBMS
or Relational Constraints

     

Incoming search terms:

  • keys in dbms
  • different keys available in dbms
  • key in d
  • keys in dbms with examples
  • what are keys in dbms
  • what is S4 super key
  • alternative key in dbms with example
  • keys in dbms edugrabs
  • super candidate secondary attribute in dbms

Leave a Reply