.
A step by step process to produce more
efficient and accurate database design.
Normalization is the process of dividing
redundant tables into smaller and less
redundant tables.
Performed after the logical database design.
The Purpose of normalization is to produce
an anomaly free design.
A strongly recommended step.
Normalized design makes the maintenance
of database easier.
Normalization is applied on each table of the
database design.
There are different forms or levels of
normalization.
Called first, second, third normal form and so
on.
Each form has got certain conditions.
If a table fulfils the conditions for a normal
form then the table is in that normal form.
Process is applied on each table.
The minimum form in which all tables are in is
called the normal form of the entire
database.
Objective is to place the DB in highest form
of normalization
A relation is in first normal form if every
attribute (column) in every tuple (row)
contains an atomic value.
There is no multi-valued (repeating group) in
the relation.
Multiple values create problems in
performing different operations, like select or
join.
In this table there is no unique value for every tuple.
For employeeID we have some multiple values in skills
Column.
Now this table is in first normal form and for every tuple
there is a unique value.
What is the problem with this table?
A relation is in second normal form if and only
if it is in first normal form and all the non-key
attributes are fully functionally dependent on
the primary key.
Clearly, if a relation is in 1NF and the key
consists of a single attribute, the relation is
automatically in 2NF.
The only time we have to be concerned about
2NF is when the key is composite.
A relation that is not in 2NF exhibits the
update, insert and delete anomalies.
We know that the primary key must uniquely
identify a single row in a table.
So primary key is in the previous table is
{employeeID, skills}.
Employee (employeeID, name, job,
departmentID , skills)
{employeeID, skills}name, job, departmentID
but we also have
employeeID name, job, departmentID
we can determine the name, job, and
departmentID from the employeeID alone. This
means that these attributes are partially
functionally dependent on the primary key.
Hence, this table is not in second normal form.
We need to decompose the table into tables
in which all the non-key attributes are fully
functionally dependent on the key.
we can achieve this by breaking the table into
two tables.
Employee (employeeID, name, job,departmentID)
employeeSkills (employeeID , skill)
This schema is in first normal form because the values are all
atomic.
It is also in second normal form because each non-key
attribute is now functionally dependent on all parts of the
keys.
A table is in third normal form if it is in 2NF
and there is no transitive dependency, that is,
no non-key attribute is dependent on another
non-key attribute.
The table must already be in second normal
form.
Okay, so what's a transitive dependency?
This table contains the following functional dependencies:
• employeeID name, job, departmentID, departmentName
• departmentID departmentName
If we see
we can determine departmentID from EmpID
employeeID departmentID
Also I can determine department name from
departmentID
departmentID departmentName
So we have a transitive dependency like:
EmpID departmentID departmentName
Means deptName is dependent on deptID
and both of these are dependent on EmpID.
To get to third normal form, we need to
remove this transitive dependency.
As with the previous normal forms, to convert
to third normal form we decompose this
table into multiple tables.
employee(employeeID, name,
job,departmentID )
department(departmentID,
departmentName)
Student_id St_name Address Prog_name Prog_crdts Book_id
1020 Ali Peshawar city BCS 48 10001
1022 Tahir Peshawar cant MCS 44 10022
1023 Shumaila Swat BIT 36 10031,
10043
1030 Zaheer Noshehra BCS 48 20011
Convert this table to first, second and third normal forms.