Answer:
See explaination
Explanation:
The given relation is:
CAR_SALE(Car#, Date_sold, Salesperson#, Commission%, Discount_amt)
Primary key: {Car#,Salesperson#}
We will first check whether the relation is in 1NF.
A relation is said to be in the 1st Normal form(1NF) if all the columns in the relation have atomic values. In this case, the relation is in 1NF.
A relation is said to be in the second normal form(2NF) if it satisfies the condition that all non keys are completely dependent on the primary key and just a part of the primary key.
In the case of the CAR_SALE relation, the Commission% depends only on the Salesperson# and not on the combination of Car# and Salesperson#. This means that the given relation is not in 2NF. To satisfy 2NF, we make two relationships as given below:
SALE_DETAIL(Salesperson#, Commission%)
and CAR_SALE(Car#, Date_sold, Salesperson#, Discount_amt)
Now both the relations are in 2NF.
For a relation to be in 3NF, no non-key should be functionally dependent on another non key.
In the case of the CAR_SALE relation, it is specified that the Date_sold field determines the Discount_amt. Therefore the modified CAR_SALE is not in 3NF.
To satisfy 3NF, we seperate the non key and the dependent non key and create a different table.
Final Relations:
SALE_DETAIL(Salesperson#, Commission%)
Discount_Detail(Date_Sold, Discount_Amt)
CAR_SALE(Car#, Date_sold, Salesperson#)
