Definition: In order to be in Second Normal Form, a relation must first fulfill the requirements to be in First Normal Form. Additionally, each nonkey attribute in the relation must be functionally dependent upon the primary key.
Example:
The following relation is in First Normal Form, but not Second Normal Form:
Order # | Customer | Contact Person | Total |
1 | Acme Widgets | John Doe | $134.23 |
2 | ABC Corporation | Fred Flintstone | $521.24 |
3 | Acme Widgets | John Doe | $1042.42 |
4 | Acme Widgets | John Doe | $928.53 |
In the table above, the order number serves as the primary key. Notice that the customer and total amount are dependent upon the order number -- this data is specific to each order. However, the contact person is dependent upon the customer. An alternative way to accomplish this would be to create two tables:
Customer | Contact Person |
Acme Widgets | John Doe |
ABC Corporation | Fred Flintstone |
Order # | Customer | Total |
1 | Acme Widgets | $134.23 |
2 | ABC Corporation | $521.24 |
3 | Acme Widgets | $1042.42 |
4 | Acme Widgets | $928.53 |
The creation of two separate tables eliminates the dependency problem experienced in the previous case. In the first table, contact person is dependent upon the primary key -- customer name. The second table only includes the information unique to each order.