Redundancy is the multiple copies of same data in the database. It occurs when a database is not normalized. Redundancy problem can removed or reduced through Normalization.
Types of Redundancy
There are two types of redundancy level, given below
1. Row level redundancy
When two rows are the exactly same is called row level redundancy. Then It will never accepted by RDMS.
Keep in mind: Row level delicacy can removed by set a primary key in the table.
2. Column Level Redundancy
When any column in a relation contains similar data then it will be column level redundancy. So, It is problematic in some cases but not in all cases.
Redundancy Problem Reasons
Redundancy Problems can occur due to following reasons.
- Insertion Anomaly
- Deletion Anomaly
- Updation Anomaly
Anomaly: Anomaly is a problem because it occurs in some cases.
Let explain all anomalies through the following table (Student_details)
1. Insertion Anomaly
This problem occurs when the new insertion of a data record is not possible without adding some additional unrelated data to the record.
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);
Example: If a new student detail need to be inserted while the course and faculty is not still decided. Then student insertion will not be possible till the course and faculty is decided for student. As in the following SQL query
INSERT INTO Student_details (Std_ID, STD_Name, Course_ID, Course_Name, Faculty_ID, Faculty_Name, Faculty_Fee) VALUES (‘5’, ‘Khalid’, ”, ”, ”, ”, ” );.
2. Deletion Anomaly
This anomaly occurs, when deletion of record results in losing some other information’s that was stored as part of the record that was deleted from a table.
DELETE FROM table_name WHERE condition;
SQL Query: Delete from student_detail where Std_ID = 2.
Execution of above query leads toward the loss of Course 2 information. So, deletion is also an anomaly.
3. Updation Anomaly
This anomaly occurs when changing in one field leads toward the changing in many fields.
UPDATE table_name SET column1 = value1, column2 = value2, ……. WHERE condition;
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 be not necessary.
So, this problem happen due to Updation Anomaly.
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 above tables there is no problematic anomaly exist.