When will it be in 3NF?

Question

I am having a hard time understanding the 3 Normal form.

3 NF: 2 NF + No transitions

So, for eg: If I have,

A -> B
B -> C

Then the above is sort of a transition relation and hence won't be in 3 NF? Am I understanding it correctly?

But in this answer What exactly does database normalization do? , by paxdiablo, it says,

Third normal form (3NF) - 2NF and every non-key column in a table depends on nothing but the key.According to this, it will be in 3 NF. Where am I going wrong?

This is one of those simple concepts that the texts make way too complicated. Read something like this: http://www.essentialsql.com/get-ready-to-learn-sql-11-database-third-normal-form-explained-in-simple-english/ — Brian MacKay, Jul 25 '15 at 14:36
Is B a key column or not? If it is not one of the candidate keys, then the B⟶C FD is not a dependency on the key of the table If it is a candidate key, then either both B⟶A and A⟶C are also functional dependencies (both A and B are candidate keys, so both determine C, and each determines the other) or A⟶B is a non-key dependency. If the only functional dependencies are the ones shown, then you can't have both A and B being the candidate key, so the relation cannot be in 3NF or BCNF. — Jonathan Leffler, Jul 25 '15 at 14:41
What are all the columns of your example? What are *all* the FDs? Are those two supposed to be a *minimal cover*? Ie are the FDs in the table exactly the ones that must be present when those two are? Those two can't be the *only* ones because if those FDs hold then A ⟶ C also holds. — philipxy, Aug 06 '15 at 06:24
@BrianMacKay Except that 1. It refers to "the primary key", but what matters is candidate keys. 2. Its definition of transitive dependence is wrong. X transitively determines Z iff there's a Y where X → Y and Y → Z *and not* Y → X — philipxy, Aug 06 '15 at 06:40
@JonathanLeffler A ⟶ B and B⟶ C so A⟶C. So A is a CK. So there are only 2 cases, B⟶ A or not, with B a CK or not (respectively), giving 2NF or BCNF (respectively). — philipxy, Aug 06 '15 at 07:03

Renzo · Accepted Answer · 2015-08-06T05:56:59.893

1

A relation is in 3NF if it is in 2NF and:

either each attribute depends on a key,
or, if an attribute depends on a non-key, then it is prime.

(being prime means that it belongs to a key).

See for instance Wikipedia.

A relation is in Boyce-Codd normal form if only the first condition hold, that is:

each attribute depends on a key

So, in your example, if the relation has only three attributes A, B and C and the two dependencies, it is not in 3NF, since C is not prime, and depends on B, which is a not a key. On the other hand, if there are other attributes, and C is a key or part of a key, then it could be in 3NF (but this depends on the other functional dependencies, that should satisfy the above conditions).

The 2NF says that each non-prime attribute depends on each whole candidate key, and not by part of it. For instance, if a relation has attributes A, B and C, the only key is AB, and B -> C, then this relation is not in 2NF.

edited Aug 06 '15 at 05:56

answered Jul 25 '15 at 17:50

Renzo

26,848
5
49
61

Prime means, that let's say, in some database, `AB` turn out to be candidate key, then `A` and `B` will be prime, right? – John Lui Jul 25 '15 at 18:16
Yes, you are right. BTW, to know if an attribute is prime or not, one shold know every candidate key of a relation. – Renzo Jul 25 '15 at 18:43
Right. Got it. Thanks. Can you add a similar statement for the 2NF? Yours is the most clear answer I have ever seen. Thanks again. :) – John Lui Jul 25 '15 at 19:07
I edited the answer to add the definition for the 2NF. – Renzo Jul 25 '15 at 19:19
Thanks once again! :) – John Lui Jul 25 '15 at 19:59
And sorry for being so greedy, but 1 NF is just that each column of the table will contain only a single value, right? – John Lui Jul 25 '15 at 19:59
Yes, you are right. Its name (as *normal form*) is still maintaned just for historical reasons, since it is now considered an essential part of the definition of the *relational data model*. – Renzo Jul 25 '15 at 21:29
can you also add the explanation for BCNF (Boyce Codd Normal form), it still confuses me and I always refer your answer for the explanation for other normal forms too. – John Lui Aug 05 '15 at 09:30
@JohnLui & Renzo The 2nf definition is correct. But every attribute depends on every CK. So the 3nf definition is *wrong*. – philipxy Aug 06 '15 at 05:50
@philipxy, can you explain your comment? Note that if an attribute depends on a CK, it depends on *any* CK. Or are you referring to the second part of the definition? – Renzo Aug 06 '15 at 05:53
@philipxy, I added explicitly in the definition of 3nf that it must be also in 2nf, if this is the meaning of your comment. – Renzo Aug 06 '15 at 05:57
@Renzo Read your point 1. It is always true. So the either-or is always true. Your point 2 is ambiguous but either way you disambiguate it it's wrong even if 1 was right. It doesn't help to require 2NF. You are confounding two different definitions of 3NF. The further limitations you add are still wrong. Read the wiki page closely. – philipxy Aug 06 '15 at 06:10
@philipxy, 1 is not always true. "each attribute depends on a key" ≡ "there are not attributes that depends on a non-key" (De Morgan), in other words, there is no such FD with X → A and X is not a key (or obviously a superkey) (and A is not prime, for the second part). – Renzo Aug 07 '15 at 07:45
@Renzo The possible meanings of "each attribute depends on a key" are clear. (By "key" you apparently mean CK.) 1. "For every attribute there's some key it depends on." A superkey is a set of attributes that determines all other attributes; it ("trivially") determines the ones in it. So every attribute depends on every superkey. Including every CK. There's always a CK. So for every attribute there's some key it depends on. 2: "There's some key that every attribute depends on." There's always a CK. Each determines every attribute. So there's some key that every attribute depends on. – philipxy Aug 07 '15 at 09:51
@Renzo PS: Formally the above are 1: forall a: exists k: k determines a. 2: exists k: forall a: k determines a. There is no AND or OR for applying DeMorgan's law; there's a "(functionally) determines". Anyway, whatever its name, your transform is invalid. Your new version still quantifies over attributes and keys though; but then you rephrase invalidly introducing quantification over *FDs*. (That version at least is close to correct, but the parenthetical text is unclear.) Your answer's language just does not define correctly. (And your transformations are invalid & your language is unclear.) – philipxy Aug 07 '15 at 09:59
@philipxy, consider which is the negation of "each attribute depends on a key". It is "*it is not true that* each attribute depends on a key", i.e. "there exists at least an attribute which *does not* depends on a key" (¬∀(x) P(x) ≡ ∃(x) ¬ P(x), where the P(x) means "x depends on a key", and this is an extension of the De Morgan Law). So, there are two cases: either the attribute does not depend on anything (but in this case is prime), or it depends on a non-key. And, in this case, the relation is not in 3NF. This seems to me very linear. And I can't find anything incorrect with this. – Renzo Aug 07 '15 at 19:25
@philipxy For what concern "(By "key" you apparently mean CK.)", here is a quote from a well known textbook: Garcia-Molina, H., Ullman, J.D, Widom, J. Database Systems: The Complete Book. 2nd ed., pag.72: "In some books and articles one finds different terminology regarding keys. One can find the term “key” used the way we have used the term “superkey,” that is, a set of attributes that functionally determine all the attributes, with no requirement of minimality. These sources typically use the term “candidate key” for a key that is minimal — that is, a “key” in the sense we use the term." – Renzo Aug 07 '15 at 19:35
@Renzo Everything you just wrote is already contradicted by what I have written. (So I don't expect further commenting by me to be fruitful.) If you want to see how to use language & logic properly then study my comments and answer. Re "key": I asked what *you* meant by "key". What someone else means by it is irrelevant. *Your own quote says it is commonly used both ways.* In fact you *still* haven't said what *you* mean by it. – philipxy Aug 07 '15 at 19:45
My use of the term “key” is the same as that of the textbook cited, for what other reason should I have cited it? – Renzo Aug 07 '15 at 19:48

score 0 · Answer 2 · edited May 23 '17 at 12:08

The 2-part 3nf definition you are trying for is:

2NF holds and every non-prime attribute of R is non-transitively dependent on every superkey. (X transitively determines Z when there's a Y where X → Y and Y → Z and not Y → X.)

The other definition of 3NF is:

For every non-trivial FD X → Y, either X is a superkey or the attributes in Y but not in X are prime. (X → Y is trivial when X contains Y.)

Then BCNF is:

For every non-trivial FD X → Y, X is a superkey

See this answer.

If your example's only columns are A, B and C and your two FDs form a minimal cover then the only candidate key is A and C is dependent on a non-superkey so it is not in 3NF (or BCNF).

You are (mis)using terms so sloppily that your sentences don't mean anything. Learn the terms and how they are used in their definitions to refer to various things and use them that way in reference to appropriate things. And get your definitions from a (reputable) textbook.

When will it be in 3NF?

2 Answers2