NULL Values, Join Multiplication and Cascading Deletes in a Library Database
The school library database has Members(member_id, name, class), Books(book_id, title) and Loans(loan_id, member_id, book_id, issue_date, return_date). There are 420 members and 1,800 loan rows. A substitute librarian entered some walk-in loans with member_id left NULL, because those readers were not members. The librarian runs SELECT name FROM Members WHERE member_id NOT IN (SELECT member_id FROM Loans); and gets zero rows, although at least 60 members have never borrowed. A report query that joins Loans to Fines(fine_id, loan_id, amount) returns a count of 3 for a member who has only one loan but three fine rows. The librarian also tries to delete a member who has two unreturned books; the database rejects the delete because Loans.member_id has a foreign key with no ON DELETE action. The principal asks whether adding ON DELETE CASCADE is the quickest solution.
1. Explain why the `NOT IN` query returns zero rows and rewrite it to return the members who have never borrowed. Include one change to the data model that would prevent the NULL problem.
Read the worked answer
NOT IN (subquery) is equivalent to testing member_id <> ALL (subquery). If the subquery returns even one NULL, then every comparison with that NULL evaluates to UNKNOWN, not TRUE. A row is kept only when the WHERE condition is TRUE, so all members are filtered out and the result is empty. The NULLs come from walk-in loans where member_id was left blank. A correct query uses NOT EXISTS, which is null-safe in this sense:
SELECT m.name
FROM Members m
WHERE NOT EXISTS (
SELECT 1 FROM Loans l
WHERE l.member_id = m.member_id
);
A LEFT JOIN with WHERE l.member_id IS NULL also works. For the data model, make Loans.member_id NOT NULL and represent walk-in readers with a real row in Members such as a generic walk-in member, or move walk-in loans to a separate table. That removes the ambiguous NULL and preserves referential integrity.
2. The fine report counts 3 for a member with one loan. Explain the cause and give a corrected query.
Read the worked answer
Joining Loans to Fines multiplies the loan row once for each matching fine. The member has one loan and three fines, so the joined result has three rows. If the query uses COUNT(*), it counts those three joined rows and reports 3 loans. The fix is to count distinct loans, not joined rows:
SELECT m.name, COUNT(DISTINCT l.loan_id) AS loan_count
FROM Members m
JOIN Loans l ON l.member_id = m.member_id
LEFT JOIN Fines f ON f.loan_id = l.loan_id
GROUP BY m.member_id, m.name;
COUNT(DISTINCT l.loan_id) counts each loan once even when several fines exist. The LEFT JOIN keeps loans that have no fine. Grouping by member_id as well as name avoids merging two members who happen to share a name. The same multiplication problem would affect sums of fine amounts, so those should be computed in a separate pre-aggregated subquery if both counts and totals are needed.
3. Should the librarian add `ON DELETE CASCADE` to the `Loans.member_id` foreign key? Advise the principal with reasoning.
Read the worked answer
No. ON DELETE CASCADE would delete every loan row belonging to a member when that member is deleted. That would destroy the record of the two unreturned books and any related fine history, which the library needs for recovery and audit. The immediate foreign-key error is protecting data, not obstructing the librarian. The correct process is to return or account for the outstanding books first, then decide whether the member may be removed. A better design is a soft delete: add an active or withdrawn flag to Members, keep the row and its history, and exclude inactive members from current lists. If old records must eventually be purged, archive them under a retention policy rather than cascading from a routine delete. Cascading is appropriate for rows that have no independent meaning, such as a member's optional contact rows, not for financial and circulation history.