SQL - Normalization: Second Normal Form (2NF)

Database normalization is a technical term to improve the data accuracy and efficiency and reduces the data redundancy and inconsistent data dependency. This post is based on the First Normal Form. Please visit the First Normal Form of the Database Normalization at Database Normalization

Database Normalization- Second Normal Form (2NF)
A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent (means the value of field is determined by the value of another field(s)) on the primary key. The primary key provides a means to uniquely identify each row in a table.

In 2NF we remove the partial dependencies of any non-key field. The process of converting the database table into 2NF is as follows:

1.    Remove the partial dependencies (A type of functional dependency where a field is only functionally dependent on the part of primary key) of any non-key field.

2.    If the field B depends on the field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B in to new database table where B will be primary key and also marked as foreign key in parent table.

Apply the Model to 2NF Standards

Since the columns identified in red aren’t completely dependent on the table’s primary key, it stands to reason they belong elsewhere.  In both cases, we need to move these columns into the new tables.

The changes to make Reservation staff a second normal form table are a little trickier.  Rather than move the offending columns Project Name, Project Manager, and Project Budget to new table, recognize that the issue is Emp ID!  The three columns don’t depend on this part of the key.  Really this table is trying to serve two purposes:

1. To indicate which reservations are called upon by each employee?
2. To identify reservations and their budgets.

For the moment remove Emp ID from the table.  Now the table’s purpose is clear, it is to identify and describe each reservation.

Now let’s create a table named Reservation Staff to describe which reservation an Employee calls upon.  This table has two columns Project Code and Emp ID.  Together, they form a primary key.  Separately, they are foreign keys to the Reservation Master and Employee Master.

With these changes made the data model, in second normal form, is shown below.

2NF of above 1NF tables is as follows:

Reservation Master
Project Code	Project Name	Project Manager	Project Budget
PC001	Reservation	Mike Towery	125,000
PC002	Attendance	Chris Gray	155,000
PC003	Human Resources	Kimmy Wang	225,000

Reservation Staff
Project Code	Emp Id
PC001	E0001
PC002	E0002
PC003	E0003
PC001	E0004
PC002	E0005
PC003	E0006
PC001	E0007
PC002	E0008
PC003	E0009
Composite Key  ( Unique Key) = Project Code + Emp Id

Employee Master
Emp Id	Employee Name	EmployeeDept	EmployeeRate
E0001	Ryan Arjun	Database	240
E0002	Tony Towery	Testing	230
E0003	Lucy Gray	IT	250
E0004	Will Smith	Database	245
E0005	Chao Milk	Testing	225
E0006	Chris Gyal	IT	210
E0007	Bill Gray	Database	190
E0008	Red Bill	Testing	210
E0009	Tom Ramsay	IT	200

After implement the 2NF normalization in the database, we have the clear and meaningful data tables which are capable to improve the data accuracy and efficiency and reduces the data redundancy and inconsistent data dependency.