databasedatabase-designdatabase-normalizationfunctional-dependencies3nf

3NF and lossless decomposition of relation and functional dependencies


I am trying to find the 3NF lossless decomposition of the following relation with respect to the functional dependencies:

enter image description here

I started by deriving the keys from the functional dependencies given above. The keys are {L,T}, {E,T} and {T,M} because all of the attributes in the relation can be obtained using any of these keys.

The definition of 3NF:

A relation schema R is in 3NF if, whenever a function dependency X -> A holds in R, either
a. X is a superkey of R, or
b. A is a prime attribute of R.

Applying this to the FDs:

  • LT -> E satisfies (a) because LT is a key (hence a superkey).
  • ET -> L satisfies (a) because ET is a key (hence a superkey).
  • TM -> E satisfies (a) because TM is a key (hence a superkey).
  • E-> M satisfies (b) because M is a prime attribute.

How can one obtain a 3NF lossless decomposition of the relation with respect to the functional dependencies?

I might be wrong that the relation is in 3NF because there are transitive dependencies.

Where I could be going wrong?


Solution

  • Your answer is correct:

    1. All the keys are actually LT, ET, TM, since they determines all the other attributes, and there are no other keys, since no subset of them satisfies this property.

    2. The dependencies are already a canonical cover of the relation.

    3. The relation is already in 3NF exactly for the reasons that you stated.

    Note that, if you follow the definition of Third Normal Form as you have correctly done, you don't need to check if there are or not transitive functional dependencies.

    We can also note that the original relation instead is not in Boyce-Codd Normal Form, for the dependency E → M, and by applying the analysis algorithm to bring it in BCNF produces the decomposition:

    R1 <(E M), {E → M}>
    
    R2 <(E L T), {L T → E, E T → L}>
    

    that has the property that the dependency T M → E is lost.