Cleaning a Library Loan Register Before Querying
A school library has a single spreadsheet with 4,800 loan records. Columns are Student Name, Class, Book Title, Author, Accession No., Loan Date, Due Date, and Return Date. There are 620 students but 655 name spellings; the same book title appears with different authors because of data entry. Some accession numbers are blank, and 12 accession numbers are repeated. The librarian wants to know which books are overdue, which student has the most loans, and which title is borrowed most. A volunteer suggests deleting duplicate rows and adding a column 'Overdue?' with a formula. The library has one week before its annual report is due.
1. Explain why deleting duplicate rows in the spreadsheet is risky here, and propose a relational table design before import.
Read the worked answer
Deleting duplicate rows is risky because duplicates are not all errors: one student can borrow many times, and two borrowers can share a name. The 655 spellings for 620 students show that a name is not a key. Blank and repeated accession numbers mean the sheet cannot uniquely identify a copy. Deleting rows would destroy loan history and could merge different students.
Proposed design:
Students(StudentID, Name, Class)- use admission number as StudentID and a name-mapping table to merge spelling variants.Books(BookID, Title, Author)- assign one BookID per title/author combination.Copies(AccessionNo, BookID)- AccessionNo is the primary key; blank or repeated values must be quarantined and corrected before import.Loans(LoanID, StudentID, AccessionNo, LoanDate, DueDate, ReturnDate)- one row per loan event, with foreign keys to Students and Copies.
Clean and reconcile in a staging sheet, then import. This preserves history and makes queries reliable.
2. Describe the logical steps or queries for overdue books, most-borrowed title, and the student with the most loans. Include how to handle unreturned loans and invalid accession numbers.
Read the worked answer
Overdue: select loans where ReturnDate IS NULL AND DueDate < CURDATE(), joined to Students and Copies/Books for names and titles. Use a fixed report date rather than the computer clock if the annual report needs a repeatable figure.
Most-borrowed title: count loans per AccessionNo, then sum by BookID and join Books: SELECT b.Title, COUNT(*) AS Loans FROM Loans l JOIN Copies c ON l.AccessionNo=c.AccessionNo JOIN Books b ON c.BookID=b.BookID GROUP BY b.BookID,b.Title ORDER BY Loans DESC;. This handles multiple copies of one title.
Most loans by student: SELECT s.Name, COUNT(*) FROM Loans l JOIN Students s ON l.StudentID=s.StudentID GROUP BY s.StudentID,s.Name ORDER BY COUNT(*) DESC;.
Data-quality rule: report loans with an invalid or blank accession number separately rather than silently dropping them, so the annual total reconciles.