database-normalization

Can we normalize a relation directly to 3NF without normalizing to 2NF first?

I have to find the keys and normalize this to BCNF:

R(A,B,C,D,E,F,H)

FD set:

A->D
AE->H
DF->BC
E->C
H->E

I found 2 candidate keys: A, F, E and A, F, H.

If I normalize to 2NF I lose some relations, which doesn't suit 3NF.

What are the steps?

Can we normalize a relation directly to 3NF without normalizing to 2NF first?

Solution

  • There are two basic requirements for a database to be in third normal form:

    If you normalize in 2NF you wont lose any relations, rather you will get another relation. Okay so lets do your homework now.

    Lets start by assuming that your relations are already in 1NF. Now for 2NF

    R1 = AE -> {H}(AE->H)
R2 = DF -> {B, C}(DF->BC)
R3 = A  -> {D}(A->D)
R4 = E -> {C}(E->C)

    The above relation is in 2NF, none of the non-prime attributes are partially dependent on the candidate key. And also in 3NF because there is not transitive relationship within a relation.

    okay now for BCNF, all the relation obey BCNF except for R1 because H->E relationship holds in R1 and H does not belong to the candidate key in R1.

    Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B} cannot be represented by a BCNF schema by preserving the dependencies that held in the original table. Read wiki for further details.

    But you can still convert it into BCNF,

    R1 = A -> {E}
R2 = E -> {H}
R3 = DF -> {B, C}
R4 = A  -> {D}
R5 = E -> {C}

    But the above tables dose not hold the original relationship AE-> H, making it inconsistent and this relationship being BCNF non-achievable by preserving the dependencies that held in the original table.