Explaining 2NF vs 3NF with an example

Question

I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students.

Let's have a table with 5 fields.

Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grade}

Dependencies are this way:

StudentName, SubjectCode, #Exam -> Grade

SubjectCode -> SubjectName

SubjectName -> SubjectCode

Therefore, candidate key 1 is {StudentName, SubjectCode, #Exam} and candidate key 2 is {StudentName, SubjectName, #Exam}.

Prime attributes are {StudentName, SubjectCode, SubjectName, #Exam} and non-prime attributes is Grade

According to the definition of second normal form, a non-prime attribute cannot depend on a part of a candidate key. The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .

The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.

Gradings = {StudentName, Subject Code, #Exam, Grade}

Subjects = {Subject Code, SubjectName}

But 2NF does not produce this. 3NF is about dependencies between non-prime attributes so it does not produce this either. But it seems to me that this is the right outcome, because it has no redundancy.

I guess if non-prime attribute was defined as "attribute that is not a candidate key", 2NF would produce the desired result. But I have checked this again and again and non-prime attribute is defined as "attribute that does not BELONG to a candidate key".

What am I doing wrong?

Answer 1

Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs.

The relation is not in BCNF, because the left hand side of the two small FDs is not a superkey.

You can, however, losslessly decompose the relation to (SubjectCode, SubjectName) and either (StudentName, SubjectCode, #Exam, Grade) or (StudentName, SubjectName, #Exam, Grade)

This decomposition gives you two BCNF relations and preserves all functional dependencies. This isn't always possible (you can always decompose a relation to 3NF, but not necessarily to BCNF).

2NF

If you want an example of 2NF (and not 3NF), your relation needs to contain transitive dependencies.

For instance, say you have a Score column. Intuitively Score->Grade since all exams with the same score should get the same grade (it would be rather unfair otherwise), but note that we cannot say Grade->Score since several scores can have the same grade (11% and 12% would likely be "Fail", for instance).

Now your relation is:

Gradings(StudentName, SubjectCode, SubjectName, #Exam, Score, Grade)

and you have a new form of redundancy since every time you enter a result with the same score as another Gradings record you also have to repeat the corresponding Grade. To get to 3NF you could therefore decompose to

ScoreGrades(Score,Grade)

with Score as the key, and

Scores(StudentName, SubjectCode, SubjectName, #Exam, Score)

Answer 2

You are right in everything you say. Subject Code, SubjectName need to go in their own table in order to enforce the desired dependencies. This is a good example of why 2NF and 3NF are not sufficient to produce good database designs - you need Boyce Codd Normal Form (BCNF) instead.

2NF and 3NF are superseded by BCNF which practically speaking makes those lesser NFs obsolete*. BCNF is the more important and arguably simpler to explain and apply. As a teacher I suggest you spend more time on BCNF and less on 2NF and 3NF. If a table satisfies the requirements of BCNF then it also satisfies 2NF and 3NF as well.

---

* 3NF is not the highest dependency-preserving Normal Form. Elementary Key Normal Form (EKNF) is. Strictly speaking it is EKNF, not BCNF, that makes 3NF obsolete, but EKNF is unjustly neglected and most textbooks and courses don't even mention it. What amounts to the same thing is to design to BCNF and then check that all the desired dependencies and any other integrity rules can be properly enforced - if not, then modify the design. None of the NFs is a complete solution to data integrity but BCNF generally comes closest and is the easiest to explain and use.

Answer 3

I won't say how long it has been since I first learned all this. But I do remember I had a prof who, after dutifully teaching us the proper meaning of "functional dependency" and "non-prime attribute" and all the other buzzwords, gave us a series of simple questions to ask to see if a particular normal form was reached. Let's see if I can remember that far back...

We've identified the candidate key(s) so we ask this question of the remaining non-prime attributes. In this case, there is only one: grade.

What is the absolute minimum information do we need to uniquely identify the grade? We need to know the student, the subject and the exam being taken. Fine, this is one of the candidate keys.

EDIT: V V V

But the answer could just as well have been student name, subject name and exam. This would match the second candidate key.

Assuming that SubjectCode and SubjectName are both candidate keys for the Subject entity, there is no reason to have both these fields here. One unique reference to a row in the Subjects table is quite enough. So we may safely get rid of the SubjectName field altogether without sacrificing any integrity of the model.

However, in my original answer, in my desire to show another level of normalization, I ignored that SubjectName had been used in a candidate key and considered it just another non-prime attribute. I guess it was so obvious to me that this was a useless field I thought it would be just as obvious to everyone and since either way we got rid of the field, what did it matter?

But instead of removing that part of the answer, I'll keep it in for comparison.

END EDIT: ^ ^ ^

What is the absolute minimum information do we need to uniquely identify the subject name?

SubjectName is dependent only on SubjectCode -- a subset of the candidate key. So this tuple is not in 2nf. SubjectCode should be the primary key of a Subjects table so that is the proper location to place SubjectName. Remove it from this tuple and it is now in 2nf.

If we ask the question of an attribute and the answer is not all or part of the candidate key, then the tuple is not in 3nf. But this tuple is also trivially in 3nf and beyond, as we've run out of fields to ask questions of. ;)

Note: when we say "normalize", we are referring to a process that is applied to a logical entity. As the supplied tuple seems to be the definition of an entity called "grade", then we can normalize it. However, at one point I said "this tuple is not in 2nf," which more properly should have been, "this entity is not in 2nf." I apologize if this has caused confusion.

Answer 4

The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .

It is in 2NF.

The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.

There is no reason to expect that subjects should have their own table for a decomposition of the original table to 2NF. You are confusing some vague notion of "should" with what any particular normal form actually gives you.

3NF is about dependencies between non-prime attributes so it does not produce this either.

"3NF is about dependencies between non-prime attributes" is not a proper definition of 3NF so "so it does not produce this either" is not a sound conclusion. Although applying an actual definition does show that the table is in 3NF, with no student table needed. But again, there is no reason to expect that there would be.

But it seems to me that this is the right outcome, because it has no redundancy.

Again, "redundancy" is unhelpfully vague, so your "because" and student table expectation are unsound. Different normal forms are free of and subject to particular kinds of anomalies and associated "redundancy". But other "redundancy" not addressed by normalization can remain.

This table is not in BCNF, since it has FDs that aren't out of CKs. Decomposing it per BCNF leads to having the student table. BCNF is the highest normal form for dealing with JDs (join dependencies) that accompany FDs. However, other JDs can be problematic (ie not "implied by the CKs") and should be removed by normalization to 5NF.

PS The original table also satisfies the FD {StudentName, SubjectName, #Exam} -> Grade.

Dependencies are this way:

What is this supposed to mean? It is not a clear.

Do you mean, "These are all the non-trivial FDs that hold"? No, because they imply the fourth. "Here are some FDs that hold"? No, that means that the FDs in the transitive closure hold, but it doesn't say that other ones don't hold, yet you were able to determine the CKs. "The FDs that hold are exactly the ones in the transitive closure of these"? If you meant that, you would only know it if you had shown it, ie you would have to have found that closure (typically, via a minimal/canonical cover) and then shown that there are no other FDs; did you? Regardless, what you wrote just does not mean that. So I expect that you are not reasoning soundly about the FD & CK situation.

Answer 5

You are correct, subjects requires its own table. If you pick one of your candidate keys, either subject_code or subject_name becomes a non-primary candidate key. You then remove the non-primary subjects field from the gradings table.

You have a functional dependency on subject for which you have two unique identifiers. This is demonstrated by the transitive dependency between subject_code and subject_name. This indicates a requirement to create a table containing those two fields and remove one of these fields from all other tables. This table could well have additional dependent columns, although I see none in this example. In 3rd normal form you have selected.

grade depends on the other three fields (candidate key) in the new gradings table. As noted above you need to pick one of the candidate fields for the subjects table. Normally this would be a code value if available as they tend to be more stable. The resulting model is in 3nf as all non-key fields are fully dependent on the fields in the primary key.

Further analysis of the the problem (requirements) is likely to yield a sessions table against which marks are applied. The current model is unlikely to cover a student repeating a course. This would be covered in a later lesson.

Students are also likely become a separate table as it is possible to have multiple students with the same name. This would likely be resolved by the addition of a synthetic primary key (student number?).

subjects --->  sessions ---+--> grades
students  -----------------+

Answer 6

I'm preparing to delete this as it is considered incorrect

Subject Name is also a non-prime attribute and it depends on part of the primary key Subject Code (breaks rule - there must not be any partial dependency of any column on primary key).

This is prohibited in 2nd Normal Form and should therefor be placed in it's own table as you have suspected.

I think where you came unstuck is in identifying two sets of candidate keys, when you create the table you must choose one set of candidate keys to make the primary key. The remaining columns become non-prime attributes i.e., if you chose your second candidate key, Subject Code becomes a non-prime attribute dependent on part of the primary key (Subject Name) and should be placed in it's own table.

It is important to teach 1st, 2nd and 3rd normal forms in order as they build on each other. BCNF is also essentially an extension to 3rd normal form so a strong grasp on the lower levels is essential.

Further; an experienced developer will not consider the independent levels of normalisation because many rules become intuitive.

They will also know when to break normalisation rules to solve certain design and optimisation problems. Normalisation should be treated as a guide to good design not a stringent rule, I believe that would also be a good teaching point.