Second Normal Form (2NF)

C.J. Date defines 2NF as…

“A table is in 2NF if and only if it is in 1NF and every non-key attribute is irreducibly dependent the primary key.”1

This too can be a bit much to swallow so let’s look at an example of a table that is in second normal form (2NF), and one that is not.

Tables in Second Normal Form (2NF)

Teacher_ID

Teacher_Lastname

Department

1

Jones

Physics

2

Smith

Math

3

Hally

Social Sciences

4

Johnson

Physics

5

Smith

Computer Science

 

Student_ID

Student_Lastname

Student_GPA

22

Frapples

3.25

35

Smith

3.8

65

 Wire

2.55

45

Smith

3.55

34

Smith

2.75


A table not in Second Normal Form (2NF)

In the example below the Student_Lastname and StudentGPA columns are dependent on the StudentID. The TeacherLastname and Department columns are dependent on the TeacherID column.

TeacherID

TeacherLastname

Department

StudentID

Student_Lastname

StudentGPA

1

Jones

Physics

22

Frapples

3.25

2

Smith

Math

35

Smith

3.8

3

Hally

Social Sciences

65

 Wire

2.55

4

Johnson

Physics

45

Smith

3.55

5

Smith

Computer Science

34

Smith

2.75


The above example is a rather obvious example. It also creates an association between a Student entity to a Teacher entity within a single table. We could presume an association that would match a student with a teacher for the purpose of a class the student would take. If we were to extend the notion of this data for this presumption then we could have data that looks as follows.

Teacher_ID

Teacher_Lastname

Department

Student_ID

Student_Lastname

Student_GPA

1

Jones

Physics

22

Frapples

3.25

1

Jones

Physics

35

Smith

3.8

1

Jones

Physics

65

 Wire

2.55


Besides the unnecessary replication of teacher data in the row which would create all sorts of problems for us, we would also need to modify the primary key of the table to guarantee uniqueness of the row.


1 C.J. Date. An Introduction To Database Systems 6th Edition. Addison-Wesley. p 300

 

I like to get feedback but do not have time to combat all the spammers out there. The best means to contact me is by sending me a message through LinkedIn (I think you need to be a member to do so). Once you signup, just request to add me to your network and I will accept. I will do my best to respond to your message.

View Eric Matthews's profile on LinkedIn




Back to sqlinfo.net homepage.

Homepage
By Eric Matthews on sqlinfo.net