KEYS IN DBMS

KEYS is an attribute or set of attributes(columns) that is use to identify a row(tuple) in a relation(table).

• It establishes a relation between the two tables.
• Keys are used to uniquely identify a row/record in a table.
• Help to enforce identity and integrity in the relationship or relation.

 Example:

Employee ID	FirstName	LastName
1	And	John
2	Tomi	Woo
3	Ale	Ha

In this example, employee ID is a primary key used for uniquely identifying an employee record.

 

Types of Keys

Mainly Eight types of Keys in DBMS are:

1. Super Key
2. Primary Key
3. Candidate Key
4. Alternate Key
5. Foreign Key
6. Compound Key
7. Composite Key
8. Surrogate Key

 Super key

A super key is a set of an attribute (a group of single or multiple keys), to uniquely identify a tuple (rows).

• It is a superset of a candidate key.
• The Primary Key of a table is a key from the super key set.

 For example: in the EMPLOYEE table, the super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

 Primary Key

PRIMARY KEY is a column or group of columns in a table that uniquely identifies every row in that table.

• A table cannot have more than one primary key.

Rules for defining Primary key:

• Two rows can’t have the same value (duplicate values)
• every row has a primary key value.
• Have atomic value.
• Have unique value.
• Cannot be null.
• All keys are fully functionally depending on the primary key.
• The primary key value can never be modified if any foreign key refers to that primary key.

 Example:

SID is a Primary Key.

SID	RNO	SNAME	Email
1	101	abc	abc@gmail.com
2	102	xyz	xyz@gmail.com
3	103	mno	mno@ gmail.com

 

Alternate key

In a table, All the keys except the primary key are called Alternate keys.

Example:

SID is a Primary Key, RNO, SNAME and Email are alternate keys.

SID	RNO	SNAME	Email
1	101	abc	abc@gmail.com
2	102	xyz	xyz@gmail.com
3	103	mno	mno@ gmail.com

 

Candidate Key

A candidate key is a key or set of keys (minimal set of attributes) that can uniquely identify a tuple.

• The primary key is a key that is selected from candidate keys.
• The candidate key can be simple (only one attribute) or composite (more than one attribute) as well.

Example:

• SID, RNO, Email are candidate keys.

SID	RNO	SNAME	Email
1	101	abc	abc@gmail.com
2	102	xyz	xyz@gmail.com
3	103	mno	mno@ gmail.com

•  Candidate keys have the same properties as the primary keys which are:-

Properties of Candidate key:

• It contains unique values
• Not contain null values
• Contain minimum fields to ensure uniqueness
• Uniquely identify each record in a table.
• It is possible to have multiple candidate keys in relation.

Example-

Foreign key

FOREIGN KEY is a key that creates a relationship with other tables.

• It maintains data integrity.
• It is use for navigation between two different instances of an entity.
• It acts as a cross-reference between two tables.
• It is a references of primary key which are connected with another table.
• It can take only those values which are present in the primary key of the referenced relation.
• It can take the NULL value.
• No restriction on a foreign key to be unique.
• Referenced relation is called as the master table or primary table.
• Referencing relation is called as the foreign table.

Example:

Compound key

COMPOUND KEY has two or more keys that uniquely identify a specific record.

• The purpose of the compound key is to uniquely identify each record in the table.

Example:

OrderNo	PorductID	Product Name	Quantity
B005	JAP102459	Mouse	5
B005	DKT321573	USB	10
B005	OMG446789	LCD Monitor	20
B004	DKT321573	USB	15
B002	OMG446789	Laser Printer	3

In this example, OrderNo or  ProductID can’t be a primary key as an individual because it does not uniquely identify a record so combine  Order ID with Product ID as a compound key for access uniquely identified each record.

 

Composite key

COMPOSITE KEY is a combination of two or more keys that uniquely identify records in a table, though individually uniqueness is not guaranteed.

• The difference between the compound and the composite key is that the compound key may be a foreign key, but the composite key may or maybe not be a part of the foreign key.

 

Surrogate key (Artificial key)

SURROGATE KEYS is key which use to uniquely identify each record.

• It is used or created when doesn’t have any natural primary key.
• It do not give any meaning to the data in the table.
• It is generally an integer.
• It is a value that generated right before the record into a table.

Fname	Lastname	Start Time	End Time
Ann	bose	09:00	18:00
Jack	Mc	08:00	17:00
Ann	Mc	11:00	20:00
Shown	morfy	14:00	23:00

In this example, no primary key so use a surrogate key to access uniquely record.

 

======================================================================