Database Fundamentals

Problem Cover the fundamentals of databases - the core concepts you are expected to reason about, not just define.

Be ready to discuss

• ACID: what Atomicity, Consistency, Isolation, and Durability each actually guarantee, and the mechanism behind each (undo logs, constraints, MVCC, WAL).
• Normalization: 1NF through 3NF and BCNF, the anomalies they eliminate, and when denormalization is a defensible trade-off for read performance.
• Indexing: B-Tree vs hash indices and what each is good for (ranges and ordering vs point lookups), plus composite indices and leftmost-prefix rules.
• Clustered vs non-clustered indices: how the clustered index defines physical row order, and why secondary index lookups may need a second hop to fetch the row.
• The cost of indices: every write must update every index, so indices trade write throughput and storage for read speed.
• Transaction isolation levels from Read Uncommitted to Serializable, and exactly which anomaly each prevents - dirty reads, non-repeatable reads, phantom reads.
• Query optimization: what EXPLAIN shows, how the planner uses statistics and cardinality estimates, and why a query "ignores" your index.
• Reasoning about trade-offs out loud rather than reciting definitions - the framing that separates a strong answer from a memorised one.