The previous lesson ended by saying that one thing is left which you can't learn by reading: doing it yourself. That starts here. For eleven modules you've worked on GreenStore, a database that came to you ready-made: the schema was written, the data loaded and the deliberate gaps placed exactly where the lessons needed them. That never happens in a real job. What arrives is an email, a forty-minute meeting and a few spreadsheets, and out of that you have to produce a model, a DDL, some data and queries that answer questions nobody has yet asked you precisely.

That's why the final project isn't GreenStore again. It's a new domain —related, but different— that exercises exactly the same skills and forces you to model from scratch: the Alvorada public library network. It has two twists GreenStore didn't have, and they're precisely where someone who has understood the relational model parts company with someone who has memorised queries: the loan has states and dates, with its delays and its fines, and a single title has several physical copies.

1. The brief, as it arrives
2. Reading the brief with a modeller's eye
3. The twist in the project: work versus copy
4. The business rules, numbered
5. The life cycle of a loan
6. What you deliver and what's out of scope
7. GreenStore as a map: where it helps and where it breaks
8. How the work is organised
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

What follows is the email from Helena Corvo, director of Alvorada's public library network. Read it all before going on, and read it for what it is: the words of someone who knows about libraries and not about databases.

Good morning,

We're three public libraries: the Central, the one in Vila Nova and the Infantil do Parque, which opened recently. Between the three of them we have a few thousand members and right now we run everything on spreadsheets, one per library, and it no longer works for us. We need to know what books we have, where each one is and who has it right now.

The main problem is that one book can be in several libraries at once. Of The Garden of Hours we have three, two at the Central and one in Vila Nova, and in the spreadsheet it appears three times with the same title and the same ISBN, so if somebody looks the book up they don't know which of the three rows to read. And when we lend it we write down the title, not the specific book, so we never know which of the three has come back.

Members come in three kinds: children, general and senior, and they don't all keep the books for the same length of time. We need to be able to renew a loan, but not always, and if someone is late there's a small fine per day. If they build up a lot of debt we suspend their card until they pay. We also want to manage reservations: when every copy of a book is out, the member puts their name down and we let them know when one comes back, in order.

We're very keen to be able to pull reports: the most-read books, the ones nobody ever takes out, the members who haven't been in for a long time, how much money we're failing to collect in fines and how each library is doing compared with the others. Right now we do that by hand and it takes us a whole day.

About the staff, we only need to know who works at each library and who they report to, for the rotas.

Best wishes, Helena

That email is your starting specification. It contains everything you need and, like every real specification, it also contains ambiguities, words used with two meanings and rules mentioned in passing ("not always", "a small fine", "a lot of debt"). The work starts here.

The first pass consists of underlining the nouns and classifying them. Not every noun is a table: some are entities, some are attributes of an entity, and some are both depending on the context.

Notice three things the client hasn't said and that you have to ask about, or decide and document:

• "A few thousand members" isn't a number. For the project we pin it down: the system must work with tens of thousands of members and hundreds of thousands of historical loans. That order of magnitude is what justifies module 8's indexes; with fifteen rows, anything works.
• "A small fine per day" isn't a rate. It has to be fixed, and you have to fix which day it starts counting from and whether it's capped.
• "The members who haven't been in for a long time" isn't a definition. A year without loans? No loans since signing up? It's exactly the problem 11-04 raised: half of analysis errors aren't about SQL, they're about definitions.

This is the most important section of the lesson, and the part of the project where most people come unstuck.

When Helena writes "of The Garden of Hours we have three", she's using the word book with two meanings at once:

• The work: The Garden of Hours, by Marina Solís, Editorial Aurora, 2015, ISBN 978-84-1001-001-1. It's one thing. It has a title, an author, a publisher, a year, an ISBN and a subject. You search for it in the catalogue, you reserve it and you recommend it.
• The copy: the physical volume with barcode ALV-0002, bought in March 2016, which is at the Central, has a coffee stain on page 40 and is currently with Sofía Terán. It's one of three distinct things. It's lent, returned, repaired and withdrawn.

That diagram contains the whole idea. You lend a copy; you reserve a work. When a member reserves The Garden of Hours they don't care in the slightest which of the three they get; when they bring one back, it matters enormously which one it is, because it has to be checked in against the right loan and put back in its branch.

The temptation, and it's a strong one, is to have a single books table with a copies INTEGER column saying "3". That's the same shortcut as products.stock in GreenStore, and here it doesn't hold. The difference:

The general rule, and it's worth burning into memory because it comes back in a thousand domains (vehicles in a fleet, software licences, hotel rooms, aircraft seats): if the object has its own attributes, its own location or its own history, it needs its own row. If it's interchangeable and all you care about is how many there are, a counter is enough.

And the practical consequence in the project, which you'll see in every query of the module:

• "How many works are there in the catalogue?" → COUNT(*) FROM works → 12.
• "How many books are there on the shelves?" → COUNT(*) FROM copies → 20.
• Both questions are legitimate, both answers are correct and whoever conflates the two publishes reports that don't add up.

These are the rules the project has to satisfy. They're numbered so you can refer to them in the code, in the comments and in the final report: "the chk_loan_due constraint implements BR-01" is a sentence a reviewer understands without asking anything.

BR-09 and BR-10 together are the metric-definition example 11-04 was asking for: "fines for the month" and "money owed to us" aren't the same thing, because the second includes the potential debt of overdue loans not yet returned and the first doesn't. Deciding which one you publish is your job; not saying so is the mistake.

GreenStore had an orders.status with five values stored in a column. Here the cycle is richer and, above all, most of it is derivable from the dates: that's a design decision we'll discuss in 12-03, and it has defensible alternatives.

Read it carefully, because out of this diagram come three columns and one whole table:

• loan_date and due_date are born with the row.
• return_date is NULL while the loan is alive. That NULL is the "active" state, and it's the column half the project will revolve around: the partial unique index, the overdue view and almost every query.
• renewals counts the turns of the active → active loop.
• overdue isn't a column: it's return_date IS NULL AND due_date < CURRENT_DATE. Storing it would mean storing something that changes just because midnight went by, and you'd have to update it every night.
• fines is a table because the "fined → paid" node has its own date, its own amount and its own life.

What you deliver is four files, detailed in 12-02:

What's out of scope, and saying so is part of working professionally —a scope with no boundaries is a project that never ends—:

• The user interface. No screens, no website, no front-desk application. Everything is done from SQL.
• Real bibliographic cataloguing. No MARC21, no authority control, no ISBD. Here a work has a title, an author, a publisher, a year, an ISBN and a subject, and that's enough.
• Purchasing, budget and suppliers. The copy shows up with its acquisition date; where it came from and what it cost aren't modelled.
• System users, passwords and sessions. There are database roles (11-03), which is a different thing.
• Events, rooms and digital cards. These are real libraries and they do far more than this; the project sticks to catalogue, loan, reservation and fine.

Don't start from a blank page. A good part of the project is GreenStore under other names, and taking advantage of that isn't cheating: it's what a professional does when they recognise a pattern.

And here is where the parallel breaks and you have to think afresh:

1. orders + order_lines versus loans. An order is a header with N lines; a loan is one row and a single copy. If a member takes three books away, that's three loans, not one loan with three lines — because each one is returned on its own, renewed on its own and generates its own fine. If one day "loan tickets" were needed to group what somebody took out in one go, then there would be a header and a detail. Today it isn't needed, and adding it is over-engineering.
2. products.stock versus copies. Already covered in section 3. A number against a table.
3. orders.status versus the loan's state. In GreenStore the status is a column with a CHECK, because shipped can't be deduced from any date. Here, active, overdue and returned are deduced from two dates and the clock, and storing them would mean maintaining by hand something that changes on its own.
4. The N:M moves. In GreenStore the N:M (order_lines) carried its own data: quantity, price, discount. Here the N:M is works_authors, almost pure, with a composite primary key and no id of its own — which is exactly the alternative 05-01 raised and GreenStore didn't choose. Now you do choose it, and you have to know why.

The order matters and the advice is firm: don't read 12-04 before you've attempted it. The solution set teaches an enormous amount compared against your own work and almost nothing read cold. Build the model, write the DDL, get it wrong, fix it, and only then compare.

• Starting with CREATE TABLE. The urge to open the editor and start writing tables is very strong and it's a mistake. First the diagram on paper, even if it's only fifteen minutes: changing a drawn box costs a crossing-out; changing it in a schema with data loaded costs a migration (05-06).
• Modelling what the client said instead of what they meant. Helena says "book" and means two things. Your job is to spot it and go back and ask, not to guess in silence.
• Modelling everything that occurs to you. You could add rooms, events, donations, bindings and disciplinary sanctions. Don't: every table that doesn't answer a requirement is debt. The scope in section 6 is the boundary.
• Storing what can be computed. Before creating a column, ask yourself whether it's a fact (the date it was lent) or a consequence (that it's overdue). Facts get stored; consequences get computed — except for due_date, which 12-03 justifies.
• Trusting your memory for the business rules. Write them down numbered, as in section 4, and cite the number in every constraint and every query. In three weeks' time you won't remember why the fine cap was €20.
• Tip: name everything from minute one. snake_case, PK id, FK <table>_id, constraints with pk_, fk_, uq_, chk_ and indexes with idx_ (05-01, 11-02). Renaming later is tedious and you always forget one.
• Tip: fix a "today" for the project. Every query in this module uses DATE '2026-06-30' instead of CURRENT_DATE, so the results are reproducible. In production it would be CURRENT_DATE; in a deliverable somebody is going to mark, a fixed date is the difference between "I get something different" and "I get the same thing".
• Tip: reread this brief when you finish. The final test of a model isn't that it's elegant: it's that it answers what Helena's email asked for, including the reports in the fifth paragraph.

The exercises in 12-01 and 12-02 are project tasks. They have no closed solution: they have a sketch and a rubric, because the full solution arrives in 12-04 and comparing yourself against it before attempting the work robs you of the exercise.

Write a one-page glossary with all the terms in the brief, and for each one: what it means exactly, whether it's an entity or an attribute, and —if it's ambiguous— the two possible readings and which one you choose. It must include: work, copy, member, loan, renewal, reservation, fine, branch, subject, available, overdue, suspended.

The brief has at least eight decisions left untaken. Find them and, for each one, write (a) the question you'd put to Helena and (b) the decision you take while you wait for an answer, with its justification. Worked example: "Can a work that does have available copies be reserved? Ask Helena. In the meantime: no (BR-07), because if there's one on the shelf the member can take it right away and the queue adds nothing."

Without writing a single line of SQL, draw the project's entity-relationship diagram: boxes, relationships with their cardinality (1:N, N:M, self-referencing) and the three or four key columns of each box. Mark in a different colour the boxes that don't exist in GreenStore and write next to each one why it's needed.

Sketch for Task 1

The four entries that decide whether the glossary is right:

Rubric for Task 2

You're expected to have found at least six of these eight: (1) the fine rate and its cap; (2) which day the delay starts counting from; (3) the debt threshold that triggers suspension; (4) whether a suspended member can reserve; (5) what "a member who hasn't been in for a long time" means; (6) whether a copy can move between branches and whether that leaves a trace; (7) what happens to a lost copy and its open loan; (8) whether the member type is recalculated on a birthday or stays as it was at signup. The first three are settled in BR-09 and BR-11; the remaining five are your decisions, and what's assessed is that they're written down, not which one you pick.

Sketch for Task 3

The complete diagram is in 12-03, so compare only these five points, which are the ones that separate a correct sketch from a wrong one:

1. works and copies are two boxes, joined 1:N, and the arrow runs from the work to the copy.
2. loans hangs off copies, not works. If your arrow goes to works, the model can't say which specific book is out.
3. reservations hangs off works, not copies. If it goes to copies, you're reserving an object that may never come back, instead of a title.
4. works_authors is a box between works and authors, with a composite primary key and no id of its own.
5. librarians has an arrow to itself (supervisor_id) and another to branches.

New boxes compared with GreenStore: copies, reservations, works_authors and fines (this last one is a cousin of returns, but with a payment cycle of its own).

You now have the brief and you know how to read it:

• The project is the Alvorada library network: three branches, a few thousand members, a catalogue, loans, reservations and fines. A new domain, chosen precisely so that you model from scratch instead of inheriting a finished schema.
• The central twist is the work / copy distinction: a work is catalogued and reserved, a copy is lent. Three copies of The Garden of Hours spread across two branches are three rows with their own history, not the number 3 in a stock column. The rule that generalises it: its own attributes, its own location or its own history ⇒ its own row.
• The twelve business rules (BR-01 to BR-12) fix the terms by member type (14/21/30 days), the simultaneous maxima (3/5/5), the renewals (1/2/2) and when renewal isn't allowed, the FIFO reservation queue, the fine of €0.20/day capped at €20 issued on return, suspension above €10 unpaid and the history that is never deleted.
• The loan life cycle is mostly derivable: return_date IS NULL is "active", and "overdue" is that condition plus a date comparison. What does have a life of its own —the fine and its payment— is a table.
• GreenStore works as a map for subjects, publishers, members and librarians, and it breaks in four places: there's no header and detail, stock becomes a table, the state is derived instead of stored, and the N:M becomes a pure bridge table with a composite key.

With the domain understood, it's time to turn Helena's email into something that can be verified. In the next lesson, Project Requirements, you'll get the formal brief: the data requirements with the entities and attributes that aren't negotiable, the integrity ones with the constraints that must be declared in the database and not only in the application —including the hard one, the two active loans—, the numbered list of the 15 queries you have to deliver with the technique and the lesson each one exercises, the performance and security requirements, the delivery files and the marking rubric you can use to assess yourself.