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