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

        Contact Us

programmersdoor@gmail.com

  • LinkedIn
  • Facebook
  • Instagram

©2023 by Programmers Door