Keys

Keys

AU: May-06, 07, 12, Dec.-06, Marks 4

Keys are used to specify the tuples distinctly in the given relation.

Various types of keys used in relational model are - Superkey, Candidate Keys, primary keys, foreign keys. Let us discuss them with suitable example

1) Super Key(SK): It is a set of one or more attributes within a table that can uniquely identify each record within a table. For example - Consider the Student table as follows-

The superkey can be represented as follows-

Clearly using the (RegNo) and (RollNo, Phone,Name) we can identify the records uniquely but (Name, Marks) of two students can be same, hence this combination not necessarily help in identifying the record uniquely.

2) Candidate Key(CK): The candidate key is a subset of superset. In other words candidate key is a single attribute or least or minimal combination of attributes that uniquely identify each record in the table. For example - in above given Student table, the candidate key is RegNo, (RollNo,Phone). The candidate key can be

Thus every candidate key is a superkey but every superkey is not a candidate key. 

3) Primary Key(PK): The primary key is a candidate key chosen by the database designer to identify the tuple in the relation uniquely. For example - Consider the following representation of primary key in the student table

Other than the above mentioned primary key, various possible primary keys can be (RollNo), (RollNo, Name), (RollNo, Phone)

The relation among super key, candidate key and primary can be denoted by

Candidate Key= Super Key- Primary Key

Rules for Primary Key

(i) The primary key may have one or more attributes.

(ii) There is only one primary key in the relation.

(iii) The value of primary key attribute can not be NULL.

(iv) The value of primary key attributes does not be NULL.

4) Alternate Key:The alternate key is a candidate key which is not chosen by the database designer to uniquely identify the tubles. For example-

5) Foreign key: Foreign key is a single attribute or collection of attributes in one table that refers to the primary key of other table.

• Thus foreign keys refers to primary key.

• The table containing the primary key is called parent table and the table containing foreign key is called child table.

• Example-

From above example, we can see that two tables are linked. For instance we could easily find out that the 'Student CCC has opted for ComputerSci course

Review Question