Skip to content
Tutorials Center
My learning

Topics

Lesson 73 of 79 ยท Projects

Project: Library Management System

Design a library database end to end: requirements, ER diagram, relational mapping, constraints, a loan-limit trigger, overdue and availability reports, and a 3NF check.

This is the first of the track's projects. You will take a small library from a paragraph of requirements to a working database, using what the earlier chapters taught: ER modelling, mapping to tables, integrity constraints, triggers and normalization. Everything runs live in your browser in SQLite. The lesson is a guided walk-through: read each step, run the code, then change it and see what breaks.

Work in the order a real designer does: requirements, conceptual model, tables and constraints, business rules, reports, and finally a design review against the normal forms. Each step has a small question to answer before you move on, so try to predict before you run.

Design first

Requirements What must the library track?
ER model Entities and relationships.
Tables Keys and constraints.
1 / 2

Step 1: requirements

A small public library asks for a system with these features.

  1. Members register with a name and a unique email address, and the join date is recorded.
  2. The catalog contains books. A book has an ISBN, a title and one category (Fiction, Science...). A book can have several authors, and an author can write several books.
  3. The library owns one or more physical copies of each book, each on a shelf. Members borrow copies, not titles.
  4. A loan records who borrowed which copy, when, the due date and the return date (empty while the book is out).
  5. A copy can be out on only one loan at a time, and a member may have at most 3 books on loan.
  6. Late returns cost a fine of 25 cents per day.
  7. Staff want reports: what is on loan, who owes fines, which titles are available, and how popular titles and members are.

Underline the nouns: member, book, author, category, copy, loan. Underline the rules: unique email, one open loan per copy, three books per member, fines. A good habit is to ask, for each noun, "does it have its own identity and attributes?" and, for each relationship, "how many on each side?"

Step 2: the ER model

Two modelling decisions are worth discussing before drawing.

Book versus copy. A title ("Salt Roads") and a physical object on a shelf are different things. A title has an ISBN and authors; a copy has a shelf location and a loan history. Merging them would repeat the title for every copy. So we have two entities with a 1:N relationship.

Loan as an entity. The same member may borrow the same copy many times, and each loan has its own dates. So a loan is a record with its own key, not a plain M:N link between member and copy.

erDiagram
    MEMBER ||--o{ LOAN : borrows
    COPY ||--o{ LOAN : "is lent in"
    BOOK ||--|{ COPY : "exists as"
    CATEGORY ||--o{ BOOK : classifies
    BOOK }o--|{ AUTHOR : "is written by"

Entities and cardinalities of the library: loan connects member and copy, and book-author is many-to-many.

Step 3: tables and constraints

Applying the mapping rules gives seven tables: member, category, author, book, copy, loan, and book_author for the M:N relationship. The constraints do a lot of the work:

  • Primary keys on every table, and UNIQUE on natural candidate keys (email, isbn).
  • Foreign keys for all links, with ON DELETE CASCADE only on book_author (a link row has no meaning without its book).
  • CHECK constraints on dates: a due date after the loan date, and a return date not before it.
  • A partial unique index on loan(copy_id) WHERE returned_on IS NULL expresses rule 5a: at most one open loan per copy, while any number of closed ones is fine.
  • Rule 5b (three books per member) cannot be expressed as a key or a simple check, because it depends on counting other rows. We use a trigger.

Step 4: build it, load it, ask questions

This script is the whole project: schema, trigger, sample data and four reports. The library's "today" is fixed at 2025-06-30 so the output never changes. Fines are calculated in a view: days late is the number of days between the due date and the return date (or today, if the book is still out), never below zero.

SQL The library database and its reports

Press Run (or Ctrl + Enter) to see the result here.

Check the four results against the data:

  • On loan (4 rows): "The Quiet Engine" is out to Ana and already overdue (due 2025-06-24); the others are due in July.
  • Fines: Ana owes 150 cents for 6 days late on a book she still has, and Boris owes 50 cents for returning a book 2 days late. Chen and Dara owe nothing and do not appear, because of the HAVING filter.
  • Availability: "Salt Roads" and "Small Numbers" have one of two copies free, while "Maps of Rain" and "The Quiet Engine" have no copy free.
  • Activity: Boris 3 loans, Ana 2, Chen 1 and Dara 0. Dara appears at the bottom only because of the LEFT JOIN; an inner join would drop her.

Notice a design decision hiding in Report 3: there is no status column on copy. Whether a copy is available is derived from the loans, which can never disagree with itself. A stored status column would have to be updated on every loan and return, and sooner or later it would be wrong. Store facts, derive state.

Step 5: do the rules really hold?

Constraints are only worth something when they refuse bad data. The next script gives Ana three open loans, shows that Boris is unaffected, shows that returning a book frees a slot, and finally tries a fourth open loan. That last statement fails on purpose with the message from the trigger.

SQL The three-book limit in action (ends with an error)

Press Run (or Ctrl + Enter) to see the result here.

The run ends with member already has 3 books on loan. To test the other rule, replace the last statement with a second open loan for copy 2 by Boris (INSERT INTO loan VALUES (6, 2, 2, '2025-06-12', '2025-06-26', NULL);). Now the trigger stays quiet and the partial unique index reports UNIQUE constraint failed, because copy 2 is already out.

A trigger counting rows is fine at library scale. In a busy system, two concurrent transactions could each see two open loans and both insert a third. Real servers close that gap with locking, serializable isolation or a counter row that is updated atomically (see Isolation Levels and Concurrency Anomalies).

Step 6: review the design against 3NF

A schema is in third normal form when, for every non-trivial functional dependency X -> A, either X is a superkey or A is a prime attribute (part of some candidate key). Go table by table and list the dependencies:

Table Key(s) Dependencies Verdict
member member_id; email member_id -> name, email, joined_on; email -> all Every determinant is a key
book book_id; isbn book_id -> isbn, title, category_id; isbn -> all OK
category category_id; name key determines everything OK
author author_id author_id -> name OK
book_author (book_id, author_id) no non-key attributes OK
copy copy_id copy_id -> book_id, shelf OK
loan loan_id loan_id -> all OK

The interesting case is the category. If book stored category_name directly, then isbn -> category_name and category_name would depend on the category and not on the key of the table, a transitive dependency that repeats the name in every book of that category. Splitting category out is the 3NF fix.

You can feel the difference by building the wide table the library might have started with, one row per loan with everything copied in. The next script tests which dependencies hold in the wide data. A dependency X -> Y is violated when some X value has more than one Y value, which a GROUP BY ... HAVING COUNT(DISTINCT ...) > 1 query finds.

SQL Finding dependencies in a wide table

Press Run (or Ctrl + Enter) to see the result here.

Zero violations for the first three lines means those dependencies hold in this data (data can only disprove a dependency, never prove it; the meaning of the business decides). But the key of the wide table is loan_id, and email -> name, isbn -> title and isbn -> category all have a determinant that is not a key. That is exactly why Ana's name is stored three times in five rows, and why the normalized design has one row per member and per book instead (3 members and 3 books against 5 wide rows). loaned_on -> email has 2 violations, as expected: a date does not determine a member.

Extension ideas

  • Add a reservation table for members waiting for a book that is out, with a queue order.
  • Make the loan period depend on the category (Science for 21 days) using a table of loan rules.
  • Add a rule that blocks new loans when a member has unpaid fines above a limit.
  • Create indexes for the report queries and compare plans with EXPLAIN QUERY PLAN (see Indexing Basics).
  • Grant staff and members different views of the data, following Authentication, Authorization and Access Control.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why are book and copy separate entities?

    A title and a physical object have different attributes and different relationships. Separating them avoids repeating title data per copy.
  2. 2. Which mechanism enforces "a copy is on only one open loan at a time"?

    The index is unique only among open loans, so many closed loans per copy are allowed but two open ones are not.
  3. 3. Why does the schema have no status column on copy?

    A stored status would have to be kept in sync with every loan and return. Deriving it from loan rows keeps one source of truth.
  4. 4. Why does Report 4 use a LEFT JOIN from member to loan?

    An inner join would drop members without loans. The left join keeps them, and COUNT of the loan column gives 0.
  5. 5. In the wide table, why is email -> name a 3NF problem?

    A non-trivial dependency whose determinant is not a superkey (and whose right side is not prime) violates 3NF and causes redundancy and update anomalies.

Summary

  • Build a database in order: requirements, ER model, tables and constraints, business rules, reports, design review.
  • Separate things that have different attributes and lifetimes (book and copy); give relationships that carry their own data an identity (loan).
  • Use keys, foreign keys, CHECK, and partial unique indexes first; use a trigger for rules that need counting other rows.
  • Derive state (availability, fines) from the facts instead of storing it.
  • Test constraints by trying to break them; a rule you never tried to violate is a rule you do not know works.
  • Check every table for 3NF by listing its functional dependencies; a GROUP BY ... HAVING COUNT(DISTINCT ...) query can test a dependency against data.

Next: Project: University Course Registration.