Consider the following relation: CAR-SALE(Car#, DateSold, Salesman, Commission%,

Question

Consider the following relation: CAR-SALE(Car#, DateSold, Salesman, Commission%, DiscountAmt). Assume that a car may be sold by multiple salesmen and hence CAR#, Salesman#1 is the primary key. Suppose the following dependencies exist: DateSold -> DiscountAmt, and Salesman#-> Commission% (1) Is this relation in 1NF? Why or why not? (5 points) (2) Is this relation in 2NF? Why or why not? (5 points) (3) Is this relation in 3NF? Why or why not? (5 points) (4) Please successively normalize this relation up to 3NF (include the detailed normalization steps for 1NF, 2NF, and 3NF, respectively). For each new relation obtained in the normalization/decomposition process, please underline the primary key of the relation. (30 points)

Explanation / Answer

1) This relation is in 1NF because it(Relation) follows following requirements:

  

2) This relation is not in 2NF because it does not have a single column primary key(i.e. not following the rule--No non-prime attribute is dependent on the proper subset of any candidate key of table.) here the Commission% is dependent on Salesman#.

3) since the relation is not in 2NF, it is obviously not in 3NF.

4) Decomposing the Relation in 2NF as follows-

CAR_SALE(Car#, Date_Sold, DiscountAmt, Salesman#)

primary key {Car#,Salesman#}

non-prime attributes {Date_Sold, DiscountAmt}

and

SALESMAN_COMM(Salesman#, Commission%)

primary key {Salesman#}

    non-prime attributes {Commission%}

now, the relations are in 2NF because

why it is not in 3NF--because in   CAR_SALE(Car#, Date_Sold, DiscountAmt, Salesman#) the exist a transitive dependency i.e. Car#,Salesman#->Date_Sold and Date_Sold->DiscountAmt that makes Car#,Salesman# ->DiscountAmt

Decomposing the Relation in 3NF as follows-

CAR_SALE(Car#, Date_Sold, Salesman#)

primary key {Car#,Salesman#}

non-prime attributes {Date_Sold}

and

DATE_DISCOUNT(Date_Sold, DiscountAmt)

primary key {Date_Sold}

    non-prime attributes {DiscountAmt}

and

SALESMAN_COMM(Salesman#, Commission%)

primary key {Salesman#}

    non-prime attributes {Commission%}

now, the relations are in 3NF because

thank you!

please do upvote :)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.