NORMALIZATION
z
Wesonga
Bob
z
Normalization
Normalization is a systematic approach of decomposing tables
to eliminate data redundancy(repetition) and undesirable
characteristics like Insertion, Update and Deletion Anomalies
Database Normalization is a technique of organizing the data in
the database
z
Normalization
It is a multi step process that puts data into tabular form,
removing duplicated data from the relation tables
Normalization is mainly used for two purposes i.e.;
1. eliminating redundant data and ensuring that data
2. dependencies make sense (data is logically stored)
z
Anomalies and unnormalized Data
Unnormalized form ( also known as an unnormalized relation or
non first normal form, is a simple database data model
(organization of data in a database) lacking the efficiency of
database normalization
Unnormalized data is associated with the following anomalies;
Insert, deletion, update anomalies
Insertion, Update and Deletion Anomalies are very frequent if a
database is not normalized.
z
Understanding Anomalies
Consider the table STUDENT below
z
Understanding Anomalies
Insertion Anomaly
If we have to insert data of 100 students of same branch, then the
branch information will be repeated for all those 100 students.
These scenarios are nothing but Insertion anomalies
z
Understanding Anomalies
Update Anomaly
What if Mr X leaves the college? or is no longer the HOD of
computer science department? In that case all the student records
will have to be updated, and if by mistake we miss any record, it will
lead to data inconsistency. This is Update anomaly
z
Understanding Anomalies
Deletion Anomaly
In our Student table, two different types of information are kept
together, Student information and Branch information.
Hence, at the end of the academic year, if student records are
deleted, we will also lose the branch information. This is a Deletion
anomaly
z
Normalization Rule
Normalization rules are divided into the following normal forms:
1. First Normal Form
2. Second Normal Form
3. Third Normal Form
z
First Normal Form (1NF)
For a table to be in the First Normal Form, it should follow the
following 4 rules
1. It should only have single(atomic) valued attributes/columns
2. Values stored in a column should be of the same domain
3. All the columns in a table should have unique names
4. And the order in which data is stored, does not matter
z
Lets Workout this
Consider
Consider table below Before Normalization
z
Lets Workout this
Consider
Consider the table above After Normalization
z
Second Normal Form (2NF)
For a table to be in the Second Normal Form;
1. It should be in the First Normal form
2. And, it should not have Partial Dependency
z
Data dependency
1. Functional dependency is a constraint between two sets of
attributes in a relation from a database. In other words,
functional dependency is a constraint between attributes in a
relation
2. Partial dependency, Where an attribute in a table depends on
only a part of the primary key and not on the whole key
z
Third Normal Form (3NF)
A table is said to be in the Third Normal Form when;
1. It is in the Second Normal form
2. And, it doesn't have Transitive Dependency
Transitive dependency
When a non prime attribute depends on other non prime attributes
rather than depending upon the prime attributes or primary key
z
Third Normal Form (3NF)
Advantage of removing Transitive Dependency
1. Amount of data duplication is reduced
2. Data integrity achieved
z
Normalization – Exercises
1. The table shown above is susceptible to update anomalies. Provide
examples of insertion, deletion, and modification anomalies.
2. Describe and illustrate the process of normalizing the table shown in
the table above to 3NF. State any assumptions you make about the
data shown in this table.
z
Normalization – Exercises
1. The table shown above is susceptible to update anomalies. Provide
examples of insertion, deletion, and modification anomalies.
2. Describe and illustrate the process of normalizing the table shown in
the table above to 3NF. State any assumptions you make about the
data shown in this table.
