Tag: alternate key

Keys in DBMS

Keys in DBMS-

 

A key is a set of attributes that can identify each tuple uniquely in the given relation.

 

Also read- Attributes in DBMS

 

Different Types Of Keys in DBMS-

 

There are following 10 important keys in DBMS-

 

 

  1. Super key
  2. Candidate key
  3. Primary key
  4. Alternate key
  5. Foreign key
  6. Partial key
  7. Composite key
  8. Unique key
  9. Surrogate key
  10. Secondary key

 

NOTE-

 

Before proceeding further, Kindly note-

  • The terms ‘relation’ and ‘table’ are used interchangeably.
  • The terms ‘tuple’ and ‘record’ are used interchangeably.

So, don’t get confused!

 

1. Super Key-

 

  • A super key is a set of attributes that can identify each tuple uniquely in the given relation.
  • A super key is not restricted to have any specific number of attributes.
  • Thus, a super key may consist of any number of attributes.

 

Example-

 

Consider the following Student schema-

Student ( roll , name , sex , age , address , class , section )

 

Given below are the examples of super keys since each set can uniquely identify each student in the Student table-

  • ( roll , name , sex , age , address , class , section )
  • ( class , section , roll )
  • (class , section , roll , sex )
  • ( name , address )

 

NOTE-

 

All the attributes in a super key are definitely sufficient to identify each tuple uniquely in the given relation but all of them may not be necessary.

 

2. Candidate Key-

 

A minimal super key is called as a candidate key.

OR

A set of minimal attribute(s) that can identify each tuple uniquely in the given relation is called as a candidate key.

 

Example-

 

Consider the following Student schema-

Student ( roll , name , sex , age , address , class , section )

 

Given below are the examples of candidate keys since each set consists of minimal attributes required to identify each student uniquely in the Student table-

  • ( class , section , roll )
  • ( name , address )

 

NOTES-

 

  • All the attributes in a candidate key are sufficient as well as necessary to identify each tuple uniquely.
  • Removing any attribute from the candidate key fails in identifying each tuple uniquely.
  • The value of candidate key must always be unique.
  • The value of candidate key can never be NULL.
  • It is possible to have multiple candidate keys in a relation.
  • Those attributes which appears in some candidate key are called as prime attributes.

 

3. Primary Key-

 

A primary key is a candidate key that the database designer selects while designing the database.

OR

Candidate key that the database designer implements is called as a primary key.

 

NOTES-

 

  • The value of primary key can never be NULL.
  • The value of primary key must always be unique.
  • The values of primary key can never be changed i.e. no updation is possible.
  • The value of primary key must be assigned when inserting a record.
  • A relation is allowed  to have only one primary key.

 

Remember-

 

 

4. Alternate Key-

 

Candidate keys that are left unimplemented or unused after implementing the primary key are called as alternate keys.

OR

Unimplemented candidate keys are called as alternate keys.

 

5. Foreign Key-

 

  • An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its values are dependent on the values of attribute ‘Y’.
  • The attribute ‘X’ can assume only those values which are assumed by the attribute ‘Y’.
  • Here, the relation in which attribute ‘Y’ is present is called as the referenced relation.
  • The relation in which attribute ‘X’ is present is called as the referencing relation.
  • The attribute ‘Y’ might be present in the same table or in some other table.

 

Example-

 

Consider the following two schemas-

 

Here, t_dept can take only those values which are present in dept_no in Department table since only those departments actually exist.

 

NOTES-

 

  • Foreign key references the primary key of the table.
  • Foreign key can take only those values which are present in the primary key of the referenced relation.
  • Foreign key may have a name other than that of a primary key.
  • Foreign key can take the NULL value.
  • There is no restriction on a foreign key to be unique.
  • In fact, foreign key is not unique most of the time.
  • Referenced relation may also be called as the master table or primary table.
  • Referencing relation may also be called as the foreign table.

 

6. Partial Key-

 

  • Partial key is a key using which all the records of the table can not be identified uniquely.
  • However, a bunch of related tuples can be selected from the table using the partial key.

 

Example-

 

Consider the following schema-

Department ( Emp_no , Dependent_name , Relation )

 

Emp_noDependent_nameRelation
E1SumanMother
E1AjayFather
E2VijayFather
E2AnkushSon

 

Here, using partial key Emp_no, we can not identify a tuple uniquely but we can select a bunch of tuples from the table.

 

7. Composite Key-

 

A primary key comprising of multiple attributes and not just a single attribute is called as a composite key.

 

8. Unique Key-

 

Unique key is a key with the following properties-

  • It is unique for all the records of the table.
  • Once assigned, its value can not be changed i.e. it is non-updatable.
  • It may have a NULL value.

 

Example-

 

The best example of unique key is Adhaar Card Numbers.

  • The Adhaar Card Number is unique for all the citizens (tuples) of India (table).
  • If it gets lost and another duplicate copy is issued, then the duplicate copy always has the same number as before.
  • Thus, it is non-updatable.
  • Few citizens may not have got their Adhaar cards, so for them its value is NULL.

 

9. Surrogate Key-

 

Surrogate key is a key with the following properties-

  • It is unique for all the records of the table.
  • It is updatable.
  • It can not be NULL i.e. it must have some value.

 

Example-

 

Mobile Number of students in a class where every student owns a mobile phone.

 

10. Secondary Key-

 

Secondary key is required for the indexing purpose for better and faster searching.

 

Next Article- Functional Dependency in DBMS

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.