Search code examples
databasedatabase-normalization

Why is the following table in the 2nd normal form?

Here`s my table in the 1. Normal Form with the Primary Keys Student_ID and Course_Nr:

enter image description here

Here's my table in the 2. Normal Form: enter image description here

For a table to be in the 2nd normal form, it should NOT have partial dependency.

But the column Teacher in the student table is dependat on the column Co.Nr (Course Nr). Shouldn't that be in a seperate table (Course Nr# | Teacher) as well?

Solution

  • The definition of Second Normal Form requires that in the relation schema no non-prime attribute should be dependent on a proper subset of any candidate key.

    In the second relation schema the only candidate key is StudentId, and since the attribute Teacher depends on CourseNumber, which is not part of any candidate key, the definition is respected and the relation schema is in Second Normal Form.

    Obviously there is a redundancy since the relation is not in Third Normal Form (3NF), and should be normalized.

    In fact, the Second Normal Form is not considered important any more, and it is described just for historical reason, since all the normalization techniques aim to bring the relation schemas at least in the Third Normal Form or in the Boyce-Codd Normal Forms. This is so true that there are no formal algorithms to bring a relation schema in Second Normal Form, while there are algorithms for Third and Boyce-Codd Normal Forms.