DBMS Notes

Redundancy Problems

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. 

Syntax:

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

Output

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.  

Syntax:

DELETE FROM table_name WHERE condition;

For example:

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.

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 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.

Help Other’s By Sharing…

Contact Us

Burewala, Vehari, Punjab, Pakistan

cstaleem1@gmail.com

Website: CStaleem.com

Pin It on Pinterest