5

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?

  • 1
    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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.