Library Loans Spreadsheet to Relational Tables
A school library keeps all loans in one spreadsheet with columns LoanID, MemberName, Class, BookTitle, Author, IssueDate, DueDate, ReturnDate, Fine. Each row is one loan. Two students named Aman in Classes 9A and 9B have separate rows, and the title Python Basics lists two authors in one cell as 'R. Kumar; S. Verma'. ReturnDate is blank for books still out. Fine is supposed to be ₹2 per day after DueDate. A report uses COUNT(ReturnDate) to count loans and returns 0 for a book that has never been returned. On 1 February 2024 the librarian needs a list of overdue loans, with member name, book title, days overdue, and fine. A row with ReturnDate earlier than IssueDate also exists, and one BookTitle has trailing spaces so it does not match in a lookup.
1. Identify the normalization problems in the spreadsheet and propose a set of tables in third normal form.
Read the worked answer
The single sheet violates 1NF because Author holds multiple authors in one cell. It also mixes entities: member data depends on the member, book data on the book, and loan data on the loan. Fine is derived from DueDate, ReturnDate, and the ₹2 rate, so storing it invites inconsistency.
Suggested tables:
Members(MemberID PK, MemberName, Class)- MemberID distinguishes the two Amans; Name+Class is not a safe key.Books(BookID PK, Title)Authors(AuthorID PK, AuthorName)BookAuthors(BookID FK, AuthorID FK, PK(BookID, AuthorID))Loans(LoanID PK, MemberID FK, BookID FK, IssueDate, DueDate, ReturnDate NULL)
This removes update anomalies (changing a class in one row only), insert anomalies (cannot add a member without a loan), and delete anomalies (deleting the last loan loses the member).
2. Write an SQL query for overdue loans on 1 February 2024, showing member name, title, days overdue, and fine. Explain the NULL and date conditions.
Read the worked answer
SELECT m.MemberName, b.Title,
DATEDIFF('2024-02-01', l.DueDate) AS days_overdue,
2 * DATEDIFF('2024-02-01', l.DueDate) AS fine
FROM Loans l
JOIN Members m ON m.MemberID = l.MemberID
JOIN Books b ON b.BookID = l.BookID
WHERE l.ReturnDate IS NULL
AND l.DueDate < '2024-02-01';
Reasoning: ReturnDate IS NULL means still out; DueDate < '2024-02-01' excludes books due on 1 February; the difference gives completed overdue days. DATEDIFF is MySQL-style; in standard SQL use DATE '2024-02-01' - l.DueDate. Clean trailing spaces in Title (or join on BookID, as here) before matching text. A returned book, even if late, is not in this list; it needs a separate fine query using ReturnDate.
3. Explain why COUNT(ReturnDate) returns 0 for an unreturned book, and give the correct expressions for total loans, returned loans, and still-out loans. Mention one data-quality fix.
Read the worked answer
COUNT(ReturnDate) counts only non-NULL values. For a book never returned, ReturnDate is NULL, so it contributes 0 - that is why the report shows 0. Use COUNT(*) for all loan rows, COUNT(ReturnDate) for returned loans, and SUM(CASE WHEN ReturnDate IS NULL THEN 1 ELSE 0 END) for still-out loans. If the question is ‘how many distinct books’, use COUNT(DISTINCT BookID).
Two data-quality fixes: add CHECK (ReturnDate IS NULL OR ReturnDate >= IssueDate) to reject the impossible row, and standardise titles with TRIM or, better, join on BookID. The trailing-space title is not a SQL bug; it is a data-entry defect that should be corrected at source.