Prepare for the conversation
DBMS interview questions
6 questions to strengthen your understanding. Try your answer before revealing the explanation.
Switch subject
What are ACID properties in DBMS?DBMS
The explanation
ACID properties ensure database transactions are processed reliably:
- Atomicity: The transaction must succeed fully or fail completely ('all-or-nothing'). If one statement fails, the entire transaction is rolled back.
- Consistency: Database state must remain valid according to all schema rules, constraints, triggers, and cascades before and after the transaction.
- Isolation: Controls how concurrent transactions can observe and affect one another. Serializable isolation aims for an outcome equivalent to a serial execution; weaker isolation levels allow some anomalies. The guarantees depend on the database and chosen isolation level.
- Durability: Once a transaction commits, its modifications are permanently recorded on non-volatile media (disk) and will not be lost even in a system crash.
Points to cover in your answer
- Atomicity
- Consistency
- Isolation
- Durability
Take it one step further
Try answering the follow-up before moving on.
Explain the differences between Read Committed, Repeatable Read, and Serializable transaction isolation levels.
Explain Database Normalization and its FormsDBMS
The explanation
Normalization is the process of structuring a relational database to minimize redundancy and avoid insertion, update, and deletion anomalies.
- 1NF (First Normal Form): Attributes must contain only atomic (indivisible) values, and there must be no repeating groups or multi-valued columns.
- 2NF (Second Normal Form): Must be in 1NF, and all non-key attributes must be fully functionally dependent on the entire primary key (eliminating partial dependencies where an attribute depends on only a part of a composite key).
- 3NF (Third Normal Form): Must be in 2NF, and no non-prime attribute should be transitively dependent on the primary key (meaning a non-prime attribute cannot depend on another non-prime attribute).
- BCNF (Boyce-Codd Normal Form): A stronger version of 3NF. For every non-trivial functional dependency X -> Y, X must be a super key.
Points to cover in your answer
- Anomalies
- Atomic values
- Partial dependencies
- Transitive dependencies
- Super key
Take it one step further
Try answering the follow-up before moving on.
When would you deliberately de-normalize a database table, and what are the trade-offs?
Explain Database Indexing and when to use B-Trees vs Hash IndexesDBMS
The explanation
Indexing is a data structure technique to quickly locate data without scanning the entire table.
B-Tree Index (default in most RDBMS):
- Stores data in a balanced tree structure with sorted keys.
- Supports range queries (WHERE age > 25), ORDER BY, and pattern matching with prefix (LIKE 'abc%').
- Time complexity: O(log N) for search, insert, delete.
Hash Index:
- Uses a hash function on the key to directly map to the data block.
- Only supports equality lookups (WHERE id = 42). Does NOT support range queries.
- Better for exact-match lookups (e.g., primary key lookups, unique column lookups).
- Time complexity: O(1) average for equality lookups.
When to use which? - B-Tree: Range scans, sorting, composite index on multiple columns (leftmost prefix rule applies). - Hash Index: High-throughput key-value lookups (e.g., in-memory cache with adaptive hash indexes in InnoDB).
Points to cover in your answer
- B-Tree: sorted
- range queries
- O(log N); Hash: equality-only
- O(1); Clustered vs Non-clustered
Take it one step further
Try answering the follow-up before moving on.
What is a covering index and how does it eliminate table lookups?
What is a Database Transaction and how does Concurrency Control work?DBMS
The explanation
A transaction is a sequence of database operations treated as a single logical unit of work.
Concurrency Control problems: 1. Dirty Read: Reading uncommitted data from another transaction. 2. Non-Repeatable Read: Same query returns different results within the same transaction because another transaction modified the data. 3. Phantom Read: Same query returns different rows because another transaction inserted/deleted rows.
Isolation Levels (lowest to highest consistency): 1. Read Uncommitted: No isolation; dirty reads possible. 2. Read Committed: Only committed data is read (prevents dirty reads). Default in PostgreSQL, Oracle. 3. Repeatable Read: Same read always returns the same rows (prevents non-repeatable reads). Default in MySQL/InnoDB. 4. Serializable: Transactions execute as if they ran sequentially (prevents all anomalies including phantoms).
Mechanisms:
- Pessimistic Locking: Locks rows/tables explicitly (e.g., SELECT ... FOR UPDATE).
- Optimistic Locking: Version numbers or timestamps; check at commit time and roll back on conflict.
- MVCC (Multi-Version Concurrency Control): Each transaction sees a snapshot of data as of a point in time; writers don't block readers.
Points to cover in your answer
- Dirty Read
- Non-Repeatable Read
- Phantom Read
- MVCC
- Isolation Levels
- Row-level locks
Take it one step further
Try answering the follow-up before moving on.
How does MVCC work in PostgreSQL vs MySQL InnoDB?
What does transaction isolation guarantee, and how do isolation levels differ?DBMS
The explanation
Isolation controls which effects of concurrent transactions each transaction may observe. It does not mean every database implements the same level in exactly the same way.
At Read Committed, a statement sees committed data, so a repeated query can see a later commit. Repeatable Read provides a stronger stable view or locking behavior, depending on the database. Serializable aims for outcomes equivalent to some serial ordering; the database may reject a conflicting transaction, which the application must retry.
For a money transfer, explain both the invariant being protected and the retry path. The right level is a correctness choice with performance trade-offs, not simply “always use the strongest.”
Points to cover in your answer
- Visibility of concurrent writes; database-specific semantics; serializable retries
Take it one step further
Try answering the follow-up before moving on.
How would you prevent two buyers from purchasing the last item?
How does a composite index help a query, and when might it not help?DBMS
The explanation
A composite index stores keys in a defined column order. For an index on (customer_id, created_at), a query filtering by customer_id and ordering or filtering by created_at can often use the index efficiently. A query filtering only by created_at usually cannot use the same leading-key access pattern as effectively.
The optimizer also considers selectivity, table size, returned columns, and whether scanning the table is cheaper. Indexes consume storage and slow writes because they must be maintained. Verify a proposed index with the database's execution plan and representative data instead of assuming every indexed query is fast.
Example: For WHERE customer_id = 42 ORDER BY created_at DESC, an index on (customer_id, created_at) can locate one customer's rows and read them in time order. Reverse the index columns and this access pattern may be less useful. If the query returns most of a tiny table, a sequential scan can still win. Ask for the actual query, cardinalities, and EXPLAIN plan before recommending another index.
Points to cover in your answer
- Column order; leftmost prefix; read/write trade-off; execution plan
Take it one step further
Try answering the follow-up before moving on.
What is a covering index, and how would you measure its benefit?
Step 6 of 7 · Core interview practice
Put this step into practice.
Answer three questions out loud before revealing their explanations.
Enable JavaScript to save progress. You can still continue through the path.
