Relational Model
Relations, keys, integrity constraints, the algebra, transactions, ACID, isolation levels.
The relational model is the substrate on which most production databases run. It's not the only model that works, but it's the only one with thirty years of optimiser research, a closed algebra (any operation on relations produces a relation), and a query language (SQL) that survived every fad.
The two interview hot-spots here are keys (primary, candidate, foreign, composite — know what each is for) and transactions (ACID + isolation levels + the anomalies each level prevents). Get those deep and you'll handle any DBMS interview question that isn't a system-design pivot.
Key concepts
- A relation is a set of tuples; sets have no order and no duplicates
- Keys come in flavours — primary, candidate, foreign, composite, surrogate — each for a different purpose
- Referential integrity is enforced by foreign keys + ON DELETE / ON UPDATE clauses
- ACID is four properties; isolation levels are the user-facing knob on the I
- Phantom reads, write skew, and lost updates are the anomalies isolation levels are designed to prevent
Reference template
// Reasoning about a transaction
1. What are the reads? (and at what isolation level are they consistent?)
2. What are the writes? (and what locks does each take?)
3. What's the conflict set? (other transactions that overlap)
4. What's the failure mode? (deadlock? abort? serialization failure?)
5. What's the retry policy? (deterministic backoff? idempotency token?)Adapt to your problem; the structure is the load-bearing part.
Common pitfalls
- Treating READ COMMITTED as a serialization guarantee — it isn't
- Confusing SERIALIZABLE in Postgres (snapshot-based) with SERIALIZABLE in SQL Server (lock-based)
- Ignoring lost-update anomalies under READ COMMITTED
- Skipping the failure case in transaction design — every transaction can abort
Related topics
Items (6)
- Relational Model Concepts
Relations, tuples, attributes, domains. Why the relational model won — composability and a closed algebra.
Concept Foundational - Keys — Primary, Candidate, Foreign, Composite
What each key is for, how to choose a primary key, why surrogate keys are usually the right default.
Concept Foundational - Integrity Constraints
Entity integrity, referential integrity, domain constraints, CHECK constraints, and triggers as the catch-all.
Concept Foundational - Relational Algebra Basics
Selection, projection, join, union, difference. The algebra SQL is compiled into.
Building Block Intermediate - Transactions and ACID
Atomicity, Consistency, Isolation, Durability. What each one promises and where each one bends in practice.
Concept Foundational - Isolation Levels and Anomalies
Read-uncommitted to serializable; the anomalies each prevents (dirty read, non-repeatable, phantom, write skew).
Concept Intermediate