DBMS | Keys in Relational Model

First of all let us understand what is a Key? A Key is an attribute or a set of attributes which uniquely identifies every tuple or record(row) in a relation(table).

Depending on a relation the Keys can either be formed using one or two columns.


For example:

Employee

In the above Employee table the EMP_ID is the Primary Key which uniquely identifies every employee record.



Types of keys in Relational Model


1. CANDIDATE KEY : It is the set of minimum attributes which can easily identify a tuple in a table. Every table must have at least one candidate key. A table can have more than one candidate keys.


Properties of a Candidate key -

  • It must contain unique values.

  • It can be made up of multiple attributes, making it a composite candidate key.

  • No candidate key should have a NULL value.

Example: EMP_ID is the candidate key in the table given below.

2. PRIMARY KEY : It is a column or a group of columns which identifies every row in a table. An entity can contain multiple keys in a table. The key which is most suitable from the table become a primary key.


Properties of Primary Key -

  • It must be unique and not null.

  • Every row must have a primary key.

  • If any foreign key refers to a primary key, then its value cannot be changed or updated.

Example : EMP_ID and EMP_EMAIL together or separately can become the primary key of the table given below.

3. SUPER KEY : It is an attribute or a set of attributes which uniquely identifies a row in a table. A super key should not be confused with a candidate key as there is a basic difference between both of them, i.e., a super key is a super set of all the possible keys in a table whereas a candidate key is chosen from the given set of super keys; the chosen candidate key should not contain redundant attribute/es.

Example :

Following sets are the super keys of the above given table:

  • {EMP_ID}

  • {EMP_EMAIL}

  • {EMP_ID, EMP_NAME}

  • {EMP_ID, EMP_EMAIL}

  • {EMP_ID, EMP_NAME, EMP_EMAIL}

  • {EMP_EMAIL, EMP_NAME}



4. FOREIGN KEY : It is a column(attribute) that creates a relationship between two tables. It points to the primary key of another table. Its purpose is to maintain data integrity and allow a transit between two different instances of an entity.


Example:

From the above, it is clear that EMP_DEPT act as a foreign key in table 1 and a primary key in table 2.

Follow us on Instagram @programmersdoor

Join us on Telegram @programmersdoor


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed.


Follow Programmers Door for more.

#blog #interview #placement #learn #computer #science #dbms

138 views2 comments

        Contact Us

programmersdoor@gmail.com

  • LinkedIn
  • Facebook
  • Instagram

©2023 by Programmers Door