Consider the following relation:CAR_SALE (Car#, Date_sold, Salesperson#, Commission%, Discount_amt) Assume that a car may be sold by multiple salespeople, and hence{Car#, Salesperson#}is the primary key. Additional dependencies areDate_sold? Discount_amtSalesperson#? Commission%Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or whynot? How would you successively normalize it completely?

The answer to this question can be given as:

Normalization:

Normalization usually includes the division of a table into two or more tables as well as defining a relation between the table. It is also used to check the quality of the database design. In the normalization, we use three-level that are 1NF, 2NF, 3NF.

First Normal Form (1NF):

In the 1NF each table contains unique data. for example employee id.

Second Normal Form (2NF):

In the 2NF form, every field in a table that is not a determiner of another field's contents must itself be a component of the table's other fields.

Third Normal Form (3NF):

In the 3NF form, no duplication of information is allowed.

Explanation:

The explanation of the question can be given as:

Since all attribute values are single atomic, the given relation CAR_SALE is in 1NF. Salesperson# → commission% ... Given Thus, it is not completely dependent on the primary key {Car#, Salesperson#}. Hence, it is not in 2 NF.

The 2 NF decomposition:

CAR_SALE_1 (Car#, Salesperson#, Date_sold, Discount_amt)

CAR_SALE_2 (Salesperson#, Commission%)

The relationship in question is not in 3NF because the nature of a transitive dependence occurs Discount_amt → Date_sold → (Car#, Salesperson#). Thus, Date_sold is neither the key itself nor the Discount amt sub-set is a prime attribute.

The 3 NF decomposition:

CAR_SALES_1A (Car#, Salesperson#, Date_sold)

CAR_SALES_1B (Date_sold, Discount_amt)

CAR_SALE_3 (Salesperson#, Commission%)