Do you think IIT Guwahati certified course can help you in your career?
Introduction
DBMS is one of the most asked topic in interviews. So if you are preparing for interviews, it’s essential to be clear with certain critical topics of DBMS. One such vital topic is Keys.
Most students tend to get confused with the different types of keys, but we have got you covered! In this blog, we will be discussing everything you need to know about Keys.
Before understanding Keys, we should be thoroughly familiar with the following terminologies:
Entity: An entity can be a person, place, object or a concept that is distinguishable from other entities, and about which we can store data in a database.
Database: A database is an organised and self-described collection of data about the entities related to an organisation. It is usually controlled by a DBMS.
A simple example of a database is a College database that stores information related to students, faculty, staff and courses.
DBMS: DBMS( Database Management System) is a software that consists of a collection of databases and related programs to create, manipulate, and display a database's structure and content. It acts as a bridge between user and database, allowing users to
create
read
update
delete
Data in a database.
Examples of some popular DBMS are MySQL, Microsoft Access, FoxPro, dBase etc.
Attribute: Attribute refers to a group of elementary data that describes the characteristics of an entity.
For example, student is an entity and name, ID, registration number, section, roll number are the different attributes of the student entity.
Tuple: Any record or horizontal collection of data in a table or relation is called a tuple. A tuple is also known as a Record or Row.
A key is an attribute or set of attributes that help to uniquely identify a record or a row of data in a table(relation).
Why do we need Keys?
Keys are used to establish and identify relationships between tables/relations.
They uniquely identify any record/row inside a table/relation.
They also ensure that data integrity is maintained.
Types
The different types of keys in DBMS/RDBMS are:
Super Key
Candidate Key
Primary Key
Alternate Key
Foreign Key
For understanding the different types of keys better, let us consider the below table. In this ‘Student’ table, ID, Registration_No. and Name of students are given.
Super Key
If a set of one or more attributes can uniquely identify the entities of an entity set, then it is called a super key.
Example: In the above table the super keys are
{ ID }
{Registration_No.}
{ ID, Registration_No.}
{ID, Name}
{Registration_No. ,Name}
{ID, Registration_No., Name}
NOTE:{Name} is not a super key as there is more than one record/row with the same name.
Candidate Key
Candidate keys are selected from a set of super keys. If a super key has no unnecessary attributes, then such a minimal super key is called a candidate key. It is also termed as ‘Minimal Super Key’.
Example: In the student entity set {ID}, {Registration_No.}, {ID, Registration_No.} etc. are candidate keys. But {ID, Name} is not a candidate key, because {Name} is a redundant attribute.
Primary Key
Primary keys are selected from a set of candidate keys. It is a candidate key selected by the database designer to uniquely identify the records of a table.
The primary key field of a table must be unique and cannot be null.
A table does not contain more than one primary key.
Alternate Key
If a candidate key is not selected as a primary key then it is called an alternate key.
Example: If {ID} is selected as primary key, then {Registration_No.} can be selected as an alternate key
Foreign Key
If two relations or tables have a common attribute, which is the primary key in one table and a non-key attribute in another table, then such a non-key attribute is called a foreign key.
A table can contain zero or more foreign keys.
It helps to combine the tuples of two tables during the join operation.
In the above table, {ID} is the primary key in ‘Student’ Table, but foreign key in the ‘Marks’ Table.
Ans: A key is an attribute or set of attributes that help to uniquely identify a record(row) of data in a table(relation).
The different types of keys are:
Super Key
Candidate Key
Primary Key
Alternate Key
Foreign Key
Q2 Explain “Every candidate key is a super key, but the reverse is not always true”.
Ans: If a set of one or more attributes can uniquely identify the entities of an entity set, then it is called a super key.
From the list of super keys, the keys which have no unnecessary attributes are the candidate keys. So every candidate key is essentially a super key, but every super key is not always a candidate key.
Q3 Explain ACID properties in DBMS.
Ans: ACID properties are four properties that are followed before and after a transaction is made in the database. ACID properties maintain the consistency of data in the database.
The ACID properties are,
Atomicity
Consistency
Isolation
Durability
Q4 What is a Join operation?
Ans: Join Operation is a binary operation, which can be applied on two relations/ tables having at least one common attribute. It produces a new relation with a combination of tuples.
There are three types of join operations:
Theta Join
Equi Join
Natural Join
Key Takeaways
In this blog, we discussed Keys in DBMS. We saw why it is important in DBMS and also the different types of keys present. We hope you learned something new in this blog.
You can also consider our DBMS Course to give your career an edge over others!
Don't stop here, Ninja; check out the Top 100 SQL Problemsto get hands-on experience with frequently asked interview questions and land your dream job.
83+ registered 