I need to know how to put this in first, second, and third normal form OrderLine
ID: 3803065 • Letter: I
Question
I need to know how to put this in first, second, and third normal form
OrderLineItems(OrderId, OrderLine, EmpEmail, Phone, ProductId, FirstName, LastName, ProdName, Quantity, UnitPrice, Discount1, Discount2, Discount3, DatePlaced, SalesChannel).
Discussion with a subject matter expert reveals the following functional dependencies:
OrderId, OrderLine any other attribute in the table, but perhaps transitively
OrderId DatePlaced
OrderId SalesChannel
EmpEmail FirstName
EmpEmail LastName
EmpEmail Phone
ProductId ProdName
ProductId UnitPrice
Explanation / Answer
Given,
OrderId, OrderLine can determine any other attribute in the table.
Hence our primary key to the table is OrderId, OrderLine.
we say that a table is in 2 NF if partial dependencies are not there a partial dependency
means part of key determining other attributes.
In the given dependencies the below are partial dependencies as OrderId is a part of key
OrderId ----> DatePlaced
OrderId ----> SalesChannel.
we say that a table is in 3 NF if transitive dependencies are not there a transitive dependency
means non key attribute determining other non key attributes.
In the given dependencies the below are transitive dependencies
EmpEmail ----> FirstName
EmpEmail ----> LastName
EmpEmail ----> Phone
ProductId ----> ProdName
ProductId ----> UnitPrice
Resolving Partial dependencies, find the clousre of the determinent in the partial dependency and
make a separate table for it (do the same for every Partial dependency).
For OrderId ----> DatePlaced
OrderId+ = { OrderId , DatePlaced , SalesChannel }
For OrderId ----> SalesChannel
OrderId+ = { OrderId , DatePlaced , SalesChannel }
Hence the new table will be OrderId , DatePlaced , SalesChannel and the other table will be
OrderId, OrderLine, EmpEmail, Phone, ProductId, FirstName, LastName, ProdName, Quantity, UnitPrice,Discount1, Discount2, Discount3, DatePlaced, SalesChannel
2 NF tables :-
1) OrderId, OrderLine, EmpEmail, Phone, ProductId, FirstName, LastName, ProdName, Quantity, UnitPrice,Discount1, Discount2, Discount3, DatePlaced, SalesChannel
2)OrderId , DatePlaced , SalesChannel
Resolving Transitive dependencies, find the clousre of the determinent in the transitive dependency and
make a separate table for it (do the same for every Transitive dependency).
For EmpEmail ----> FirstName, EmpEmail ----> LastName, EmpEmail ----> Phone
EmpEmail+ = { EmpEmail ,FirstName, LastName,Phone}
For ProductId ----> ProdName, ProductId ----> UnitPrice
ProductId+ = { ProductId,ProdName,UnitPrice }
Hence the tables in 3NF will be as below
1) OrderId, OrderLine, EmpEmail, ProductId, Quantity, Discount1, Discount2, Discount3, DatePlaced, SalesChannel
2)OrderId , DatePlaced , SalesChannel
3)EmpEmail ,FirstName, LastName,Phone
4) ProductId,ProdName,UnitPrice