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?

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).