1

It seems to me that Bernstein's synthesis / 3NF synthesis always yields BCNF subrelations, but that's apparently not true.

When one uses 3NF synthesis, one will have subrelations as a result, and they will each consist of either:

  1. just one functional dependency along with all attributes of the schema, so the left side of the lone functional dependency will be a superkey, and that subrelation will therefore be in BCNF.

  2. multiple functional dependencies each of which have the same left side, so they're each superkeys, and that subrelation will therefore be in BCNF.

  3. no functional dependency where the schema includes the attributes making up the primary key of the original / non-decomposed relation, which would satisfy BCNF vacuously because of there being no functional dependencies.

What is an example of the 3NF synthesis algorithm yielding a non-BCNF decomposition and why it is so?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Alfred Kaminski
  • 33
  • 5

2 Answers2

2

Bernstein's algorithm returns (one or more) components in EKNF, which lies between 3NF & BCNF.

Your claims of "that subrelation will therefore be in BCNF" are wrong. The FDs that hold in a component are all the ones in the closure of the original relation whose attributes are all in the component. So FDs could hold in a component that are not out of its superkeys. (Which by definition of BCNF is just another way of saying a component could be not in BCNF. Obviously--since we are told that the algorithm doesn't always give BCNF.)

Since your reasoning is unsound, finding a counterexample seems moot. But just about any presentation of BCNF gives an example non-BCNF 3NF relation, which it then decomposes to BCNF. You can join the non-BCNF 3NF relation with a projection on attributes of one of its CKs extended by a fresh non-prime attribute, and Bernstein's algorithm can decompose back to the 2 tables.

Chris Date's classic An Introduction to Database Systems has a non-BCNF 3NF schema R(S, J, T) with minimal/irreducible cover

{S, J} -> T
{T} -> J

CKs are {S, J} & {T, J}. Berstein gives component (S, J, T)--non-BCNF 3NF input R--in which both given FDs hold--plus redundant component (T, J).

For an example with an additional non-redundant component, extend the cover by {T} -> X. CKs are the same. {S, J} -> T again gives (S, J, T)--non-BCNF--plus component (T, J, X).

So, could someone please give me an example of the 3NF synthesis algorithm yielding a non-BCNF decomposition and tell why it is so?

A better "So, [...]" would be, So, what is wrong with my reasoning? You would do well to examine the assumptions you made about what FDs could hold in a component. (That article happens to point out (with reference) that "A 3NF table that does not have multiple overlapping candidate keys is guaranteed to be in BCNF.")

There is no "why" in mathematics. We assume things ("assumptions", "axioms", "premises") & other things follow. We can ask for a proof of something, but the proof does not say "why" the something is so, it's a demonstration that it is so. "Why" might be used trying to ask for a proof or for steps that you got wrong in or are missing from whatever almost-proof you have in mind.

PS Such a ubiquitous non-BCNF 3NF relation is Today's Court Bookings in the Wikipedia article on BCNF as I write. But beware that that particular example has perhaps unintuitive FDs. Indeed beware that almost every relational model Wikipedia page--including that one--has errors & misconceptions. So do many, many textbooks, especially re normalization.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thanks for the information; I did learn some stuff, but I wasn't asking about a relation *in general* that is in 3NF, but not in BCNF; I was asking for a *(tangible) example* where at least one of the *sub*relations that are returned is not in BCNF (preferably, but not necessarily, along with the reason why it fails BCNF). If I'm still unclear, basically, I know how to recognize whether a relation is in BCNF or not, but I'm struggling to come up with an example (in practice) where Bernstein's algorithm yields such a subrelation. (I feel like an example would make it all make sense for me.) – Alfred Kaminski Aug 18 '19 at 06:30
  • 1
    I added an example decomposition following my previous edit & clarified. PS There are variant algorithms presented as Bernstein's, but you didn't give yours, so if this is not clear, give yours. PS I told you how to construct an example decomposition in my first version. Was it not clear that I did? Why did you still ask for one? I tried to make that clear(er) in my edit. – philipxy Aug 19 '19 at 04:26
1

The answer of philipxy is correct. Since you are asking for an example, here there are a couple of them.

The relation (with a cover of the functional dependencies):

R (A B C D)
A B → C
C → D
D → B

through the synthesis algorithm is decomposed in:

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

and R1 is not in BCNF for the dependency C → B (the candidate key is AB). Note that C → B is not present in the original cover, but is a dependency implied from it.

Here is another (classical) example:

Phones (AreaCode, PhoneNumber, Subscriber, Town, Street)

AreaCode, PhoneNumber → Town
AreaCode, PhoneNumber → Subscriber
AreaCode, PhoneNumber → Street
Town → AreaCode

The Bernsteins’s synthesis algorithm produces two subschemas:

R1 (AreaCode, PhoneNumber, Subscriber, Town, Street)
AreaCode, PhoneNumber → Town
AreaCode, PhoneNumber → Subscriber
AreaCode, PhoneNumber → Street

and:

R2 (Town, AreaCode)
Town → AreaCode

since R2 is included in R1, the algorithm eliminates the second relation. The resulting relation is in 3NF but not in BCNF, since the relation has two candidate keys, (AreaCode, PhoneNumber) and (PhoneNumber, Town) and the functional dependency Town → AreaCode violates the BCNF.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Ah, so for the second example, subrelations that are subsets of others have their functional dependencies "swallowed up". That was what I needed to get a "visceral understanding" of Bernstein’s algorithm not guaranteeing BCNF. Thanks! – Alfred Kaminski Aug 18 '19 at 19:54
  • About the first example, though, I don't see how an implied functional dependency can be allowed to be re-introduced (since, if I'm correct, that would violate the initial requirement of Bernstein's algorithm, which is to be given a canonical cover of the set of functional dependencies since adding the implied functional dependency would be adding a redundant functional dependency). (The process of determining a canonical cover includes a step where one must remove all redundant functional dependencies!) – Alfred Kaminski Aug 18 '19 at 19:56
  • The dependency is not re-introduced, it simply holds in R since it is derived from the initial set of FD. The algorithm starts from a canonical cover, that is a set of FDs from which we can derived all the FDs holding in R. – Renzo Aug 18 '19 at 20:02
  • Okay, so to be clear, you're saying that I should write all non-trivial implied / not-directly-stated functional dependencies somewhere on the side (of my paper, for example) and add them to the sub-relations that can take them, in order to preserve the functional dependencies of the original / non-decomposed relation R, which were obtained by considering only the directly-stated / non-implied functional dependencies, right? I ask because, unless I'm mistaken, none of the sources I've consulted stated that that addition of non-trivial implied functional dependencies is part of the process. – Alfred Kaminski Aug 18 '19 at 22:21
  • @AlfredKaminski Simply: In any decomposition the FDs holding in a component are those holding in its original relation & having all attributes in the component. Here, not just the input cover FD leading to the component or those implied by it. If you are going to reason about component FDs, CKs & NFs then you must account for that. The agorithm steps do not use all FDs that hold, but they still hold. – philipxy Aug 19 '19 at 01:42
  • @AlfredKaminski To better understand, reason & communicate, restrict yourself to technical terms used properly. Eg: Use (the given) cover, (its) closure, etc & not "can take", "obtained by", "directly-stated", etc. Putting words in scare quotes does not make clear the idiosyncratic specific meaning that you didn't write out. You misuse "preserve" to refer to keeping track of FDs--an FD is preserved by a decomposition when it holds in the join of its components. Also it is clearer to talk about what things have what relevant properties & associations, rather than what you are keeping track of. – philipxy Aug 19 '19 at 01:46
  • Renzo (cc @AlfredKaminski): In the 2nd example R1 is the input Phones. Since as you say R2 is dropped, {R1} is certainly the output but since it is the input I don't know whether the asker would consider this a (non-trivial) "decomposition" (that is not just an example of a non-BCNF 3NF relation). I guess the FDs below each component are the cover FDs generating it. More helpful would be a (possibly minimal/irreducible) cover for each component & explicit explanation of what original (closure) FDs hold in components. Which you rely on to say "Note that..." & R1 "is in 3NF but not in BCNF" etc. – philipxy Aug 19 '19 at 05:07
  • (But I guess you were leaving the "and why" to my answer.) – philipxy Aug 20 '19 at 09:54