Data Modelling
Which tables there should be
A table and the rules that govern it, and the question every course defines and nobody drills: which normal form does this break, on which dependency, and what does it cost on an update. Then keys, constraints a real engine applies in front of you, and the module that keeps the rest honest — when to put a copy back.
- Modules
- 5
- Drills
- 11
- Build steps
- 0
- Time
- ~5h
Walk out able to
Diagnose any table from its dependencies, split it without losing a row, and write the constraints that make the broken states unrepresentable.
What you drill
Rules into tables
Cardinality, junction tables, and what a cell may hold.
Closures and keys
Run the loop, then split the columns prime from non-prime.
Which form, which FD
1NF to BCNF as four tests, and the anomaly priced in rows.
Enforce it, then trade it
Constraints a real engine applies — and the cost of a copy.
The pitch
What you practise, and what you leave with
A six-table library schema in SQLite, ten statements it refuses, a cascade you measure, and one denormalisation you pay for.
You will practise
- Cardinality, junction tables, weak entities
- Attribute closures, superkeys, candidate keys
- 1NF to BCNF, and the FD that breaks each
- Constraints, referential actions, copy costs
Afterwards you can
- Name the form a table reaches, and why
- Price a redundancy in rows, not adjectives
- Write constraints that actually refuse writes
- Defend a schema you deliberately left in 3NF
Modules
5 modules, 31 items
Lessons explain one idea. Drills repeat it until it sticks. Build steps make something that exists afterwards.
- 1
Model what is actually there
~44 minTurn a page of stated rules into tables, and name the anomaly a wide one causes.
- LessonOne table that holds everything6 min
- LessonThree kinds of thing in one row6 min
- LessonThree ways the same copy hurts6 min
- LessonHow many, on each side8 min
- DrillRead the cardinality off the rule8 min
- DrillCount the tables it becomes10 min
- 2
Find the keys first
~40 minRun a closure to a fixpoint and decide, mechanically, what is a key and what is not.
- LessonWhat a dependency actually claims6 min
- LessonRun the closure to a fixpoint8 min
- LessonSuperkey, candidate key, prime7 min
- DrillClose a set of columns9 min
- DrillSuperkey, candidate key, or neither10 min
- 3
Name the form, name the FD
~51 minRun the four tests in order, name the dependency that fails, and price what it costs.
- LessonFirst: one value per cell5 min
- LessonHow a fact escapes its key8 min
- LessonThe loophole, and what closing it costs8 min
- DrillName the form it breaks12 min
- DrillPrice the redundancy9 min
- DrillJoin the pieces back together9 min
- 4
Make bad states impossible
~1hWrite the five constraint kinds, and find out which writes a real engine refuses.
- LessonThe five declarations6 min
- LessonWhat a null slips past9 min
- LessonWhat happens to the children8 min
- DrillWhich constraint refuses it9 min
- DrillWhat the parent delete leaves9 min
- DrillFind the line it will not take8 min
- LessonCreate the schema on your own machine9 min
- LessonNow break it12 min
- LessonDelete a branch and count the damage12 min
- 5
Decide when to put it back
~59 minPrice a copied fact in writes and staleness, then defend a schema you chose.
- LessonEvery copy has a price7 min
- LessonThree ways to keep a copy9 min
- DrillCount what the copy costs9 min
- LessonDenormalise one column, and pay for it14 min
- LessonThe schema you would defend20 min
More in Data & databases
Keep going
SQL: Queries
Say what a query returns before you run it.
Intermediate~3h6 modules
ORMs
Count the queries before the mapper sends them.
Intermediate~4h6 modules
SQL: Joins & NULL
The rows that found no partner, and the values that are not there.
Advanced~4h5 modules
SQL: Advanced
The clause you did not write decided the number.
Advanced~6h7 modules
Name the form it breaks, and what the break costs you.
The first item is free. ~5h of focused work, at your own pace.
Intermediate~5h5 modules