Database Assignment 1
Discuss insertion, deletion, and modification anomalies. Why are they considered bad? Illustrate with examples.
An insertion anomaly is said to happen when the data cannot be added to the database because of its dependency on other data which is not present. One example is in an employee table we have a student group which is defined as not null. When a new employee record is added and not assigned to any group then the employee data cannot be added to the database which will lead to inconsistencies in the database (Saylor, 2021). A deletion anomaly is said to happen when there is a loss of data because of some other deletion. This type of deletion is unintentional and can cause inconsistencies in the database. One of the examples is if we have an employee table where each employee is assigned to a specific group. If for some reason one of the groups is deleted from the group’s table then the person related to that group alone in the employee table becomes an orphan and will relate to a record that does not exist. This is called a deletion anomaly (Saylor, 2021). An update anomaly is said to happen when there is redundant data added to the database and a partial update. This leads to inconsistency and causes anomalies. One of the examples of an update anomaly is in an employee table if an employee got promoted and his role should be changed. Instead of updating the existing record if the person adds a new record it causes redundancy and leads to inconsistencies (Saylor, 2021). Anomalies are bad because they create inconsistencies in the database. These anomalies can be prevented using normalization
Saylor. (2021). CS403: Data anomalies. Saylor Academy. Retrieved March 17, 2022, from https://learn.saylor.org/mod/page/view.php?id=23144 forceview=1#:~:text=An%20insertion%20anomaly%20is%20the,be%20entered%20into%20the%20database
Discussion Post 2
Insertion, Deletion and Modification Anomalies
Database Assignment Insertion, deletion and modification anomalies are different classifications of update anomalies. Update anomalies are encountered when problems arise from storing natural joins of base relations. Insertion anomalies arise when we are unable to add new data due to the absence of certain attributes of that data. For example, a new employee cannot be added to the database if they have not been assigned to a department yet (assuming their department name or number cannot be NULL). This would violate the integrity of the EMPLOYEE database. Similarly, a new DEPARTMENT database may not be possible if one or more fields in the database tables are classified as NOT NULL fields. Insertion anomalies can result in inconsistent data in database systems. Deletion anomalies are the opposite of insertion anomalies, in that you cannot delete a specific attribute in a record without first deleting the entire record itself. For example, if we want to remove the last record in a database table, we will lose all the integrity of that table itself. Hence, any relations that might exist with any of the fields in that table with other tables may be lost and violate the integrity of the database (Elmasri & Navathe, 2016). Modification anomalies are a third type of database anomaly; these occur when change to the value of one attribute in record requires a change to all the values of that attribute related to a set of records. For example, if a TEACHER ID from the TEACHER table is associated to all records in a STUDENT table, and if that ID changes because a new teacher has been assigned, we need to ensure all the records of the STUDENT table are modified. If not, we might have a student record associated to a non-existent TEACHER ID, which might make the data in that table inconsistent (Elmasri & Navathe, 2016).
Elmasri, R. & Navathe, S. B. (2016). Basics of Functional Dependencies and Normalization for Relational Databases. In Fundamentals of Database Systems (pp. 459-502, 7th edition). Pearson Company.
Database Assignment 3
Database anomalies are simply inconsistent or missing data due to limitations or errors in the database. Databases are created to collect data and deliver or sort data to end-users in a specific way. Assume your database is limited or insufficient. Entering or deleting information, whether updates or new entries, can cause problems. Insertion anomalies are problems that occur when data is first entered into a database (Batra & Sachdeva, 2018). You need to put the correct data into the table to make sure it’s compatible with the values in the other rows. The two most common pasting errors are missing or incorrectly shaped elements. Most programmers expect this to happen and include an error code that explains exactly what went wrong.
Deletion anomalies are when data deletion is difficult when an error blocks the deletion attempt, or the data is automatically deleted. When you delete a table entry representing the last piece of data, the database loses information about that element. These are the ones that are least likely to be found or prevent them from continuing (Evina et al., 2018). Many deletion errors go unnoticed for long periods of time, so they can be the most expensive to fix. Data inconsistencies due to data duplication or incomplete updates are called correction anomalies or update anomalies. Update anomalies are problems caused by duplicate data in database tables. The database will be inconsistent unless all related records have changed. An update anomaly is defined as any database insert, delete, or change that puts the database in an inconsistent state. Database Assignment
Database anomalies are inevitable. We will all encounter them at some point in our lives. The importance of checking backups, offshore storage, and data consistency become clear when you consider what might be lost. Anomalies are considered bad because they produce erroneous results when queries are executed and do not display accurate data (Batra & Sachdeva, 2018). The delete anomaly deletes data without notifying the user, resulting in incorrect results when running the query. This can be solved by regularization, so you will get the correct result. Database Assignment
Batra, S., & Sachdeva, S. (2018). Anomaly free search using multi-table entity attribute value data model. International Journal of Computational Science and Engineering, 16(4), 363-377.
Evina, P. A., Ayachi, F. L., Jaidi, F., & Bouhoula, A. (2018, March). Anomalies Correlation for Risk-Aware Access Control Enhancement. In ENASE (pp. 299-304).
For Database Assignment Help please click here