Question

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?

Answer 1

Answer:

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