4

If not, then how do I make it BCNF while being lossless and dependency preserving?

I can see that the primary key of R is AB. And in BCNF every functional dependency should be of the form [Key] -> [Key or Non-key]. But C->B doesn't satisfy that.

I know I will lose the AB->C dependency if I do any decomposition. But I am unable to figure out which decomposition would be lossless. I would be grateful if someone could help me out.

3
+100

Given AB->C, C->B we can infer only that AB and AC are keys. It follows that C is not a superkey and therefore R does not satisfy BCNF.

The following is a possible decomposition that would satisfy BCNF

R1{A,B} KEY {A,B}
R2{A,C} KEY {A,C}
R3{C,B} KEY {C}
| improve this answer | |
  • 1
    Any thoughts on how to transform it into BCNF? – Lennart Dec 30 '16 at 17:19
  • 1
    I'll award the bounty, though I think it would have been good with some thoughts on how/why not the relation can be transformed into BCNF. – Lennart Jan 3 '17 at 9:20
  • How is the AB->C preserved in the decomposition? (or isn't it?) – ypercubeᵀᴹ Jan 3 '17 at 12:55
  • In any schema decomposition extra constraints may be necessary to enforce dependencies between relations. In this example, the join dependency between R1, R2, R3. Note that this is true not just in the case of BCNF decomposition but also for lesser NFs as well. – nvogel Jan 3 '17 at 15:45
-1

Based on the following criteria for the definition of BCNF:

X → Y is a trivial functional dependency (Y ⊆ X)

or

X is a super key for schema R

I would say that your relation R(A,B,C) is in BCNF with the functional dependencies of {AB → C, C → B}

The reason why is that there is an implicit relationship of {A → C}. B is directly determined by C, but C is also directly determined by the combination of AB. If B were to actually matter in determining C, we'd have a dependency loop.

This implicit relationship allows {AB → C} to fall under "X is a super key for schema R", since A is a primary key of the schema. A → C, C → B, therefore A → BC. Any relation that contains A is a superkey.

The second functional dependency is a trivial dependency, {C → B}.

| improve this answer | |
  • 2
    If A happened to be a key then you would be correct. However there is no basis here for saying that A is a key. AB->C, C->B implies only that AC->B, not that A->C. C->B is not a trivial FD because B and C are different attributes. – nvogel Dec 30 '16 at 16:10

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.