The interviewer draws one table on the whiteboard: student id, student name, course code, course title, instructor, grade. "What's wrong with it?" You say it isn't normalised. "Which normal form does it break? What happens when I rename a course?" You know the words 1NF and 3NF. Naming which dependency causes which anomaly, on this table, is what gets tested.
The short answer
DBMS interviews for freshers and backend roles test concepts, not query syntax: the ER model, keys, normal forms up to BCNF, ACID, concurrency, locking, indexes, recovery and the basics of distributed data. Interviewers want the reason behind each rule, because that lets you apply it to a table you have never seen. The same ground covers most DBMS viva questions, so this list works for a college viva too.
For writing queries, joins, window functions, index basics and isolation levels, see the SQL interview questions and answers.
Foundations and the ER model
1. What is a DBMS, and how is an RDBMS different?
A DBMS is software that manages data for many users and programs at once, handling concurrent access, crash recovery, integrity constraints, security and querying. An RDBMS follows the relational model: data lives in tables, tables are linked by keys, and you query declaratively with SQL. MongoDB is a DBMS, just not a relational one.
2. What are data models, and what is data independence?
A data model is how data is structured: hierarchical, network, relational, and later document and key-value. The three-schema architecture separates the external level (views), the conceptual level (tables and constraints) and the internal level (files and indexes). Physical data independence: adding an index does not change the logical schema. Logical data independence: changing tables does not break applications, which is harder.
3. What is the ER model?
A conceptual design tool used before creating tables. An entity is a thing with independent existence, such as Student. Attributes can be composite, multi-valued or derived. A relationship, such as Student enrols in Course, can carry attributes like grade.
Likely follow-up: "What is a weak entity?" One identified only through its owner, such as a Room within a Building. It has a partial key, not a key of its own.
4. Explain cardinality and participation, and how they map to tables.
Cardinality is one-to-one, one-to-many or many-to-many; participation says whether every instance must take part (total) or may not (partial). One-to-many becomes a foreign key on the "many" side. Many-to-many becomes a junction table, enrolments(student_id, course_code, grade), keyed by both foreign keys and holding the relationship's attributes. One-to-one becomes a foreign key with a unique constraint, usually on the total-participation side.
Likely follow-up: "Why not store a list of course codes in the student row?" It breaks 1NF, cannot be constrained or indexed properly, and turns "who takes DB101?" into a string search.
Keys and functional dependencies
5. Explain super, candidate, primary, alternate, composite and foreign keys.
A super key is any column set that uniquely identifies a row, including bloated ones like {student_id, name}. A candidate key is a minimal super key. The primary key is the candidate you choose; the rest are alternate keys, enforced with unique constraints. A composite key has several columns. A foreign key must match a candidate key, usually the primary key, of another table.
Likely follow-up: "Natural or surrogate key?" A natural key (email) has meaning but can change; a surrogate (auto-increment id) never changes but needs a unique constraint on the real identifier to stop duplicates.
6. What is a functional dependency?
X → Y means any two rows that agree on X agree on Y: student_id → student_name. It is a rule about meaning; sample data can disprove a dependency but never prove one. Armstrong's axioms (reflexivity, augmentation, transitivity) derive new ones, and the closure X+ is everything X determines. If X+ covers every column, X is a super key.
Likely follow-up: "R(A, B, C, D) with A → B and B → C: candidate keys?" A and D appear on no right-hand side, so both are in every key. {A, D}+ = {A, B, C, D}, so AD is the only candidate key.
Normalisation, one table at a time
7. What is normalisation, and what is 1NF?
Normalisation stores each fact once, removing three anomalies. Running example: enrolments(student_id, student_name, phones, course_code, course_title, instructor_id, instructor_name, grade). Update anomaly: renaming a course touches every enrolment row, and missing one leaves the data contradicting itself. Insert anomaly: a course cannot exist until someone enrols. Delete anomaly: removing the last student loses the course title.
1NF requires atomic values. phones holding two numbers breaks it, so phones move to student_phones(student_id, phone). The key is now (student_id, course_code).
8. What is 2NF?
No non-prime column may depend on part of a candidate key. student_id → student_name and course_code → course_title are partial dependencies. Move them to students(student_id, student_name) and courses(course_code, course_title), leaving enrolments(student_id, course_code, instructor_id, instructor_name, grade). A 1NF table whose candidate keys are all single columns is automatically in 2NF.
9. What is 3NF?
No non-prime column may depend on another non-prime column. (student_id, course_code) → instructor_id → instructor_name is a transitive dependency, so move it to instructors(instructor_id, instructor_name). Formally: for every non-trivial X → A, X is a super key or A is part of a candidate key. Informally: every non-key column depends on the key, the whole key and nothing but the key.
10. What is BCNF, and how does it differ from 3NF?
BCNF drops 3NF's exception: for every non-trivial X → Y, X must be a super key. Add a rule: each instructor teaches one course, so instructor_id → course_code. Now enrolments(student_id, course_code, instructor_id, grade) has two candidate keys, (student_id, course_code) and (student_id, instructor_id). Since course_code is prime, 3NF allows it; BCNF does not, because instructor_id is not a super key. The symptom: "instructor 7 teaches DB101" repeats for every student. Decompose into teaches(instructor_id, course_code) and enrolments(student_id, instructor_id, grade).
Likely follow-up: "What did you lose?" Dependency preservation: "one instructor per student per course" is no longer enforced by any single table's key. That is why many designs stop at 3NF, which can always be reached without that loss.
11. When would you denormalise, and what does it cost?
To avoid joins on a hot read path, such as a comment_count on posts. The cost: writes update several places, copies can drift, and something must own keeping them correct. Normalise first and denormalise a measured bottleneck.
Transactions and concurrency
12. What is a transaction, and what states does it go through?
A sequence of operations treated as one unit, such as debiting one account and crediting another. It is Active while running, Partially committed after its last statement, and Committed once its commit record is durable. On error it becomes Failed and is rolled back to Aborted, after which it may be restarted.
13. How does a database actually guarantee ACID?
Atomicity: undo information; InnoDB keeps undo logs, while PostgreSQL marks the transaction aborted so its row versions are never visible. Consistency: declared constraints, plus your application's rules. Isolation: locking, multi-version concurrency control (MVCC), or both. Durability: the write-ahead log is flushed to disk before commit returns.
Likely follow-up: "Can you trade durability for speed?" Yes: innodb_flush_log_at_trx_commit (MySQL) and synchronous_commit (PostgreSQL) can skip the per-commit flush, risking recent commits in a crash.
14. Explain dirty reads, non-repeatable reads, phantoms and lost updates.
A dirty read sees another transaction's uncommitted change, which may be rolled back. A non-repeatable read gets different values for the same row because another transaction committed an update in between. A phantom is new rows appearing when a range query is re-run. A lost update is two transactions reading 500, each subtracting 100 and writing 400, so one withdrawal vanishes. The SQL guide maps isolation levels to these.
Likely follow-up: "How do you prevent a lost update?" An atomic UPDATE ... SET balance = balance - 100, SELECT ... FOR UPDATE, or optimistic concurrency with a version column.
15. What are shared and exclusive locks, and what is two-phase locking?
Shared locks are for reading and many can coexist; an exclusive lock is for writing and conflicts with everything. Two-phase locking (2PL) has a growing phase that acquires locks and a shrinking phase that releases them, with no acquiring after the first release, which guarantees a conflict-serialisable schedule. Strict 2PL holds exclusive locks until commit, so no one reads uncommitted data and aborts do not cascade.
Likely follow-up: "Does 2PL prevent deadlocks?" No. And under MVCC, used by InnoDB and PostgreSQL, plain reads see a snapshot and take no row locks (except under InnoDB's SERIALIZABLE), so readers and writers do not block each other.
16. What causes a database deadlock, and how is it handled?
T1 locks row A and waits for B; T2 locks B and waits for A. The database finds the cycle in a wait-for graph and rolls back a victim: InnoDB detects it immediately (error 1213), PostgreSQL after deadlock_timeout. In practice: lock in a consistent order (lower account id first), keep transactions short, index so updates lock only needed rows, and retry the victim.
Indexing and query processing
17. Why do databases use B+ trees, and when is a hash index better?
A B+ tree keeps keys sorted in a balanced tree; internal nodes hold only keys and pointers, and row references (or, in a clustered index, the rows themselves) live in the leaves. High fan-out keeps even large tables a few levels deep, so a lookup is a few page reads, and linked leaves let range queries and ORDER BY walk along the bottom. A plain B-tree stores data in internal nodes too, lowering fan-out. A hash index gives constant-time equality lookups but no ranges or ordering.
18. What is the difference between clustered and non-clustered indexes?
A clustered index stores the rows themselves in key order, so there is one per table; InnoDB clusters on the primary key. A non-clustered index is separate; in InnoDB its leaves store the primary key, so a lookup traverses two trees. So a long primary key bloats every secondary index. PostgreSQL stores tables as heaps, so all its indexes are non-clustered.
19. How does a database process and optimise a query?
The parser checks syntax and resolves names. The rewriter expands views. The cost-based optimiser pushes filters down. The cost-based optimiser compares plans (access path, join order, join algorithm) using statistics such as row counts and histograms, and the executor runs the cheapest. Join algorithms include nested loop, hash and merge joins.
Likely follow-up: "Why would it pick a bad plan?" Usually stale or misleading statistics; refresh with ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL).
Views, procedures and triggers
20. When would you choose a materialised view over a view?
A view runs its query on every read: always current, never cheaper. A materialised view stores the result: cheap to read, stale until refreshed. Choose it when an expensive aggregate is read far more often than its data changes and staleness is acceptable, such as a daily sales dashboard. In PostgreSQL, REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers but needs a unique index.
21. What are stored procedures and triggers, and when should you avoid them?
A stored procedure is named code in the database, called explicitly; it saves round trips and lets you grant execute without table access. A trigger runs automatically before or after an insert, update or delete, and suits audit trails. The case against both: hidden logic is harder to test, version and debug, and a trigger can quietly slow bulk writes. Give both sides.
Recovery, scale and distribution
22. How does a database recover after a crash? What is write-ahead logging?
Pages change in memory and reach disk later, so a crash can leave uncommitted changes on disk and committed ones missing. The WAL rule: a change's log record reaches stable storage before the changed page does, and a transaction's log is flushed before commit is acknowledged. On restart the database redoes logged changes, then undoes those of uncommitted transactions; ARIES does this in analysis, redo and undo passes. Checkpoints limit replay.
23. What is the difference between replication and sharding?
Replication copies the same data to several servers: a primary takes writes, replicas serve reads. It scales reads and enables failover, not writes. Asynchronous replicas lag, so route read-your-own-writes to the primary. Sharding splits rows across servers by a shard key, scaling writes and storage, at the cost of cross-shard joins, rebalancing and hot shards. These trade-offs drive a system design round.
24. What does the CAP theorem actually say?
When a network partition splits a distributed store, it must choose between consistency (every read sees the latest write or errors) and availability (every working node answers, possibly stale). Partitions are unavoidable, so "pick two of three" misleads: the choice is what to give up during one. Two points earn credit: CAP's C means linearisability, not ACID's C, and PACELC adds a latency-versus-consistency trade-off even without partitions.
25. SQL or NoSQL: how do you choose?
Relational databases give schemas, joins, ad-hoc queries and strong transactions, which suits related data where money is involved: orders, payments, bookings. NoSQL (key-value, document, wide-column, graph) suits known, simple access patterns at large scale or flexible records. Start from the workload: what you query, how consistent it must be, and how far it must scale.
How to practise these
Normalise the enrolments table from question 7 on paper, naming each dependency as you remove it. Then reproduce a lost update and a deadlock in two local database sessions. When an interviewer pushes past what you know, read how to handle a question you don't know. If you are early in your career, pair this with the IT interview questions for freshers and the fresher interview preparation guide.
To rehearse out loud, a backend practice round on 99interview with Sia, our AI interviewer, asks one question at a time and follows up on what you actually said; you pick your track and stack, a round length, and Beginner, Standard or Senior difficulty, and get a scored written breakdown when you finish. A live 40-minute Zoom interview with a working engineer is scored on five criteria, led by technical depth (35%) and problem solving (25%), and comes with a numbered certificate.