One Table for Everything: Redesigning a Coaching Centre's Admission Database
A small coaching centre keeps all student data in one LibreOffice Base table called ADMISSION: RollNo, StudentName, GuardianName, GuardianPhone, Course1, Course2, FeePaid, and ReceiptNo. A student can join up to two courses. Several problems have appeared. Guardian phone numbers are repeated for siblings, and one sibling's number was updated while the other's was not. Course names are typed as 'Sc', 'Science', and 'SCI'. The same receipt number has been entered for two different students. Deleting a student's row also deletes the only record of their payment. The centre wants to send fee reminders, list students by course, and ensure that one receipt number cannot be used twice. There are 240 students, 18 courses, and no professional database administrator. The manager proposes 'just add Course3 and Course4 columns'. Another proposes a separate PAYMENT table.
1. Identify two update anomalies and one deletion anomaly in the ADMISSION table using the centre's examples. Explain why adding Course3 and Course4 columns makes the design worse rather than better.
Read the worked answer
Update anomaly: the guardian phone is stored once per student, so siblings duplicate it. When one row is updated and the other is not, the database holds two different phone numbers for the same guardian and no one knows which is current. Course names are also repeated in Course1 and Course2; changing 'Sc' to 'Science' requires finding every variant, and a missed row keeps the old spelling.
Deletion anomaly: deleting a student's row removes FeePaid and ReceiptNo. The payment record disappears even though the money was received, so the accounts no longer reconcile.
Adding Course3 and Course4 is worse: it creates a repeating group, leaves NULLs for students who take fewer courses, and still limits a student to four courses. Queries must search Course1 OR Course2 OR Course3 OR Course4, and adding a fifth course requires changing the table structure and every query. It does not separate students from courses or payments, so the phone duplication and receipt duplication remain. The correct direction is to model Student, Course, Enrolment, and Payment separately, with the many-to-many student-course relationship handled by an enrolment table.
2. Propose tables in third normal form for students, courses, enrolments, and payments. Show primary and foreign keys, one relationship, and the data type for phone and receipt number.
Read the worked answer
A workable design:
- STUDENT(RollNo PRIMARY KEY, StudentName, GuardianName, GuardianPhone TEXT)
- COURSE(CourseCode PRIMARY KEY, CourseName UNIQUE, FeeAmount)
- ENROLMENT(RollNo, CourseCode, PRIMARY KEY(RollNo, CourseCode), FOREIGN KEY RollNo REFERENCES STUDENT, FOREIGN KEY CourseCode REFERENCES COURSE)
- PAYMENT(ReceiptNo PRIMARY KEY, RollNo, CourseCode, Amount, PaymentDate, Mode, FOREIGN KEY (RollNo, CourseCode) REFERENCES ENROLMENT)
Each non-key field depends on the key, the whole key, and nothing but the key. Guardian phone is stored once per guardian record; if siblings share a guardian, a separate GUARDIAN table could be added, but the four-table design already removes course and payment duplication.
Relationship: one STUDENT has many PAYMENT rows, and one ENROLMENT can have zero or more payments. The composite foreign key in PAYMENT prevents a payment being attached to a course the student is not enrolled in, if the DBMS enforces it.
Data types: GuardianPhone should be TEXT, not a number, to preserve leading zeros and avoid arithmetic on a phone number. ReceiptNo should be TEXT with a PRIMARY KEY or UNIQUE constraint, because receipt numbers may contain letters and must be unique.
3. Write the query logic to list students enrolled in 'Science' whose fee is pending, and explain one constraint that prevents duplicate receipt numbers.
Read the worked answer
Assuming COURSE has FeeAmount and PAYMENT is linked to (RollNo, CourseCode), the logic is:
SELECT s.RollNo, s.StudentName
FROM STUDENT s
JOIN ENROLMENT e ON s.RollNo = e.RollNo
JOIN COURSE c ON e.CourseCode = c.CourseCode
LEFT JOIN PAYMENT p
ON e.RollNo = p.RollNo AND e.CourseCode = p.CourseCode
WHERE c.CourseName = 'Science'
GROUP BY s.RollNo, s.StudentName, c.FeeAmount
HAVING COALESCE(SUM(p.Amount), 0) < c.FeeAmount;
The LEFT JOIN keeps students with no payment; COALESCE turns their NULL total into 0, so they appear as pending. GROUP BY collects multiple part-payments. A student who has paid exactly the course fee is excluded.
Duplicate receipt prevention: declare ReceiptNo as the PRIMARY KEY of PAYMENT, or add a UNIQUE constraint if a surrogate PaymentID is used as the primary key. The DBMS then rejects a second row with the same receipt number, even if two clerks type it at the same time. A form-level check alone is not enough because two users can pass the check before either saves. The centre should also make ReceiptNo NOT NULL and validate the format on entry.