Can tables lose prime attributes during 2NF normalization?

Question

I want to bring this table into a 2NF design:

Student-ID	Teacher-No	Teacher	StudentSkill
S1	1	Ott	Python
S1	2	Biegler	Python
S1	1	Ott	SQL
S1	2	Biegler	SQL
S2	1	Hansen	Python
S2	2	Muller	Python
S3	1	Smith	HTML
S3	1	Smith	Java

Student-ID uniquely identifies a student.
Teacher is the name of a teacher, depedent on the combination of Student-ID and Teacher-No.
StudentSkill is a skill a student possesses, completely unrelated to the teacher.

The table says:

Student S1 has two teachers (Ott, Biegler) and two unrelated skills (Python, SQL).
Student S2 has two teachers (Hansen, Muller) and one unrelated skill (Python).
Student S3 has one teacher (Smith) and two unrelated skills (HTML, Java).

The primary key is the combination of (Student-ID, Teacher-No, StudentSkill).

There is a partial dependency. Teacher is dependent only on Student-ID and Teacher-No. So I create a new table for the teacher names:

Student-ID	Teacher-No	Teacher
S1	1	Ott
S1	2	Biegler
S2	1	Hansen
S2	2	Muller
S3	1	Smith

and the original table loses the Teacher column:

Student-ID	Teacher-No	StudentSkill
S1	1	Python
S1	2	Python
S1	1	SQL
S1	2	SQL
S2	1	Python
S2	2	Python
S3	1	HTML
S3	1	Java

What about the column Teacher-No in the (Student-ID, Teacher-No, StudentSkill) table?

It does not do anything useful (since skills are unrelated to teachers), but it's part of the primary key, so I cannot touch it during creation of the 2NF.

Can tables lose prime attributes during 2NF normalization?

Answer 1

The decomposition you proposed satisfies 2NF.

The (Student-ID, Teacher-No, Teacher) table (key attributes bold) is in 2NF because Teacher depends on both Student-ID and Teacher-No.

The (Student-ID, Teacher-No, StudentSkill) table is in 2NF because it has no non-key attributes.

However, this is not the only way to achieve 2NF in this scenario, and a better design would be to decompose the original table into the following tables.

Student-ID	Teacher-No	Teacher
S1	1	Ott
S1	2	Biegler
S2	1	Hansen
S2	2	Muller
S3	1	Smith

Student-ID	StudentSkill
S1	Python
S1	SQL
S2	Python
S3	HTML
S3	Java

This design removes the redundant Teacher-No column from the table with the skills and also satisfies 2NF.

So to answer your original question whether during 2NF normalization tables can lose prime attributes: Yes, but they don't have to. In this concrete scenario, the design that removes the prime-attribute Teacher-No from the skill table is the better one.

Interestingly, even going to 3NF would not force the removal of the redundancies in your proposed decomposition, because 3NF it is not concerned with dependencies between attributes that are part of the primary key. BCNF however would catch it.