A Flat Library Table That Cannot Answer the Librarian
The library of a +2 vocational institute issues about 40 books a day. The librarian asks a student to build a database. The student creates one flat table named Issues with columns accession_no, title, author, member_roll, member_phone, issue_date, due_date, return_date and fine. member_roll is set as Number because rolls look like 0012, and member_phone is also Number. Title and author are typed again for every issue; sometimes the same author appears as 'R.K. Narayan', 'R K Narayan' and 'R.K.Narayan'. When a member leaves, the librarian deletes that member's row and the entire issue history for that member disappears. The librarian now wants three things: a list of books currently overdue, a list of all books by one author, and a rule that a member who still has a book cannot be removed. The student's design cannot deliver these reliably, and the annual inspection is in two weeks.
1. Identify the two most serious design faults and their consequences, then propose a corrected table structure with keys and relationships.
Read the worked answer
Fault 1 is storing member and book details repeatedly in every issue row. This creates update anomalies (one changed phone number must be edited in many rows and will be missed), insert anomalies (an author or member cannot be recorded until an issue exists) and delete anomalies (deleting a member destroys issue history). Fault 2 is choosing Number for member_roll and member_phone. Number drops leading zeros, so 0012 becomes 12, and a phone number may lose its leading 0 or be stored as an arithmetic value that cannot hold separators or country codes.
Corrected structure: Members(member_roll TEXT PRIMARY KEY, name, phone TEXT, status); Books(accession_no TEXT PRIMARY KEY, title, author_id, category); Authors(author_id PRIMARY KEY, author_name) optional but useful; Issues(issue_id PRIMARY KEY, accession_no FOREIGN KEY REFERENCES Books, member_roll FOREIGN KEY REFERENCES Members, issue_date, due_date, return_date NULL). One row per issue, with member and book data stored once.
Judgement: make the foreign keys restrictive, not cascading, so a member with an open issue cannot be deleted. Use a controlled author list or standard spelling to fix the Narayan variants.
2. The librarian wants books overdue 'as on 20 January 2026'. Describe the query conditions and the role of NULL return_date, and explain why due_date should be stored rather than calculated from a fixed 14-day rule at query time.
Read the worked answer
The condition is: return_date IS NULL AND due_date < '2026-01-20'. A book is overdue only if it has not come back and its promised due date has passed. NULL means 'unknown or not yet returned', so it must be tested with IS NULL, not with = NULL, which never evaluates to true.
Storing due_date preserves the promise made at issue time. If the query calculates issue_date + 14 every time, then changing the loan period to 21 days silently rewrites the status of every book already on loan, including books issued under the old rule. It also makes it impossible to grant an individual extension without corrupting the general rule. Storing the date at issue time fixes the obligation for that transaction.
Judgement: use a date data type, not text, so comparisons work correctly. Compute the fine from the difference between the return date (or today, if still out) and the stored due_date, and define the daily rate and any cap in one place.
3. Why can't the librarian simply add author_phone to Issues? Explain with an example and say when denormalization might be acceptable.
Read the worked answer
Author phone depends on the author, not on the issue. Adding it to Issues repeats it once per loan, so if R.K. Narayan changes his number, the librarian must update every open and historical row and will probably miss some, leaving contradictory data. It also prevents recording an author who has no issued book yet, and deleting the last issue for an author would erase the only contact record. These are the classic update, insert and delete anomalies caused by a partial dependency on a non-key attribute.
The correct place is an Authors table keyed by author_id, with phone stored once, referenced from Books. Issues then refers to a book, and the book refers to an author.
Judgement: denormalization is acceptable for a read-only report or a snapshot, such as a printed overdue list that must preserve the phone number as it was on that date. It is not acceptable for live operational data unless a defined refresh process keeps the copies consistent, and even then the duplication should be documented and limited.