KEYS IN DBMS


Keys In DBMS | DBMS Tutorial | Minigranth

Keys In DBMS : Introduction

  • The keys in DBMS are one of the most important concepts that was introduced along with the introduction of concept of RDBMS. The logic behind use of keys in DBMS is to identify and retrieve records from database in the form of tuples using unique identification concept.
  • Just for an example, Aadhar No. in India and SSN No(Social Security Number) in America can be called as keys which can help in identification of citizens uniquely.
  • Same concept goes for the databases too. Being such a large repository of data, identification and retrieval of data is very difficult. Hence, concept of keys comes handy while retrieving any data(in form of tuple) uniquely. Only a unique attribute can be called as a key. Also, in some cases, combination of multiple attribute forms a key too. We will see the examples soon.
 

Keys in DBMS : Types

  • A major of six types of keys in DBMS are present. These keys are used according to the type of relation and database available. In some cases, multiple keys are also used in order to link two or more than two tables containing data.
  • The objective of introduction of keys is – They help in relationship identification among tables. All the keys are discussed below along with the examples. Stick to this tutorial and you will surely get the concept of keys.

Keys In DBMS : Types
Keys In DBMS : Types

1. Primary Key

  • Primary key is the one which helps in unique identification of each data record present in the tables. It can be a single attribute of the table or combination of attribute.
  • Primary key can also be called as a key which is most suitably chosen from set of candidate keys. Let’ make it this concept of primary key clear with an example.

For example : Consider the data of professors of a college.


Keys In DBMS : Primary Key Example
Keys In DBMS : Primary Key Example

Here, “College ID No.” can be termed as a primary key because it can uniquely identify all the details of the professors easily. Two professor can have same name that is why name is not a primary key.

2. Candidate Key

  • Candidate keys are similar to primary key. The only difference is, primary key for a table can only be one but candidate key can be more than one.
  • Candidate key is also called as minimal super key with each tuple having unique value.
  • The set of multiple keys possible for a relation is called candidate key and when suitable key is selected from that set, it is called primary key.

For example : Consider the data of members in a family. If one needs to be identified uniquely, it can be done by the concept of keys.


Keys In DBMS : Candidate Key Example
Keys In DBMS : Candidate Key Example

Here, Voter Id No, Passport No & PAN No can be termed as candidate keys because each of these can uniquely identify all the records present in the table.

Note: Out of  Voter Id No, Passport No & PAN No, any of can be selected as a primary key because of their uniqueness.

3. Super Key

  • Super key is the one which is able to determine any tuples/data/entity from record table.
  • Super key can be a single attribute or a set of attributes.

For example : Consider the data of students in a class.


Keys In DBMS : Super Key Example
Keys In DBMS : Super Key Example

Here, super key can be a single attribute such as “(Roll No.)” or a collection of multiple attributes such as “(Name, Mobile No)” which can help in identification of record of every student.

4. Foreign Key

  • A foreign key is nothing but an attribute that is commonly linked between two relation using that same attribute. Both the relations/tables must contain the same attribute.

For example : Consider two relations R1(Roll No, Name, Marks) and R2(Roll No, Address, Age) of students of a school. Roll No being the common attribute will act as a key for relation R1 and can reference to Roll No attribute of relation R2 with the help of which any tuple can be retrieved from the tables.

 
Keys In DBMS : Foreign Key Example
Keys In DBMS : Foreign Key Example

5. Composite Key

  • Composite key is the one having combination of more than one attribute to uniquely determine the records/tuples in a table.
  • The only condition that is necessary before choosing composite key is, the attributes which are responsible for forming the composite key must not be a key individually.

For example : Consider the table of student having their details. If we wish to choose a composite key, it can be done as:


Keys In DBMS : Composite Key Example
Keys In DBMS : Composite Key Example

6. Alternate Key

  • Candidate keys can be more than one. Out of which only one is chosen as a primary key. The remaining keys which are not selected are called as alternate key.

Previous Next