Question

Assume that you have been given the following attributes:

Inv_Num, Prod_Num, Sale_Date, Prod_Desc, Vend_Num, Vend_Name. Num_Sold, Prod_Price

Assume there are no repeating groups. An invoice can contain multiple products and a product can be sold by only one vendor. Identify all dependencies and covert to 3 NF. First convert the above to 1NF then 2NF and lastly, 3NF. Show all work.

Answer 1

Consider the relation

Product_Sale(Inv_Num, Prod_Num, Sale_Date, Prod_Desc, Vend_Num, Vend_Name. Num_Sold, Prod_Price)

First Normal Form (1NF) :

• Given relation is in the first normal form because there us no repeating groups
• This signifies that all columns are atomic in nature

Second Normal Form (2NF) :

• Above relation need to normalize into 2NF to remove partial dependency
• This means all non key columns must depends upon primary key columns
• In second normal form needs to identify new tables like
  • Vendor :This table contains vendor details like Vend_Num, Vend_Name
  • Product :This table contains products details like Prod_Num, Prod_Desc ,Prod_Price
  • Invoice :This table contains invoice details like Inv_Num, Sale_Date

Below are tables in 2NF

1.Table Name :Vendor

Schema :Vendor(Vend_Num, Vend_Name)

FD :Vend_Num==> Vend_Name

2.Table Name :Product

Schema :Product ( Prod_Num, Prod_Desc ,Prod_Price)

FD : Prod_Num==>Prod_Desc ,Prod_Price

3.Table Name :Invoice

Schema :Invoice (Inv_Num, Sale_Date)

FD : Inv_Num==>Sale_Date

Third Normal Form (3NF) :

• Above tables needs to normalize into 3NF to remove transitive dependency.
• In Transitive dependency non key columns should depends upon non key columns.

Below are tables in 3NF.

1.Table Name :Vendor

Schema :Vendor(Vend_Num, Vend_Name)

FD :Vend_Num==> Vend_Name

2.Table Name :Product

Schema :Product ( Prod_Num, Prod_Desc ,Prod_Price,Vend_Num)

FD : Prod_Num,Vend_Num==>Prod_Desc ,Prod_Price

3.Table Name :Invoice

Schema :Invoice (Inv_Num, Sale_Date)

FD : Inv_Num==>Sale_Date

4.Table Name :InvoiceDetails

Schema :Invoice (Inv_Num, Prod_Num,Num_Sold)

FD : Inv_Num,Prod_Num==>Num_Sold