# Transforming table to third normal form

I have a table like this: `L(A, B, C, D, E)` and functional dependencies are:

``````AB -> CDE
C -> D
D -> B
D -> E
``````

I need to transform this table into 3NF. I think it's not even in 2NF. I found 3 candidate keys:

1. first one is obviously `AB`
2. from `D->B` we can change `AB->CDE` to `AD->BCE`. So another candidate key is `AD`
3. using similar logic we get another key `AC`

(I am not sure if I did this correct).

From `D -> E` (I think there are more dependencies like that) I assume that this table is not in 2NF. What is the correct way to split this table up to get 3NF?

• Assuming c.k (I have not checked) are AB, AD and AC then E is the non-prime attribute. But E is functionally dependent of D and D is part of a c.k. so L is not in 2NF. Makes sense? – Lennart Jun 9 '14 at 22:13

I would take following approach.

From left side of the relations we see 3 primary keys:

• Key `AB` with `AB->CDE` relation is represented in `LAB` table.
• Key `C` with `C->D` relation is represented in `LC` table.
• Key `D` with `D->E` and `D->B` relations are represented in `LD` table.

Does it make sense?

``````CREATE TABLE LD (
D INT,
B INT, /* B can be omitted here, and derived from AB */
E INT,
CONSTRAINT LD_PKEY PRIMARY KEY ( D )
);

CREATE TABLE LC (
C INT,
D INT,
CONSTRAINT LC_PKEY PRIMARY KEY ( C ),
CONSTRAINT LC_D_FKEY FOREIGN KEY ( D ) REFERENCES LD

);

CREATE TABLE LAB (
/* Attributes D and E are dependent on C, so we don't store them here */
A INT,
B INT,
C INT,
CONSTRAINT LAB_PKEY PRIMARY KEY ( A, B ),
CONSTRAINT LAB_C_FKEY FOREIGN KEY ( C ) REFERENCES LC
);
``````