contestada

CAR_SALE (Car_no, Date_sold, Agent_no, Commission_pct, Discount_amt)
Assume that a car may be sold by multiple agents, and hence {Car_no, Agent_no} is the primary key. Therefore, a single car sale may occupy multiple tuples.
Additional dependencies are:
Date_sold → Discount_amt and
Agent_no → Commission_pct
Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or
why not? How would you successively normalize it completely? Provide reasoning for each normalization.

Respuesta :

faizanzamirarayen

1NF

In 1NF the relation should follow atomicity that means no repeating groups

In the relation there is no repeating groups

So

the relation is in 1NF

2NF

In 2NF, the relation should be in 1NF and each non prime

attribute must depend on primary key and No Partial dependency

Commission% is functional dependent on Salesperson# which is only part of the primary key.

So

relation is not in 2NF

3NF

In 3NF, the relation should be in 2NF and any NON prime attribute should not describe any non prime attribute.

Already we know relation is not in 2NF

there is a non prime attribute date_sold that describe another non prime attribute Discount_amt,

the relation is not in 3NF

Finally we come to. Know

the relation satisfies only 1NF

NORMALIZATION

The relation is not in 2NF

Decompose the relation CAR_SALE

CAR_SALES (Car#, Date_sold, Salesperson#, Discount_amt)

SALESCOMM (Salesperson#, Commission%)

CAR_SALES and SALESCOMM are in 2NF

In 3NF, the relation should be in 2NF and any NON prime

attribute should not describe any other non prime attribute

SALESCOMM relation is in 3NF

but CAR_SALES is not in 3NF the reason is that there is a

non prime attribute Date_sold that describes another non prime attribute Discount_amt.

So, decomposing the relation

CARSALEDATE (car#, salesperson#, Date_sold)

DATEDISS(Date_sold, discount_amt)

The relations CARSALEDATE

SALESCOMM

DATEDIS are in 3NF

Otras preguntas