A Library Database with a Shared Phone Key
Sanjay Gandhi Public Library uses a small database with three tables: Members(member_id, name, phone), Books(book_id, title, copies), and Loans(loan_id, member_id, book_id, issue_date, due_date, return_date). The librarian made phone the primary key of Members because “every family has one phone”. Two siblings, Aarav and Ananya, share their father's phone, so the second registration was rejected. To save time, the librarian deleted a member who had moved away, and all 12 of that member's loan rows vanished. A report query SELECT COUNT(*) FROM Members, Loans; returned 20,000 when the library has 80 members and 250 loans. The librarian now wants to add a fine of ₹2 per late day and needs reliable monthly reports. She also wants to keep three years of loan history for audit.
1. Explain why phone is a poor primary key for Members and propose a better key design that handles siblings.
Read the worked answer
A primary key must be unique, non-null, and stable. A family phone is not unique: Aarav and Ananya legitimately share it, so the second insert violates the primary-key constraint or forces fake data. A phone number can also change or be reassigned. The better design is a surrogate key, member_id, generated by the database and used as the primary key. Phone should become a normal attribute with a UNIQUE constraint only if the library insists on one phone per member, which is false here; otherwise it belongs in a separate Contact(member_id, phone, type) table, allowing several numbers per member. A composite key such as (name, phone) still fails for siblings with the same name and phone and is unstable when names change. Loans should reference member_id as a foreign key. This preserves registration and history without tying identity to a shared phone.
2. The COUNT query returned 20,000. Diagnose the cause and write SQL that returns the number of loans. Explain the arithmetic.
Read the worked answer
FROM Members, Loans with no WHERE clause is a Cartesian product: every member row is paired with every loan row, so 80 × 250 = 20,000 rows. The query is counting pairs, not loans. If the aim is simply the number of loan records, SELECT COUNT(*) FROM Loans; returns 250. If member details must be included, use an inner join on the key: SELECT COUNT(*) FROM Members m JOIN Loans l ON m.member_id = l.member_id;. Because each loan has exactly one valid member, this also returns 250. To be safe against duplicate member rows, count the loan key: SELECT COUNT(DISTINCT l.loan_id) .... The judgement is to decide what one row of the result should represent before choosing COUNT(*), COUNT(column), or COUNT(DISTINCT column).
3. Explain why deleting one member removed 12 loan rows, recommend a safer foreign-key action, and write an expression for the ₹2-per-late-day fine.
Read the worked answer
Loans.member_id is a foreign key to Members. If it was declared with ON DELETE CASCADE, deleting the parent member automatically deletes the child loan rows; that is why 12 rows vanished. For audit and financial history, CASCADE is the wrong choice. Use ON DELETE RESTRICT or NO ACTION so a member with loans cannot be deleted until the loans are archived, or ON DELETE SET NULL if the column is nullable and the loan must survive. A soft-delete flag (is_active) on Members is often best because it preserves the relationship. For the fine, store dates as DATE, not text. For a returned book, a MySQL-style expression is 2 * GREATEST(0, DATEDIFF(return_date, due_date)); in standard SQL, 2 * GREATEST(0, return_date - due_date). For an unreturned book, compare the current date with due_date. The GREATEST prevents negative fines for early returns.