Lectures-2.6: Different Keys in Relational Database (DBMS)

 Different Keys in Relational Database

Keys: Keys play an important role in the relational database. It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables.

	ID	Name	SSN	Salary	Phone	Email
101	John	AA	50000	12 23	j@sw	101
102	Robin	BB	60000	13 23	r@yh	102
103	Alya	CC	35000	14	a@hm	103
104	Yusuf	DD	68000	15	y@ch	104
105	John	EE	62000	89	j@in	105
106	Raj	FF	45000	87	r@au	106
107	Jayant	GG	25000	45	j@us	107
108	John	HH	35000	52	j@de	108
109	Neil	II	25000	12	n@uk	109

Super Key: A Super Key is a group of single or multiple keys which identifies rows in a table. It may have additional attributes that are not needed for unique identification.

Features:

1)     Like superset.

2)     Uniquely identify tuples.

3)     Null values.

4)     {Name} is not a Super Key.

5)      May contain extraneous attributes.

6)     Super Keys:

{ID}, {SSN}, {ID, Name}, {ID, SSN}, {ID, Phone}, {Name, Phone}, {ID, Email}, {Name, SSN, Phone}, {Name, Email}, {ID, SSN, Phone}, ………………

 

Candidate Key: Candidate Key is a set of attributes that uniquely identify tuples in a table. Candidate Key is a Super Key with no repeated attributes.

Features:

Minimal Super keys are called Candidate Keys.

2)     The primary Key should be selected from the Candidate keys.

3)     Every table must have at least a single Candidate Key.

4)     A table can have multiple Candidate Keys but only a single Primary Key.

5)     Candidate Keys:

          {ID}, {SSN}, {Name, Phone}, {Email}

Every Candidate Key is a Super Key but every Super Key is not a Candidate Key.

Primary Key:  Primary Key is a column or group of columns in a table that uniquely identify every row in that table. The primary Key can not be a duplicate meaning the same value can not appear more than once in the table. A table can not have more than one Primary Key.

Features:

1)     To denote a candidate key.

2)     Candidate key with null values is not primary key.

3)     The primary key field cannot be null.

4)     The value in a primary key column can never be modified or updated any foreign key refers to that primary key.

5)     Primary Keys:

 {ID}; (chosen with care by DBA)

Alternate Key: Alternate keys is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an alternate key.

Features:

1)     Alternate Keys:

            {SSN}, {Name, Phone}, {Email}

 Foreign Key: Foreign key is a column that creates a relationship between two tables.     The purpose of foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table. This concept is also known as referential integrity.

Composite Key: Sometimes, a table might not have a single column/attribute that uniquely identifies all the records of a table. To uniquely identify rows of a table, a combination of two or more columns/attributes can be used.  It still can give duplicate values in rare cases. So, we need to find the optimal set of attributes that can uniquely identify rows in a table.

·        It acts as a primary key if there is no primary key in a table

·        Two or more attributes are used together to make a composite key.

Different combinations of attributes may give different accuracy in terms of identifying the rows uniquely. 

Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as Concatenated Key.

   Figure: Different Keys 

Artificial key: The key created using arbitrarily assigned data are known as artificial keys. These keys are created when a primary key is large and complex and has no relationship with many other relations. The data values of the artificial keys are usually numbered in a serial order.

For example, the primary key, which is composed of Emp_ID, Emp_role, and Proj_ID, is large in employee relations. So, it would be better to add a new virtual attribute to identify each tuple in the relation uniquely.