Important DBMS Interview Questions and Answers to Ace Your Meeting

Updated 4 March 2023

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

When you are preparing for DBMS interview questions, it is crucial to familiarise yourself with all related topics. One way to do this is to anticipate what questions the interviewer may ask you and prepare sample clear and concise answers.

Being well-versed in DBMS or Database Management Systems is of the utmost importance when applying for roles like Database Administrator. Employers use this technical round to assess how good you are, and if you are ideal for the position if hired.

This article provides an idea on how to ace your interview.

Related: What Is DBMS? (Types, Benefits And RDBMS Vs DBMS)

Preparing Compelling Answers For DBMS Interview Questions

Use these steps to stay thoroughly prepared for that upcoming interview:

Revise what you know. During the last-minute preparation, you can avoid opening new topics. Brush up on what you know and note down your answers.
Keep the answers short. You may be getting less than a minute to respond to even an in-depth question. So, frame your sentences so that they do not leave anything out, yet cover all the essentials.
Get some feedback. Practice by looking in a mirror to make improvements in your body language. You may also ask your friends to conduct a mock interview and provide some constructive feedback.

But, any job interview outcome depends on your performance, knowledge of the subject and experience. In this way you can also learn useful tricks to impress the recruiting manager.

Commonly Asked DBMS Interview Questions And Sample Answers

In this stage, the focus is on technical knowledge. Candidates face questions ranging from basic to expert level.

Have a look at the top DBMS interview questions:

What are the benefits of DBMS?
What is the difference between DBMS and RDBMS?
What are the different types of database languages?
What is normalisation?
When does checkpoint occur in DBMS?
Explain entity and entity type
What are the three levels of data abstraction?
Explain ACID properties
What are the different types of keys in DBMS?

What are the benefits of DBMS?

DBMS is a technology solution that optimises and manages data storage and retrieval processes. It allows for better data integration with appropriate security measures.

Example answer: “When there are several users, there is an increased risk of data security. With DBMS, I am able to share and transfer data more securely. The platform restricts unauthorised access while providing backup and recovery. I can also minimise data inconsistency with redundancy control.”

What is the difference between DBMS and RDBMS?

Both DBMS and RDBMS are organised ways of storing and retrieving data. RDBMS does all the above with a collection of logically related information, and in addition, it uses relational integrity.

Example answer: “RDBMS or Relational Database Management System supports multiple users and normalisation. It handles distributed database and defines the integrity constraint. In RDBMS applications, data values are stored in tables with an identifier, and the relationship between data values is also tabular.

While MySQL, SQL Server and Oracle are some examples of RDBMS, XML is a DBMS. Many of today's software products are compatible with both DBMS and RDBMS.”

Related: Top 14 RDBMS Interview Questions (With Example Answers)

What are the different types of database languages?

DBMS has appropriate database languages that are used to update, store and read data. They express database queries and one of the examples is Structured Query Language (SQL).

Example answer: “The four different types of database languages are Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL) and Transaction Control Language (TCL).

DDL commands like CREATE, TRUNCATE, RENAME and DROP update data, whereas, DML commands, such as SELECT, INSERT and DELETE are used to manipulate the data which is already updated.

The purpose of DCL commands is to allow or remove user access. Examples of such commands are GRANT and REVOKE. TCL manages database transactions made by DML.”

What is normalisation?

Normalisation is a process that is used to reduce data redundancy by analysing relational schemas. It also minimises insert, delete and update distractions.

Example answer: “Normalisation is an essential DBMS process that analyses relation schemas per their respective functional dependencies and primary keys to minimise insertion distractions, update anomalies and delete anomalies. Using this process will eliminate or minimise redundancies in the database.

Normalisation forms commonly used are:

1NF - First-Normal Form
2NF - Second Normal Form
3NF - Third Normal Form
BCNF - Boyce & Codd Normal Form”

What is a checkpoint in DBMS, and when does it occur?

A checkpoint is a mechanism that declares a point where all previous logs get deleted. Before this, there were committed transactions, and the DBMS was consistent.

Example answer: “Checkpoint is a mechanism where all previous transactions get undone and get stored on a storage disk permanently. It occurs automatically and periodically in a log-based recovery system while maintaining shadow pages and transactions. These are the points where the database engine stores logs in the case of an after-crash recovery.

You can decrease the amount of work to recover committed data while restarting after system crashes. It eliminates the need for performing transactions from the beginning.”

Explain entity and entity type

Anything that has a distinct value and independent existence can be an entity. A collection of entities with similar attributes is an entity type.

Example answer: “A set of attributes is called an entity in a database. It could be a physical, real-world object with defined characteristics. For instance, an employee of an organisation works in a particular department. The employee, designation, organisation and department are all entities with corresponding traits, known as attributes.

An entity type is a collection of several entities with the same specific attributes. In the above example, the employee table with similar attributes, such as age, gender, address, contact number and employee ID becomes an entity type.”

What are the three levels of data abstraction?

DBMS tries to hide irrelevant details, such as how it stores and maintains data. Since a DBMS consists of complex data structures, users can only see part of the database. Data abstraction refers to providing an abstract view of the database to the end-user.

Example answer: “Data abstraction process in DBMS pertains to concealing irrelevant details like coding and binaries from the user, and providing a simple GUI. To access the data, users may prefer to avoid complex processing, where data abstraction helps.

There are three different layers in the system to make the process well defined:

Physical level: The lowest level of data abstraction that describes data storage blocks.

Logical level: It is the intermediate level of data abstraction that makes fields and records visible to programmers. It describes the kind of data stored in the database and the relationship between the collection of information.

View level: This is the highest level where the users interact with the system and can see only a part of the entire database.”

Explain ACID properties

Transactions are logical units of work that involve accessing and modifying data using operations. To maintain database consistency, ACID properties are followed before and after transactions take place.

Example answer: “ACID properties are the basic rules that are the perquisites for transactions to preserve integrity. They can be classified as:

Atomicity: It has Abort and Commit operations, and is also called the “all or nothing rule”. Transactions run to completion or do not get executed at all. Accordingly, they are either visible or not visible.

Consistency: This property refers to the correctness of the database and maintaining the integrity constraints. The database should have the same value before and after the execution of transactions.

Isolation: This property refers to the occurrence of multiple transactions independently at the same time without causing inconsistency in the database state.

Durability: Durability is a property that ensures database updates and modifications do not get lost. All executed transactions will be stored in non-volatile memory that does not get affected even during a system failure.”

What are the different types of keys in DBMS?

A DBMS key identifies a tuple (row) in a relation (table) using one or more columns. Keys also allow you to find the relation between tables.

Example: “The following are the types of DBMS keys:

Primary key: The Primary key is a compulsory attribute in a table, which can be a column or a combination of columns. In the employee table example, employee ID is the primary key.

Super key: Super key is either a single key or set of keys that identify distinct rows in a table. It can also have extra attributes and is a superset of a Candidate key.

Candidate key: Any table must have at least one Candidate key, which uniquely identifies each row. When a Super key does not have duplicate attributes, it can be a Candidate key.

Foreign key: Also known as Referential Integrity, a Foreign key establishes relationships with other tables. It maintains data integrity and allows easy navigation between two entities.

Alternate Key: Alternate key is any key that is eligible to be selected as Primary key but currently is not. Employee email id and mobile number can be Alternate keys.

Compound Key: A Primary key consisting of more than a single column is a Compound key. As no column is uniquely in itself, a Compound key combines two columns.

Surrogate Key: When there is no primary key in a table, we use a Surrogate key to identify each tuple distinctly. These artificial keys are typically serially ordered integers.”