CODEWITHSUNDEEP
databasedatabase-designdatabase-normalizationfunctional-dependencies3nf
bawse
bawse

Reputation: 230

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:

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?

Upvotes: 1

Views: 1807

Answers (2)

임재동
임재동

Reputation: 1

I think that relation can't be 3NF.

Because 3NF's definition is

  1. The relation R (table) is in second normal form (2NF).
  2. No non-prime attribute of R is transitively dependent on the primary key.

In your figure doesn't have primary key, so one of candidate key can be assumed primary key. But the {E->M} E is subset of candidate key (ET).

Edit: Above definition of 3NF took from wikipedia(https://en.wikipedia.org/wiki/Third_normal_form), it was wrong definition. But point of mine is that relation can't be 2NF. Because of subset of ET is in partial functional dependency {E->M}.

Upvotes: -1

Renzo
Renzo

Reputation: 27424

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.

Upvotes: 1

Related Questions