How to Prepare DBMS for GATE and Technical Interview | Important Topics

Hi Readers, 

Are you preparing for GATE or a job in the IT industry?

This is a blog about how to study DATABASE MANAGEMENT SYSTEM (DBMS) for GATE, and other technical interviews. 

Now, first of all, before I tell you how to prepare DBMS for GATE, let me tell you about the importance of DBMS in GATE. 

If you look at the GATE CSE syllabus, EVERY YEAR GATE ASKS 8-10 MARKS OF QUESTION FROM DBMS ONLY.

The main thing is that this subject does not require that much hard work. With some tricks and smart work, one can achieve 9-10 marks easily here.

AND IN THE EXAM LIKE GATE, EVERYONE KNOWS THE VALUE OF 10 MARKS. 

Now, in this blog first I will list some important topics and then tell the approach to study that (WITH SOME SHORTCUT TRICKS ALSO). 

Important Topics to Prepare DBMS for GATE 

1. BASIC INTRODUCTION (SCHEMA, ARCHITECTURE)
2. ER MODEL (ATTRIBUTES, RELATIONSHIP) 
3. KEYS (PRIMARY, SECONDARY, SUPER, FOREIGN)
4. Normalization (1NF, 2NF, 3NF, BCNF,), FUNCTIONAL DEPENDENCY 
5. PROBLEMS WITH CONCURRENCY (RW, WW)
6. CONFLICT SERILIZABILITY 
7. 2 PHASE LOCKING, JOINS 
8. B TREE, PRIMARY AND SECONDARY INDEXING
   (just understand the basics, no tough concept)

Now, let’s discuss some important topics in detail with shortcuts. 

1. Basic Introduction

This topic is quite important for a technical interview. Start with database and DBMS basics. Database and DBMS are two different things. Don’t get confused as most of the students.

To improve your understanding learn why DBMS is important over file systems.

They ask the basics like 3- schema architecture components, types of attributes, a symbol of attributes, RELATIONSHIPS LIKE one to one, one to many with examples. 

GATE mainly asks about attributes and relationship types from these topics. 

Important subtopics – 3 schema architecture, attributes, relationship type. 

2. Database Keys

This is the main topic in the whole DBMS. There are different types keys used in relational databases.

While doing normalization, you have to find the candidate key first. 

Understand some of the basic examples of keys and how to find them. 

Focus more on REFERENTIAL INTEGRITY, and how to find the foreign key when two tables are connected. 

SHORTCUTS:

Check which attribute does not have any incoming edge, that attribute is an essential attribute. The essential attribute itself is a candidate key. 

If that does not satisfy, then check every condition. (Try to give other attributes a chance to be candidate key and take their closure.) 

3. Normalization

This is one of the most important topic as far as GATE is concerned. Every year GATE asks 2-3 questions on this. 

TYPE OF QUESTION asked in GATE on Normalization

Some functional dependency will be given in the question along with the set. You need to find out which is the candidate key, and its normal form. 

How to find the normalization form?

[Step-by-step] TRICK TO SOLVE THE PROBLEM

1. Find out the candidate key (be careful here, check all the combination) 
2. Write all the prime attribute (the attribute which is used to make candidate key). 
3. List out all non-prime attributes. 
4.  NOW CHECK THE CONDITION FOR normalization. 
5. BCNF – the left-hand side of the F.D must be a candidate key. 
6. 3NF – Either the left-hand side will be a candidate key or the right-hand side will be a prime attribute. 
7. 2NF – any type of partial dependencies are there, the table is not in 2NF. (a part of candidate key alone can find any non-prime attribute) 

Cheat Code:

• At any time if a single F.D fails to satisfy the criteria, no need to check all. 
• If an FD is not in BCNF, 3NF, 2NF, then it’s always in 1Nf (no need to check for 1NF).

Remembering this simple diagram will help you answer most of the normalization questions.

Do not go deep into normalization. Only this much and some understanding about LOOSE LESS AND LOOSY DECOMPOSITION will fetch you full mark in GATE. 

Are you preparing for job in IT?

Make a note of every term here. They are quite important in interviews. Interviewers usually ask about the definition of terms like ESSENTIAL ATTRIBUTE, PRIME AND NON-PRIME ATTRIBUTE, FUNCTIONAL DEPENDENCY, REDUNDANCY, CANONICAL COVER etc. 

4. Problem with Concurrency

In this topic, GATE does not ask that many questions. The only thing is to just understand the meaning of “DIRTY READ”, “BLIND WRITE “, etc. This will help you later in Database Transaction. 

But in a technical interview, they stress much on this part. Questions like- what is a transaction? and how to avoid its concurrency problem? are very common questions. 

You can also expect some questions from ACID properties in DBMS.

5. Conflict Serializability

This is again one of the most important topics for GATE. You have to learn about different types of read-write conflict in DBMS transaction.

Few points to remember while solving questions from this topic:

• Do not check for conflict within the same column. 
•  Conflict only arises when some of the conditions are satisfied – There must be two transactions, each transaction operates on the same data items, There must be a write operation. 
•  No conflict for the read-only operation. 

Tricks to solve conflict serializability problems:

• Always solve using the vertices method. (In GATE, they generally ask the order of execution of transaction)
• In case a loop exists, we cannot determine. 
• If there is no loop, then the transaction having no incoming edge executes first, having the highest incoming edge execute at last. 

6. 2 Phase Locking (2PL) Protocol

This topic is quite simple. GATE ask theory question from this, like drawbacks of Conservative 2PL, the difference between all of them, etc. 

Some imp points to remember:

• It has growing and shrinking phase, so, called 2 phase.
• In the growing phase, transactions can apply the lock and in the shrinking phase, they can unlock. 
• Once the Lock is removed, it cannot apply for lock again. 
• It has two types of lock (exclusive and shared). In Exclusive transaction can read and write both but on shared it can read-only. 
• Shared locking can be applied by two transactions at a time. 
• The main aim behind rigorous and strict locking is to achieve cascadelessness and recoverability. (important question for interview) 
• When a transaction commits, it automatically unlocks all the data items.

7. Join in DBMS

From this topic, GATE only asks about the type of joins (by giving two tables in the questions).

Join operation is used to joins the multiple rows from the two or more tables. It is easy to remember if you visualize it using this simple Venn diagram as below.

Read DBMS Join operation in detail.

In an interview, interviewer can ask you questions from this topics. 

Some important points to remember about DBMS joins. 

• The main aim of join is to remove redundancy among various tables. 
•  Natural join, Inner join, and outer join have data loss. We have different methods to cater to that. 
• Join is an advanced version of the cartesian product. 
• Focus more on Natural and Equijoin. They are quite a frequent topics. 

THAT’S IT. FOCUS ON THESE IMPORTANT TOPICS. THE SAME PATTERN is REPEATING ALMOST EVERY YEAR.

If you are preparing for GATE, follow self study plan to crack GATE.

HOPE IT HELPS you to prepare DBMS for GATE. In case of any questions, ask me in the comment section below.

HAPPY LEARNING! 

Umakant Biswal

This is Umakant, a B.tech(CSE) student, quora writer, and a good problem solver. I like to write about technical topics and enjoy solving problems efficiently. I'm a Python lover and always curious to learn new facts.