Normalisation, Slow Queries and Lost Updates in an Examination Database
A university examination database stores results in a single table: Result(student_id, student_name, dept, exam_id, exam_date, subject_id, subject_name, marks, evaluator_id). It has about 2 million rows. The same subject_name and exam_date are repeated for every result row. Clerks update marks; two clerks sometimes update the same row, and one update is lost. A query that lists a student's marks with subject names takes 12 seconds; there is a single index on student_id. The table is growing by 200,000 rows per semester; 80% of queries filter by student_id and subject_id, and 15% filter by exam_id. The database administrator proposes adding indexes on every column and denormalising further to 'avoid joins'. The examination branch wants faster retrieval and no lost updates.
1. Identify the functional dependencies, the normal form, and the update anomalies in this table.
Read the worked answer
The key is (student_id, exam_id, subject_id), which determines marks and evaluator_id. There are also partial and transitive dependencies: student_id determines student_name and dept; exam_id determines exam_date; subject_id determines subject_name. Because non-key attributes depend on part of the key, the table is not in second normal form, and because subject_name depends on subject_id rather than on the whole key, it is not in third normal form. The repetitions cause update anomalies: changing a subject name or exam date requires many row updates and can leave inconsistent duplicates. Insertion anomalies occur when a new subject must be recorded before any result exists; deletion anomalies occur when deleting a student's last result loses department information. These anomalies are independent of the indexing problem and will not be solved by adding indexes.
2. Propose a 3NF schema and explain how it removes the anomalies.
Read the worked answer
A suitable decomposition is: Student(student_id, student_name, dept); Exam(exam_id, exam_date); Subject(subject_id, subject_name); Result(student_id, exam_id, subject_id, marks, evaluator_id). The primary key of Result is the full combination of the three ids, and each non-key attribute depends only on its own table's key. Subject names and exam dates are stored once, so changing them requires one update. A new subject can be inserted without a result, and deleting a result no longer deletes student or subject facts. Foreign keys should enforce referential integrity. The Result table remains large, but it is narrower, which helps caching and index size. The join back to Subject is cheap because subject_id is a small key, so the claim that joins must be avoided is not supported.
3. Critically evaluate the DBA's proposal and recommend an indexing and concurrency strategy.
Read the worked answer
Indexing every column is counterproductive: each index consumes space and slows inserts and updates, and low-selectivity columns such as dept or subject_name are unlikely to help. Denormalising further would reintroduce the update anomalies and increase the risk of inconsistent copies. The main query should be supported by a composite index on (student_id, subject_id), possibly covering marks so that the subject lookup needs fewer page reads. A separate index on exam_id supports the 15% of queries that filter by exam. For lost updates, the clerks' update should use a transaction with a row-level lock, or an optimistic version column checked in the WHERE clause. The application should also avoid read-modify-write outside a transaction. The correct priority is normalise first, then index for the actual query patterns, then enforce concurrency control.