Library loans with transactions

01 Coverage

02 Requirements

1. Tables books, members and loans with primary and foreign keys (schema below). Turn foreign keys on.
2. book title|author|copies and member name add rows and print the new id.
3. borrow memberId bookId yyyy-mm-dd: fail with No copies left: title when copies is 0. Otherwise decrement copies and insert a loan due 14 days later, in one transaction.
4. return loanId yyyy-mm-dd: set returned only on an open loan and restore one copy, in one transaction. Print No open loan #id otherwise.
5. overdue yyyy-mm-dd lists open loans whose due date is before that date, oldest first.
6. delete-book id refuses while loans are open, and refuses when past loans still reference it.
7. Parameterised queries only.

03 Input and output

book Noli Me Tangere|Jose Rizal|1
book Florante at Laura|Francisco Balagtas|2
member Liza Manalo
member Paolo Tan
borrow 1 1 2026-03-01
borrow 2 1 2026-03-02
borrow 2 2 2026-03-02
return 1 2026-03-10
return 1 2026-03-11
overdue 2026-03-20
delete-book 2
delete-book 1

Book #1 added
Book #2 added
Member #1 added
Member #2 added
Loan #1: Noli Me Tangere to Liza Manalo, due 2026-03-15
No copies left: Noli Me Tangere
Loan #2: Florante at Laura to Paolo Tan, due 2026-03-16
Returned loan #1
No open loan #1
Overdue on 2026-03-20: loan #2 Florante at Laura (Paolo Tan), due 2026-03-16
Cannot delete book #2: it has open loans
Cannot delete book #1: it has loan history

Match the sample exactly first, then write at least two test inputs of your own.

04 Edge cases to test

• Borrowing the last copy twice
• Returning the same loan twice
• An unknown member or book id
• A badly formatted date

05 Database

Engine: SQLite (Java: org.xerial sqlite-jdbc; C#: Microsoft.Data.Sqlite). An embedded database needs no server, which suits a timed sitting.

• Schema and the key SQL statements are shown below.
• Any embedded or local database is fine in practice; the reference uses SQLite.

PRAGMA foreign_keys = ON;

CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  author TEXT NOT NULL,
  copies INTEGER NOT NULL CHECK (copies >= 0)
);

CREATE TABLE members (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  book_id   INTEGER NOT NULL REFERENCES books(id),
  member_id INTEGER NOT NULL REFERENCES members(id),
  borrowed  TEXT NOT NULL,          -- ISO date, e.g. 2026-03-01
  due       TEXT NOT NULL,
  returned  TEXT                    -- NULL while the book is out
);

-- Borrow (in one transaction):
--   SELECT title, copies FROM books WHERE id = ?;          -- must have copies > 0
--   UPDATE books SET copies = copies - 1 WHERE id = ?;
--   INSERT INTO loans(book_id, member_id, borrowed, due)
--     VALUES (?, ?, ?, date(?, '+14 days'));
-- Return:
--   UPDATE loans SET returned = ? WHERE id = ? AND returned IS NULL;
--   UPDATE books SET copies = copies + 1 WHERE id = (SELECT book_id FROM loans WHERE id = ?);
-- Overdue on a date:
--   SELECT l.id, b.title, m.name, l.due FROM loans l
--     JOIN books b ON b.id = l.book_id JOIN members m ON m.id = l.member_id
--    WHERE l.returned IS NULL AND l.due < ? ORDER BY l.due;
-- Delete a book only when it has no open loans:
--   SELECT COUNT(*) FROM loans WHERE book_id = ? AND returned IS NULL;   -- must be 0
--   DELETE FROM books WHERE id = ?;

06 Starter outline and hints

07 Practice rubric

Our own weighting for practice. It is not the DICT scoring scheme.

08 Self-check

Tick only what you have verified by running your own program.

09 Reference solution

Both versions compiled and reproduced the sample output on Java 21 and .NET 8 when this site was built. Reference solutions are in Java and C#. The 2023 FAQ copy also lists C, C++ and Visual Basic 6.0 / .NET; the same structure carries over, but you will need your language's own file and database APIs.

10 Exam-day habits

• Work without internet search to simulate exam conditions.
• Type the sample input yourself and compare your output line by line before you look at the reference.