Explaining 2NF vs 3NF with an example

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?

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)

StackExchange Database Administrators Q#115990, answer score: 9