Database Normalizations is a
technique of organizing the data in the database. Normalization is a systematic
approach of decomposing tables to eliminate data redundancy and undesirable
characteristics like insertion, update and deletion anomalies. It is a
multi-step process that puts data into tabular form by removing duplicated data
from the relation tables.
Normalization is used for mainly
two purpose,
(i) Eliminating redundant (useless)
data.
(ii) Ensuring data dependencies
make sense i.e. data is logically stored.
Problem without Normalization:
Without
Normalization, it becomes difficult to handle and update the database, without
facing data loss. Insertion, Updating and Deletion Anomalies are very frequent
if Database is not normalized.
|
S_id |
S_Name |
S_Address |
Subject_opted |
|
401 |
Adam |
Noida |
Bio |
|
402 |
Alex |
Panipat |
Maths |
|
403 |
Stuart |
Jammu |
Maths |
|
404 |
Adam |
Noida |
Physics |
Table 1: Students Database
·
Updating Anomaly: To update address of a student who occurs twice or more than twice in a
table, we will have to update Address column in all the rows,
else data will become inconsistent.
·
Insertion Anomaly: Suppose for a new admission, we have a
Student id (S_id), name and address of a student but if student has not opted for any subjects yet then we have to
insert NULL there, leading to Insertion Anomaly.
·
Deletion Anomaly: If (S_id) 401 has only one subject and
temporarily he drops it, when we delete
that row, entire student record will be deleted along with it.
Theory
of Data Normalization in Sql is still
being developed further. For example, there are discussions even on 6th Normal
Form. But in most practical applications normalization achieves its best in 3rd
Normal Form. The evolution of Normalization theories is illustrated below-
