0

This is a question from Databases course (now self-study at coursera.org), fall 2011.

Consider the following relational schema:

R1(A,B,C), R2(B,D)

(a) Consider the schema and suppose that the only functional dependencies that hold on the relations in this schema are A → B, A → C, B → A, A → D, and all dependencies that follow from these. Is the schema in BCNF?

(b) Suppose we omit dependency A → D from part (a). Is the schema in BCNF?

The answer to (a) question is yes, it does correspond to my answer. The correct answer to the second question is yes as well. But it doesn't correspond to my answer.

When we omit A → D dependency, then D is not more depenedent on B and therefore B is not the key in B → D. So, we have BCNF violation.

What am I doing wrong?

SGrebenkin
  • 572
  • 4
  • 10

1 Answers1

2

And where is the dependency B->D that you mention in the original problem statement ?

Besides.

The original problem statement is itself fairly flawed. It is highly unusual to talk of dependencies that "span" more than one relation schema in a database design. A->D does exactly that.

It presumably means that the FD is supposed to hold in the relation that results from making the natural join between the given relation schemas, but that is a very unorthodox assumption wrt classical normalization theory. Classical normalization theory as I know it does not deal with such FD's.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • I suppose it's ok to consider cross-relation dependencies, since they show the logical data dependency (i.e. it is not bound to schema). The dependency B → D can be derived from B → A, A → D pair. And it is not true after deletion of A → D. – SGrebenkin Dec 30 '13 at 06:30
  • 1
    The question was : if B->D no longer holds, then how can there still be a BCNF violation ? For there to be a BCNF violation, there has to be an FD !!!!!!!!! – Erwin Smout Dec 30 '13 at 13:39
  • 1
    And no, it is not really OK to "consider cross-relation dependencies" as lightly and sloppily as is done here. FD theory has been developed on single relation schemas. "Considering cross-schema dependencies" can only be done if you first turn the multiple schemas into a single one, e.g. by NATURAL JOIN. The only thing you'll get out of such proceedings is an observation of whether the JOINed schema is in BCNF or not (usually it won't be), and what are the keys of the NATURAL JOIN. – Erwin Smout Dec 30 '13 at 13:52
  • I would say it's ok to consider those cross-relation dependencies. Why not? Since we know the logical data dependency. – SGrebenkin Jan 09 '14 at 06:31
  • 1
    You can say all you want. The axioms of normalization theory apply to functional dependencies that are known to hold __INSIDE A SINGLE RELATION SCHEMA__. The theorems that follow from those axioms are subject to the same assumption/restriction. Abandon that assumption and the theory simply no longer applies. – Erwin Smout Jan 09 '14 at 07:32
  • +1. I think it's better not to use "cross-relation dependency" term. It's true the normalization theory works inside a single relation schema. I agree that presumably FDs are supposed to hold in the final joined relation. Otherwise some of the parts of original question seem senseless (like A->D dependency). In this case it's clear that after deletion of A->D, B->D no longer holds and BD is just a key in R2. So R1 and R2 are both in BCNF, therefore schema is in BCNF. Thanks a lot. – SGrebenkin Jan 09 '14 at 11:45