Practice And Capstone
Where to Go Next
Module 4 ER modelling and normalisation
JrCodex·6 min read
Jr Codex DBMS Notes
Level: All levels Prerequisites: Chapter 2: Capstone Time to complete: ~15 minutes
Table of Contents
- What This Curriculum Covered
- The Six Ideas Worth Keeping
- Where This Sits in the Jr Codex Path
- Interview Preparation
- Directions for Depth
- A Final Word
1. What This Curriculum Covered
The Arc
─────────────────────────────────────────
Module 1 why databases exist; ACID
Module 2 relations, keys, the algebra
Module 3 SQL, fluently
Module 4 ER modelling and normalisation
Module 5 pages, B+ trees, the buffer pool
Module 6 plans, joins, the optimiser
Module 7 transactions, isolation, MVCC
Module 8 write-ahead logging and recovery
Module 9 NoSQL, CAP, choosing a system
Module 10 tuning, and a full design
─────────────────────────────────────────
The Shape of It
─────────────────────────────────────────
Modules 1-4 are what most courses cover: the
model, the language, the design.
Modules 5-8 are what separates someone who USES a
database from someone who UNDERSTANDS one — and
they are where every hard production problem
actually lives.
─────────────────────────────────────────
2. The Six Ideas Worth Keeping
1. A FACT BELONGS IN EXACTLY ONE PLACE
─────────────────────────────────────────
Normalisation is not bureaucracy. Every copy of a
fact is a future disagreement, and the database
cannot tell you which copy is right.
2. CONSTRAINTS IN THE SCHEMA, NOT THE CODE
─────────────────────────────────────────
A rule in the schema holds for every writer,
forever, including the migration and the 2am
manual fix. A rule in application code holds for
the paths someone remembered.
3. DISK READS DOMINATE EVERYTHING
─────────────────────────────────────────
B+ trees, the buffer pool, covering indexes,
narrow rows, column stores — every one is an
answer to "touch fewer pages".
4. A BAD PLAN IS A BAD ESTIMATE
─────────────────────────────────────────
Not a bad optimiser. Find the first
estimate-versus-actual divergence and fix the
input, and the plan corrects itself — and keeps
correcting itself as the data grows.
5. CONCURRENCY IS A DIAL YOU MUST SET
─────────────────────────────────────────
Isolation levels, quorums, consistency models:
the database offers a range and defaults to a
weak setting. Choosing per operation is your job,
not the database's.
6. LOG THE INTENT BEFORE DOING THE WORK
─────────────────────────────────────────
Write-ahead logging, idempotent replay,
compensation records. The most transferable idea
in the curriculum — you will meet it in
filesystems, queues, payment APIs and agent
frameworks.
─────────────────────────────────────────
3. Where This Sits in the Jr Codex Path
The Systems Strand
─────────────────────────────────────────
Python the tool
↓
DSA algorithms and structures
↓
┌─────────────────┬──────────────────────┐
│ DBMS │ Computer Networks │
│ (this one) │ │
└─────────────────┴──────────────────────┘
the two systems subjects every backend
engineer is expected to know
These sit alongside the AI strand — Data Science,
ML, AI, Deep Learning, NLP/LLM, Generative AI,
Agentic AI — rather than inside it.
─────────────────────────────────────────
What Came From Where
─────────────────────────────────────────
DSA Module 7 (Trees) ──► B+ trees, rebuilt
for disk
DSA Module 8 (Hashing) ──► hash indexes and
hash joins
DSA Module 1 (Complexity) ──► the optimiser's
cost model
Python Module 3 ──► sqlite3 throughout
And forward: the Computer Networks Notes explain
the round trips that make distributed
transactions expensive (Module 9), and the
Agentic AI Notes reuse this curriculum's
write-ahead logging for durable agent state.
─────────────────────────────────────────
4. Interview Preparation
What Is Actually Asked
─────────────────────────────────────────
VERY COMMON
Normalisation to 3NF, with an example
(Module 4)
Difference between the join types (Module 3)
What ACID means, with a real scenario
(Module 1)
Why an index makes a query fast (Module 5)
Clustered vs non-clustered index (Module 5,
Chapter 1)
COMMON AT MID-LEVEL
Isolation levels and which anomalies each
permits (Module 7)
Deadlock: cause and prevention (Module 7,
Chapter 5)
Reading an execution plan (Module 6)
Composite index column order (Module 5,
Chapter 3)
SENIOR
Design a schema for <domain>, then justify it
MVCC versus locking (Module 7, Chapter 4)
CAP and what your system chose (Module 9)
How would you diagnose this slow query
(Module 10, Chapter 1)
─────────────────────────────────────────
The Two Answers That Distinguish People
─────────────────────────────────────────
"WHY IS THIS QUERY SLOW?"
Weak: "add an index".
Strong: "I would EXPLAIN ANALYZE it, find the
first node where the estimate diverges from
actual, and work out why that estimate is
wrong — usually stale statistics or correlated
columns."
"HOW DO YOU PREVENT OVERSELLING?"
Weak: "check before inserting".
Strong: "that is a lost update. Put the guard
in the UPDATE's WHERE so check-and-write is one
statement, add a CHECK constraint so the state
is unrepresentable, and an idempotency key so
retries are safe."
Both differences are METHOD, not memorised facts.
─────────────────────────────────────────
5. Directions for Depth
IF YOU BUILD APPLICATIONS
─────────────────────────────────────────
→ Modules 5, 6 and 7 in depth. Read plans until
it is automatic. Learn your ORM's generated SQL
— it is usually where the N+1 queries hide.
IF YOU WANT TO BE THE DATABASE PERSON
─────────────────────────────────────────
→ Module 8 and Module 10. Learn your database's
internals documentation, run a real replication
setup, and rehearse a PITR restore until it is
boring.
IF YOU WANT DISTRIBUTED SYSTEMS
─────────────────────────────────────────
→ Module 9, then consensus: Raft first, since it
was designed to be understandable, then Paxos.
Then read the Spanner and Dynamo papers — they
are the two poles of the design space.
IF YOU WANT DATABASE INTERNALS
─────────────────────────────────────────
→ Build one. A small B+ tree, a page manager, a
WAL, a simple executor. Every module here
becomes concrete the moment you implement it,
and it is genuinely achievable in a few
weekends.
─────────────────────────────────────────
Reading Worth the Time
─────────────────────────────────────────
The ARIES paper (Module 8's recovery algorithm)
The Dynamo paper (Module 9's eventual
consistency)
Your database's own source and documentation —
PostgreSQL's is unusually readable
"Designing Data-Intensive Applications", for the
distributed half of Module 9
─────────────────────────────────────────
6. A Final Word
Databases are the part of a system that outlives everything around it. Frameworks are replaced, services are rewritten, the language changes — and the schema, with its constraints and its accumulated data, persists through all of it. That is why a wrong decision in Module 4 costs more than a wrong decision almost anywhere else, and why the discipline of getting it right is worth the effort.
The most valuable thing this curriculum can leave you with is not a list of normal forms or isolation levels. It is a habit: when something is slow, measure before changing; when a rule matters, put it where the database enforces it; when data must be correct, use a transaction; and when someone says a database cannot do something, check whether they mean cannot or have not tried.
That habit will still be useful when every specific technology named here has been replaced.
→ Pair this with the Computer Networks Notes — the other systems subject, and the one that explains why every distributed idea in Module 9 costs what it does.
Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index