Lesson 20 of 20

In progress

Course progress

SQL

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.

SQL
Editable. Runs in your browser.





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

SQL
Editable. Runs in your browser.





Press Run to see the output.

Question one: what is out now

SQL
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

SQL
Editable. Runs in your browser.





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

SQL
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

SQL
Editable. Runs in your browser.





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
SQL
Editable. Runs in your browser.





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?
A `fines` table keyed by loan is the natural next step. Money in paise, a paid flag and a payment date cover most cases.
What if a member wants to borrow the same copy again?
The loan history keeps both rows. The open-loan condition is `returned_on IS NULL`, so a returned copy can be borrowed again.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.