Consider a database storing the data of invoices, and each invoice takes the following form:
Invoice_Num: 1480 Handled By: John Keaton Staff Num: 263 Staff Position: Trainee Item_Num Item_Desc Unit_Price Qty ======== ============== ========== === 15003 Blue Ballpen 2.00 5 15012 5ml Gluestick 0.80 3
- Each invoice is handled by 1 and only 1 staff.
- Each item in the same invoice is unique.
So, in 1NF, the data can be represented as:
Invoice [ Invoice_Num, Staff_Num, Staff_Name, Staff_Pos, Item_Num, Item_Desc, Unit_Price, Qty ]
where Invoice_Num and Item_Num form the primary key.
Now we know that Invoice_Num -> Staff_Num, and Staff_Num -> Staff_Pos, and Staff_Num -> Staff_Name.
But then when the database is normalized to 2NF, should we move the Staff_Pos and Staff_Name to another table? If we use the transitive property, we have Invoice_Num -> Staff_Pos, and Invoice_Num -> Staff_Name, then we should move them to another table to eliminate partial dependency. However, with the use of transitive property, should these 2 dependencies be considered transitive dependency instead, and hence moving Staff_Pos and Staff_Name to another table should be done in 3NF?