Consider the following relation: CAR_SALE (Car#, Date_sold, Salesman#, Commision%, Discount_amt Assume that a car may be sold by multiple salesmen and hence {CAR#, SALESMAN#} is the primary key. Additional dependencies are: Date_sold → Discount_amt and Salesman# → commission% Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely

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#)