database-design Normalization Second Normal Form (2NF)

Example

To normalize the database in the second form, there must not be any partial dependency of any column on primary key.

Let's consider the following example:

id	name	dob	subject
1	Mark	1-1-1981	Physics
2	Jack	2-2-1982	Math
2	Jack	2-2-1982	Biology
3	John	3-3-1983	Math

This table is considered to have a composite primary key (id and subject), but the *name and dob columns only depends on the id, not the subject, so they have partial dependency on the primary key. As a result, we can see the redundancy of information in the table. To normalize the database in the second form, we must split this table into two tables like this:

Students table

id	name	dob
1	Mark	1-1-1981
2	Jack	2-2-1982
3	John	3-3-1983

Subjects table

student_id	subject
1	Physics
2	Math
2	Biology
3	Math

The student_id column of the Subjects table is a foreign-key that references the primary key id of the Students table.