Wednesday, June 3, 2015

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.

No comments:

Post a Comment

Popular Posts