Lesson 20 of 20
In progress
Capstone: a library database
Design and query a small library: authors, books, copies, members and loans, with constraints, an index, a view and a transaction.
This is the last lesson in the SQL track. You will design five tables, enforce the rules that matter, and answer the questions a librarian actually asks.
What you will learn
- Model a many-to-many relationship with a join table.
- Separate a title from its physical copies.
- Answer real questions with joins, aggregates and a view.
The design
authors— people who write books.books— a title, with a reference to its author.copies— physical items of a book, each with its own id.members— people who borrow.loans— one row per borrowing, linking a copy to a member.
The split between books and copies is the decision worth noticing. A library owns three copies of one title, and each can be on loan independently. Storing the loan against the title would lose which physical item is out.
Editable. Runs in your browser.
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
born_year INTEGER CHECK (born_year IS NULL OR born_year BETWEEN 1000 AND 2100)
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
title TEXT NOT NULL,
published_year INTEGER,
UNIQUE (author_id, title)
);
CREATE TABLE copies (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
condition TEXT NOT NULL DEFAULT 'good'
CHECK (condition IN ('new', 'good', 'worn', 'lost'))
);
CREATE TABLE members (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
joined_on TEXT NOT NULL DEFAULT (date('now')),
active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))
);
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
copy_id INTEGER NOT NULL REFERENCES copies(id) ON DELETE RESTRICT,
member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
borrowed_on TEXT NOT NULL,
due_on TEXT NOT NULL,
returned_on TEXT,
CHECK (due_on >= borrowed_on),
CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);
CREATE INDEX idx_loans_member ON loans (member_id, borrowed_on);
CREATE INDEX idx_loans_copy ON loans (copy_id, returned_on);
Press Run to see the output.
Every table has a primary key. The two foreign keys in loans are RESTRICT, because deleting a member or a copy with history should fail rather than erase the record. copies cascades from books, since a copy without a title is meaningless.
The unique constraint on (author_id, title) stops the same title being catalogued twice for one author, which is the most common import mistake.
Sample data
Editable. Runs in your browser.
INSERT INTO books (id, author_id, title, published_year) VALUES
(1, 1, 'Small Systems', 2021),
(2, 1, 'Small Systems Workbook', 2023),
(3, 2, 'Quiet Data', 2019),
(4, 3, 'The Reading Room', 2024);
INSERT INTO copies (id, book_id, condition) VALUES
(1, 1, 'good'), (2, 1, 'worn'), (3, 3, 'new'), (4, 4, 'good');
INSERT INTO members (id, name, email, joined_on) VALUES
(1, 'Sam', 'sam@example.com', '2024-11-02'),
(2, 'Tara', 'tara@example.com', '2025-01-20'),
(3, 'Umar', 'umar@example.com', '2025-05-11');
INSERT INTO loans (copy_id, member_id, borrowed_on, due_on, returned_on) VALUES
(1, 1, '2025-06-01', '2025-06-15', '2025-06-12'),
(2, 2, '2025-06-20', '2025-07-04', NULL),
(3, 1, '2025-07-01', '2025-07-15', NULL),
(4, 2, '2025-05-10', '2025-05-24', '2025-05-20');
Press Run to see the output.
Question one: what is out now
Editable. Runs in your browser.
Press Run to see the output.
Three joins, each on a key, filtered to unreturned loans. The copies table sits between loans and books, which is why the query needs both.
Question two: overdue against a reference date
Editable. Runs in your browser.
SELECT * FROM overdue_loans ORDER BY days_late DESC;
Press Run to see the output.
A view keeps the definition in one place: the member-facing list, the reminder email and the dashboard all agree on what overdue means. julianday subtracts dates and returns days, which is the SQLite way to get a duration.
Question three: most borrowed titles
Editable. Runs in your browser.
Press Run to see the output.
The LEFT JOIN matters: a book with no copies, or copies never borrowed, should still appear with zero. COUNT(l.id) counts loans rather than the placeholder row, which is the pattern from the left-join lesson.
Question four: borrowing inside a transaction
Editable. Runs in your browser.
INSERT INTO loans (copy_id, member_id, borrowed_on, due_on)
SELECT 2, 3, '2025-07-11', '2025-07-25'
WHERE NOT EXISTS (
SELECT 1 FROM loans
WHERE copy_id = 2 AND returned_on IS NULL
);
SELECT changes() AS inserted;
COMMIT;
Press Run to see the output.
The WHERE NOT EXISTS makes the insert conditional: it adds nothing when the copy is already out. changes() reports how many rows were affected, and the transaction means a failure anywhere leaves the database as it was.
In an application, the check and the insert would be one statement like this, or the insert would be wrapped in a unique partial index. The pattern to remember is "make the write express the rule", not "check, then hope nothing changed in between".
What the schema refuses
- A loan for a member that does not exist.
- A due date before the borrow date.
- A return date before the borrow date.
- A duplicate email address.
- Deleting an author who still has books.
- A copy whose book was deleted, because the cascade removes it first.
Those refusals are the design working. A database that accepts everything pushes the rules into every application that touches it, and they will not all agree.
Recording a loan against a book rather than a copy. Two copies of one title then cannot be on loan at once, and the librarian cannot tell which physical item is missing.
Add a query for members with no loans at all, and one for the number of copies of each title that are currently available.
Show solution
Editable. Runs in your browser.
SELECT
b.title,
COUNT(c.id) AS copies,
SUM(CASE WHEN l.id IS NULL THEN 1 ELSE 0 END) AS available
FROM books AS b
JOIN copies AS c ON c.book_id = b.id
LEFT JOIN loans AS l ON l.copy_id = c.id AND l.returned_on IS NULL
GROUP BY b.id, b.title
ORDER BY b.title;
Press Run to see the output.
Umar has no loans, so the first query returns him. The second counts every copy and marks it available when no open loan exists, because the join condition includes returned_on IS NULL: returned loans do not match, so they do not hide the copy. Putting that condition in WHERE would remove the available copies instead.
Recap
- Separate a title from its copies so each physical item can be tracked.
- Enforce the rules with keys, checks and a unique constraint, and index the columns joins filter on.
- Put the shared definition of overdue in a view and make the borrow write express its own condition.
That completes SQL: reading, summarising, joining, changing, designing and protecting data.
Should the library store fines?
What if a member wants to borrow the same copy again?
Progress is saved in this browser only. No account, no email.