Guides › Interviews
Top 50 DBMS Interview Questions (with Answers)
Keys, normalization, transactions and ACID, isolation levels, locking, indexing and B+ trees, views, sharding and NoSQL — 50 DBMS questions with short answers.
DBMS is one of the "big four" CS subjects in fresher interviews (with OOP, OS and networks), and it's often paired with a SQL query on the spot. These 50 questions cover keys, normalization, transactions, indexing, concurrency and NoSQL. For SQL queries specifically, see Top 50 SQL Interview Questions.
Basics
1. What is a DBMS? Software that stores, retrieves and manages data — handling concurrency, security, backups and integrity. Examples: MySQL, PostgreSQL, Oracle, MongoDB.
2. DBMS vs file system? A DBMS avoids redundancy and inconsistency, supports concurrent access, transactions, security and querying; a file system leaves all that to the application.
3. What is an RDBMS? A DBMS that stores data in tables (relations) with rows and columns and relationships through keys.
4. What are the three levels of abstraction? Physical (how data is stored), logical (what tables and relationships exist), view (what each user sees).
5. What is data independence? Changing one level without affecting the next: physical independence (change storage without changing the schema) and logical independence (change the schema without breaking apps/views).
6. What is a schema vs an instance? The schema is the structure (tables, columns, types). An instance is the data at a particular moment.
7. DDL, DML, DCL, TCL? DDL: CREATE/ALTER/DROP/TRUNCATE. DML: SELECT/INSERT/UPDATE/DELETE. DCL: GRANT/REVOKE. TCL: COMMIT/ROLLBACK/SAVEPOINT.
8. DELETE vs TRUNCATE vs DROP? DELETE removes chosen rows (WHERE, can roll back). TRUNCATE removes all rows quickly and resets identity. DROP removes the table itself.
ER model and keys
9. What is an ER diagram? A diagram of entities, their attributes and relationships, used to design a database. See ER Model and Relational Schema Design.
10. What are cardinalities? One-to-one, one-to-many, many-to-many. Many-to-many needs a separate junction table.
11. Weak entity?
An entity that can't be identified by its own attributes and depends on another (e.g. a Dependent of an Employee).
12. Super key, candidate key, primary key? Super key: any set of columns that uniquely identifies a row. Candidate key: a minimal super key. Primary key: the chosen candidate key (unique, not null). See Keys and Normalization.
13. Foreign key? A column that refers to the primary key of another table, enforcing referential integrity.
14. Composite key?
A key made of two or more columns, e.g. (student_id, course_id).
15. Unique key vs primary key? Both enforce uniqueness, but a table has one primary key (not null) and can have many unique keys (usually one NULL allowed).
16. What are constraints? Rules on data: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
Normalization
17. What is normalization? Splitting tables so each fact is stored once, removing redundancy and update/insert/delete anomalies.
18. What are anomalies? Update anomaly (must change data in many rows), insert anomaly (can't insert without unrelated data), delete anomaly (deleting one fact loses another).
19. What is a functional dependency? X → Y: rows with the same X always have the same Y.
20. 1NF? Every cell holds a single atomic value; no repeating groups.
21. 2NF? 1NF + no partial dependency (no non-key column depends on only part of a composite key).
22. 3NF? 2NF + no transitive dependency (non-key columns depend only on the key, not on other non-key columns).
23. BCNF? For every functional dependency X → Y, X must be a super key. Stricter than 3NF.
24. What is denormalization and when is it used? Adding redundancy on purpose for faster reads (fewer joins) — common in reporting and NoSQL designs.
Transactions and concurrency
25. What is a transaction? A group of operations executed as one unit — all succeed or all fail. See Transactions and ACID.
26. What are ACID properties? Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).
27. How is atomicity achieved? Durability? Atomicity through undo logs (roll back partial work). Durability through write-ahead / redo logs flushed to disk before commit.
28. Transaction states? Active → partially committed → committed; or active → failed → aborted.
29. Dirty read, non-repeatable read, phantom read? Dirty: reading uncommitted data. Non-repeatable: the same row gives different values in one transaction. Phantom: the same query returns a different set of rows.
30. Isolation levels? Read uncommitted, read committed, repeatable read, serializable — each prevents more anomalies at more cost.
31. What is a schedule? Serializable schedule? A schedule is the order in which operations of concurrent transactions run. Serializable means its result equals some serial (one-after-another) order. See Concurrency Control, Schedules and Locking.
32. Shared vs exclusive locks? Shared (read) locks can be held by many transactions; an exclusive (write) lock by only one.
33. What is two-phase locking (2PL)? A transaction acquires all its locks before releasing any (growing phase, then shrinking phase). It guarantees conflict serializability.
34. Deadlock in databases — how is it handled? The DBMS detects cycles in the wait-for graph and aborts one transaction (the victim), which is then retried.
35. Optimistic vs pessimistic concurrency control? Pessimistic locks data before using it. Optimistic lets transactions run and checks for conflicts at commit (e.g. a version column).
Indexing and storage
36. What is an index? A separate sorted structure that lets the DB find rows without scanning the whole table — O(log n) instead of O(n). See Indexing, B+ Trees and SQL vs NoSQL.
37. Why do databases use B+ trees? High fan-out keeps the tree shallow (few disk reads), and linked leaves make range queries fast.
38. Clustered vs non-clustered index? Clustered: rows are stored in index order (one per table). Non-clustered: a separate structure pointing to rows (many per table).
39. Disadvantages of indexes? Slower INSERT/UPDATE/DELETE (indexes must be updated) and extra storage.
40. What is a composite index and the leftmost-prefix rule?
An index on (a, b) helps queries filtering on a or a and b, but not b alone.
Views, procedures and more
41. What is a view? A saved query that behaves like a virtual table — used for simplicity and security. See Views, Stored Procedures and Triggers.
42. Stored procedure vs function? A procedure performs actions and may return several results; a function returns a single value and can be used in queries.
43. What is a trigger? Code that runs automatically on INSERT/UPDATE/DELETE — e.g. writing an audit log.
44. What is a cursor? A pointer to process query results row by row inside procedures.
Distributed data and NoSQL
45. What is replication? Keeping copies of data on several servers for availability and read scaling (primary for writes, replicas for reads).
46. What is sharding? Splitting rows across machines by a shard key to scale writes and storage. See Database Sharding and Replication.
47. What is the CAP theorem? In a network partition, a distributed system must choose between consistency and availability. See CAP Theorem and Consistency.
48. SQL vs NoSQL? SQL: fixed schema, joins, strong ACID, mostly vertical scaling. NoSQL: flexible schema, horizontal scaling, often eventual consistency.
49. Types of NoSQL databases? Document (MongoDB), key-value (Redis), wide-column (Cassandra), graph (Neo4j).
50. When would you choose MongoDB over MySQL? When the data is naturally document-shaped, the schema changes often, or you need easy horizontal scaling — and you don't need complex multi-table joins and transactions. For banking or inventory, MySQL/PostgreSQL is usually the safer choice.
Practise
- Write the SQL for questions 8, 15 and 40 yourself on SQL practice.
- Revise with DBMS interview questions and flashcards.
- Explain normalization on your own project's tables — interviewers love that.
More guides
- How to Use Codify: A 7-Day Start Plan — New here? Pick a roadmap, make a study plan, start the DSA sheet, revise with notes, practise for placements and test yourself — one step a day, then a daily routine.
- Infosys Preparation Guide: Online Test, Pseudocode and Interview — Roles (SE, DSE, SP), what each section of the Infosys online test checks, how to crack pseudocode and reasoning, a 4-week plan and the interview.
- Wipro Elite NLTH Preparation: Aptitude, Essay and Coding — The Wipro fresher test round by round — quant, logical and verbal, the written essay, two coding problems — plus a 3-week plan and interview tips.
- Accenture Preparation Guide: Cognitive, Technical, Coding and Communication — Every Accenture round explained — cognitive, MS Office/networking/cloud technical MCQs, pseudocode, coding and the spoken-English test — with a 4-week plan.