E-R and EER Models: Keys

A key is an attribute or set of attributes that is used to identify data in entity sets. The attributes which are used as key are known as key attributes. Rest of all are known as Non-key attributes.

1. Types of Keys

There are many keys that are used in the different tables. These are as follows:

1. Super Key : A super key is a set of collection of one or more than one attributes that can identify data uniquely.

Any entity set has more than one super key.

Ex. In entity set  Employee, shown in  Figure 2.9(a), Super  Keys are

• 1. (ID, Name, Salary, No.)
  2. (ID, Name, No.)
  3. (ID)

All combinations can identify data uniquely.

2. Candidate Key : The minimal super key is known as candidate Consider a super key and then take all of its proper subsets. If no one of the proper subsets are super key. Then this super key  is taken  as candidate  key.

Ex. ID and  Reg. No.  are candidate  key

Example: Find all possible candidate keys for the following relation based on its current tuples:

Ans. There are three candidate keys of this relation

{A, B}, {A, C}, {B, C}

Example: Given  a relation  STUDENTS as  follows:

STUDENTS (SSN, Name, Home_Address, Birthdate, GPA).

• 1. Determine some candidate keys of  this relation?
  2. Determine a super key that is not a candidate key?

Ans. (a) {SSN}, {Name, Home_Address, Birthdate} are candidate keys.

         (b) {SSN, Name} is a super key but not a candidate key.

3. Primary Key : An attribute which identifies data uniquely is known as Primary key.

OR

The term Primary Key is used to denote Candidate key.

Any entity  set  can  have  more than  one  Candidate  key  but  only one  Primary  Key.

Ex. In entity set Employee, either Reg. No. is primary key or ID is primary key.

4. Alternate Keys : All the candidate keys other than Primary Key are known as Alternate Keys.

Ex. If you take ID as Primary Key. Then, Reg. No. is an alternate key.

5. Secondary Key : An attribute or set of attributes which doesn’t identify data uniquely but identifies a group of data is known  as secondary key.

Ex. Name,  Salary and  Department No.  are  all secondary  keys.

6. Foreign Key : A foreign key is an attribute in any entity set which is also a Primary Key in any other entity

Ex. Dept_ID: This is an attribute in entity set Employee and also a primary key in entity set Department.  Thus,  it  is  a  foreign  key  in  Employee.

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Database Management System, 2nd Edition-University Science Press (2017)