DBDBMS

23 topics · interview-ready · SQL-heavy

Databases, from ER diagram to query plan

A database is a bet that the same data will be asked different questions for decades. Everything here — normalisation, indexing, transactions, recovery — exists to make that bet safe, and each one has a price you should be able to name.

Start at topic 1 › Interview Q&A (64) Printable cheat sheet All four subjects

How to study DBMS

DBMS is the highest-yield of the four subjects for most backend interviews, because it's the one you'll be asked to do rather than describe — write this query, explain this plan, design these tables.

  1. Write SQL by hand, on paper. Not in an editor with autocomplete. Interviews ask for joins, aggregates and window functions written cold, and the gap between reading SQL and producing it is enormous. Three queries a day beats an hour of reading.
  2. Learn the internals through EXPLAIN. B+ trees, buffer pools and join algorithms feel abstract until you see the planner choose a hash join over a nested loop and can say why. Every internals topic here connects to something a query plan shows you.
  3. For every guarantee, ask what it costs. Serialisable isolation costs throughput and retries. An index costs write speed. Normalisation costs joins. Durability costs an fsync. Interviewers grade the tradeoff, because anyone can name the feature.
What interviewers are checking

Three things, in order of weight: can you write correct SQL under mild pressure; can you reason about why a query is slow and what index would fix it; and do you understand what a transaction actually promises when two users hit the same row. Definitions of 3NF matter for written exams; those three matter for the job.

The 23 topics

Modelling — getting the schema right

01
What a DBMS gives you
Why not files, three-schema architecture, data independence, DBMS vs RDBMS.
 02
The relational model
Relations, tuples, every kind of key, integrity constraints, referential actions.
 03
ER modelling
Entities, relationships, cardinality, weak entities, and mapping to tables.
 04
Relational algebra
Selection, projection, joins, division — the language behind the optimiser.
 05
Functional dependencies
FDs, Armstrong's axioms, attribute closure, finding candidate keys.
 06
Normalisation
1NF to BCNF worked step by step, plus 4NF and lossless decomposition.
 07
Denormalisation
When to break the rules deliberately, and how to keep copies honest.

SQL — the part you'll be asked to write

08
DDL, DML & constraints
Types, constraints, DDL vs DML vs DCL vs TCL, generated columns.
 09
Joins
Inner, left, right, full, cross, self, anti-join — with row-count intuition.
 10
Aggregation
GROUP BY, HAVING, the execution order that explains every error.
 11
Subqueries & CTEs
Correlated vs not, EXISTS vs IN, recursive CTEs for trees.
 12
Window functions
ROW_NUMBER, RANK, LAG, running totals — the top-N-per-group pattern.
 13
Views, triggers, procedures
Materialised views, when triggers are a trap, stored procedure tradeoffs.
 14
Correctness traps
NULL and three-valued logic, DISTINCT vs GROUP BY, injection, collation.

Internals — why your query is slow

15
Storage & buffer pool
Pages, heap files, row formats, the buffer pool, row vs column stores.
 16
Indexes
B+ trees, clustered vs secondary, composite order, covering, when not to index.
 17
Query processing
Parse to plan, join algorithms, cost estimation, reading EXPLAIN.

Transactions — what happens when two users collide

18
ACID
What each letter actually guarantees, and what it costs to provide.
 19
Schedules & serialisability
Conflicts, precedence graphs, conflict vs view serialisability, recoverability.
 20
Concurrency control
2PL and strict 2PL, timestamp ordering, MVCC, optimistic control, deadlock.
 21
Isolation levels
Every anomaly, which level prevents it, and write skew.
 22
Recovery
WAL, checkpoints, ARIES redo/undo, the durability/latency trade.
 23
Beyond one node
Replication, 2PC, sharding, NoSQL and CAP — where DBMS meets system design.
Plus

The interview section has a 64-question bank with answers, ten SQL problems with solutions (the format most rounds actually use), a query-tuning drill, and the schema-design walk-through.

The 23-day plan

One topic a day. Tick a day when you can explain it out loud and name what it costs. Progress is stored in this browser only.

0 of 23 done
Day 1read
Day 2read
Day 3read
Day 4read
Day 5read
Day 6read
Day 7read
Day 8read
Day 9read
Day 10read
Day 11read
Day 12read
Day 13read
Day 14read
Day 15read
Day 16read
Day 17read
Day 18read
Day 19read
Day 20read
Day 21read
Day 22read
Day 23read

The one big idea

Everything in this subject is one of four bargains. Naming which bargain a feature belongs to is how you sound like you understand it rather than remember it.

1 · Normalisation store each fact ONCE → writes can't create contradictions, and the schema enforces it COST: reads need joins, which get expensive — and impossible across shards 2 · Indexing keep a second sorted structure → find rows without scanning the table COST: every INSERT/UPDATE/DELETE maintains every index, plus memory and disk 3 · Isolation make concurrent transactions behave as if they ran one at a time COST: locking or version tracking, lower throughput, deadlocks and retries 4 · Durability a committed transaction survives a crash, guaranteed by the write-ahead log COST: an fsync per commit — which is why commit rate is bounded by disk, not CPU Every DBMS question is "which of these four am I buying, and what am I paying?"
Four bargains. If you can place a feature in one of these boxes and state the cost, you have answered the question properly.

Numbers worth memorising

8 KBPostgres page size (16 KB InnoDB)
3–4B+ tree levels for millions of rows
~100skeys per B+ tree node (high fan-out)
~100 nsbuffer pool (RAM) page read
~100 µsSSD page read on a cache miss
>99%healthy buffer pool hit rate
~1 msfsync on SSD — bounds commit rate
10k–50ksimple writes/s, one Postgres primary
~5%selectivity below which an index wins
+30–100%index overhead vs table size

The mistakes that cost marks

Wrong, and commonly said
  • "WHERE x = NULL finds the null rows." It finds nothing — NULL comparisons are UNKNOWN. Use IS NULL.
  • "Indexes make the database faster." They make reads faster and every write slower.
  • "Normalise as far as possible." Normalise to 3NF/BCNF by default, then denormalise deliberately where reads demand it.
  • "DELETE and TRUNCATE are the same." Truncate is DDL, doesn't fire row triggers, and can't be filtered.
  • "Serialisable just means slower." It also means retryable failures your application must handle.
Imprecise, and noticed
  • Confusing a candidate key with the primary key.
  • Saying "it's in 3NF" when the example actually violates BCNF.
  • Mixing up WHERE and HAVING.
  • Treating a view as if it stored data (it doesn't; a materialised view does).
  • Calling MVCC "a kind of locking" — its whole point is that readers don't lock.
  • Saying "NoSQL is faster" without naming which operation.
What a strong answer sounds like

"I'd add a composite index on (customer_id, created_at) in that order, because the query filters on customer and sorts by date — so the index satisfies both the lookup and the ordering, and the planner can stop early on the LIMIT. The cost is that this table takes 2,000 writes a second and every one now maintains an extra index, so I'd check whether an existing index already covers the prefix before adding another."

Up
‹ All four subjects

DBMS · All topics · Interview Q&A · Cheat sheet · CS Study