GCE A/L Information & Communication Technology: Denormalised Tuition Register, a Broken NOT IN Query and NULL Payment Amounts

Denormalised Tuition Register, a Broken NOT IN Query and NULL Payment Amounts

Wisdom Institute, a private tuition centre in Kandy, keeps all registrations in one sheet, AllData, with 1,800 rows and the columns RegNo, StudentName, Phone, ClassCode, ClassName, TeacherName, TeacherPhone, MonthlyFee, PaidMonth and AmountPaid. A row exists for every invoiced month, and AmountPaid is left blank until the student pays. The office has also begun pasting payments into a second sheet, Payments(RegNo, PaidMonth, AmountPaid); 40 rows there have a blank RegNo because walk-in students pay at the counter before they are registered. Staff report that changing a teacher's mobile number means editing about 300 rows and some are always missed; that a student who paid for January and February but not March appears three times in some lists and not at all in others; and that this query, meant to list students who have not paid for March, returns zero rows:

SELECT StudentName FROM AllData WHERE RegNo NOT IN (SELECT RegNo FROM Payments WHERE PaidMonth = 'March');

The institute now plans a web portal where each student sees only their own payment history, and expects 400 concurrent users during results week.

1. Identify the functional dependencies in AllData, state the highest normal form it currently satisfies with justification, and decompose it to 3NF, naming keys and stating which anomalies each new relation removes.

Read the worked answer

Assume each student is enrolled in exactly one class, and each class is taught by one teacher.

Functional dependencies:

RegNo → StudentName, Phone, ClassCode
ClassCode → ClassName, TeacherName, MonthlyFee
TeacherName → TeacherPhone
RegNo → ClassCode → ClassName, TeacherName, TeacherPhone, MonthlyFee (transitive)
(RegNo, PaidMonth) → AmountPaid, and (RegNo, PaidMonth) is the only candidate key

Highest normal form: 1NF only. Values are atomic, so 1NF holds. 2NF fails because StudentName, Phone and ClassCode depend on RegNo alone, which is a proper subset of the candidate key (RegNo, PaidMonth) - that is why a student invoiced for three months appears three times. 3NF also fails through RegNo → ClassCode → TeacherName and TeacherName → TeacherPhone.

Decomposition:

Student(RegNo, StudentName, Phone, ClassCode)
Class(ClassCode, ClassName, TeacherName, MonthlyFee)
Teacher(TeacherName, TeacherPhone)
Payment(RegNo, PaidMonth, AmountPaid)

Anomalies removed: the update anomaly on TeacherPhone disappears because one row now holds it (no 300-row edit, no inconsistency); the insertion anomaly disappears because a new class or teacher can be recorded before any student enrols; the deletion anomaly disappears because deleting a student's last payment no longer destroys class or teacher data; and the redundancy of repeating ClassName and MonthlyFee 1,800 times (and the risk of two different MonthlyFee values for one class) is eliminated.

2. Explain precisely why the NOT IN query returns zero rows, then rewrite it correctly in two different ways. Also explain why COUNT(AmountPaid) and COUNT(*) give different counts for March.

Read the worked answer

The subquery returns a list that contains NULLs, because 40 walk-in payments have a blank RegNo. x NOT IN (a, b, NULL) is shorthand for NOT (x = a OR x = b OR x = NULL). When x matches no listed value, x = NULL evaluates to UNKNOWN, so the OR chain is UNKNOWN, and NOT UNKNOWN is UNKNOWN. A WHERE clause keeps only rows whose predicate is TRUE, so every student is filtered out and the result is empty. The trap is that the query would work perfectly on a clean dataset and silently break the day one NULL arrives.

Correct rewrites:

-- 1. NOT EXISTS is NULL-safe
SELECT a.StudentName FROM AllData a
WHERE a.PaidMonth = 'March'
  AND NOT EXISTS (SELECT 1 FROM Payments p
                  WHERE p.RegNo = a.RegNo AND p.PaidMonth = 'March');

-- 2. LEFT JOIN ... IS NULL
SELECT a.StudentName FROM AllData a
LEFT JOIN Payments p ON p.RegNo = a.RegNo AND p.PaidMonth = 'March'
WHERE p.RegNo IS NULL;

The data-quality fix still matters: declare RegNo NOT NULL in Payments and give walk-in payments a placeholder key or a separate UnregisteredPayment relation, so the nullable column cannot poison other queries.

For March, COUNT(*) counts rows (invoices), while COUNT(AmountPaid) counts only non-NULL amounts, i.e. paid invoices. The difference is the number of unpaid invoices, and SUM(AmountPaid) normally equals the sum over the non-NULL rows - an unpaid invoice contributes nothing, not zero.

3. The portal must display one student's full history within 2 seconds while 400 students log in at once. Recommend the indexes and one schema decision you would make, justifying each in terms of the query pattern.

Read the worked answer

The dominant query is 'given a student, show their rows'. That is a single-table, high-selectivity lookup on Payment:

Primary key (RegNo, PaidMonth) already gives a clustered/B-tree path for WHERE RegNo = ?, and because RegNo is the leading column the same index serves ORDER BY PaidMonth within that student without a sort. Do not add a separate index on RegNo alone - it would be redundant with the composite key and only add write cost.
Student(RegNo) primary key is required for the login lookup; a unique index on a login identifier (email or index number) is also needed if students authenticate by that instead of RegNo. Add a unique constraint, not just an index, so duplicates cannot exist.
Class(ClassCode) is already a key; no extra index unless reports filter by TeacherName, in which case index the foreign key column used in the join.

Avoid indexing every column: each secondary index costs disk and slows the nightly import, and low-selectivity columns such as PaidMonth give the optimiser little benefit compared with a composite that starts with RegNo.

Schema decision: make AmountPaid NOT NULL with a default of 0 and keep a separate Paid flag (or simply derive 'paid' as AmountPaid = MonthlyFee). This removes the NULL semantics that broke the anti-join, lets SUM/AVG behave predictably in reports, and makes aggregate queries index-friendly. The 400 concurrent readers also argue for a read replica or caching layer rather than heavier indexing, since the workload is read-dominated and bursty.