1NF
In 1NF the relation should follow atomicity that means no repeating groups
In the relation there is no repeating groups
So
the relation is in 1NF
2NF
In 2NF, the relation should be in 1NF and each non prime
attribute must depend on primary key and No Partial dependency
Commission% is functional dependent on Salesperson# which is only part of the primary key.
So
relation is not in 2NF
3NF
In 3NF, the relation should be in 2NF and any NON prime attribute should not describe any non prime attribute.
Already we know relation is not in 2NF
there is a non prime attribute date_sold that describe another non prime attribute Discount_amt,
the relation is not in 3NF
Finally we come to. Know
the relation satisfies only 1NF
NORMALIZATION
The relation is not in 2NF
Decompose the relation CAR_SALE
CAR_SALES (Car#, Date_sold, Salesperson#, Discount_amt)
SALESCOMM (Salesperson#, Commission%)
CAR_SALES and SALESCOMM are in 2NF
In 3NF, the relation should be in 2NF and any NON prime
attribute should not describe any other non prime attribute
SALESCOMM relation is in 3NF
but CAR_SALES is not in 3NF the reason is that there is a
non prime attribute Date_sold that describes another non prime attribute Discount_amt.
So, decomposing the relation
CARSALEDATE (car#, salesperson#, Date_sold)
DATEDISS(Date_sold, discount_amt)
The relations CARSALEDATE
SALESCOMM
DATEDIS are in 3NF
