2

Schema R = (A,B,C,D,E)

Functional Dependency F1 = {A->BC , CD->E, B->D, E->A}
Functional Dependency F2 = {A->D, A->E, DE->BC, B->A, D->C}

According to F1, candidate keys - A, E, BC, CD
According to F2, candidate keys - A, B, DE

Condition for a schema to be in 3NF:
For all X->Y, at least one of the following is true:
1. X is a superkey
2. X->Y is trivial (that is,Y belongs to X)
3. Each attribute in Y-X is contained in a candidate key

I am aware that R is in 3NF according to F1 but not in 3NF according to F2.

R is not in 3NF according to F2 because in functional dependency D->C,
1. D is not a superkey
2. D->C is not trivial
3. C-D which is C is not contained in any candidate keys.
So R is not in 3NF according to F2.

Now how can I convert it into 3NF?

I tried the following:
Decomposing R into (A,B,D,E) (C,D) (B,C,D,E) in such a way that dependency is also preserved.

Is this right and is there any other way to decompose?

philipxy
  • 14,867
  • 6
  • 39
  • 83
mukund
  • 317
  • 5
  • 10
  • 22

2 Answers2

3

A decomposition in third normal form of your example is the following (after each schema I've put the projection of the functional dependencies on it):

R1 <(A D E), {A → DE, DE → A}>    
R2 <(B D E), {DE → B, B → DE}> 
R3 <(A B), {B → A, B → A}>     
R4 <(C D), {D → C}>

The algorithm used is the classical Bernstein’s algorithm. A sketch of the algorithm is the following:

  1. Transform the dependencies in canonical form: in this case the result is {A → D, A → E, DE → B, B → A, D → C}
  2. Group the dependencies with the same left part, in this case: {A → DE, DE → B, B → A, D → C}
  3. From each group produce a decomposition, in this case: (ADE, DEB, BA, DC)
  4. Check if a relation is contained in another (in this case this does not happen)
  5. Check if at least a key is contained in a subschema (true, since the keys are {DE, B, A})

Note that your decomposition is not correct.

Renzo
  • 26,848
  • 5
  • 49
  • 61
0
  1. After you decompose, the original relation is gone, and the new components have their own FDs (functional dependencies) & CKs (candidate keys), so you may have to keep decomposing.
  2. Just decomposing per a problematic FD doesn't mean there aren't more problematic FDs.
  3. When some FD hold, others hold, per Armstrong's axioms. So there may be problematic FDs that you weren't explicitly given.
  4. A decomposition might fail to "preserve" a FD, ie no component contains all the attributes of the FD, so you might not then properly decompose if that FD is problematic.

So if you want to decompose to a particular NF (normal form), use an algorithm that has been proven to do that.

philipxy
  • 14,867
  • 6
  • 39
  • 83