Normalisation and a Double-Booking Race at a Library
Greenfield Library uses a single table to record loans: Loans(loan_id, book_id, book_title, borrower_email, borrower_name, date_out, date_due). The library has 4,000 borrowers and 12,000 books, and about 300 active loans. Staff have noticed three problems. First, when a borrower changes an email address, staff must update many rows and some rows are missed. Second, when the last loan for a newly bought book is returned and deleted, the book disappears from the catalogue. Third, two librarians can issue the same copy at the same moment: each checks that the book is available, both see 'available', and both insert a loan. The library plans to move to a relational database management system and wants a design that prevents these problems rather than relying on staff vigilance.
1. Identify the functional dependencies and normalisation problems in the Loans table, and propose a 3NF schema. Explain how the new schema prevents the update and deletion anomalies.
Read the worked answer
The dependencies are: loan_id determines all loan attributes; book_id determines book_title; and borrower_email determines borrower_name. The table is not in 3NF because non-key attributes depend on other non-key attributes: book_title depends on book_id, and borrower_name depends on borrower_email. Email is also a poor key because it changes.
Proposed schema:
- Borrowers(borrower_id PK, name, email UNIQUE)
- Books(book_id PK, title)
- Loans(loan_id PK, borrower_id FK, book_id FK, date_out, date_due, date_returned NULL)
The update anomaly disappears because an email change updates one Borrowers row. The deletion anomaly disappears because Books exists independently of Loans; deleting the last loan no longer deletes the only copy of the title. The insert anomaly also disappears because a new book can be added before it is loaned. Add constraints: date_due greater than date_out, foreign keys, and a partial unique index on book_id where date_returned is null to prevent duplicate active loans.
2. Design a concurrency-safe process for issuing a book. Explain why a simple check-then-insert fails and discuss one trade-off of your solution.
Read the worked answer
The check-then-insert is a race condition. Under the default isolation level, both transactions can read 'available' before either writes, so both insert loans and the last write wins or both succeed. The application cannot rely on a check that is separate from the write.
Use an atomic conditional update: UPDATE books SET available = FALSE WHERE book_id = ? AND available = TRUE;. If zero rows are affected, tell the librarian the book is taken. Alternatively, open a transaction, SELECT ... FOR UPDATE the book row, re-check, insert the loan, and commit. A partial unique index on active loans provides a database-level guarantee: CREATE UNIQUE INDEX ... ON loans(book_id) WHERE date_returned IS NULL. The conditional update is simple and holds a row lock briefly.
Trade-off: locking serialises issues for the same book, which is desired, but it can deadlock if multiple rows are locked in different orders. Keep transactions short and access rows in a consistent order. Higher isolation is safer but may cause retries and lower throughput; optimistic versioning is better when conflicts are rare.