The primary key of an order table (order_no, item_no, qty, cust_no, cust_name) is {order_no, item_no}, and its functional dependencies are {order_no, item_no} → qty, order_no → cust_no and cust_no → cust_name. Which is the correct result of normalizing this table up to third normal form (3NF)?

Answer: B. order_line(order_no, item_no, qty), order_header(order_no, cust_no), customer(cust_no, cust_name)

Second normal form is a state in first normal form in which no column depends on only part of the primary key (no partial functional dependency). order_no → cust_no is a partial functional dependency, because cust_no is determined by order_no alone, which is only part of the primary key, so the original table is in first normal form but not in second normal form. Moving the columns determined by order_no into order_header(order_no, cust_no, cust_name) gives second normal form. Third normal form (3NF) is a state in second normal form in which no column is determined via a column other than the primary key (no transitive functional dependency). order_header still has the transitive functional dependency order_no → cust_no → cust_name, so customer(cust_no, cust_name) is split off as well. The three tables order_line(order_no, item_no, qty), order_header(order_no, cust_no) and customer(cust_no, cust_name) are then in third normal form. Joining them on order_no and cust_no restores the original table.

• A. order_line still contains cust_no, which is determined by order_no alone; this partial functional dependency means it is not even in second normal form.
• B. Correct. Both the partial and the transitive functional dependencies have been removed, giving third normal form.
• C. This is in second normal form, but order_header keeps the transitive functional dependency order_no → cust_no → cust_name.