Case Study 1 — A Complete Capstone: The Library System

A worked, end-to-end capstone for the library domain — showing how the whole book comes together into one portfolio piece. Use it as a model for your own (whatever domain you chose).

1. Requirements & questions

A library tracks members, books (and their copies), loans, holds, and fines. Questions it must answer: which members have overdue loans? what are the most-borrowed titles this year? which books are currently available? which members owe fines? how many active loans does each member have (vs. their limit)?

2. Data model (ER)

Entities and relationships (Chapter 17): member, book (the catalog title), copy (a physical item of a book — a book has many copies), loan (a copy borrowed by a member), hold (a member's reservation on a book), author, category, fine. Key relationships: book↔author is M:N (junction book_authors); book→copies and member→loans are 1:N; a loan references one copy and one member.

3. Schema (DDL, normalized)

3NF, fully constrained (Chapters 14, 18–19):

CREATE TABLE members (
    member_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL, email text NOT NULL UNIQUE,
    joined_on  date NOT NULL DEFAULT current_date,
    status     text NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended'))
);
CREATE TABLE books (
    book_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn       text UNIQUE, title text NOT NULL,
    category_id integer REFERENCES categories(category_id)
);
CREATE TABLE copies (
    copy_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id    integer NOT NULL REFERENCES books(book_id) ON DELETE CASCADE,
    barcode    text NOT NULL UNIQUE
);
CREATE TABLE loans (
    loan_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    copy_id    integer NOT NULL REFERENCES copies(copy_id),
    member_id  integer NOT NULL REFERENCES members(member_id),
    loaned_on  date NOT NULL DEFAULT current_date,
    due_on     date NOT NULL,
    returned_on date,
    CHECK (due_on >= loaned_on),
    CHECK (returned_on IS NULL OR returned_on >= loaned_on)
);
CREATE TABLE book_authors (        -- the M:N junction (Ch. 17–18)
    book_id integer NOT NULL REFERENCES books(book_id) ON DELETE CASCADE,
    author_id integer NOT NULL REFERENCES authors(author_id),
    PRIMARY KEY (book_id, author_id)
);
-- (fines, holds, authors, categories similarly)

Note the constraints encoding rules (CHECK on dates, UNIQUE on email/isbn/barcode), the M:N junction for authors, and the book→copy distinction (a deliberate, correct modeling choice — Chapter 17's "course vs section" lesson applied).

4. Realistic data

A deterministic seed (a few dozen members/books/loans for demos) and a generator (Chapter 31) producing thousands of loans across years — enough that indexing matters.

5. Core queries (answering the requirements)

-- Overdue loans (not returned, past due)
SELECT m.name, b.title, l.due_on
FROM loans l
JOIN members m ON m.member_id = l.member_id
JOIN copies c  ON c.copy_id = l.copy_id
JOIN books b   ON b.book_id = c.book_id
WHERE l.returned_on IS NULL AND l.due_on < current_date
ORDER BY l.due_on;

-- Most-borrowed titles this year (aggregation + join, Ch. 6–7)
SELECT b.title, COUNT(*) AS times_borrowed
FROM loans l JOIN copies c ON c.copy_id=l.copy_id JOIN books b ON b.book_id=c.book_id
WHERE l.loaned_on >= date_trunc('year', current_date)
GROUP BY b.title ORDER BY times_borrowed DESC LIMIT 10;

-- Available copies (anti-join: copies with no open loan, Ch. 6/9)
SELECT b.title, c.barcode
FROM copies c JOIN books b ON b.book_id=c.book_id
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.copy_id=c.copy_id AND l.returned_on IS NULL);

Plus a window-function "rank members by loans this year" and a recursive query if categories are hierarchical (Chapters 11–12).

6–8. Indexes, transactions, views

  • Indexes (Ch. 23–24): loans(member_id), loans(copy_id), copies(book_id), a partial index loans(due_on) WHERE returned_on IS NULL for the overdue query — each justified by a query, EXPLAIN-verified.
  • Transaction (Ch. 26–27): "check out a book" = verify the copy is available (SELECT ... FOR UPDATE), insert the loan, enforce the member's loan limit — atomic, race-free.
  • View (Ch. 15): active_loans (open loans with member/title) used by the app and reports.

9–11. Security, app layer, operations

  • Security (Ch. 32): an app_rw least-privilege role; all access parameterized; member PII access controlled.
  • App layer (Ch. 29–30): a LoanRepository (parameterized) with check_out, return_copy, overdue_for_member; the checkout wrapped in a transaction.
  • Operations (Ch. 38): a nightly pg_dump, test-restored; monitoring of slow queries and dead tuples.

12. Documentation

A README (setup in minutes), a data dictionary + ER diagram, a query catalog, and a design-decisions doc: why book/copy are separate, why the partial index, why a surrogate PK + unique ISBN, why PostgreSQL (Chapter 37), and what would change at scale (partition loans by year? read replica for reports?).

The result

A complete, documented, working library database that touches every part of the book — and a design-decisions document that demonstrates judgment. Cloned from the repo, it sets up in minutes and answers every requirement question. That's a portfolio piece.

Discussion questions

  1. Why are books and copies separate entities? What breaks if you merge them?
  2. Which queries justify which indexes? Why a partial index for overdue loans?
  3. Why must "check out a book" be a transaction with FOR UPDATE?
  4. What would you put in the design-decisions document that a reviewer would find most impressive?
  5. ⭐ At 10 million loans, what one scaling change (Chapters 25/35) would you make, and why?