Anomalies in Database Manipulation

 

Normalization in database design is a process used to organize data to reduce redundancy and improve data integrity.

It involves dividing a database into multiple tables and define relationship between them.

There are several types of anomalies that can arise when a database is not normalized.

The three main types of normalization anomalies.

1.      Insertion anomaly

An insertion anomaly normally occurs when you can’t insert data into the database without the presence of other data.

 

Students

StudId	StuName	Course	Instructor
1	Jack	Math	Prof. Raghu
2	Jim	Science	Prof. Radha
3	Harry	Computer Science	Prof. Venkat
4	Henry	Digital Marketing	Prof. Jasmine
5	Rose	Finance	Ms. Smith

 

The college/Institute decides to start a new course – “Java Full Stack” – there are no students enrolled in this new course

We can’t insert the new course details into the above table because there are no students enrolled and without PK value we can’t insert data into students table. We need to normalize the table.

So, we divide the above table into two different connected tables.

 

Courses

CourseId	Course	Instructor
C100	Math	Prof. Raghu
C101	Science	Prof. Radha
C102	Computer Science	Prof. Venkat
C103	Digital Marketing	Prof. Jasmine
C104	Finance	Ms. Smith
C105	Java Full Stack	Ms. Rani

 

Students

StudId	StuName	CourseId
1	Jack	C100
2	Jim	C101
3	Harry	C102
4	Henry	C103
5	Rose	C104

 

CourseId in Students table is the FK referring to PK of Courses table.

 

2.      Update Anomaly

An update anomaly occurs when changes to data in one place requires changes to be made in multiple places

Students

StudId	StuName	Course	Instructor
1	Jack	Math	Prof. Raghu
2	Jim	Science	Prof. Radha
3	Henry	Computer Science	Prof. Venkat
4	Henry	Digital Marketing	Prof. Jasmine
5	Jack	Finance	Ms. Smith

 

Let’s say the Course of Jack changes to something else. Since there are multiple entries in the students table it causes anomalies if we incorrectly or incompletely update. Normalize the table to solve the anomaly.

3.      Delete anomaly

A delete anomaly occurs when deleting data inadequately deletes/removing other important data.

 

Students

StudId	StuName	Course	Instructor
1	Jack	Math	Prof. Raghu
2	Jim	Science	Prof. Radha
3	Henry	Computer Science	Prof. Venkat
4	Henry	Digital Marketing	Prof. Jasmine
5	Jack	Finance	Ms. Smith

 

Let’s say the student named Jim is leaving the college due to some reason. If we delete the details of Student Jim we lose the data related to the Science course and its instructor.

To solve this break, it in to two tables Courses and Students.

 

 

Normalization Summary

Type of Normalization	Rules
First Normal Form – 1NF	1.      All columns contain atomic values (indivisible/single values) – no multi valued columns 2.      Each column contains values of same type 3.      Each column must have a unique name 4.      The order in which data is stored doesn’t matter.
Second Normal Form – 2NF	1.      The table must already be in 1NF i.e. there must be no non atomic values 2.      All non-key attributes must be fully dependent on primary key – there should be no partial dependency
Third Normal Form – 3NF	1.      The table must be in 2NF already 2.      No transitive dependency exists. Non key attributes must depend only on the primary key. The non key attributes must not depend on non-key columns.
BCNF – Boyce Codd Normal Form – 3.5NF	1.     The first condition is the table should already be in 3NF 2.      It aims to minimise redundancy further by ensuring that non key attributes depend only on the primary key and other non-key attributes
Fourth Normal Form – 4NF	1.      It is in BCNF already 2.      It contains no multivalued dependencies, meaning that one attribute in a table can determine multiple values of another attribute independently of other attributes
Fifth Normal Form – 5NF	1.      It should already be in 4NF 2.      Every join dependency in the table is a consequence of candidate keys.