1

I've been trying to figure out the difference between the 2nd and 3rd Normal Form using this example. The definitions didn't do the trick for me...

These are the functional dependencies:

A is the candidate key. (A --> A,B,C,D)
FDs:
A --> CD
AC --> D
CD --> B
D --> B

My idea: it's in 1st and 2nd, but not in 3rd Normal form because A, the candidate key, doesn't consist of two or more columns. But B is transitively dependent on D. So it's not in 3rd.

Ist that correct? Especially the argument that A consits of less than two columns?

J. Wu
  • 69
  • 1
  • 1
  • 7
  • You need to memorize definitions & theorems. Otherwise you can't use a word since you don't know what it means & you can't claim anything because you can't justify. This question is full of misconceptions. Eg one can't find CKs or NFs from FDs without knowing all the attributes of the relation in which they hold. Eg "CK consists of two or more columns" is not part of a definition or requirement for 3NF. Why do you think it is? (It's part of a common misconception about 2NF though.) Eg A -> B transitively when there exists X where A -> X -> B *and not X -> A*. Eg other FDs hold when those do. – philipxy Jan 31 '18 at 20:22

1 Answers1

0

First, let us see what 2NF and 3NF are. From the context of the question it is clear that 1NF is understood, so I will refer to it. If it is unclear as well, let me know, I will clarify that as well.

2NF: R is in second normal form, if and only if it is in first normal form and no non-prime attribute is dependent on any proper subset of any candidate key of the relation.

non-prime attributes are attributes which are not part of any candidate keys. So, if a non-prime attribute can be determined by a functional dependency which holds a non-whole subset of a candidate key, then the relation is not in 2NF.

For example, let's consider an invoices(number, year, age) table where (number, year) is a candidate key. age can be determined by the year alone, so the table is not in 2NF.

In your case, since the key is one dimensional, assuming it is in 1NF, we can say it is in 2NF as well. However, it is in 3NF if and only if it is in 2NF and every non-prime attribute is non transitively dependent on every key.

In your case, A is the key, but since

A -> D -> B

B is transitively dependent on A, so your table is not in 3NF. To achieve 3NF, you will need to create another table, which will be in relation with this one via D and will hold B. Possible solution:

T1(A, C, D)

T2(D, B)

Note, that AC -> D and A -> CD are trivial, since A is the candidate key and the candidate key determines everything else. If that's not the case, you will need to take a look at 1NF as well.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Yes I already understood the 1st normal form. Thank you very much! – J. Wu Jan 31 '18 at 17:15
  • @J.Wunderlich This gets "transitively dependent" wrong. X -> Y transitively when there exists W where X -> W -> Y *and not W -> X*. This gets "trivial" wrong. X -> Y is trivial when Y is a subset of X. "is one dimensional" doesn't mean anything here; I guess "is not compound" or "is simple" or "has exactly one element" was meant. PS ["1NF" has no generally agreed-upon meaning.](https://stackoverflow.com/a/40640962/3404097) But (although "If that's not the case" is unclear) [FDs have nothing to do with any notion of "1NF".](https://stackoverflow.com/a/42153542/3404097) – philipxy Jan 31 '18 at 20:44