Transforming table to third normal form

normalizationrelational-theory

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?

Best Answer

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
);
Related Question