33

Struggling to see the differences between them. I know we say 2NF is "the whole key" and 3NF "nothing but the key".

Referencing this great answer by Smashery: What are 1NF, 2NF and 3NF in database design?

The example used for 3NF is exactly the same as 2NF - its a field which is dependant on only one key attribute. How is the example for 3NF different from the one for 2NF?

Thanks

philipxy
  • 14,416
  • 5
  • 32
  • 77
Marcus
  • 8,463
  • 10
  • 44
  • 65
  • 9
    The 2NF violation is because the `Course Name` column is dependant upon part of the key (`CourseID`). The 3NF violation is because `TeacherName` is functionally dependant on `TeacherID` but this is not part of the key. – Martin Smith May 16 '11 at 20:40
  • 1
    So a table in 2NF could have `CourseID`, `Semester`, `#Places`, `TeacherID`, `TeacherName`? Seems odd considering `TeacherName` is clearly duplicate data from `TeacherID`. – Marcus May 16 '11 at 20:46
  • 4
    Yes. 2NF is only concerned with functional dependencies on partial key. If the table is in 1NF and does not have a composite key than AFAIK it is automatically in 2NF. – Martin Smith May 16 '11 at 20:49
  • 2
    @Martin I think you deserve the correct answer for your quick comment but without it as an actual answer I can't award this to you! – Marcus May 16 '11 at 20:53
  • 1
    Saying "TeacherName is functionally dependant on TeacherID" is one thing. Saying "TeacherName is clearly duplicate data from TeacherID" is quite a different thing. You'd be well-served to try to understand the difference. – Mike Sherrill 'Cat Recall' May 16 '11 at 21:18
  • 1
    @MartinSmith "If the table is in 1NF and does not have a composite key than AFAIK it is automatically in 2NF." Common misconception.(Even wrongly given in textbooks.) Only if also {} isn't a determinant, ie if also no column is constrained to have only one value. Also re your 1st comment, by definition any violation of 2NF is a violation of 3NF. Also FDs that violate are really per a particular definition. But for standard definitions the type of FDs that violate 2NF are all of the type of FD that violate 3NF. – philipxy Apr 22 '20 at 07:41

5 Answers5

14

Suppose that some relation satisifies a non-trivial functional dependency of the form A->B, where B is a nonprime attribute.

2NF is violated if A is not a superkey but is a proper subset of a candidate key

3NF is violated if A is not a superkey

You have spotted that the 3NF requirement is just a special case (but not really so special) of the 2NF requirement. 2NF in itself is not very important. The important issue is whether A is a superkey, not whether A just happens to be some part of a candidate key.

Michael Mior
  • 27,152
  • 8
  • 85
  • 111
nvogel
  • 24,234
  • 1
  • 40
  • 79
12

Since you ask very specific question about an answer for existing so question here is an explanation of that (and basically I'll say what dportas already said in his answer, but in more words).

The examples of design that is not in 2NF and not in 3NF are not the same.

Yes, the dependency in both cases is on a single field.

However, in non 2NF example:

  • dependency is on the part of the primary key

while in non 3NF example (which is in 2NF):

  • dependency is on a field that is not a part of the primary key (and also notice that in that example it does satisfy 2NF; this is to show that even if you check for 2NF you should also check for 3NF)

In both cases to normalize you would create additional table which would not exhibit update anomalies (example of update anomaly: in 2NF example, what happens if you update Coursename for IT101|2009-2, but not for IT101|2009-1? You get inconsistent=meaningless=unusable data).

So, if you memorize the key, the whole key and nothing but the key, which covers both 2NF and 3NF, that should work for you in practice when normalizing. The distinction between 2NF and 3NF might seem subtle to you (question if in the additional dependency the attribute(s) on which the data is dependent are part of candidate key or not) - and, well, it is - so just accept it.

Community
  • 1
  • 1
Unreason
  • 12,340
  • 2
  • 33
  • 47
11

2NF allows non-prime attributes to be functionally dependent on non-prime attributes

but

3NF allows non-prime attributes to be functionally dependent only on super key

Thus,when a table is in 3NF it is in 2NF and 3NF is stricter than 2NF

Hope this helps...

Lordferrous
  • 652
  • 8
  • 8
5

You have achieved the 3rd NF when there are no relations between the key and other columns that don't depend on it.

Not sure my professor would have said that like this but this is what it is.

If you're "in the field". Forget about the definitions. Look for "best practices". One is DRY : Don't Repeat Yourself.

If you follow that principle, you already master everything you need for NF.

Here is an example. Your table has the following schema:

PERSONS : id, name, age, car make, car model

Age and name are related to the person entry (=> id) but the model depends to the car and not the person.

Then, you would split it in two tables:

PERSONS : id, name, age, car_models_id (references CAR_MODELS.id)
CAR_MODELS : id, name, car_makes_id (references CAR_MAKES.id)
CAR_MAKES : id, name

You can have replication in 2FN but not in 3FN anymore.

Normalization is all about non-replication, consistency, and from another point of view foreign keys and JOINs.

The more normalized the better for data but not for performance nor understanding if it gets really too complicated.

tiktak
  • 1,216
  • 8
  • 15
  • Thanks, I'd agree with the DRY principle hence why I was surprised to see duplication of data in the 2NF. – Marcus May 16 '11 at 20:52
  • 2
    "You have achieved the 3rd NF when there are no relations between the key and the other columns." I'm not really sure what you mean by that sentence but I am very sure that it's wrong. Being in 3NF has nothing to do with there being no "relations [sic]" between the key and other columns! – nvogel May 17 '11 at 05:56
  • 4
    If someone was "in the field" of, say, building bridges, would you also advise him to "forget about the definitions" of, say, the laws of gravitation ? – Erwin Smout May 17 '11 at 09:09
  • You know that not all what you learn at school can be applied in the real job. In the IT domain as in others it's better to have common sense that to know all theories but no clue how to use them. – tiktak May 17 '11 at 11:33
  • BTW, I didn't want to say YOU are stupid but you're playing dumb here. You don't have to know formulas to know what gravitation is. (I'd call this "gravity"?) Experience and basic understanding of how things work are enough. A child knows his bike is gonna fell down if he doesn't put his feet on the ground. A man knows he's gonna get hurt if he jumps from a building, even if he never took any physics lesson. – tiktak May 17 '11 at 11:51
  • 2
    user753136, Do you really not see the point of Erwin's comment? An engineer ought to know the formulae that define the forces acting on a bridge. Without that knowledge he can guesstimate when it will break but he won't know for sure. Similarly, the NFs are a formal method for evaluating the accuracy of a database design against a set of dependencies. Redundancy in database design can be quite subtle and simply saying DRY isn't enough to be sure you identify every possible problem. Your advice to "forget the definitions" is no help at all. Why would ignorance be any advantage? – nvogel May 17 '11 at 18:22
  • 2
    @user753136, your advice to prefer best practices over theories and definitions is confusing, since in design of relational databases all good practices come from the theory. Understanding theory and definitions helps you understand the reasons for the 'best practices' and gives you much more powerful thinking tools. – Unreason May 18 '11 at 10:12
1

2NF follows the partial dependency whereas 3NF follows the transitive functional dependency. It is important to know that the 3NF must be in 2NF and support transitive functional dependency.

rashedcs
  • 3,209
  • 2
  • 35
  • 39