Keys in DBMS (Database Management System)

In this article, we will be talking about the Keys in DBMS. In the Database, there are a lot of tables created with lots of attributes. All of them have their use and properties. So, it is mandatory to have some relation between the tables and also to query from those tables.

Keys in DBMS

Keys in DBMS

Keys in DBMS play a very vital role. It is very mandatory to have some unique identifier in all the tables that are stored in the database.

Let us understand this directly by taking an example.

Consider the following Employee Table

ID Name Salary Phone
1001 John 12000 1XXXXX
1002 Alice 27000 9XXXXX
1003 John 12000 1XXXXX
1004 John 80000 5XXXXX
1005 Robin 40000 0XXXXX

 

In this Table, if you have instructed the database to increment the salary of the Employee by 10% whose name is John.

See, also Functional Dependency in DBMS

As you can see, the Employee table has 2 entries by the name John and they are referring to two different people. So, as per the given query, this will increment the salary of both John, which is wrong.

So, here comes the Keys in DBMS. the ID column in the Employee table is the key which will be unique for every single entry getting created in the database.

The query can be written easily with the help of Keys in DBMS. You can query like increment the salary of a person by 10% whose ID is 1004.

2 entries with same NAME attributes but with different IDs

In short, Keys in DBMS are required to identify the tuples uniquely.

Types of Keys in DBMS

There are different types of Keys in DBMS available and they are:

  • Super Key
  • Candidate Key
  • Primary Key
  • Alternate Key
  • Unique Key
  • Composite Key
  • Foreign Key

Let’s understand each of the Keys in DBMS one by one,

Super Key in DBMS

Super Key is like a superset, from which another key can be derived. It is used to uniquely identify the tuple. Super Key will not be NULL and will always be unique. This is the first key in the article Keys in DBMS.

Super Key may contain extraneous attributes. Let’s understand this by example.

ID Name Salary Phone
1001 John 12000 1XXXXX
1002 Alice 27000 9XXXXX
1003 John 12000 1XXXXX
1004 John 80000 5XXXXX
1005 Robin 40000 0XXXXX

 

In the above table, ID is a super key as it will be always unique because the employee ID for every employee is different and unique, Name cannot be a super key as the same name is possible for 2 different people. Salary cannot be a super key as the same salary for more than 1 employee is possible, and Phone can also not be a super key as the employee may have a phone number or not, possible to have null values.

Also, the combination of the super keys is possible in this table the possible super key is:

{ID}, {ID, NAME}, {ID, SALARY}, {ID, PHONE}, {ID, NAME, PHONE}, etc.

So, these multiple values are the superset, that’s the reason the super key has various attributes.

Candidate Key in DBMS

The next key in Keys in DBMS is Candidate Key. Candidate Key can be derived from the Super Key. The minimal super keys are referred to as the candidate key.

The Super keys are {ID}, {ID, NAME}, {ID, SALARY}, {ID, PHONE}, {ID, NAME, PHONE}, {SALARY, PHONE} etc.

From these Super Keys, the candidate keys will be {ID}, {SALARY, PHONE}.

{ID, NAME} can’t be considered as the candidate key as ID is already a candidate key, same with {ID, SALARY}, {ID, PHONE}, {ID, NAME, PHONE}.

Primary Key in DBMS

Primary keys are the keys in DBMS that are used to denote the candidate keys. primary keys are always unique and cannot contain any NULL values. This is the third key in the article Keys in DBMS.

From the above discussion, the candidate keys are {ID}, {SALARY, PHONE}.

Among these mentioned candidate keys, only the {ID} column will be having unique values and will not be having any NULL values. Rest other candidate keys like {SALARY, PHONE} can have null values also possible that can have duplicate entries also.

Therefore, the primary key is only {ID} in this case.

Remember that the primary key is always chosen from the set of candidate keys.

The primary key is always chosen with care by the Database Administrator (DBA), as he/she is the only person who can access the full database and who has the right to choose the primary key.

Do you know who DBA is, if not then read Classification of DBMS Users.

In a  table, there can be a set of Super Keys, and from that set of candidate keys can be derived, and from the set of candidate keys, there will be one primary key.

The primary key is the attribute which will never be changed or possibly have changed very rarely.

A candidate key with NULL values should never be chosen as the Primary Key.

In simple terms, Primary Key = UNIQUE + NOT NULL

Alternate Key in DBMS

The alternate Key is the set of the candidate key other than the primary key. This is the fourth key in the article Keys in DBMS.

From the above discussion,

The candidate keys are {ID}, {SALARY, PHONE}.

The primary key is only {ID}.

Therefore the Alternate Keys are {SALARY, PHONE}.

Unique Key in DBMS

The next key in Keys in DBMS is Unique Key. Unique Keys are the key that should be unique in terms of values, but they can also have NULL values.

From the above discussion,

The candidate keys are {ID}, {SALARY, PHONE}.

The primary key is only {ID}.

The Alternate Keys are {SALARY, PHONE}.

Therefore, the unique keys are {SALARY, PHONE}.

Composite Key in DBMS

Composite Keys are keys which are comprised of more than 1 attribute.

From the above discussion,

The candidate keys are {ID}, {SALARY, PHONE}.

The primary key is only {ID}.

The Alternate Keys are {SALARY, PHONE}.

The unique keys are {SALARY, PHONE}.

Therefore, the composite keys are {SALARY, PHONE}.

Foreign Key in DBMS

Foreign Key in DBMS is different from all the Key in DBMS which is being discussed till now, as Foreign key is applicable on 2 tables. This is the last key in the article on Keys in DBMS.

Let us understand this by example by taking 2 tables Student and Department.

S_ID S_name Dept_code Credits
1001 John 101 13
1002 Alice 102 14
1003 Alya 103 17
1004 Ria 104 10

This is the Student Table

Dept_code Dept_name
101 CSE
102 ECE
103 EEE
104 CHEM

This is the Department Table

From these two tables, the Student Table is dependent on the Department Table as Student Table can only have those records from which the department exists in the Department Table.

The dept_code attribute of the student table is inherited from the Dept_code of the Department Table. This type of inheritance is known as Referential Integrity and is known as Foreign Keys in DBMS relations.

Types of Keys in DBMS

In simple terms, Referential Integrity is achieved by Foreign Keys in DBMS.

Keys in DBMS are essential and play a vital role in managing the database and accessing the data in a well-mannered form. It also helps in avoiding redundancy and is managed by the DBA. People pursuing Database studies should be aware of the Keys in DBMS.