Consider the following relation: Sales: [Product#, Salesman#, Customer#, Commiss
ID: 3699321 • Letter: C
Question
Consider the following relation:
Sales: [Product#, Salesman#, Customer#, Commission%, Discount-rate, Final-Price]
Assume that a product may be sold by multiple salesmen, and hence (Product#, Salesman#) is the candidate key. Additional functional dependencies are:
Customer# ? Discount-rate
Salesman# ? Commission%
Based on the given candidate key, is this relation in 1NF, 2NF, or 3NF? Why or why not?
How would you successively normalize it completely? Provide the final set of relations.
Explanation / Answer
Sales: [Product#, Salesman#, Customer#, Commission%, Discount-rate, Final-Price]
Assume that a product may be sold by multiple salesmen, and hence (Product#, Salesman#) is the candidate key.
Additional functional dependencies are:
Customer# ? Discount-rate
Salesman# ? Commission%
There is a partial dependency that the given are in 2NF
Since candidate key is (Product#, Salesman#)
There will be partial dependency