on Leave a Comment

Data Redundancy in DBMS with example

Data redundancy means redundant copies of data within database. Data redundancy leads to data inconsistency which will cause overall database performance. To avoid redundancy problem in database design Normalization is used. 

Opensource DBMS mysql provides some ways to avoid redundancy such as joins.

Example of data redundancy in DBMS

Data redundancy in DBMS

In the above table, Dep_id and Dep_manager_name is copied into multiple tuples . For example, Dep_id 22 is placed two times in table. If we need to update Dep_manager_name for Dep_id 22, then we have to update all records having Dep_id 22. If Dep_id 22 is placed with thousand records, then we need to update thousand record for small change and this results to increase in computational time.

Disadvantages of data redundancy in DBMS

Insertion anomaly

If there is one more Dep_id, let say 21 and currentlty no employee is working in this department. Then there will be no entry related to this Dep_id in table. Such a situation is known as insertion anomaly.

Deletion anomaly

In the above table, if record Emp_code 3 is deleted, then there will no record related to Dep_id 17. This will cause losing information about Dep_id 17.   

Updation anomaly

If we need to change Dep_manager name, then we have to update to record having same Dep_id. This will increase computation time.


Advantages of data redundancy in RDBMS

But sometimes, data redundancy is solution when you have unlimited storage and your basic need is faster access of data. When we store data in multiple tables for example student information in STUDENT table and student courses in another table STUDENT_COURSE and we need to access complete student data in one query, then joins is solution. But when data is large enough, joins decrease performance. There are many other solutions for faster join operation like indexing and other database constraint. But if data is large and joins are not working well, then data redundancy is also a solution.  



0 comments:

Post a Comment