One Flat Table for the Book-Issue Counter
A vocational student is building a database for the book-issue counter of a cooperative higher secondary school. She begins with a single flat table: Issue(StudentID, StudentName, Phone, Class, BookID, BookTitle, Author, Publisher, IssueDate, DueDate, Fine). In the sample data, student 1042 (Sunita) has borrowed three different books; her name, phone and class appear three times, and her phone number is typed differently in two of those rows. Book titles repeat for every issue, and one title is spelled two different ways in different rows. The Fine column holds '5' in one row, '100' in another and is blank in a third, because the librarian typed the values straight from the keyboard without setting a data type. The librarian's requirements are: find which books are due today; list students whose unpaid fine is more than ₹50; update Sunita's phone number once when she changes her SIM; and allow the same student to borrow the same book again in a later month. The counter issues about 40 books a day and the table already holds roughly 900 rows.
1. Identify the design faults in the flat table, naming each type of anomaly and pointing to the specific evidence in the setup that demonstrates it.
Read the worked answer
Redundancy. Sunita's name, phone and class are stored three times, once per issue. Every extra copy is another place to get it wrong, and that is exactly what happened: her phone number already differs between two rows.
Update anomaly. When Sunita changes her SIM, a careless update will change one or two rows and leave the third stale. The database then holds two 'correct' answers and cannot say which is right.
Insertion anomaly. With a single Issue table you cannot record a newly purchased book, or a newly admitted student, until that book is actually issued - every row requires a valid StudentID and BookID.
Deletion anomaly. Delete the last issue of a rare book and every trace of that book's title, author and publisher disappears with it.
No usable primary key. StudentID alone is not unique (three rows for 1042), and neither is StudentID + BookID, because the requirement explicitly allows the same student to borrow the same book again in a later month.
Wrong data type. Fine is text. In text order '100' sorts before '5', and SUM silently ignores text, so any total or filter on fine gives an answer that looks plausible and is wrong. Blank instead of 0 or NULL compounds it.
2. Propose a normalised set of tables with primary and foreign keys, and justify your choice of key for the issue table.
Read the worked answer
Student(StudentID PK, StudentName, Phone, Class) Book(BookID PK, BookTitle, Author, Publisher) Issue(IssueID PK, StudentID FK → Student, BookID FK → Book, IssueDate, DueDate, FineAmount NUMBER DEFAULT 0, FinePaid CHAR(1) DEFAULT 'N')
Why a surrogate IssueID. The natural candidate key is (StudentID, BookID, IssueDate), and it is technically unique because a student cannot sensibly borrow the same copy twice on the same day. But a three-column key is clumsy for every foreign key that will point at it, and a same-day return-and-reissue would break it. A single auto-numbered IssueID is short, stable and survives any later rule change; keep a UNIQUE constraint on (StudentID, BookID, IssueDate) if you want the business rule enforced.
Why the other splits. Student details are now stored once, so the phone update is a single UPDATE Student ... WHERE StudentID = 1042, and the misspelling problem disappears because each title exists once in Book. Fine becomes a number with a defined default, so blank rows cannot silently drop out of a total. Each table is in third normal form: no repeating groups, no partial dependencies (every non-key field depends on the whole key), and no transitive dependencies, since Class belongs to the student and the publisher belongs to the book.
3. The librarian's three questions must now be answered with queries. Write the query for each and explain one trap in each that the flat table would have sprung.
Read the worked answer
1. Books due today
SELECT StudentID, BookID, DueDate FROM Issue WHERE DueDate = CURRENT_DATE;
Trap: if dates were typed freehand and stored as text, '05/07/2025' will not equal a real date value and the query returns nothing. Store dates as DATE and enter them with a date format, never as typed strings.
2. Unpaid fine over ₹50
SELECT StudentID, BookID, FineAmount FROM Issue WHERE FineAmount > 50 AND FinePaid = 'N' ORDER BY FineAmount DESC;
Trap: with Fine stored as text, > 50 compares text with a number in ways that depend on the database, and in plain text ordering '100' comes before '5'. Changing the column to NUMBER and converting the existing values ('5' → 5, '' → 0) is a prerequisite, not an optional tidy-up.
3. Sunita's new phone number
UPDATE Student SET Phone = '<new number>' WHERE StudentID = 1042;
Trap: the flat table needed three coordinated edits and had already drifted out of agreement. One row per student makes the update both complete and verifiable.
After any update, run the SELECT again to confirm the change and check that no duplicate student rows exist.