A key allows us to identify a set of attributes that suffice to distinguish entities from each other. Keys also help uniquely identify relationships, and thus distinguish relationships from each other because no two entities in an entity set are allowed to have exactly the same value for all attributes.

1. Super key

• A super key is a set of one or more attributes that allow us to identify an entity uniquely in the entity set.
• For example, the employee-id attribute of the entity set Employee is sufficient enough to distinguish one employee from another. Thus, employee-id is a super key.
• Similarly, the combination of employee-name and employee-id is a super key for the entity set employee.
• However, the employee-name attribute of employee is not a super key, because several people in an organization might have the same name.

1. Candidate key

• Candidate key is nothing but minimal super keys for which no proper subset is a super key.
• Several distinct sets of attributes could serve as a candidate key.
• Let’s assume that a combination of employee-name and employee-address is sufficient to distinguish among members of the employee entity set. Then, both {employee-id} and {employee-name, employee-address} are candidate keys.
• Although the attributes employee-id and employee-name together can distinguish employee entities, their combination {employee-id, employee-address} does not form a candidate key, since the attribute employee-id alone is a candidate key.

1. Primary key

• The term primary key is used to denote a candidate key that is chosen by database designer as principal means of identifying entities within an entity set.
• Primary key entity in the set cannot have the same value for two or more tuples i.e. unique and it cannot be null.
• Example: Aadhar number of an Indian person or Social security number of US resident would qualify for a good primary key.
• However, since not all countries do not have a unique number associated with every individual, international enterprise must generate their own unique identifiers. An alternative is to use some unique combination of other attributes as a key.
• The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person should not be part of the primary key, since it is likely to change. Social-security numbers, on the other hand, are guaranteed to never change.

1. Foreign key:

• The concept of foreign key is used to establish referential integrity between two relations and maintain consistency among tuples in the two relations.
• The referential integrity constraint simply states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation. By default, foreign key references the primary key attributes of the referenced table.
• Foreign keys can be specified as parts of the SQL CREATE table statement by using the FOREIGN key clause. SQL also supports a version of the references clause where a list of attributes of the referenced relation can be specified explicitly. The specified list of attributes must be declared as a candidate key of the referenced relation.

branch-name char(15) references branch

Following tables illustrates various keys as follows:

Super key: {Emp_id, Emp_name}

Candidate key: Emp_id OR Emp_AadharNo

Primary key: Emp_id (Note: Emp_AadharNo qualifies for primary key too but choice is of database designer)

Foreign key: Emp_id in table Salary referenced from table Employee

Employee | Emp_id | Emp_name | Emp_AadharNo | |--------|----------|--------------| | EMP001 | Jack | INDA204234 | | EMP002 | Jill | INDA203487 | | EMP003 | Hill | INDA983921 |

Salary | Emp_id | Emp_salary | |--------|------------| | EMP001 | 11000 | | EMP002 | 9000 | | EMP003 | 25000 |