CodesJava

Easy learning with example program codes

Keys in Relational Database

 

Understanding Keys in Relational Database

Keys are an integral element of Relational database model. These are used in identifying and establishing relationships between tables. Moreover, keys are necessary in recognizing any row of data or record in a table. These can either be a group of aspects or a single aspect wherein the combination of the same would act as a key.

In order to understand Keys in a better way, we should consider the following two tables-

(table 1)

Student
Name
Student_ID
Course

 

(table 2)

Personal Information
Name
Address
Social_Security_Number
License_Number
Passport_Number

 

We can see in the table no. 1 that the Student_ID of each student would be unique, which makes it the key for this relational database, wherein the table no. 2 contains Social_Security_Number of each person, which acts as the key in this table.

 

Different types of Keys

There are four various types of keys – Primary Key, Candidate Key, Foreign Key, and Super Key. To understand these in detail, read further.

Primary Key

This is the first key that helps in identifying a single instance of an entity, in a distinct manner. There can be numerous keys present in a table, which can be seen in the “Personal Information” table. That one key that is most suitable among other key attributes in a table becomes the Primary Key.

For instance, if we take the Personal Information table, we can see that along with the Social_Security_Number, the License_Number too can become the Primary Key as both these attributes are unique to respective individuals. The selection of any primary key depends on the developer and the specific needs.

 

Candidate Key

This is a set of columns or a single column which helps in uniquely recognizing any database record without referring to any other information. Any attribute apart from the Primary Key is known as the Candidate Key. It is as strong as the Primary Key. For instance, in the Personal Information table, the Social_Security_Number is the Primary Key whereas the License Number or the Passport Number are Candidate Keys.

 

Foreign Key

This is that attribute of a table that is used in identifying a Primary Key of another table. To understand this type of Key in depth, we will consider the following tables-

Employee
Name
Employee_ID
Social_Security_Number
License_Number
Department_ID
Department
Department_ID
Department_Head

 

In these two tables, we see that every employee is from a separate department, which is again an individual entity and therefore cannot be added to the same table of “Employee”. Therefore, it has been added separately to “Department” table. So, to link both these tables, the Primary Key of one table is added to another. So, in the Department table, Department_ID is the Primary Key, which has been added as a Foreign to the Employee table that helps in relating these two tables.

 

Super Key

A combination of columns that helps in uniquely recognizing any row in a table. Candidate Key is related to Super Key wherein the latter is a superset of the candidate key.

 

Please follow and like us:
error
Posted in DBMS   









Copyright © 2019 CodesJava DMCA.com Protection Status