Invoice relation

Is this relation in 1NF? 2NF? 3NF?
Convert the relation to 3NF.

Inv# date custID Name Part# Desc Price #Used Ext Price Tax rate Tax Total
14 12/63 42 Lee A38 Nut 0.32 10 3.20 0.10 1.22 13.42
14 12/63 42 Lee A40 Saw 4.50 2 9.00 0.10 1.22 13.42
15 1/64 44 Pat A38 Nut 0.32 20 6.40 0.10 064 7.04


Table not in 1NF because
- it contains derived values
EXT PRICE(=Price X # used)
3.2 = 0.32 X 10

-  Tax (=sum of Ext price of same Inv# X Tax rate)
1.22 = (3.2 + 9.00) X 0.10

-   Total (=sum of Ext price + Tax)
13.42 = (3.20 + 9.00) + 1.22

To get 1NF, identify PK and remove derived attributes

Inv# date custID Name Part# Desc Price #Used Tax rate
14 12/63 42 Lee A38 Nut 0.32 10 0.10
14 12/63 42 Lee A40 Saw 4.50 2 0.10
15 1/64 44 Pat A38 Nut 32 20 0.10

To get 2NF
- Remove partial dependencies
- Partial FDs with key attributes.
- Inv#  -> Date, CustID, Name, Tax Rate
- Part# -> Desc, Price

Remove Partial FDs

|–K1-||———————–D1———————————||—K2—||——-D2———|

Inv# date custID Name Tax rate Part# Desc Price #Used
14 12/63 42 Lee 0.10 A38 Nut 0.32 10
14 12/63 42 Lee 0.10 A40 Saw 4.50 2
15 1/64 44 Pat 0.10 A38 Nut 32 20

=

Inv# date custID Name Tax rate
14 12/63 42 Lee 0.10
14 12/63 42 Lee 0.10
15 1/64 44 Pat 0.10

 

Inv# Part# #Used
14 A38 10
14 A40 2
15 A38 20

 

Part# Desc Price
A38 Nut 0.32
A40 Saw 4.50
A38 Nut 32


Remove transitive FD

Inv#(PK) -> CustID  -> Name

Inv# date custID Name Tax rate
14 12/63 42 Lee 0.10
15 1/64 44 Pat 0.10

=

Inv# date custID Tax rate
14 12/63 42 0.10
15 1/64 44 0.10

+

custID Name
42 Lee
44 Pat


All relations in 3NF

Inv# Part# #Used
14 A38 10
14 A40 2
15 A38 20

 

Part# Desc Price
A38 Nut 0.32
A40 Saw 4.50

 

Inv# date custID Tax rate
14 12/63 42 0.10
15 1/64 44 0.10

 

custID Name
42 Lee
42 Pat