The Library Spreadsheet with Three Copies of the Same Phone Number

Data redundancy is the same fact stored in more than one place. The student's phone number is repeated on every loan row; if a student borrows 14 times, the number is stored 14 times. Redundancy wastes space and, worse, allows the copies to disagree - the nine-digit entry shows this, and the trailing-space name shows the same problem for a text identifier.

Update anomaly occurs when one real-world change must be made in several rows and a missed row leaves inconsistent data. The move from 2nde A to 1ère C was entered in 12 rows but missed in 2. The database now says the same student is in two classes, so reminder letters are misdirected. In a properly normalised design the class is stored once with the student, so one edit changes everything.

Insertion anomaly occurs when the structure cannot record one fact without another. A new book or a new copy cannot be added until some student borrows it, because every row demands a borrower name, phone and dates. Similarly, a student who has never borrowed cannot be registered. The table is forced to hold nulls or dummy values.

A deletion anomaly also exists: deleting the last loan row for a borrower may erase the only record of that student and of the book copy. These anomalies all follow from one wide table mixing student, book and loan facts.

Proposed tables:

• STUDENTS(student_id PK, name, class, phone) - one row per student; class and phone stored once.
• BOOKS(book_id PK, title, author) - one row per title, so author is not repeated for every copy.
• COPIES(copy_id PK, book_id FK, condition) - one row per physical copy; copy 3 of a title is a distinct row.
• LOANS(loan_id PK, student_id FK, copy_id FK, date_borrowed, date_returned) - one row per loan event, holding only loan facts.

How the anomalies disappear:

• Redundancy: a student's phone appears once in STUDENTS; a book's author appears once in BOOKS. A change is made once, so the missed-row problem cannot occur.
• Identity: student_id is a number, so 'Nkeng Christelle ' and 'Nkeng Christelle' cannot create two borrowers; names are attributes, not keys. A validation rule can also trim spaces.
• Insertion: a new book and its copies are added to BOOKS and COPIES with no borrower required; a new student is added to STUDENTS with no loan required.
• Overdue lists: LOANS can be queried on date_returned IS NULL without scanning a wide sheet.
• Duplicate copy 3: foreign keys ensure the copy and student exist. A copy may appear in many historical loans, but only one open loan is allowed. This is enforced by a partial unique index on copy_id where date_returned IS NULL, or by a trigger that rejects a second open loan for the same copy.

Online catalogue: create a view exposing only title, author and availability, and grant the web account SELECT on the view rather than on STUDENTS, so phone numbers are never visible.

SELECT b.title, s.name
FROM LOANS l
JOIN STUDENTS s ON s.student_id = l.student_id
JOIN COPIES c  ON c.copy_id = l.copy_id
JOIN BOOKS b   ON b.book_id = c.book_id
WHERE l.date_returned IS NULL
  AND l.date_borrowed < '2024-11-01'
ORDER BY l.date_borrowed;

Reasoning: the title is in BOOKS and the borrower name is in STUDENTS, but the link between them is the loan. The query therefore joins LOANS to STUDENTS directly and to BOOKS through COPIES; joining LOANS straight to BOOKS would be wrong because the loan points to a copy, not to a title. IS NULL must be used rather than = NULL, because NULL means 'unknown', and any comparison with NULL yields unknown, not true. The date literal is written in ISO order (YYYY-MM-DD) so it compares correctly, and ORDER BY makes the oldest overdue loans appear first.

Integrity rule: a unique constraint on open loans - for example a partial unique index on LOANS(copy_id) restricted to rows where date_returned IS NULL, or a BEFORE INSERT trigger that counts existing open loans for that copy and rejects the insert if one exists. A foreign key alone would not stop the duplicate: it only checks that the copy exists, not that it is already on loan. The trigger or index enforces the business rule 'one physical copy, one borrower at a time' at the database level, so a clerk cannot override it by accident.