Library Database: String Comparison, Referential Integrity and NULL Logic
A higher secondary school library in Palakkad has computerised its records. Three tables exist:
BOOK(BookID CHAR(4) PRIMARY KEY, Title VARCHAR(60), Author VARCHAR(40), Category VARCHAR(20), Price VARCHAR(8))
MEMBER(MemberID CHAR(5) PRIMARY KEY, Name VARCHAR(40), Class VARCHAR(10))
ISSUE(IssueID INT PRIMARY KEY, BookID CHAR(4), MemberID CHAR(5), IssueDate DATE, ReturnDate DATE)
with ISSUE.BookID and ISSUE.MemberID declared as foreign keys. The BOOK table holds 40 rows: Science 12, Fiction 9, Reference 6, Biography 5, Competitive 8. The librarian created the Price column as VARCHAR because she typed the rupee symbol into some entries, and the query SELECT Title, Price FROM BOOK WHERE Price > '500' returns books costing 60, 90 and 45 along with the genuinely expensive ones. Attempting to remove a damaged title with DELETE FROM BOOK WHERE BookID='B017' produces an error about a foreign key constraint, and a query using WHERE ReturnDate = NULL returns an empty result although five books are overdue.
1. Explain why `WHERE Price > '500'` returns a book costing 60 and misses others, state the correct way to fix the column, and then write a query listing each category with 10 or more books along with the number of books and the average price, most numerous first.
Read the worked answer
Why the comparison misbehaves. Price is stored as text, so '60' > '500' is a string comparison: character by character, '6' is compared with '5', and since '6' > '5' the answer is TRUE and evaluation stops. Similarly '90' > '500' is TRUE, while '45' > '500' is FALSE because '4' < '5'. Text is being ordered like a dictionary, not like money. ORDER BY Price is wrong for the same reason. Where the rupee symbol is present, even a numeric cast fails, so the data must be cleaned first.
Fix. Strip symbols and commas from the existing values, then ALTER TABLE BOOK MODIFY Price DECIMAL(6,2); and compare with a number, never with quotes: WHERE Price > 500. DECIMAL (not FLOAT) is used so that averages of money do not drift.
Grouped query.
SELECT Category, COUNT(*) AS No_of_Books, AVG(Price) AS Avg_Price
FROM BOOK
GROUP BY Category
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC;
Only Science (12) qualifies. WHERE filters individual rows before grouping, so it cannot test COUNT(*); HAVING filters groups after grouping. Using WHERE COUNT(*)>=10 is the classic wrong answer and raises an error.
2. The delete of BookID 'B017' fails. Explain the mechanism behind the error, and give two acceptable ways to remove the damaged title while keeping the data consistent.
Read the worked answer
The mechanism. ISSUE.BookID is a foreign key referencing BOOK.BookID. The DBMS enforces referential integrity: a child row may not point at a parent row that does not exist. B017 has at least one row in ISSUE (possibly one already returned, but the row still exists and still carries B017). Deleting the parent would leave an orphan row in ISSUE, so the DBMS rejects the statement. This is the constraint doing its job, not a bug.
Option 1 - delete the children first, in the correct order.
DELETE FROM ISSUE WHERE BookID = 'B017';
DELETE FROM BOOK WHERE BookID = 'B017';
Both statements belong in one transaction so that a failure cannot leave the ISSUE rows deleted but the book still present. Before doing this, archive the issue history (a BOOK_ARCHIVE table or a printed register), because circulation history is usually needed for audits and for the library's annual report.
Option 2 - keep the history and retire the book. Add a Status VARCHAR(10) DEFAULT 'Available' column and set it to 'Withdrawn' or 'Damaged'. The row stays, all ISSUE references remain valid, and the issue query simply filters on Status = 'Available'. This is the usual choice in real libraries.
Alternatives worth naming. ON DELETE CASCADE on the foreign key automates Option 1 but silently destroys history, so it should be used only for genuinely dependent data; ON DELETE SET NULL works only if the column permits NULLs. A deactivate-instead-of-delete policy is the safest of all.
3. The librarian's overdue query `SELECT Name FROM MEMBER WHERE MemberID IN (SELECT MemberID FROM ISSUE WHERE ReturnDate = NULL)` returns nothing. Explain the cause and write a corrected query that lists the member name, book title and issue date for books not yet returned.
Read the worked answer
Cause. NULL means 'unknown', not 'empty' and not zero. Any comparison with NULL yields UNKNOWN, and a WHERE clause keeps only rows for which the condition is TRUE. ReturnDate = NULL is UNKNOWN for every row, so the subquery is always empty; the same trap makes MemberID NOT IN (...) return no rows when the list contains a NULL.
Correct comparison. Use IS NULL (or IS NOT NULL) - the only operators designed for NULL.
Corrected query.
SELECT M.Name, B.Title, I.IssueDate
FROM ISSUE I
JOIN MEMBER M ON M.MemberID = I.MemberID
JOIN BOOK B ON B.BookID = I.BookID
WHERE I.ReturnDate IS NULL
ORDER BY I.IssueDate;
Why a join rather than a subquery. The question asks for columns from three tables, and a join brings them together in one pass; the original subquery could return only MEMBER columns. Note the aliases: once two tables both have a MemberID column, an unqualified MemberID is ambiguous and the DBMS raises an error, so every column in a multi-table query must be qualified.
Marking point. Do not 'fix' the original by writing ReturnDate = ''. An empty string is a value and will match nothing useful; only NULL-aware predicates work.