The 42,000-Row Marks Table: Normalisation, Duplicate Entry and a 14-Second Report
A Rajasthan district education office runs a marks-entry portal on MySQL 8.0 (InnoDB, default settings) on a 40 GB SSD server. All data sits in one table: marks(id PK, student_name, father_name, dob, subject, marks_obtained, max_marks, teacher_name, teacher_phone, school_code, school_address). About 7,000 students × 6 subjects give roughly 42,000 rows. Three problems have appeared. (1) When one school shifts campus, the clerk must update 6,000 rows, and after an interrupted run half the rows show the new address and half the old. (2) Two clerks entering marks for the same student and subject at the same time have twice produced two rows for the same student-subject pair, so the printed total does not match the sum of the subject columns. (3) The report 'subject-wise top three per school' takes 14 seconds, and the slow log shows a full table scan with a filesort. Only the primary key is indexed. No downtime is permitted during board season.
1. State which normal form the table actually satisfies, then decompose it to 3NF. For each new table, name the anomaly it removes and justify your choice of primary key.
Read the worked answer
The table is in 2NF but not 3NF. Because the key is the single surrogate column id, there is no partial dependency to worry about, so people often wrongly call it normalised. The real defect is transitive dependency: school_code → school_address, teacher_phone → teacher_name, and id → student identity. Every non-key attribute must depend on the key, the whole key and nothing but the key.
Decomposition:
student(student_id PK, student_name, father_name, dob, school_code FK)- removes the repeated student identity in 6 subject rows.school(school_code PK, school_address)- kills the update anomaly in problem (1): one row updated, not 6,000, and no half-updated state.teacher(teacher_id PK, teacher_name, teacher_phone, school_code FK)- removing the last marks row of a teacher no longer destroys the phone number (delete anomaly), and a new teacher can be recorded before any marks exist (insert anomaly).subject(subject_code PK, subject_name, max_marks)-max_marksis a property of the subject, not of the student.marks_entry(student_id, subject_code, teacher_id, marks_obtained)with a composite PK on (student_id, subject_code), or a surrogate id plus a UNIQUE constraint on that pair.
The composite key is the better choice here because it states the business rule directly: one student takes one subject once. Teacher is kept as a foreign key only to record who entered the marks.
2. Explain why duplicate student-subject rows appeared even though each clerk's own entry was individually valid, and describe the schema change and the transaction change that each independently make the duplicates impossible.
Read the worked answer
Each clerk ran an INSERT that was valid on its own: the surrogate id was different, so the database had no reason to refuse either row. The uniqueness rule lived only in the application's head. Isolation level cannot fix this - a constraint can.
Schema fix: add UNIQUE (student_id, subject_code) (or make that pair the primary key). The second concurrent insert then fails with a duplicate-key error, and the application must catch it and show 'already entered' rather than a 500 page.
Transaction fix: the entry screen almost certainly did read-modify-write - SELECT the existing total, add the new marks in the application, UPDATE the row. Under REPEATABLE READ two sessions can both read the old value; whoever writes last wins and the other entry vanishes. Replace it with a single atomic statement, UPDATE marks_entry SET marks_obtained = ? WHERE student_id = ? AND subject_code = ?, or read with SELECT ... FOR UPDATE inside a short transaction. Also make the endpoint idempotent with a per-submission token, so a clerk who refreshes the page does not post twice.
Judgement point: the unique constraint is the load-bearing fix. It is the only thing that holds when two clerks press Enter in the same second.
3. Bring the 14-second report under 2 seconds on the same hardware. Specify the index and the query rewrite, and name one index you would refuse to add, with the reason.
Read the worked answer
First confirm with EXPLAIN that the cost is a full scan of 42,000 rows plus a filesort, which is not a lot of data - 14 seconds means the query is doing something pathological, typically a correlated subquery evaluated per row or a SELECT * joined back repeatedly.
Index: a composite covering index on marks_entry(school_code, subject_code, marks_obtained) - but school_code is in student, so in practice index student(school_code) and use marks_entry(subject_code, marks_obtained) for the sort, or denormalise school_code into marks_entry as a reviewed decision to get one index (school_code, subject_code, marks_obtained DESC). With that, the rows arrive already ordered per partition and the filesort disappears.
Rewrite: use a window function instead of a correlated subquery - ROW_NUMBER() OVER (PARTITION BY school_code, subject_code ORDER BY marks_obtained DESC) in a derived table, then filter rn <= 3. Select only the columns the report prints.
Refused index: one on father_name, or one on marks_obtained alone. Both are low-selectivity or unselective for this query, and every index is a cost during board-season bulk entry, when thousands of inserts per hour pay for it.