Databases & SQL Practice Question 6: Third Normal Form
Databases & SQL · Test 1 · question 6 of 10
A table is defined as employees(emp_id PRIMARY KEY, emp_name, dept_id, dept_name). Every employee in a department repeats the same dept_name. When the "Ops" department was renamed to "Platform", some rows were updated and some were missed, so reports now show both names for department 7.
Which schema change removes the redundancy that caused this problem?
Show answer and explanation
Answer:
dept_name depends on dept_id, which in turn depends on the key emp_id. It does not depend directly on the key. This is a *transitive dependency*, and it breaks third normal form (3NF). The practical symptom is an update anomaly: one fact (department 7's name) is stored in many rows, so one logical change needs many physical updates, and missing any of them leaves the data inconsistent. If you move the fact into its own table keyed by dept_id, it is stored exactly once and a rename becomes a single-row update. The foreign key keeps employees.dept_id valid.
Option A only makes the multi-row update faster. The name is still duplicated, so a partial update is still possible.
Option B changes nothing about where dept_name is stored. It also weakens the model, because it would let the same employee appear once per department.
Option D automates keeping the copies in sync, but the redundancy is still there, and so is extra logic that can fail or be bypassed. The stem asks to remove the redundancy, not to manage it.
Reference: MySQL 8.4: Optimizing for Data Size (Normalization)