Helena's email is the context; this is the contract. A requirement is only useful if it can be checked: "the system must handle loans properly" isn't a requirement, it's a wish. "A copy can't appear in two loans with return_date IS NULL, and the database must reject the attempt" is one, because there's an objective way to find out whether it holds: try it and see whether it fails.

This lesson is that specification, grouped into six blocks —data, integrity, query, performance, security and delivery— and closed with the rubric the project is marked against, so you can assess yourself before calling it finished. Within each block there are two layers: what isn't negotiable, because it's what the project sets out to teach, and what's left to your judgement, because designing means choosing and in 12-04 you'll see that several different solutions are equally correct.

1. Mandatory conventions
2. Data requirements (DR)
3. Integrity requirements (IR)
4. Query requirements (QR): the 15 queries
5. Performance requirements (PR)
6. Security requirements (SR)
7. Delivery requirements (DL)
8. Quality criteria
9. Marking rubric
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

They're the course's own, and they're taken as read (05-01, 11-02). They're listed because they form part of the marking:

These are the minimum mandatory entities. You can add columns, and you can add tables if you justify them; what you can't do is remove any of these or merge two of them.

What isn't negotiable, and why:

1. The work / copy separation (DR-05 + DR-07). It's the point of the project. A single books table with a counter is an automatic fail, however well written the rest may be.
2. The N:M works_authors (DR-06). An author column in works doesn't allow two authors, and an author1/author2 pair is the repeated-columns antipattern (01-05). Here, yes: a composite PK (work_id, author_id) with no id of its own — it's a pure bridge table and that was the alternative 05-01 described.
3. The librarian hierarchy (DR-09). supervisor_id referencing its own table, nullable only at the top. It's what feeds 03-06's SELF JOIN and 10-02's recursive CTE.
4. The history that is never deleted (DR-10). No DELETE on loans. Closing member and copy records is logical, and the FKs pointing at loans must physically prevent the parent from being deleted.
5. loans hangs off copies and reservations hangs off works. Inverting either one breaks the model.

What's left to your judgement: whether publishers is a table or a text column (a table is asked for, but it's open to discussion), whether works carries a language or a page count, whether members stores a phone number, whether copies stores the shelfmark, whether reservations has a status table or a CHECK, and how you distinguish a lost copy from a withdrawn one.

Minimum volume of test data: 3 branches, 6 subjects, 5 publishers, 8 authors, 10 works, 15 copies, 12 members, 25 loans, 4 reservations and 4 fines. Any less and you can't check the queries; a great deal more adds nothing when it's done by hand. (12-03's reference data set uses 3 / 6 / 5 / 10 / 12 / 20 / 15 / 36 / 5 / 6.)

All these rules must be declared in the database. The application checking them as well is fine; only the application checking them is exactly what 05-01 called "trusting everybody to remember to validate".

This requirement deserves its own section because it's where you find out whether you've understood module 8. The rule is: there can be many loans of copy 1 —its entire history— but at most one with return_date IS NULL.

The four possible ways out, and why three of them don't work:

The solution the project asks for is a partial unique index (08-02): an index that contains only the rows meeting a condition, and which therefore imposes uniqueness only among them.

It reads literally: "among loans not yet returned, copy_id is unique". It's declarative, the engine enforces it, it takes up only as many entries as there are live loans —six, not hundreds of thousands— and as a bonus it speeds up every active-loan query. In 12-03 it's implemented and in 12-04 it's compared with the EXCLUDE constraint alternative.

Dialect note: partial indexes exist in PostgreSQL, SQLite and (with caveats) SQL Server, where they're called filtered indexes. MySQL doesn't have them, and there the usual solution is a generated column that is NULL when the loan is closed, plus a UNIQUE on it — because UNIQUE ignores nulls.

These fifteen queries are the 03-queries.sql deliverable. They're ordered by increasing difficulty and each one carries the technique it exercises and the lesson where it was taught, so you know where to go back to if you get stuck. They all use DATE '2026-06-30' as the reference date.

Five conditions that apply to all fifteen:

1. Every query is preceded by a comment saying what question it answers and, if it handles a metric, how it defines it (11-04). Does "loans" include the active ones? What about those of closed members? Write it down.
2. No query may lose rows through a badly chosen JOIN. QR-06, QR-12 and QR-15 have to show the zeros: the work with no copies, the month with no loans and the branch with nothing in a subject.
3. Every ranking's ORDER BY needs a tie-breaker. Without one, two runs can return different orders and the report stops being reproducible.
4. Every query delivered comes with its result. The SQL isn't enough: you have to have run it.
5. No query uses SELECT *. Explicit columns, always (11-02).

The project is tested with dozens of rows, but it's designed for tens of thousands of members and hundreds of thousands of loans. The indexes are justified at that size, not at the size of the test file.

PR-05 and PR-07 are the ones that actually get marked. Adding ten indexes is easy; explaining why those ten and not others is what shows judgement.

Three roles, following 11-03's model: privileges are granted to group roles and users are made members of them, never the other way round.

And four requirements about personal data, which here aren't decoration: a loan history is a record of what a person reads, one of the most sensitive pieces of data a library can hold.

• SR-04. No application role has DELETE on the history, and none owns the tables.
• SR-05. Management reports are served from aggregate views that don't expose what each member has read. Whoever needs the aggregate figure doesn't need the individual one.
• SR-06. Closing a member's account is logical; the subsequent anonymisation (replacing name, id card and email with neutral values while keeping the id) must be planned for, so an erasure request can be met without destroying the statistics.
• SR-07. The test data is entirely fictitious: made-up names, id numbers with a valid format but no real holder, and emails at @example.com, a domain reserved for precisely this. Never use real data, not even your own, in a file that's going to end up in a repository.

Optionally, and as an exercise from 11-03: implement RLS so a librarian only sees the members of their own branch. It adds to the rubric, but it isn't mandatory and it isn't worth delivering it badly.

DL-01. The files are run in order: 01, 02, 03. The whole sequence must work in one go on an empty database:

DL-02. The schema is idempotent by the same mechanism as greenstore.sql (01-06): DROP TABLE IF EXISTS ... CASCADE at the top, in reverse dependency order. That's the right thing for a create-from-scratch script; for a production system it would be versioned migrations (05-06), and saying so in the report earns marks.

DL-03. The files go into a git repository with their README.md (12-05), with no credentials of any kind.

DL-04. Every query in 03-queries.sql carries its QR-nn number above it, the question it answers and the lessons it applies.

These aren't numbered requirements: they're the difference between a project that works and one that is also well made. They come straight out of 11-02.

• Uniform formatting. Keywords in upper case, one clause per line, short and consistent table aliases, stable indentation. If two queries in the same file look different, it shows.
• Names that don't need a comment. return_date yes; rd no. uq_active_loan_per_copy yes; idx3 no.
• Comments that explain the why, not the what. -- The LEFT JOIN is mandatory: there are works with no copies at all is useful. -- Joins works with copies is noise.
• Not one query left unrun. An 03-queries.sql with a query that errors is the most expensive failure of all, because it costs nothing to avoid.
• Not one implicit definition. Every metric in the report says what it includes and what it excludes (11-04).
• No over-engineering. Three triggers, five materialized views and an audit table in a twelve-table project don't add up: they subtract, because they have to be maintained and defended.

Use it as a self-assessment checklist before you hand in. The weight column shows how much each block counts.

Four failures wipe out a whole block, however good the rest may be:

1. A single books table instead of works + copies → model scores zero.
2. IR-03 unsolved or solved only in the application → integrity scores zero.
3. A query in the deliverable that errors when run → queries score zero.
4. Real personal data in the repository → delivery scores zero.

• Reading the requirements once, at the start. Come back to this lesson at the end of each block and cross off what's done. Half of what gets lost on the rubric is forgotten material, not badly done material.
• Treating the minimums as targets. "12 members" is the floor for the queries to mean anything, not the goal. But more than a couple of hundred rows by hand is wasted time: for volume, generate_series (12-03).
• Confusing requirement with solution. IR-03 says what must hold; the partial index is how we solve it here. If you find another way that also guarantees it in the database, it's valid — and 12-04 has one.
• Solving in the application what the database is asked for. "My code already checks it before inserting" doesn't satisfy IR-03. Two simultaneous requests slip straight through, and that's exactly what module 9 was explaining.
• Writing the fifteen queries in one sitting and running them at the end. Run each one as soon as you write it and check the row count at every JOIN (11-04). A mistake in the third contaminates the twelve that follow.
• Tip: turn this lesson into a requirements.md file in the repository, with checkboxes. It's 11-02's checklist applied to your own project, and it's what whoever marks you will look at.
• Tip: write the hardest query you can see first (probably QR-11 or QR-12). If the model stands up to the hardest one, it stands up to the other fourteen; if it doesn't, better to find out before loading the data.
• Tip: save the output of every query to a file. When you change the schema or the data, you'll be able to compare and see what moved. It's 11-04's "control figure" applied to the project.

As in 12-01, these are project tasks: they come with a sketch or a rubric, not a full solution.

Turn the requirements into a work plan of your own: a table with the tasks, their dependencies on each other, an estimate in hours and the requirement each one closes. It must cover everything from the model to the delivery. If your plan doesn't include an explicit "generate test data with edge cases" task, it's incomplete.

Before writing any SQL, reason about IR-03: (1) why doesn't UNIQUE (copy_id, return_date) work in PostgreSQL, and in which engine would it work? (2) Write the exact INSERT that should fail and the one that must keep working. (3) What happens to the partial index if tomorrow it's decided that a copy can be lent "for reading room use" at the same time as it's out on a home loan?

Before QR-08, write the exact definition of these five metrics, saying what's in and what's out, in the style of 11-04's definitions table: loans in the period, active member, late rate, outstanding debt and available work. For each one, also say what other reasonable definition exists and how it would change the figure.

Rubric for Task 1

An acceptable plan has between 8 and 12 tasks and respects these dependencies: model → DDL → data → queries → indexes → views and security → report. The two planning mistakes that come up again and again are leaving the test data until the end —and only then discovering that the model can't represent a case— and leaving the report until the last day, when you no longer remember why you made half your decisions. Write the report as you decide.

Solution to Task 2

(1) Because in the SQL standard, and in PostgreSQL, NULL isn't equal to NULL, so two rows with (1, NULL) are not considered duplicates and the UNIQUE accepts both (04-03, 05-01). In SQL Server it would fail, because it treats all NULLs as equal for the purposes of a unique index — and in PostgreSQL 15+ you can imitate that with UNIQUE NULLS NOT DISTINCT, although for this case the partial index is still better because it also indexes only the live rows.

(2) A second open loan of the same copy must fail, and another closed loan of the same copy must keep working:

The first one returns:

(3) The index would stop being valid as it stands, because it would no longer be "at most one active loan" but "at most one of each kind". The solution would be to add a loan_type column and include it in the index: ON loans (copy_id, loan_type) WHERE return_date IS NULL. It's a good reminder that a constraint encodes a specific business rule, and that when the rule changes, the constraint changes with it — which, by the way, is an advantage: if the rule lived scattered through the application code, nobody would know where to touch it.

Sketch for Task 3

Two of the five, to set the level of detail expected:

The underlying lesson is 11-04's: there's no correct definition, there's a written definition. The serious problem isn't choosing badly; it's publishing two different figures in the same report without saying how they differ.

You now have the project's contract:

• 12 data requirements with the minimum entities and their attributes. The non-negotiable parts: the work / copy separation, the N:M works_authors with a composite PK, the self-referencing hierarchy of librarians, the history that is never deleted, and loans hanging off copies while reservations hangs off works.
• 14 integrity requirements, all declared in the database and all named. The hard one is IR-03 —one copy, a single active loan—, which can't be solved with UNIQUE (because of the nulls), nor with CHECK (it can't look at other rows), and which is solved with a partial unique index WHERE return_date IS NULL.
• 15 queries ordered by difficulty, each with its technique and its lesson: from the simple filter to the anti-join, from HAVING to the conditional aggregate, from the correlated subquery to LATERAL, and from the gapless time series to the pivot, the window and the recursive CTE. With five cross-cutting conditions: a comment with the definition, no lost rows, a tie-breaker in rankings, the result attached and not one SELECT *.
• Performance: indexes on the FKs you navigate, IR-03's partial one, one for the time series and another for title search, each justified with the query that uses it, two commented EXPLAINs and the list of what you decide not to index.
• Security: three least-privilege roles, no DELETE on the history, reports served by aggregate views, soft account closure with anonymisation planned for, and entirely fictitious data.
• Delivery: four files that run in order on an empty database, an idempotent schema, and the rubric of six criteria —model 25 %, integrity 20 %, queries 25 %, performance 10 %, security 10 %, delivery 10 %— with four failures that wipe out their whole block.

You know what has to be done and what you'll be measured against. What's missing is the how. In the next lesson, Project Implementation, you'll find the seven-step build guide: from the brief to the complete entity-relationship diagram, with the hard decisions justified —why copies is a table, why due_date is stored instead of computed, why fines aren't a column and how a queue is modelled—; the commented 01-schema.sql with the partial index explained in depth; how to generate coherent data and what edge cases it must contain; the method for writing queries without going wrong, with two worked examples; how to decide indexes from the queries and not the other way round; what to encapsulate in views, procedures and triggers without overdoing it; and the work schedule.