0
$\begingroup$

I have a relation R = {A,B,C} and F={AB -> C , C -> B}. In order to check if R is in BCNF.

I checked if AB and C are both superkeys and since C is not a superkey I conclude R is not in BCNF.

After decomposing R into BCNF, I have two relations :

R1(C,B)   F1{ C -> B}
R2(A,C)   F2{ A -> C}

Now since A and C are both superkeys, I can conclude that now R(R1, R2) is in BCNF.

But after computing (F1 U F2)+, I can't derive AB -> C and so I lose that FD.

My question is, how can I decompose than into BCNF while preserving FD's?

$\endgroup$
  • $\begingroup$ Your R(R1, R2) is not information equivalent to the original R -- that is, there are facts you can represent in R that you can't represent with (R1, R2). Then it's not a 'lossless join' decomposition. So you haven't "decomposed"; you've just created a fresh schema with the same attribute names. Why do you think bare C has to be a superkey? (It's not.) Form the closure of the FDs then look again. $\endgroup$ – AntC Nov 2 '18 at 7:02
  • $\begingroup$ According to what i know, for a relation R to be in BCNF, R should be in 3NF and every FD X-> Y in R, X should be a super key. That is why I am checking if AB is superkey and C is superkey. I dont understand how to decompose this any further while preserving the FD AB -> C $\endgroup$ – Aamir Nawab Nov 2 '18 at 8:20
0
$\begingroup$

this very example is discussed in the first place I looked.

The short answer is that you cannot always achieve all of

  • BCNF;
  • an information-equivalent schema;
  • using keys as the only declared constraint(s).

Pick any two.

In this case you must retain R = {A, B, C}, with key {A, B} -- then we can't losslessly decompose R. In order to represent the FD C -> B as a relation with a suitable key, you need R1 = {C, B} with key {C}. So far we've achieved BCNF. To retain information equivalence, you need an Inclusion Dependency (aka Foreign Key constraint) R[C] ⊆ R1[C].

Strictly speaking to retain info equivalence, that Inclusion Dependency should probably be an Equality Dependency. But this is a contrived example, so nobody in practice would do that.

$\endgroup$
  • $\begingroup$ So what foreign key can we add in R1 because we cant add attribute A in R1. In order to preserve FDs we will end up with AB -> C $\endgroup$ – Aamir Nawab Nov 2 '18 at 9:00
  • $\begingroup$ Can we decompose R into R1(C,B) F1={C -> B} R2(A,B,C) F2={AB->C} Here C and AB are superkey we can say R(R1,R2) is in BCNF ALso (F1 U F2)+ = F+. $\endgroup$ – Aamir Nawab Nov 2 '18 at 9:04
  • $\begingroup$ At least look at wikipedia before you ask any more questions. It has an article on foreign keys; see also Inclusion Dependencies under 'Referential Integrity'. Stackoverflow has over 100 q's tagged [bcnf]. Or what does your textbook say? Foreign keys are not "in" a relation, they're between relations. I gave the constraint needed. $\endgroup$ – AntC Nov 2 '18 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.