The relational database relies heavily on keys. It is employed to identify each record or data row from the table in a unique manner. Establishing and recognizing relationships between tables is another use for it.
DBMS introduced keys to prevent data redundancy. Keys facilitate quick and easy identification of the distinct records contained in a large box that is represented by a table. One or more attributes that together uniquely identify the records can serve as keys in database management systems (DBMSs). Selecting the smallest set of attributes necessary to achieve a given outcome is more efficient than using every attribute as a key.
For Example
ID is specific to each student, it serves as a key in the Student table. Since each person's passport number, license number, and SSN are unique, they are considered keys in the PERSON table.
Types of Keys in DBMS
- Primary Key
- Candidate Key
- Super Key
- Foreign Key
- Alternate Key
- Composite Key
Primary Key
It is the initial key that is utilized to uniquely identify one and only one instance of an entity. As the PERSON table demonstrated, an entity can have more than one key. From such lists, the most appropriate key is designated as the primary key.
Given that ID is distinct for every employee, it may serve as the primary key in the EMPLOYEE database. We can even designate License_Number and Passport_Number as primary keys in the EMPLOYEE table because they are both unique.
The primary key decision for each entity is determined by the developers and needs.
- It's a special key.
- It is limited to identifying a single tuple, or record, at once.
- It has unique values and no duplicates.
- It isn't capable of becoming NULL.
- A table's main key does not always have to be one column; a table might have primary keys in multiple columns.
Candidate Key
An characteristic or group of attributes that can be used to uniquely identify a tuple is called a candidate key.All other properties are regarded as candidate keys, with the exception of the primary key. Both the main key and the candidate keys have the same strength.
A candidate key is the bare minimum collection of characteristics needed to identify a tuple uniquely. Take STUD_NO in the STUDENT relation, for instance.
- It's a simple yet powerful key.
- A candidate key is a super key that has no duplicate data.
- The bare minimum of characteristics needed to distinguish one record from another.
- It needs to have distinct values.
- It may have NULL values in it.
- There needs to be at least one potential key in each table.
- There can be more than one candidate key in a table, but only one primary key.
- The Candidate Key's value is distinct and could be null in the case of a tuple.
- In a relationship, more than one candidate key may exist.
For Example
The primary key in the EMPLOYEE table should be id. The remaining properties, such as License_Number, SSN, Passport_Number, and so on, are regarded as potential keys.
Super Key
An attribute set known as a super key is capable of uniquely identifying a tuple. A candidate key's superset is known as a super key.
Super Key is the collection of characteristics that allow a tuple to be uniquely identified. Take STUD_NO, (STUD_NO, STUD_NAME), etc. as examples. A set of one or more keys used to identify rows in a database is called a super key. NULL values are supported.
- The super key is created by adding zero or more attributes to the candidate key.
- Super keys are candidate keys, but the opposite is not true.
- Values for Super Keys could potentially be NULL.
For Example
Two employees' names may match in the aforementioned EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), but their EMPLYEE_IDs may not. Therefore, this combination might potentially be important.
EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc. would be the super key.
Foreign Key
A foreign key is a column in one table that points to another table's main key.
Within a firm, each employee is assigned to a department; nevertheless, the departments and the employees themselves are two distinct entities. Therefore, the department's data cannot be kept in the employee table. For this reason, we use one table's main key to connect the other two tables.
We create a new attribute in the EMPLOYEE table called Department_Id, which is the main key of the DEPARTMENT table.
Department_Id is the foreign key in the EMPLOYEE table, and there is a relationship between the two tables.
- It's a key that functions as both a secondary key in one table and a primary key in another.
- It simultaneously combines two or more relations, or tables.
- They serve as a point of reference for the tables.
For instance, DNO is a non-key in the EMP table and a main key in the DEPT table.
Alternate Key
A relation's tuples can be uniquely identified by one or more attributes, or by a combination of attributes. The terms "candidate keys" refer to certain characteristics or sets of characteristics. Between these candidate keys, one is selected as the primary key and the other, if any, is designated as the alternate key. Stated otherwise, the total count of alternate keys is equal to the total count of candidate keys minus the primary key. It's possible that there isn't an alternate key. In a relation with only one candidate key, there isn't a backup key.- Alternate keys are any keys that are not primary keys.
- It's a backup key.
- To identify two or more records, it has two or more fields.
- Repeated are these values.
- Examples of alternate keys include SNAME and ADDRESS.
For Example
The candidate keys for an employee relation are Employee_Id and PAN_No. PAN_No, the other candidate key, serves as the alternate key in this relation since Employee_Id is selected as the primary key.
Composite Key
A main key is referred to as a composite key whenever it contains many attributes. Concatenated Key is another name for this key. An attribute or column that uniquely identifies every record in a table may not always be present in that table. Two or more columns/attributes can be combined to uniquely identify rows in a table. It may occasionally still provide duplicate values. To identify rows in a table in a unique way, we must thus determine the best combination of characteristics.
- If a table doesn't have a primary key, it serves as one.
- A composite key consists of two or more properties combined.
- The accuracy with which the rows may be uniquely identified may vary depending on the combination of criteria used.
For Example
In employee relations, it's common to anticipate that a worker may be given various jobs and engage in multiple projects at once. Therefore, the combination of all three attributes—Emp_ID, Emp_role, and Proj_ID—will make up the main key. The fact that the primary key consists of multiple attributes means that these features function as a composite key.
