The primary key of an employee table (emp_no, name, dept_code, dept_name) is emp_no, and every column holds a single value. The functional dependencies are emp_no → name, emp_no → dept_code and dept_code → dept_name. Which correctly states the normal form of this table?

Answer: A. In second normal form but not in third normal form

Every column holds a single, non-repeating value, so the table is in first normal form. Since the primary key is the single column emp_no, a partial functional dependency on part of the key cannot occur, so it is also in second normal form. However, dept_name is determined via dept_code, which is not the primary key, as in emp_no → dept_code → dept_name; this transitive functional dependency means the table is not in third normal form. As it stands, the department name is repeated in the row of every employee of the same department, and renaming a department requires rewriting many rows. Splitting off a department table (dept_code, dept_name) and keeping only dept_code in the employee table gives third normal form. The employee table's dept_code then becomes a foreign key referring to the department table.

• A. Correct. There is a transitive functional dependency dept_code → dept_name, so it is not in third normal form.
• B. The primary key is a single column, so there is no partial functional dependency and the condition for second normal form is met.
• C. dept_name is determined via dept_code, which is not the primary key; this transitive functional dependency means it is not in third normal form.