-2
  • When is BCNF not able to preserve functional dependencies?
  • When is a 3NF decomposition desired instead of a BCNF decomposition preserving functional dependencies?

Please explain with an example.

I saw this question but it does not answer my question:
Decomposition that does not preserve functional dependency

philipxy
  • 14,867
  • 6
  • 39
  • 83
Durgesh Singh
  • 31
  • 5
  • 1
    Your question is too broad for Stack Overflow. Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the [How to Ask](https://stackoverflow.com/help/how-to-ask) page for help clarifying this question. – Renzo Oct 02 '17 at 12:45
  • @philipxy I am using Database System Concepts book by Korth,Siborschatz and Sudershan. I am able to understand BCNF decomposition but not able to understand when does it is not able to preserve FD's. The book says sometimes it preserves but sometimes it does not. Also when it is desired to preserve FD's – Durgesh Singh Oct 04 '17 at 01:30
  • Re your second bullet: Don't you mean, When is 3NF desired instead of BCNF *not* preserving FDs? PS That is a faq. Not only is it addressed in your textbook (8.5.4 6th edition) but many other textbooks and also lots of stackoverflow answers address it with an example. This shows why you should only ask one question in a question. Nevertheless I have given a more comprehensive answer than I have found. – philipxy Oct 05 '17 at 01:02

1 Answers1

0
  • When is BCNF not able to preserve functional dependencies?

Turns out this question is problematic in a certain way that "ok you defined 'prime number' but when is a number prime?" is, but "ok you defined 'simplest form of a fraction' but when is a fraction in simplest form?" isn't. Definition(s) say "when". But what you mean is something like, multiple conditions apply so what more simple/intuitive definition or non-brute-force algorithm characterizes this? But it has been shown that (informally) there is no non-exponential/non-exhaustive algorithm to enumerate BCNF decompositions that do/don't preserve FDs (functional dependencies).

  • When is a 3NF decomposition desired instead of a BCNF decomposition [not] preserving functional dependencies?

If a 3NF design is not in BCNF then it preserves a FD that is not out of a superkey and so cannot be declaratively enforced in most SQL DBMSs. But the BCNF design, not having preserved the FD, needs a constraint enforced that is equivalent to two SQL FK (foreign key) constraints to each other, which cannot be declaratively enforced in most SQL DBMSs. Since there's nothing special about cycles that prevents DBMSs from enforcing them and the two designs can represent each other, there isn't any reason per se why a DBMS couldn't support both.

There's a similar mental complexity for these two design forms--3NF plus FDs not out of CKs vs BCNF plus extra equality dependencies. But since the 3NF relation is the join of its BCNF components, the meaning of a 3NF tuple is the AND/conjunction of the meanings of the BCNF components. Since a user implicitly knows this and should be explicitly told it, and since constraints are not needed to query or modify a database (they're for integrity), the BCNF design is in some sense simpler. But if the user is always wanting to update both components then the 3NF design is in some sense simpler.

Thus, in case we are not able to get a dependency-preserving BCNF decomposition, it is generally preferable to opt for BCNF, since checking functional dependencies other than primary key constraints is difficult in SQL.
-- Database System Concepts 6th Edition (2011) by Silberschatz, Korth & Sudarshan

You can find an example facing this choice in most textbooks, and dozens are online in pdf. It must involve overlapping (composite) CKs (candidate keys).

The meaning of an SJT tuple (s,j,t)--simplified notation--is that student s is taught subject j by teacher t. The following constraints apply:

  • For each subject, each student of that subject is taught by only one teacher
  • Each teacher teaches only one subject (but each subject is taught by several teachers).

[...] From the first constraint, we have the FD {S,J} → T. From the second constraint, we have the FD T → J.
-- An Introduction to Database Systems 8th Edition (2004) by Date

(A 3NF design can suffer from further problems that could be eliminated by further decomposing the BCNF design to higher normal forms. This is why we should always decompose to 5NF then if desired explicitly denormalize. So any non-BCNF 3NF table should have come from such a denormalization.)

philipxy
  • 14,867
  • 6
  • 39
  • 83