If we find two transitive relations what would we

问题:

Say we have six columns A B C D E F, while removing transitive dependency we encounter the dependency where F-> E,D,C,B,A (say F is the primary key)

A->B (A is a transitive key towards B)

C->D (C is a transitive key towards D)

In this case what would we do, would we make a new table with four columns or will we make two tables with two columns each?

回答1:

If you have a relation schema R(A B C D) with dependencies:

A → B
C → D

then the following facts hold:

• The key of the relation is:

  A C

• The relation is neither in Boyce-Codd Normal Form, since both dependencies (A → B and C → D) violates the rule that the determinant should be a superkey, nor in Third Normal Form (since, in addition the the previous fact, B and D are not prime attributes).

• The relation can be transformed in Third Normal Form (and also Boyce-Codd Normal Form) by decomposing it in three relations:

  1. R1(A B), with A as key and unique dependency: A → B
  2. R2(C D), with C as key and unique dependency: C → D
  3. R3(A C), with A C as key and no non-trivial dependencies

EDITED

Since you have changed your question, the answer has to be completely different. You should be aware that to solve a normalization problem, one should have two things (and only those two things):

1. The list of the attributes of the relation schema

2. A set of functional dependencies

No other information is needed, while to give a partial information is only confusing and does not produces a correct answer.

So, if I have understood your problem, you need to normalize a relation schema

R(A B C D E F)

for which the following functional dependencies exists:

A → B
C → D
F → A B C D E

In this particular case,

• the only (candidate) key of the relation is F;

• the relation is not in BCNF since both A → B and C → D violates the rule that the determinant should be a superkey, nor in 3NF (since, in addition the the previous fact, B and D are not prime attributes);

• the relation can be transformed in 3NF (and also BCNF) by decomposing it in three relations:

  1. R1(A B), with A as key and unique dependency: A → B
  2. R2(C D), with C as key and unique dependency: C → D
  3. R3(A C E F), with F as key and dependencies: F → A, F → C, F → E.