Normalization
The process of producing a simpler and more reliable database structure is called NORMALIZATION.
It’s a technique to reduce or remove redundancy in relation/table.
It is used to create a suitable set of relation for storing data
Normalization work through different stages known as NORMAL FORM. These stages are:
1. 1NF (Normal Form)
2. 2NF
3. 3NF so on
Each normal form has certain requirements and conditions. If the relation satisfy the conditions of
normal form, it is said to be in that normal form.
(redundancy is when same data is stored in multiple places)
There are two types of redundancy
1. ROW LEVEL REDUNDANCY: When two rows are exactly the same, it is called row-level
[Link] IN MIND: Row-level delicacy can removed by setting a primary key
2. COLUMN LEVEL REDUNDANCY: When any column in a relation contains similar data, then it
will be column-level redundancy
Redundancy problem reason
Redundancy problems can occur for the following reasons.
Insertion Anomaly
Deletion Anomaly
Updation Anomaly
Let explain all anomalies through the following table (Student_details)
1. INSERTION ANOMALY: Insertion anomaly occurs when a new
record is inserted in a relation. In this anomaly, the user cannot
insert a fact about an entity until he has an additional fact about
another entity
Example: If a new student detail needs to be inserted while the course and faculty are
not decided. Then, student insertion will not be possible till the course and faculty are decided
for the student.
Syntax:
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);
In this case we can use this syntax as :
INSERT INTO Student_details (Std_ID, STD_Name, Course_ID, Course_Name,
Faculty_ID, Faculty_Name, Faculty_Fee) VALUES (‘5’, ‘Khalid’, ”, ”, ”, ”, ” );.
Output
DELETION ANOMALY: The deletion anomaly occur when the record id
delete from the relation. In this anomaly the deletion of the fact about
an entity automatically deletes the fact of another entity.
Syntax:
DELETE FROM table_name WHERE condition;
For example:
SQL Query: Delete from student_detail where Std_ID = 2.
UPDATION ANOMALY : updation anomaly occurs when a change in one field leads to a
change in many [Link] this anomaly, the updation in the value of specific attribute requires
updation in all records in which that value occurs
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, …….
WHERE condition;
For example
SQL Query: Update Student_detail SET faculty _Fee = ‘15K’
If we want to Change the faculty_fee of Ali from 10K to 15K. It will update the faculty_fee in
many fields, which may not be necessary
Solution of removing anomalies
One of the best solutions to remove the above anomalies is to divide the table into parts, as
given below.
Thus, in the above tables there is no problematic anomaly exists.
Normalization
1- First normal form : A relation is in first normal form if it does not contain any composite or
multivalued attributes OR a relation is in first normal form if every attribute in that relation is
single valued attribute.
A table is in 1 NF if:
• There are only Single Valued Attributes.
• Attribute Domain does not change.
• There is a unique name for every Attribute/Column.
• The order in which data is stored does not matter.
Consider the example given below.
Example 1 : relation STUDENT in table 1 is not in 1NF because of multivalued attribute
STUD_PHONE . its decomposition into 1NF has been shown in table 2.
[Link] [Link] Course
1 A C/C++
2 B JAVA
3 C C/DBMS
atomic value attribute Multi-valued attribute
Now this isn’t in 1NF as it contain multivalued attributes to overcome this there are three way
1
[Link] [Link] Course
1 A C++
1 A C++
2 B JAVA
3 C C
3 C DBMS
2
3
[Link] [Link] [Link] Course
me 1 C
1 A 1 C++
2 B 2 Java
3 C 3 C
3 DBMS
Base table
Primary key = [Link] Child table
Foreign key = [Link]
[Link] [Link] Course- Course-
Composite primary key = course and [Link]
me 1 2
1 A C C++
2 B JAVA JAVA
3 C C DBMS
