The Missing Parent Record
A small library is building two tables: Members (MemberID as primary key) and Loans (LoanID as primary key, plus a MemberID column). The designer sets a relationship between Members.MemberID and Loans.MemberID but forgets to enforce referential integrity. During testing, a staff member deletes a member who still has three open loans. The loans remain in the Loans table with a MemberID that no longer matches anyone. A report that joins the tables now silently omits those three loans, and the overdue count is wrong.
1. What is referential integrity, and how would enforcing it have changed this outcome?
Read the worked answer
Referential integrity is a rule that a foreign key value must match an existing primary key value in the related table (or be null). With it enforced on Loans.MemberID, the database would refuse to delete a member who still had loans, or would require a defined action (cascade the delete, or block it). Here the delete would have been blocked, forcing staff to close or reassign the loans first. The three orphaned loan rows would never have been created, so the join report would still find them.
2. The report silently dropped rows. Explain why an inner join behaves this way and what the designer should check.
Read the worked answer
An inner join returns only rows where the key matches on both sides. Orphaned loans have no matching member, so they vanish from the result without an error - a silent data loss. The designer should check two things: (1) whether orphan rows exist at all (query Loans for MemberID values not present in Members), and (2) whether the report should use an outer join so unmatched loans still appear. The deeper fix is to enforce referential integrity so orphans cannot arise. A report that hides bad data is more dangerous than one that shows it.