Topic 17.2
Question Bank: Fundamentals, Transactions and Indexes
In one line
Eighteen high-frequency questions on modeling, keys, normalization, ACID, isolation, locking, MVCC, B-trees and index design, each with a concise model answer you can deliver in about a minute.
Think of it like this
Flashcards before an exam. Short, precise answers to common questions free your time for the hard design problems.
Key ideas
- 01
Answer shape: definition in one sentence, mechanism in one or two, an example, and the trade-off or pitfall.
- 02
Link each answer back to the phase that teaches it if you hesitate.
Code & diagrams
1. Define it (one sentence)
2. How it works (one or two sentences)
3. Concrete example
4. Trade-off or pitfallExplain it without notes
What is normalization?
Why use primary keys?
Natural vs surrogate key?
What is a foreign key and referential integrity?
When should you denormalize?
Explain ACID.
Explain isolation levels.
What causes deadlocks?
Optimistic vs pessimistic locking?
Explain MVCC.
What is write skew?
How does a B-tree index work?
What is a composite index and the leftmost prefix rule?
What is index selectivity?
What is a covering index?
Why can too many indexes hurt writes?
Why is OFFSET pagination slow?
What is the N+1 query problem?
Practice
Answer all 18 aloud in under 25 minutes, then check against the model answers.
Trade-offs
- ↔
Memorised answers help speed; interviewers probe follow-ups, so understand the mechanism behind each.
Done when you can
I can answer every core question concisely with mechanism and trade-off.