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#} is the primary key. Suppose the following dependencies exist: Date Sold rightarrow Discount Amt, and Salesman# rightarrow Commission% (1) Is this relation in 1NF? Why or why not? (2) Is this relation in 2NF? Why or why not? (3) Is this relation in 3NF? Why or why not? (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.

Explanation / Answer

1.Yes,the relation in 1NF.

Given the attribute values in the dependencies are single atomic values.

2.Not in 2NF.

Consider the dependency

Salesman# commission%

Given the primary key {Car#, Salesman#}.

As the dependency is not fully functionally dependent on the primary key. it is not in 2 NF.

3.

Given 2 dependecies cosider DateSold DiscountAmount,neighter of the attributes are not a prime attribute,nor subset of keys

4.

The 2 NF decomposition for this relation is:

we are removing the partial fuctional dependency by creating new making the preime keys are one set and primekey set with other attributes

a.CAR_SALE1(Car#, Salesman#, DateSold, DiscountAmount)

b.CAR_SALE2(Salesman#, Commission%)

The 3 NF decomposition are as follows:

Removig the ransitive depencdny.

a.CAR_SALES1A(Car#, Salesman#, DateSold)

b.CAR_SALES1B(DateSold, DiscountAmount)

c.CAR_SALE3(Salesman#, Commission%)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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