Skip to content

CS Professional · Artificial Intelligence, Data Analytics and Cyber Security - Laws and Practice · Database Management

In an Employee table, Emp_ID is the primary key, and columns are Dept_ID and Dept_Head, where Dept_ID determines Dept_Head. A company wants to remove the redundancy that causes an update anomaly when a department head changes, while keeping the DPDP-style requirement of accurate data. Which design step is correct?

Create a separate Department table holding Dept_ID and Dept_Head, and keep Dept_ID in Employee as a foreign key. This removes the transitive dependency, achieving Third Normal Form, so a change of head is updated once, avoiding anomalies and keeping data accurate.

  1. AMove Dept_ID and Dept_Head to a separate Department table, keeping Dept_ID in Employee as a foreign keyCorrect
  2. BCombine Dept_Head into the primary key of Employee
  3. CDuplicate Dept_Head in every Employee row to speed up reads
  4. DDelete Dept_ID from Employee and keep only Dept_Head

Explanation

Emp_ID -> Dept_ID -> Dept_Head is a transitive dependency violating 3NF. Splitting into Employee and Department tables with a foreign key stores each head once, so one update fixes all rows and keeps data accurate. Duplicating data worsens anomalies.

Did you get it right without looking?

One question tells you little. A timed set on Database Management shows your real accuracy, how long you take and where you lose marks.

Practise Database ManagementFree diagnostic mock

More Database Management questions