Keys In DBMS

 

KEYS In DBMS:

A key is an attribute or combination of attributes which can be used for identification of each record/row of data in a table.

Why we need keys in DBMS?

1.     To identify each row of a table uniquely

2.     To maintain relationship between tables

Types of Keys:

1.     Super key

2.     Candidate key

3.     Primary key

4.     Alternate key

5.     Foreign key

6.     Composite key

7.     Compound key

8.     Surrogate key

 

Super key:

·       A super key is nothing but a key. It is an attribute or combination of attributes for unique identification of each row of a table.

Employee table:

E_No	E_Id	E_Name	Role	E_Email
1	D-0001	Ram	Developer	ram@gmail.com
2	D-0002	Sai	Developer	Sai@gmail.com
3	T-0003	Sai	Tester	Sairam@gmail.com
4	A-0004	Krish	Architect	krish@gmail.com

 

Example:

       i.          E_No

      ii.          E_Id

    iii.          E_Email

    iv.          E_No+E_Id

      v.          E_No+ E_Email

    vi.          E_Id+ E_Email

   vii.          E_No+E_Id+E_Email

Candidate key:

·       It is nothing but a minimal subset of a super key.

·       It is minimal attribute or combination of attributes for unique identification of each row of a table.

Example:

       i.          E_No

      ii.          E_Id

    iii.          E_Email

Primary key:

·       A Primary key is a candidate key which can be used for unique identification of each row of a table.

·       PK not accepts null values & also repeated values.

Example:

In below attributes we can take one as PK

       i.          E_No

      ii.          E_Id

    iii.          E_Email

 

Alternate key:

The candidate keys which are not chosen as primary key are called alternate keys

Example:

In above PK examples without PK remaining all comes under Alternate key

 

Foreign key:

·       It is used to maintain relationship between 2 tables.

·       FK must be PK in the same table/in another table.

·       FK value must match with any of its PK value or it may be NULL.

·       FK promotes data integrity 

E_No	E_Id	E-Name	Role(FK)	E_Email
1	D-0001	Ram	Developer	ram@gmail.com
2	D-0002	Sai	Developer	Sai@gmail.com
3	T-0003	Sai	Tester	Sairam@gmail.com
4	A-0004	Krish	Architect	krish@gmail.com

Role(PK)	Team_name	Manager
Developer	Dev team	X
Tester	Test team	Y
Architect	BA team	Z

 

 

 

Composite key:

The combination of attributes for unique identification of each row of a table

Example:

       i.          E_No+E_Id

      ii.          E_No+ E_Email

    iii.          E_Id+ E_Email

Compound key:

If a composite key contains any one of the attribute as a FK attribute then that is called as Compound key

Example:

E_No + Role

Surrogate key:

If there is no any attribute in a relation for unique identification then we can create a new attribute which is used for unique identification