Which three data anomalies are most likely to be the result of data redundancy?
|
This short article discusses update, deletion, and insertion anomalies. Normalization is the process of splitting relations into well-structured relations that allow users to insert, delete, and update tuples without introducing database inconsistencies. Without normalization, many problems can
occur when trying to load an integrated conceptual model into the DBMS. These problems arise from relations that are generated directly from user views are called anomalies. There are three types of anomalies: update, deletion, and insertion anomalies. An update anomaly is a data inconsistency that results from data redundancy and a partial update. For example, each employee in a company has a department associated with them as well as the student group they participate in. Employee_ID Name Department Student_Group 123 J. Longfellow Accounting Beta Alpha Psi 234 B. Rech Marketing Marketing Club 234 B. Rech Marketing Management Club 456 A. Bruchs CIS Technology Org. 456 A. Bruchs CIS Beta Alpha Psi If A. Bruchs' department is an error it must be updated at least 2 times or there will be inconsistent data in the database. If the user performing the update does not realize the data is stored redundantly the update will not be done properly. A deletion anomaly is the unintended loss of data due to deletion of other data. For example, if the student group Beta Alpha Psi disbanded and was deleted from the table above, J. Longfellow and the Accounting
department would cease to exist. This results in database inconsistencies and is an example of how combining information that does not really belong together into one table can cause problems. An insertion anomaly is the inability to add data to the database due to the absence of other data. For example, assume Student_Group is defined so that null values are not allowed. If a new employee is hired but not immediately assigned to a Student_Group then this employee could not be entered into
the database. This results in database inconsistencies due to omission. Update, deletion, and insertion anomalies are very undesirable in any database. Anomalies are avoided by the process of normalization. Source: https://databasemanagement.fandom.com/wiki/Category:Data_Anomalies Last modified: Thursday, December 17, 2020, 4:47 PM What are the three data anomalies that are likely to occur as a result of data redundancy? Can data redundancy be completely eliminated in database approach? Why or why not? Ans: The three sorts of anomalies that can take place in the database due to redundancy are insertion, deletion and modification or updation anomalies. Refer a relation emp_dept along with attributes: E#, Ename, Address, D#, Dname, Dmgr# with the primary key as E#. Insertion anomaly: Let us suppose that a new department has been started by the organization but basically there is no employee appointed for that department, after that the tuple for this department cannot be inserted into this table as the E# will have NULL, which is not permitted as E# is primary key. This type of a problem in the relation in which some tuple cannot be inserted is termed as insertion anomaly. Deletion anomaly: Now refer there is just only one employee in some department and that employee leaves the firm, after that the tuple of that employee has to be deleted from the table, but additionally to that the information about the department as well will get deleted. This type of a problem in the relation where deletion of some tuples can lead to loss of some other data not intended to be eliminated is termed as deletion anomaly. Modification /update anomaly: Assume the manager of a department has changed, this needs that the Dmgr# in all the tuples corresponding to that department should be changed to reflect the new status. If we fail to update all the tuples of the given department, then two dissimilar records of employee working in similar department might depict different Dmgr# leading to contradiction in the database. This is termed as modification/update anomaly. The data redundancy: it Cannot be totally removed from the database, although there should be controlled redundancy, for instance, consider a relation student_report(S#, Sname, Course#, SubjectName, marks) to store the marks of a student for a course comprising some optional subjects, but all the students should not chose similar optional papers. Now here the student name appears in every tuple, that is redundant and we can have two tables as students(S#, Sname, CourseName) and Report(S#, SubjectName, Marks). Though, if we want to print the mark-sheet for every student by using these tables then a join operation that is a costly operation, in terms of resources needed to perform, has to be performed to get the name of the student. Thus to save on the resource utilization, we might opt to store a single relation, students_report just only. Related Discussions:- What are the three data anomaliesWhat 3 data anomalies are likely to be the result of a data redundancy and how can they be eliminated?There are three types of anomalies: update, deletion, and insertion anomalies. An update anomaly is a data inconsistency that results from data redundancy and a partial update. For example, each employee in a company has a department associated with them as well as the student group they participate in.
What are the three types of anomalies in database?Table of Contents. Update Anomaly.. Insert Anomaly.. Delete Anomaly.. What are the 3 anomalies that are possible if tables are not normalized?There are three types of anomalies that occur when the database is not normalized. These are – Insertion, update and deletion anomaly.
What types of problems does data redundancy cause?Data redundancy may increase the size and complexity of a database — making it more of a challenge to maintain. A larger database can also lead to longer load times and a great deal of headaches and frustrations for employees as they'll need to spend more time completing daily tasks.
|
