Keys in DBMS-
| A key is a set of attributes that can identify each tuple uniquely in the given relation. |
Also read- Attributes in DBMS
Different Types Of Keys in DBMS-
There are following 10 important keys in DBMS-
- Super key
- Candidate key
- Primary key
- Alternate key
- Foreign key
- Partial key
- Composite key
- Unique key
- Surrogate key
- Secondary key
NOTE-
Before proceeding further, Kindly note-
- The terms ‘relation’ and ‘table’ are used interchangeably.
- The terms ‘tuple’ and ‘record’ are used interchangeably.
So, don’t get confused!
1. Super Key-
- A super key is a set of attributes that can identify each tuple uniquely in the given relation.
- A super key is not restricted to have any specific number of attributes.
- Thus, a super key may consist of any number of attributes.
Example-
Consider the following Student schema-
Student ( roll , name , sex , age , address , class , section )
Given below are the examples of super keys since each set can uniquely identify each student in the Student table-
- ( roll , name , sex , age , address , class , section )
- ( class , section , roll )
- (class , section , roll , sex )
- ( name , address )
NOTE-
All the attributes in a super key are definitely sufficient to identify each tuple uniquely in the given relation but all of them may not be necessary.
2. Candidate Key-
A minimal super key is called as a candidate key.
OR
A set of minimal attribute(s) that can identify each tuple uniquely in the given relation is called as a candidate key.
Example-
Consider the following Student schema-
Student ( roll , name , sex , age , address , class , section )
Given below are the examples of candidate keys since each set consists of minimal attributes required to identify each student uniquely in the Student table-
- ( class , section , roll )
- ( name , address )
NOTES-
- All the attributes in a candidate key are sufficient as well as necessary to identify each tuple uniquely.
- Removing any attribute from the candidate key fails in identifying each tuple uniquely.
- The value of candidate key must always be unique.
- The value of candidate key can never be NULL.
- It is possible to have multiple candidate keys in a relation.
- Those attributes which appears in some candidate key are called as prime attributes.
3. Primary Key-
A primary key is a candidate key that the database designer selects while designing the database.
OR
Candidate key that the database designer implements is called as a primary key.
NOTES-
- The value of primary key can never be NULL.
- The value of primary key must always be unique.
- The values of primary key can never be changed i.e. no updation is possible.
- The value of primary key must be assigned when inserting a record.
- A relation is allowed to have only one primary key.
Remember-
4. Alternate Key-
Candidate keys that are left unimplemented or unused after implementing the primary key are called as alternate keys.
OR
Unimplemented candidate keys are called as alternate keys.
5. Foreign Key-
- An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its values are dependent on the values of attribute ‘Y’.
- The attribute ‘X’ can assume only those values which are assumed by the attribute ‘Y’.
- Here, the relation in which attribute ‘Y’ is present is called as the referenced relation.
- The relation in which attribute ‘X’ is present is called as the referencing relation.
- The attribute ‘Y’ might be present in the same table or in some other table.
Example-
Consider the following two schemas-
Here, t_dept can take only those values which are present in dept_no in Department table since only those departments actually exist.
NOTES-
- Foreign key references the primary key of the table.
- Foreign key can take only those values which are present in the primary key of the referenced relation.
- Foreign key may have a name other than that of a primary key.
- Foreign key can take the NULL value.
- There is no restriction on a foreign key to be unique.
- In fact, foreign key is not unique most of the time.
- Referenced relation may also be called as the master table or primary table.
- Referencing relation may also be called as the foreign table.
6. Partial Key-
- Partial key is a key using which all the records of the table can not be identified uniquely.
- However, a bunch of related tuples can be selected from the table using the partial key.
Example-
Consider the following schema-
Department ( Emp_no , Dependent_name , Relation )
| Emp_no | Dependent_name | Relation |
| E1 | Suman | Mother |
| E1 | Ajay | Father |
| E2 | Vijay | Father |
| E2 | Ankush | Son |
Here, using partial key Emp_no, we can not identify a tuple uniquely but we can select a bunch of tuples from the table.
7. Composite Key-
A primary key comprising of multiple attributes and not just a single attribute is called as a composite key.
8. Unique Key-
Unique key is a key with the following properties-
- It is unique for all the records of the table.
- Once assigned, its value can not be changed i.e. it is non-updatable.
- It may have a NULL value.
Example-
The best example of unique key is Adhaar Card Numbers.
- The Adhaar Card Number is unique for all the citizens (tuples) of India (table).
- If it gets lost and another duplicate copy is issued, then the duplicate copy always has the same number as before.
- Thus, it is non-updatable.
- Few citizens may not have got their Adhaar cards, so for them its value is NULL.
9. Surrogate Key-
Surrogate key is a key with the following properties-
- It is unique for all the records of the table.
- It is updatable.
- It can not be NULL i.e. it must have some value.
Example-
Mobile Number of students in a class where every student owns a mobile phone.
10. Secondary Key-
Secondary key is required for the indexing purpose for better and faster searching.
Next Article- Finding Candidate Keys of Given Relation
Get more notes and other study material of Database Management System (DBMS).
Watch video lectures by visiting our YouTube channel LearnVidFun.
