2.34. 5.4.0.3.1 Normalization
Database normalization is the process of efficiently
organizing data in the DB. It can also be described as the
accuraterepresentation of data, relationships and constraints.Normalization is
a systematic way of ensuring that a database structure is suitable for
general-purpose querying and free of certain undesirable
characteristics--insertion, update, and deletion anomalies--that could lead to
a loss of integrity. The main goals of normalization are to:
|
i) Eliminate redundant data in a DB.
|
|
ii) Ensure data dependencies make sense.
|
Figure 5.19: current form used to manage network
|
2.35. 5.4.0.3.3 Process of normalization
These are the steps taken from the unnormalized form (UNF) to
the normalized form. UNF is the table that contains one or more repeating
forms. The steps are: Table problem
|
Employee_id
|
problem description
|
Full Name
|
Department
|
Building
|
Date of problem
|
Type of problem
|
Status
|
|
|
|
Employee ID
|
Department
|
Building
|
Date of solution
|
Solution
|
Solved by
|
Type of problem
|
status
|
1. First normal form (1NF)
The first normal form (1NF) involves the removal of repeating
groups. The question remains, "What is a repeating group?" example of repeating
groups: Employee ID(JKC-FIN-0100) ,Full-name(Adam Juma) status(Unsolved),
Department(Finance, Academic), Building(LAB, Academic), Type of problem(No
connection, Connectivity), solution(solved),solved by, and type of problem
For a given problem , one or more solution and one or more solution can exist.
For each repeating group you encounter, the repeating group is moved to a
separate table. In this case, you end up with two new tables that store the
contact and category data. The following outlines the new structure and
entities:
1) Problem : Problem ID(primary
key) ,Type of problem , problem description ,Date of problem and
Status
2) Employee: Employee ID(primary
key), Full Name, Department, Building
3) Solution: Solution ID (primary
key), Date of solution, solution, Solved by,
Status
4) Report: Report ID(primary
key) ,Solution ID , Employee ID, Date of report
The problem table is a parent to the problem id and Solution
tables. The two relationships are one to many. In other words, each problem can
have one or more solution and can be associated with one or more categories.
2. Second normal form(2NF)
Second normal form (2NF) is the second step in normalizing a
database. 2NF builds on the first normal form (1NF). A 1NF table is in 2NF form
if and only if all of its non-prime attributes are functionally dependent on
the whole of every candidate key.
2NF is achieved by removing partial dependencies: the
functionally dependent attributes are removed from the relation by placing them
in a new relation along with a copy of their determinant.
Identification of attribute
Problem (Problem ID, Date of problem, problem
description, Date of problem and Status)
Employee (Employee ID, Full Name, Department and
Building)
Solution (Solution ID, Date of solution, solution,
Solved by and Status)
Report (Report ID, Solution ID, Employee ID, and Date
of report)
Functional dependency:
Employee ID Full Name, Department and Building
Solution ID Solution, Date of solution, status and
solved by
Report ID Employee ID, Solution ID, Problem ID,
Department and Building
Problem ID Type of problem, problem description, Date
of problem and Status
Figure5.20: full dependency
Partial dependency
Solution ID, Employee ID Problem ID, Type of problem,
problem description, Status, Solved by
Problem ID Type of problem, problem description,
Date of problem
Employee ID, problem ID building, department, type of
problem
3. Third normal form(3NF)
Third normal form (3NF) is the third step in normalizing a
database and it builds on the first and second normal forms, 1NF and 2NF. 3NF
states that all column reference in referenced data that are not dependent on
the primary key should be removed. Another way of putting this is that only
foreign key columns should be used to reference another table, and no other
columns from the parent table should exist
Problem table: Problem ID (primary
key), Type of problem, problem description, Date of problem and
Status
Employee table: Employee ID
(primary key), Full Name, Department, Building
Solution table: Solution ID
(primary key), Date of solution, solution, Solved by,
Status
Report table: Report ID (primary
key),Solution ID, Employee
ID, Date of report
| 
