DBMS Interview Questions
DBMS is a core topic in almost every software and data-engineering interview. These are the questions interviewers actually ask, with concise answers you can speak confidently.
17 questions with concise, interview-ready answers.
1. What is a DBMS, and how does it differ from an RDBMS?
A DBMS (Database Management System) is software that lets you store, retrieve, and manage data in a database. An RDBMS (Relational DBMS) is a type of DBMS that stores data in tables (relations) made of rows and columns and enforces relationships between them using keys. Every RDBMS is a DBMS, but a plain DBMS need not be relational. Examples of RDBMS include MySQL, PostgreSQL, Oracle, and SQL Server.
2. How is a DBMS better than a traditional file system?
A file system stores raw data in files with no built-in relationships, leading to redundancy, inconsistency, and difficult concurrent access. A DBMS removes redundancy through normalization, enforces data integrity with constraints, supports multi-user concurrent access with locking, provides transactions, and offers powerful querying with SQL. It also handles security, backup, and recovery, which file systems leave to the application.
3. What is normalization, and why is it used?
Normalization is the process of organizing columns and tables to reduce data redundancy and avoid update, insert, and delete anomalies. It splits large tables into smaller related ones and links them with keys. The result is cleaner, more consistent data that is easier to maintain. It is achieved by applying a series of normal forms.
4. Explain 1NF, 2NF, 3NF, and BCNF.
1NF requires every column to hold atomic (indivisible) values with no repeating groups. 2NF is 1NF plus no partial dependency — every non-key column must depend on the whole primary key, not just part of it. 3NF is 2NF plus no transitive dependency — non-key columns must not depend on other non-key columns. BCNF (Boyce-Codd Normal Form) is a stricter 3NF where, for every functional dependency, the left side must be a super key.
5. What is denormalization, and when would you use it?
Denormalization deliberately adds redundancy back into a normalized database by combining tables or duplicating columns. It is used to improve read performance by reducing the number of joins needed for frequent queries. The trade-off is more storage and the burden of keeping duplicated data consistent on writes. It is common in reporting, analytics, and read-heavy systems.
6. What are the ACID properties of a transaction?
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means a transaction runs fully or not at all. Consistency means it moves the database from one valid state to another, respecting all constraints. Isolation means concurrent transactions do not interfere with each other. Durability means once a transaction commits, its changes survive even a system crash.
7. What is the difference between a primary key, a unique key, and a foreign key?
A primary key uniquely identifies each row and cannot contain NULLs; a table has exactly one. A unique key also enforces uniqueness but allows one NULL (depending on the database) and a table can have several. A foreign key is a column that references the primary key of another table to enforce referential integrity between them.
8. What is the difference between a candidate key and a super key?
A super key is any set of one or more columns that can uniquely identify a row in a table. A candidate key is a minimal super key — one with no redundant columns, so removing any column would break uniqueness. Every candidate key is a super key, but not every super key is a candidate key. The primary key is chosen from among the candidate keys.
9. What are the different types of joins in SQL?
An INNER JOIN returns only the rows that match in both tables. A LEFT (OUTER) JOIN returns all rows from the left table plus matching rows from the right, with NULLs where there is no match. A RIGHT (OUTER) JOIN does the reverse, keeping all right-table rows. A FULL OUTER JOIN returns all rows from both tables, matched where possible and NULL-filled otherwise.
10. What is the difference between DDL, DML, DCL, and TCL?
DDL (Data Definition Language) defines schema — CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) works with the data itself — SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions — GRANT and REVOKE. TCL (Transaction Control Language) manages transactions — COMMIT, ROLLBACK, and SAVEPOINT.
11. What is a transaction in a database?
A transaction is a single logical unit of work made up of one or more SQL statements that must succeed or fail together. It is the mechanism that enforces the ACID properties. You start it, perform operations, and then either COMMIT to make changes permanent or ROLLBACK to undo them. A classic example is transferring money, where the debit and credit must both happen or neither does.
12. What is an index, and how does it speed up queries?
An index is a separate data structure, usually a B-tree, that stores sorted key values along with pointers to the matching rows. Instead of scanning every row (a full table scan), the database can look up values quickly, much like an index at the back of a book. This dramatically speeds up SELECT, WHERE, JOIN, and ORDER BY operations. The trade-off is slower writes and extra storage, since indexes must be updated when data changes.
13. What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order in which rows are stored on disk, so a table can have only one. A non-clustered index is a separate structure that stores key values with pointers (row locators) back to the actual data, and a table can have many. Clustered indexes are faster for range queries, while non-clustered indexes are flexible for looking up specific columns.
14. What is the ER model in DBMS?
The ER (Entity-Relationship) model is a high-level conceptual way to design a database by describing it as entities, their attributes, and the relationships between them. Entities become tables, attributes become columns, and relationships become foreign keys or link tables. It is usually drawn as an ER diagram and is the blueprint that guides the actual schema.
15. What are the types of relationships in a database?
There are three main types. One-to-one, where one row in a table relates to exactly one row in another. One-to-many, where one row relates to many rows in another table (the most common). And many-to-many, where many rows relate to many rows, which is implemented using a junction (bridge) table that holds the foreign keys of both sides.
16. What is a deadlock, and how can it be handled?
A deadlock occurs when two or more transactions each hold a lock the other needs, so none can proceed and they wait forever. Databases handle it with deadlock detection, where the system finds the cycle and aborts (rolls back) one transaction as a victim so the others continue. It can be prevented by acquiring locks in a consistent order, keeping transactions short, and using lower isolation levels or timeouts.
17. What is a view in a database?
A view is a virtual table defined by a stored SELECT query; it does not store data itself but presents the result of that query as if it were a table. Views simplify complex queries, provide a consistent interface, and improve security by exposing only specific columns or rows. A materialized view, by contrast, physically stores the result and must be refreshed to stay current.
Get these answered live in your real interview
NostrobeAI is a real-time AI interview copilot — it hears the question and drafts a strong answer on your screen, invisible on Zoom, Meet, and Teams. One-time pricing, no subscription.
Try NostrobeAI free