Intro to DBMS

Redundancy Problems

Redundancy is the multiple copies of the same data in the database. It occurs when a database is not normalized. Redundancy problems can removed or reduced through Normalization.

Types of Redundancy

There are two types of redundancy levels, given below

1. Row-level redundancy

When two rows are exactly the same, it is called row-level redundancy. Then, It will never accepted by RDMS.

 Keep in mind: Row-level delicacy can removed by setting 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 for the 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. 

Syntax:

INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2,  …);

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.  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’, ”, ”, ”, ”, ” );.

Output

2. Deletion Anomaly

This anomaly occurs when the deletion of a record results in losing some other information that was stored as part of the record that was deleted from a table.  

Syntax:

DELETE FROM table_name WHERE condition;

For example:

SQL Query: Delete from student_detail where Std_ID = 2.

Execution of the above query leads toward the loss of Course 2 information. So, deletion is also an anomaly. 

3. Updation Anomaly

This anomaly occurs when a change in one field leads to a change in many fields.

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.

So, this problem happened due to an update 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 the above tables there is no problematic anomaly exists.