The Library Card That Issued the Same Book Twice
The library at Greenfield School has 1,200 physical book copies, about 380 titles, and 450 students. For six years the librarian has tracked issues in one spreadsheet with the columns AccessionNo, Title, Author, ISBN, MemberID, MemberName, Class, IssueDate, DueDate. There are three copies of 'Wings of Fire' with different accession numbers but the same ISBN. Two students named 'A. Sharma' are in different classes, and one member changed from Class 8B to 9A last term. When a book is returned, the librarian deletes the row to keep the sheet small. The principal now wants a monthly overdue list and a history of who borrowed each copy. A CSV export is being prepared for import into a database, but some titles contain commas, some DueDate cells are blank, and a few rows have the same AccessionNo twice because of copy-paste errors. The librarian has one week and no budget for new software beyond a free relational database.
1. Identify three anomalies in the spreadsheet and propose a normalized table design with primary and foreign keys. Explain where MemberName and Class should live and why deleting returned rows is a problem.
Read the worked answer
The sheet mixes entities. MemberName and Class repeat for every loan, so a class change must be edited in many rows (update anomaly). If the last loan row for a member is deleted, the member's details disappear (delete anomaly). Copy details also repeat across loans, and the same ISBN appears for multiple accession numbers, so title or author corrections can become inconsistent.
A normalized design is: Members(MemberID PK, MemberName, Class); Books(AccessionNo PK, ISBN, Title, Author); Loans(LoanID PK, AccessionNo FK, MemberID FK, IssueDate, DueDate, ReturnDate). Optionally, a Titles(ISBN PK, Title, Author) table can hold title data once, with Books referencing ISBN; that avoids repeating title and author for each copy. MemberName and Class belong only in Members, keyed by MemberID, because they describe the member, not the loan.
Deleting a returned row destroys borrowing history and prevents the principal's history report. Instead, keep the row and set ReturnDate. A NULL ReturnDate means the copy is still out; a non-NULL date means it has been returned.
2. Write an SQL query for the overdue list as of 15 January 2025. Include member ID, member name, accession number, title, and due date. Explain how your query treats returned books and blank due dates, and why DueDate <> NULL would fail.
Read the worked answer
SELECT m.MemberID, m.MemberName, b.AccessionNo, b.Title, l.DueDate
FROM Loans l
JOIN Members m ON m.MemberID = l.MemberID
JOIN Books b ON b.AccessionNo = l.AccessionNo
WHERE l.ReturnDate IS NULL
AND l.DueDate IS NOT NULL
AND l.DueDate < DATE '2025-01-15'
ORDER BY l.DueDate;
ReturnDate IS NULL keeps only books still out, so returned books are excluded even if their due date has passed. DueDate IS NOT NULL excludes blank or missing due dates, because a book with no due date cannot be judged overdue. The comparison uses a real date, not a text string, so date ordering works.
DueDate <> NULL fails because NULL means unknown, not empty. Any comparison with NULL yields UNKNOWN, which is not TRUE, so the WHERE clause would return no rows. The correct test is IS NULL or IS NOT NULL.
3. The CSV import fails on titles with commas and duplicate AccessionNo rows. Describe a safe import plan, including how to detect duplicates and what to do with blank DueDate values.
Read the worked answer
Do not parse the CSV with a simple split(','). Use a proper CSV library that respects quoting, or ask the librarian to export with quoted fields. Import into a staging table first, with all columns as text, so bad rows do not break the live tables.
Detect duplicate accession numbers with SELECT AccessionNo, COUNT(*) FROM staging GROUP BY AccessionNo HAVING COUNT(*) > 1. Review each duplicate against the physical copy: merge true duplicates, reject conflicting rows, and log the decision. Check that the count of distinct AccessionNo matches the expected 1,200 copies before loading.
Blank DueDate cells should become SQL NULL, not an empty string or a fake date. Empty strings can sort and compare incorrectly. Load Members and Books first, then Loans inside a transaction, with foreign key checks on. Keep a backup and reconcile row counts after import. Titles containing commas should remain intact because the CSV parser, not manual splitting, handles them.