2NF - Second Normal Form.

 

The example,shown in the previous post for 1 Normal Form, would meet the standard for 2 Normal Form. It has a single attribute primary key and no partial dependencies.

A table in Second Normal form can have a Primary Key(PK) composed of 2 or more attributes, or a candidate.

The table must be in 1NF and contain no partial dependencies. For example:

User_ID is the primary key and is unique for every row. It can be used to query for any dataset/ row even with Users that share similar names.

To normalize from 1NF to 2NF partial dependencies must be removed.

The below table contains a partial dependency but still could be considered to be in 1NF:

PK / User_ID	First_Name	Last_Name	Profession
1	john	smith	baker
2	george	patton	baker
3	billy	wild	carpenter
4	kathy	smith	doctor

Each record can be queried using the User_ID.

Two records share the same profession and same last name. Indeed names can be shared as well as professions. This means that not all the fields are unique to a record.

Creating a table for Profession in this example can help avoid additional shared fields:

Profession_ID	Profession
1	baker
2	carpenter
3	doctor

This helps eliminates storing redundant data and helps avoid partial dependencies where an attribute/ field depends on just part of the primary key.

This table is in 2NF and references the PK from the Profession table.

PK / User_ID	First_Name	Last_Name	Profession
1	john	smith	1
2	george	patton	1
3	billy	wild	2
4	kathy	smith	3

The first and last name relate to the Primary key and these fields are dependent on each other.

Breaking down the tables in a database to remove partial dependencies makes data more manageable while avoiding data duplication and redundancy.

This means an attribute that is dependent on 1 but not both PK dependencies should be part of a second table that includes the single PK attribute for the original table. This will complete the requirement for 2NF.