# How to decompose into BCNF while preserving all functional dependencies

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?

• 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. – AntC Nov 2 '18 at 7:02
• 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 – Aamir Nawab Nov 2 '18 at 8:20

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.

• 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 – Aamir Nawab Nov 2 '18 at 9:00
• 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+. – Aamir Nawab Nov 2 '18 at 9:04
• 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. – AntC Nov 2 '18 at 10:08