Keys in DBMS

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.

Source: Dribble

Terminologies 

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: 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 nameIDregistration numbersection, 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.

Keys

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?

  1. Keys are used to establish and identify relationships between tables/relations.
  2. They uniquely identify any record/row inside a table/relation.
  3. 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, IDRegistration_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.

Frequently Asked Questions

Q1 What are keys in DBMS?

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:

  1. Theta Join
  2. Equi Join
  3. 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. 

If you are preparing for interviews, don’t forget to study DBMS - as it is one of the most asked topics in interviews. You should definitely check our free DBMS course designed specifically for you!

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.  

Happy Learning Ninja!

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think