What is the minimal proof that a database relation is not in BCNF?

Question

I have the following functional dependencies (they represent all the functional dependencies on my relation):

(1) BrokerName -> Office
(2) StockName -> Dividend
(3) InvestorId -> BrokerName
(4) InvestorId, Stockname -> Quantity
(5) InvestorId, Stockname -> Office

I know from using the techniques in this YouTube video that (InvestorId, Stockname) is my one and only candidate key.

According to @nvogel's solution in this SO thread:

A relation, R, is in BCNF iff for every nontrivial FD (X->A) satisfied by R the following condition is true:

(a) X is a superkey for R

Since I know that (1), (2) and (3) are all non-trivial FDs whose left hand sides are not superkeys or candidate keys for that matter, is that all I need to say to prove that my relation is not in BCNF? Is this process the correct method of demonstrating that a relation is not in BCNF or is there a better way?

Kicker

Why did you add (5) to the list? Do you understand that there are yet other non-trivial FDs that hold when the FDs in the new (& old) list hold--per Armstrong's axioms? I hope my answer explains. – Sickroom

Answer 1

We need to know all the FDs (functional dependencies) that hold to determine CKs (candidate keys), not just those in some list. Look at a (correct & general) definition of CK or algorithm for finding CKs (in a published textbook, not a youtube video). Is your list appropriately a closure (all the FDs that hold) or cover (FDs that imply the FDs in the closure via Armstrong's axioms), whichever that definition or algorithm uses? Because if not then you can't say that you know the set of CKs. Your original claim that you "have the following functional dependencies" is not enough. Your later claim that "they represent all the [nontrivial?] functional dependencies" is wrong--if those hold, {InvestorId, Stockname} -> {Office} also holds. Your later adding item 5 to the list doesn't help--there are others. But even if Armstrong's axioms wouldn't add any FDs to the list, so there wouldn't be any others that hold when the listed ones hold, why do you think the given list is exhaustive in your design if you didn't show it?

We may know that some FDs hold, and Armstrong's axioms give all the FDs that must hold if those do, but to know that given FDs form a cover we must also show that the FDs that are not generated by Armstrong's axioms don't hold. Note that if X doesn't functionally determine Y then no subset of X determines Y & X doesn't determine any superset of Y.

Similarly, that definition of BCNF is talking about all the non-trivial FDs that hold, not just some or those in a cover.

On the other hand, all you need to do to show that that particular definition of BCNF is violated is give some non-trivial FD that holds is not out of a superkey. So--given that your FDs form a cover and every attribute is mentioned in it--so that {InvestorId, Stockname} is the only CK--yes, any of 1-3 alone is adequate, since they are non-trivial & none is out of a superkey.

PS Find & follow a (good) published academic textbook on information modeling & database design. Dozens are online free in pdf. See the Stanford University free online course & its youtube videos (& its professor's textbook).

Sickroom

Yep, should probably make it clearer that those are all the FDs that exist on the relation. Just to clarify, if 1-4 represents all the FDs, is it called a cover? – Kicker

See my edited version. You claim you properly found FDs. But if that list 1-4 isn't a cover then you didn't find the CKs correctly. – Sickroom

I understand what you are saying about finding all the FDs - I've edited my post accordingly. The FDs I have listed are exhaustive. – Kicker

No that list is not exhaustive. Eg InvestorId, Stockname -> Office holds. Armstrong's axioms give the closure ie an exhaustive list. I said: is it a closure or cover, per your definition or algorithm. Quote the definition or algorithm to show you used it propery. What (unsound) justification do you (wrongly) think you have that that list is exhaustive? – Sickroom