views:

261

answers:

3

Which of these is true:

  1. If a table is in 2nd normal-form then it must be in 3rd normal-form.
  2. If a table is in 3rd normal-form then it must be in 2nd normal-form.

I know only one of them is correct, if it's the first it makes sense. If it's the second -doesn't make any sense to me. Maybe an example?

+1  A: 

If a table is in 3NF it's in 2NF for sure, but not vice versa.

That holds up though at least 5NF. In theory it should also be true of 6NF vs. 7NF as well, but at least the last I heard, it was pretty hard to say much with certainty about 7NF -- including even proving with a given dataset was in 7NF or not, and if not how to transform it to 7NF.

Edit: The specific case of 3NF vs. 2NF:

First of all, you're sort of right that these two aren't as obviously built on top of each other as most of the other normal forms. 2NF deals only with having a composite key (i.e. at least two columns treated as a key for the table) but having an associated field that really applies to only one part of that key. The classical example would be a row consisting of something like "part #, quantity, manufacturer, Division, Division address", where "part #" and "manufacturer" are treated as the key. In this case, the division and division address really apply only to the manufacturer, so to put the data in 2NF, you should separate the data into two tables, one with "part #, quantity, manufacturer" (and "part #" as the key), and another with "manufacturer", "division" and "division address" (with manufacturer as the key). This way, you have only one copy of the data about a part source, regardless of the number of parts you buy from them.

That puts the data into second normal form, but not third normal form. 3NF dictates that each non-key field provides exactly one fact about the key. In the case above, a manufacturer can have multiple divisions, and each division has its own address. Even though the data is in second normal form, the division address is providing a fact about a division, which is a non-key field. To transform the data to 3NF, we have a couple of choices: we might treat manufacturer and division as a composite key, and the address is a fact about that entire key, OR we can separate the address into still another table, so we end up with one table giving the manufacturer and the division, and another with the division and that division's address.

Jerry Coffin
How can that be?? I don't get it. If a table is in 3NF it means that each field that is not part of the key is only dependent on a field in the key. 2NF says that every non-key is dependent on the whole key! That sounds more strict!
Eve
2NF says nothing about the relationships among fields. 2NF allows derived data within a row. 3NF prohibits this. For example 2NF allows `Key, C1, 2*C1` as a row. 3NF prohibits the last two columns because `2*C1` depends on `C1`.
S.Lott
Great, thanks for the example. Just want to make sure I understood it well: The two new tables: "part #, quantity, manufacturer" and "manufacturer, division, division address" ARE in the second normal form even though the second table contains Division-> Division Address? That's not a problem in 2NF?
Eve
As long as only the "manufacturer" is the key, then it's in 2NF, yes. The requirements of 2NF (beyond those of 1NF) *only* apply in the case of a composite key.
Jerry Coffin
+1  A: 

NFs are supposed to be like incremental requirements. To be 2NF, it must be 1NF and follow a few more restrictions. To be 3NF, it must be 2NF and follow a few more restrictions. Each NF is a specialization of the "previous" NF.

Here are a few definitions for 3NF:

Definition from about.com: In order to be in Third Normal Form, a relation must first fulfill the requirements to be in Second Normal Form. Additionally, all attributes that are not dependent upon the primary key must be eliminated.

Definition From wikipedia:

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd1 in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

  • The relation R (table) is in second normal form (2NF)
  • Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.

So, it MUST be 2NF to be 3NF also

Samuel Carrijo
+6  A: 

A database in 3NF is in 2NF also. A simple mnemonic is:

  • The key
  • The whole key
  • and nothing but the key
  • So help me Codd

2NF states that a table contains no fields that are logically a function of a part of the primary key. 3NF states that a table contains no fields that are logically a function of any field of the table but the 'whole' key. 3NF can be viewed as a specialisation of 2NF.

ConcernedOfTunbridgeWells
Does a table in 2NF allow a non-key field to be dependent on a non key field?
Eve
@Eve: Yes. 2NF allows non-key dependencies.
S.Lott