Huy Vo - 8 months ago 30

SQL Question

I have this database:

`R(A, B, C, D, E)`

Keys: A

F = {A -> B, D -> E, C -> D}

I normalize it into 3NF like this:

`R(A, B, C, D, E)`

Keys: AD

F = {AD -> B, AD -> E, C -> D}

What I do is when I check D -> E, D is not a superkey and E is not a key attribute, so I treat D and A as a superkey {AD}. When I check C -> D, C is not a key but D is a key attribute so it's OK.

Is my normalization correctly?

Answer

There is a problem in your input data. If the relation `R`

has the dependencies `F = {A -> B, D -> E, C -> D}`

, then `A`

**cannot be a key**. In fact, a key is a set of attributes whose closure determines all the attributes of the relation, which is not the case here, since:

```
A+ = AB
```

From `F`

, the (only) possible key is `AC`

, in fact

```
AC+ = ABCD
```

Normalizing means to reduce the redundancy by **decomposing** a relation in other relations in which the functional dependencies do not violate the normal form, and such that joining the decomposed relations, one can obtain the original one.

In you solution, you do not decompose the relation, but only change the set of dependencies with other dependencies not implied by the first set.

A correct decomposition would be instead the following:

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

The algorithm to decompose a relation into 3NF can be found on any good book on databases.