DATA NORMALIZATION METHOD - Database Design and Development: An Essential Guide for IT Professionals - page 302

Update Anomaly
If a relational two-dimensional table does not conform to relational rules, you find
that problems arise when you try to do updates to data in a database based on such
a table. Our data model at this point consists of the randomly created PROJECT-
ASSIGNMENT table. Let us try to do an update to the data in the PROJECT-
ASSIGNMENT table and see what happens.
After the database is populated, users find that the name “Simpson” is recorded
incorrectly and that it should be changed to the correct name “Samson.” How is the
correction accomplished? The correction will have to be made wherever the name
“Simpson” exists in the database. Now look at the example of data content shown
in Figure 10-2.
Even in this extremely limited set of rows in the table, you have to make the cor-
rection in three rows. Imagine a database of 500 or 5000 employees. Even this is not
a large database. It is not unusual to store data about many thousands of employ-
ees in a typical database. Now go back to the correction. In a large database cov-
ering a large number of rows for employees, the number of rows for
PROJECT-ASSIGNMENT is expected to be high. Therefore, it is very likely that
when you make the correction to the name, you will miss some rows that need to
be changed. So, what is the effect of the update anomaly in this case?
Update anomaly
Results in data inconsistency because of possible partial update instead of the proper
complete update.
Deletion Anomaly
Again, if the relational two-dimensional table does not conform to relational rules,
you find that problems arise when you try to delete rows from a database based on
such a table. Let us try to delete some data from the PROJECT-ASSIGNMENT
table and see what happens.
Here is the situation. Employee Beeton leaves your organization. Therefore, it
is no longer necessary keep any information about Beeton in your database. You
are authorized to delete all data about Beeton from the database. Now inspect the
sample database contents shown in Figure 10-2.
How is the deletion of data about Beeton carried out? Luckily, you have to delete
just one row, namely, the second row in the PROJECT-ASSIGNMENT table, to get
rid of all data about Beeton in the database. Now, consider another aspect of this
operation. What happens when you delete this row? Data such as Beeton's EmpId,
Name, Salary, Position, and his project assignment are deleted. This is fine, because
this is what you intended to do.
Now examine the row as shown in the figure. When you delete this row, you not
only remove data about Beeton, you also delete data about Department 2. And
looking at the entire contents of the table, you notice that this is only row that has
information about Department 2. By deleting this row, you also delete all data about
Department 2 from the database. However, this is not your intention. Data about
Department 2 have to be preserved in the database for possible future uses. But if
you delete the second row, data about Department 2 are also (unintentionally) lost.
Let us express the effect of deletion anomaly.