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