Week 1

Stage 3 · Weeks 18–24 · data

Databases + SQL

Model/query persistent data

Locked

Scored stage progress

0%

Practice and implementation

0/8

Exercises + milestone requirements owned by this stage

Evidence and gate

0 · 0/10

Artifacts · checked requirements

Why it matters

Purpose of this stage

Model/query persistent data

Subjects

4 subjects · 43 canonical topics

Each subject states what you should be able to do, an order to work through it in, what to watch for, and the questions a reviewer should be able to ask you at the end. None of it is tracked or scored.

How to work through this subject

An order that works, not a checklist. Nothing here is tracked — the steps sequence this subject’s own topics and add none.

  1. Read what distinguishes a database from a file, and what an RDBMS adds on top.
  2. Take a small domain and write out its tables, columns and types on paper before touching SQL.
  3. Mark the primary key of each table, then draw the foreign keys as arrows between them.
  4. Classify each relationship as one-to-one, one-to-many or many-to-many, and note which one needs a third table.
  5. Add the constraints that make impossible rows impossible, and say for each what it prevents.
  6. Finish by explaining what the database guarantees that your application would otherwise have to enforce itself.

What to watch for

  • Constraints are not bureaucracy — they are the only thing preventing impossible data.
  • Relationship cardinality (one-to-many, many-to-many) determines table shape. Get it wrong and every query fights you.
  • A primary key is an identity promise. Without one, you cannot reliably talk about a single row at all.
  • NULL is not zero and not an empty string. It is 'unknown', and it propagates through comparisons in ways that surprise people.
  • Types are the cheapest constraint you have. Storing a date as text throws away every guarantee the database could have offered.
  • Design on paper first. Schema mistakes are the most expensive kind to fix once data exists.

Curriculum topics

database
An organised, persistent store of data that many programs can query safely.
RDBMS
A relational database management system — the software (like PostgreSQL) that stores tables and enforces their rules.
table
A named collection of rows sharing the same columns.
row
A single record in a table.
column
A named field in a table, with one type shared by every row.
type
The kind of value a column accepts — integer, text, timestamp — which the database enforces.
schema
The full definition of tables, columns, types and constraints in a database.
constraint
A rule the database enforces on the data, such as 'not null' or 'must be unique'.
primary key
The column whose value uniquely identifies each row in a table.
foreign key
A column pointing at another table's primary key, which the database keeps valid.
one-to-one
A relationship where each row on either side matches at most one row on the other.
one-to-many
A relationship where one row relates to many rows in another table — one user, many transactions.
many-to-many
A relationship where rows on both sides relate to many of the other, requiring a third join table.

Resources for this subject

  • REFERENCEPostgreSQL — Data Definition PostgreSQL
    The reference for the modelling half of this stage: column types, constraints, keys and what each one actually enforces. Read it while designing the schema, not after the first bad row.    Open
Need another explanation?1 more reference
  • REFERENCEPostgreSQL Tutorial postgresqltutorial.com
    Worked examples for schema design, constraints and psql operation, which the official tutorial covers only briefly.    Open

A reviewer should be able to answer these

Answer from memory, out loud, before moving on. These are not tracked and do not affect the gate — they tell you whether you actually understand this subject.

  1. What does a primary key guarantee, and what breaks without one?
  2. What does a foreign key enforce, and what happens on delete?
  3. How do you represent a many-to-many relationship, and why does it need a third table?
  4. When is NULL meaningful data and when is it a modelling mistake?
  5. What does the database do for you that application code would have to do otherwise?
  6. What is the difference between a schema and the tables it contains?
  7. When would a one-to-one relationship be worth two tables rather than one?
  8. Why does choosing a column type matter beyond saving space?

Exercises

Demonstrated practice · 35% base weight
This stage defines implementation through its owned project milestone rather than a separate exercise list.

Stage project

Only milestones owned by Stage 3 count here

P2 · Multi-stage project

Expense Backend

Project detail

P2.M2 · PostgreSQL persistence

Resources

0% progress weight

Resource status is a convenience marker only. It cannot change competencies, gates, or stage completion — in any tier below.

Start here

PRIMARY

PostgreSQL Tutorial

PostgreSQL

The official tutorial, worked in a real database rather than a browser sandbox. Do it against your own schema so the queries return data you can verify by hand.

Open resource
Need another explanation?6 supplementary resources · not part of the canonical library

Use these for a demonstrated gap, not as a parallel course. They are authored additions and can never be the primary resource for a subject.

  • REFERENCEPostgreSQL — Data Definition PostgreSQLThe reference for the modelling half of this stage: column types, constraints, keys and what each one actually enforces. Read it while designing the schema, not after the first bad row.Open
  • REFERENCEPostgreSQL Exercises pgexercises.comGraded exercises against a real schema, with answers hidden until you attempt. Structurally the closest thing to the source's exercise-first rule.Open
  • REFERENCESQLBolt sqlbolt.comShort interactive lessons from SELECT through joins. Useful as a first pass before the PostgreSQL tutorial's density.Open
  • REFERENCEUse The Index, Luke! Markus WinandExplains what an index actually does and when it fails to help. Directly supports the Stage 3 requirement to index one justified query.Open
  • REFERENCEPostgreSQL Tutorial postgresqltutorial.comWorked examples for schema design, constraints and psql operation, which the official tutorial covers only briefly.Open
  • REFERENCESQL & Database Design for Interviews rezapirighadim/interviewNormalization through BCNF, what a B-tree index costs on writes, and how to read EXPLAIN ANALYZE — then window functions, recursive CTEs and isolation levels. Read the indexing file when the stage asks you to justify one index on one query.The stage's PRIMARY, the PostgreSQL tutorial, never covers query plans or composite-index ordering. This fills that gap; it is not a second SQL course.Open Open Open

Stage gate

Readiness assessment

Not ready

Stage-specific requirements

Answer each prompt without notes. Expand a question only when you are ready to check your answer.
0/2
  • Design the relational schema from a small product description
    Answer

    A complete answer names the entities in the description, gives each a table with typed columns and a primary key, and connects them with foreign keys whose direction reflects the cardinality. Many-to-many relationships get a join table. Nullability and constraints are decided per column so that impossible rows cannot be inserted, and the timezone convention for timestamps is stated once.

  • Write its common queries without an ORM
    Answer

    Write the SQL directly: SELECT with WHERE, ORDER BY and LIMIT for retrieval, JOINs across the tables you modelled, GROUP BY with aggregates for summaries, and HAVING when you need to filter the groups rather than the rows. You should be able to predict how each join type changes the row count before running it, and wrap related writes in a transaction so a half-applied change is impossible.

Universal requirements

The eight conditions 01_LEARNING_RULES.md applies to every stage. Expand one to see what it actually asks for.
0/8
  • Core concepts can be explained from memory.
    What this asks for

    Every subject in this stage has review questions. The condition is met when you can answer them out loud, without notes and without looking anything up, in your own words rather than a recited definition. An explanation you can only give with the material open is recognition, not recall.

  • Required exercises are complete.
    What this asks for

    Complete means each exercise satisfies its stated requirements and its done-when criteria, not that you attempted it. Where an exercise says you should be able to explain or defend something, that explanation is part of completing it. A stage whose source lists no exercises meets this through its owned project milestone instead.

  • Stage project works.
    What this asks for

    Works means it runs end to end from a clean checkout, on the inputs it was built for, with the failure cases it claims to handle actually handled. It is not a notebook that produced a result once. Someone else following your README should be able to run it without asking you a question.

  • Tests or verification exist.
    What this asks for

    There is something that would fail if the behaviour regressed, and you have watched it fail. That can be automated tests or a written verification procedure with recorded results, depending on what the stage produces. A test that has only ever passed is evidence of nothing.

  • README explains decisions.
    What this asks for

    The README says what the project is, how to install, run and test it, and why it is built the way it is: the choices you made, the alternatives you rejected, and what you deliberately left out. A list of commands with no reasoning does not meet this condition.

  • You can reproduce the core implementation without copying.
    What this asks for

    Delete the central piece and write it again from an empty file, without your previous version, a tutorial, or generated code in front of you. It does not have to be identical, it has to work and you have to be able to explain every line. This is the condition that separates having finished from being able.

  • You pass the stage self-assessment at >= 80%.
    What this asks for

    Record a gate exam and score yourself honestly against the dimensions the roadmap defines: recall, implementation, debugging, testing and design judgment. Elapsed time, hours watched and completed resources count for nothing here. Checking the boxes without a recorded exam does not open the gate.

  • Claude/ChatGPT reviewer finds no foundational blocker.
    What this asks for

    Ask an AI reviewer to examine the work against this stage's concepts and report foundational misunderstandings, not style. A foundational blocker is a misconception that later stages would build on, such as leakage, a broken abstraction, or an explanation that is confidently wrong. Nothing in that class may remain open when you pass.

Recorded exam

No exam recorded. Checking every requirement is not sufficient.

Evidence

Artifacts linked to this stage

Add evidence
No stage evidence yet.