Wednesday, June 3, 2015

SQL- Normalization: Third Normal Form (3NF)

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 Second Normal Form. Please visit the Second Normal Form of the Database Normalization at Database Normalization - Second Normal Form.

Database Normalization- Third Normal Form (3NF)
A database table is said to be in 3NF if it is in 2NF and all non- keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.
The process of converting the table into 3NF is as follows:
1.       Remove the transitive dependencies (A type of functional dependency where a field is functionally dependent on the Field that is not the primary key. Hence its value is determined, indirectly by the primary key).
2.      Make separate table for transitive dependent Field.
What is Transitive?
When something is transitive, then a meaning or relationship is the same in the middle as it is across the whole.  If it helps think of the prefix trans as meaning “across.”  When something is transitive, then if something applies from the beginning to the end, it also applies from the middle to the end.
Dependence
An object has a dependence on another object when it relies upon it. In the case of databases, when we say that a column has a dependence on another column, we mean that the value can be derived from the other.  For example, product price is dependent on the product cost.


Apply the Model to 3NF 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.
In the case of Employee Department and Department Name, a Department Master was created.  A foreign key was then added to Employee Master so we can still describe in which Department an Employee is based.
With these changes made the data model, in second normal form, is shown below.

3NF of above 2NF 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
Primary Key= Project Code

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
Dept Id
EmployeeRate
E0001
Ryan Arjun
D001
240
E0002
Tony Towery
D002
230
E0003
Lucy Gray
D003
250
E0004
Will Smith
D001
245
E0005
Chao Milk
D002
225
E0006
Chris Gyal
D003
210
E0007
Bill Gray
D001
190
E0008
Red Bill
D002
210
E0009
Tom Ramsay
D003
200
Primary Key = Emp Id

Department Master
Dept Id
Department Name
D001
Database
D002
Testing
D003
IT
Primary Key = Dept Id

Now, you can see in the above example, how does normalization work on your data table and improve the data accuracy and efficiency and reduces the data redundancy and inconsistent data dependency.
We have distributed a single business data table into four data tables by maintaining the relationship among them with the help of the primary and foreign key relationship.

1 comment:

  1. A type of functional dependency where a field is functionally dependent on the Field that is not the primary key. Hence its value is determined, indirectly by the primary key.
    https://www.idealsvdr.com/

    ReplyDelete