tep 1: Understanding the Table Structure
Given Table Structure:
InvoiceID: Unique identifier for each invoice.
InvoiceDate: Date when the invoice was issued.
CustID: Unique identifier for each customer.
CustName: Name of the customer.
ItemID: Unique identifier for each item.
ItemName: Name of the item.
ItemQty: Quantity of the item ordered.
ItemPrice: Price of one unit of the item.
InvoiceTotal: Total amount for the invoice.
Step 2: First Normal Form (1NF)
Conditions for 1NF:
1. The table should have no repeating groups or arrays.
2. Each cell must contain a single value (atomic value).
Analysis:
The table is already in 1NF because each cell contains atomic values.
Step 3: Second Normal Form (2NF)
Conditions for 2NF:
1. The table must be in 1NF.
2. There should be no partial dependency, i.e., non-prime attributes must depend on the entire
primary key.
Analysis:
Here, the composite key could be (InvoiceID, ItemID), as one invoice can contain multiple items.
CustName is dependent on CustID alone, and InvoiceTotal is dependent on InvoiceID alone,
which violates 2NF.
Decomposition into 2NF:
1. CUSTOMER-INVOICE Table:
o InvoiceID, InvoiceDate, CustID, CustName, InvoiceTotal
ORDER-DETAILS Table:
InvoiceID, ItemID, ItemName, ItemQty, ItemPrice
Step 4: Third Normal Form (3NF)
Conditions for 3NF:
1. The table must be in 2NF.
2. There should be no transitive dependency, i.e., non-prime attributes must not depend on other
non-prime attributes.
Analysis:
The CUSTOMER-INVOICE table has a transitive dependency since CustName depends on CustID.
To remove this, we need to split it further.
Decomposition into 3NF:
1. INVOICE Table:
o InvoiceID, InvoiceDate, CustID, InvoiceTotal
1. ORDER-DETAILS Table (remains unchanged):
o InvoiceID, ItemID, ItemName, ItemQty, ItemPrice
Step 5: Boyce-Codd Normal Form (BCNF)
Conditions for BCNF:
1. The table must be in 3NF.
2. For every functional dependency, the left side must be a super key.
Analysis:
All the tables now follow BCNF, as there are no partial or transitive dependencies, and each left
side of the dependency is a super key.
Final Normalized Tables
1. INVOICE Table:
o InvoiceID, InvoiceDate, CustID, InvoiceTotal
2. CUSTOMER Table:
o CustID, CustName
3. ORDER-DETAILS Table:
o InvoiceID, ItemID, ItemName, ItemQty, ItemPrice