DIfferent types of keys in dbms | Concept of Keys in DBMS - Super, Primary, Candidate, Foreign Key

#candidatekey #primarykey #superkey
We will learn about KEYS in RDBMS. What are keys in dbms with example?
What are we going to learn?
What are keys in RDBMS?
What are different types of keys?
What is a super key?
What are candidate keys?
What is primary key?
What are keys?
RDBMS in simple words is a table of information, and each row(tuple) should be a unique combination of information.
And to uniquely identify any entity from this entity sets, we need to have a unique identifier which cannot be same for 2 entities.
This unique identifier is called a KEY.
What is the use of keys?
Keys are used to uniquely identify entities from entity sets.
Keys are also used to establish and identify relationships among tables.
What are the different types of keys in RDBMS?
There are 3 main types of keys:
Super keys
Candidate keys
Primary keys
We will also discuss about foreign keys.

What are super keys?
A super key is set of one or more attributes, taken collectively, that uniquely differentiates a tuple in relationship, i.e. it can differentiate an entity from a set of entity.
Example:
Account number is a super key because it can differentiate each entity differently as every account holder has an unique account number.
Also any superset of the account number for example, account number and name, is also a super key.
What are candidate keys?
Candidate keys are set of attributes whose subset cannot on its own be a super key.
So Candidate keys are set of more than one attributes taken collectively, differentiates each entity uniquely.

For example
What are primary keys?
Primary keys are either candidate keys or super keys chosen by the database designer, to identify primarily, the tuple from the relationship uniquely.

Example
A primary key has to be chosen very carefully according to the table.
For example in school table, name of the student is not enough to differentiate a student, but the roll number of student is enough to uniquely identify the student in that class.
Also the primary key should be chosen so that the attribute values are never or rarely changed.

Example
Bonus: Foreign Keys
Foreign keys are attributes that are primary keys in one table but are also added as attributes in another table to create a relation between two tables.
For example, every employee works for a department and a company has multiple departments.
But we cannot add all the attributes of department in employee table as they are attributes of departments and not the employee.
So to create a connection between two different tables we add primary key of department, i.e. Department_id as an attribute for an employee, which specifies, the id of the department the employee works for.
Now the Department_id becomes the foreign key in employee table and both tables are related.

Example

[QuickCS]

subscribe if you like the video. No pressure. Thank you. :-)