1

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

Assumptions:

  • 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?

  • 2
    It's a rare case when normalization lower than 3NF is justified for operations support databases. What is the main purpose of your database: operations support (75% write/25% read) or BI/decision support? – Alex Yu Mar 5 at 19:00
  • @AlexYu Honestly, this is just for conceptual / theoretical purpose on demonstrating the theory of normalization. – GreenPenguin Mar 5 at 19:15
  • 3
    Consider your use cases and potential problems. Do staff members need updated on invoices? How about the invoices themselves? Does this data need integrated into another system (accounting maybe)? Is row versioning relevant? Will the tables be used in combination with other tables or queries? These are the questions I would ask myself here. – Jacob H Mar 5 at 19:16
  • @JacobH The main aim is to reduce the amount of data redundancy, and to avoid those 3 types of update anomalies as much as possible. As I mentioned, this is rather a theoretical design question for teaching how to perform normalization, rather than a real database used in real life. – GreenPenguin Mar 6 at 13:41
0

If's a design choice, and you have to consider the trade-off:

If you use 2NF, there will be some duplication, which can potentially be detrimental to integrity - you can change a value in one place and not in the other.

So, if you decide to go with 2NF, you have to be more careful about integrity.

If you use 3NF, there will be no duplication. But you might end up with too many tables, and have hard time keeping track of them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.