Second Normal Form (2NF)

Below is an example of a First Normal Form (1NF) database. As you can see,

StudentID	FirstName	LastName	Middle Initial	Street	City	State	ZipCode	Prof	Dept
1	Joseph	Dimagio	P	123 Foo Lane	Columbus	Ohio	43235	Dr. Torre	CS
2	George	Ruth	H	124 Foo Lane	Columbus	Ohio	43235	Dr. Torre	CS
3	Reginald	Jackson	M	125 Foo Lane	Bronx	New York	10001	Dr. Martin	Math

As you can see, the above 1NF database has redundant data for the City, State, Zip Code, Professor and Department. Normally, this would not be a big deal, especially if we only had 3 records, but what if we had thousands? Then I guess you can say, we will need to normalize the database to rid it of redundant data.

Below is a simple 2NF Database. As you can see, the redundancy has been reduced.

StudentID (PK)	FirstName	LastName	Middle Initial	Address	ZipCode (FK)	DepartmentID (FK)	ProfessorID (FK)
1	Joseph	Dimagio	P	123 Foo Lane	43235	400	1
2	George	Ruth	H	124 Foo Lane	43235	400	1
3	Reginald	Jackson	M	125 Foo Lane	10001	500	2

ZipCode (PK)	City	State
43235	Columbus	Ohio
10001	Bronx	New York

ProfessorID (PK)	Professor
1	Dr. Martin
2	Dr. Torre

DepartmentID (PK)	Department
400	CS
500	Math

Updated: Jan. 12, 2005; Joe Gakenheimer