Search code examples
database-normalization3nf

Database normalization 2NF and 3NF

Assume the relation Appliance(model, year, price, manufacturer, color) with {model, year} as a key and following FDs:

model -> manufacturer
model, year -> price
manufacturer -> color

Find 2NF and 3NF.

My solution was this: Since model -> manufacturer violates 2NF because of partial dependency, I decomposed Appliance as below:

R1(model, manufacturer)
R2(model, year, price)
R3(manufacturer, color)

Similarly model -> manufacturer and manufacturer -> color violates 3NF because of transitive dependency so, I decompose Appliance as below:

R1(model, manufacturer)
R2(model, year, price)
R3(model, color)

My question is what is wrong with my normalization?

Solution

  • Your normalization for 2NF is right. You might want to think harder about whether a relation violates 2NF or 3NF, or whether a functional dependency violates 2NF or 3NF. You say both.

    In your 2NF decomposition, R1, R2, and R3 are in 5NF. (So, by definition, they're also in 3NF and 2NF.)

    For 3NF, you've lost the FD manufacturer -> color. So that's wrong.

    In the real world, normalizing a relation like "Appliance" might result in more than one 5NF decomposition.