Normalising the School Library Spreadsheet
A school library keeps its records in one spreadsheet sheet with columns: roll_no, student_name, class, book_id, title, author, issue_date, return_date. A student changes class, so the class is typed again in every issued-book row. When the librarian deletes a student who has returned all books, the only record of that student disappears. When a book’s title is corrected, some rows still show the old title. The librarian wants a small relational database with separate Students, Books, and Issue tables. There are 1,200 students, 8,000 books, and about 300 active issues. A book can be issued to only one student at a time, but a student can have up to three active issues. The librarian also wants a list of students whose books are overdue by more than 14 days, and a count of books per author. You are asked to identify the anomalies, design keys, and write SQL.
1. Identify the insertion, deletion, and update anomalies in the single sheet. Propose normalised tables with primary and foreign keys, and explain how the active-issue rules could be enforced.
Read the worked answer
The single sheet mixes three entities and causes all three anomalies. Update anomaly: a student’s class or a book’s title appears in many rows, so a correction can leave inconsistent copies. Insertion anomaly: a new book cannot be recorded until some student issues it, because roll_no and issue_date are part of the only row. Deletion anomaly: deleting the last row for a student removes the only record of that student. Normalise to:
Students(roll_no PRIMARY KEY, student_name, class)Books(book_id PRIMARY KEY, title, author)Issue(issue_id PRIMARY KEY, roll_no FOREIGN KEY REFERENCES Students, book_id FOREIGN KEY REFERENCES Books, issue_date, return_date)Issueis the bridge for the many-to-many relationship between students and books over time. The “at most three active issues” rule is a business constraint; in full SQL it needs a trigger or application code, because a simpleCHECKcannot count rows. The “one student at a time per book” rule for active loans needs a partial unique index onbook_idwherereturn_date IS NULL, or a trigger.
2. Write an SQL query that lists students with books overdue by more than 14 days. Show student number, name, book title, and issue date. Explain the NULL test and the date expression.
Read the worked answer
Overdue means not yet returned and issued more than 14 days ago. Use joins to get names and titles:
SELECT s.roll_no, s.student_name, b.title, i.issue_date
FROM Students s
JOIN Issue i ON s.roll_no = i.roll_no
JOIN Books b ON b.book_id = i.book_id
WHERE i.return_date IS NULL
AND i.issue_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
ORDER BY i.issue_date;
IS NULL is required because return_date = NULL is unknown, not true. The DATE_SUB expression is MySQL syntax; in SQLite use date('now','-14 days'), and in standard SQL use CURRENT_DATE - INTERVAL '14 days'. The join order affects readability only for inner joins; the optimiser can reorder them. If a student has three overdue books, the student appears three times; add DISTINCT if only one row per student is wanted.
3. Write a query that returns the number of books per author, ordered from most to least. Explain `GROUP BY`, `COUNT(*)`, and how to restrict the result to authors with at least two books.
Read the worked answer
SELECT author, COUNT(*) AS book_count
FROM Books
GROUP BY author
ORDER BY book_count DESC, author;
GROUP BY author makes one group per author; COUNT(*) counts rows in each group. COUNT(book_id) would give the same result here because book_id is the primary key and cannot be NULL, but COUNT(*) is clearer for a row count. If some books had a NULL author, COUNT(*) would still count them as a group, while COUNT(author) would ignore NULL authors. To count only authors with at least two books, add HAVING COUNT(*) >= 2 after the GROUP BY. WHERE cannot be used for an aggregate condition; HAVING is applied after grouping.