Definition Examples and Recommendations
2nd Normal Form (2NF) Definition
Example
2NF only comes into play when the primary key of the table is comprised of two or more fields and each of these fields is not a candidate to be a primary key of the table.
Below is our table from First normal form.
Project_Name (primary key field comb. w/Employee_Name)
Employee_Name
Emp_Hire_Date
Project_Manager
To remove the partial dependencies we move Emp_Hire_Date to the Employee table and move Project_Manager to the Project table. These fields have partial dependencies because they are not dependent on both fields that comprise the primary key.
Now our second normal form table looks like this:
Project_Name
Employee_Name
The two fields, together, create a unique record and are therefore the primary key of the table.
There are a few problems with this table, however. First, if we delete a project we lose employee names unless they are a member of another project. If the employee gets married and changes their last name then every occurrence of the person's name will need to be updated. Similarly, if someone decides to make a slight change to the
project name then many records may have to be updated. See how we prevent multiple updates with our
table design rules.
As noted above the second normal form is a special case that is designed to deal with tables that have composite primary keys. Follow this discussion with
3rd normal form examples.
