Database Reference
In-Depth Information
Deletion anomaly
Results in unintended loss of data because of possible deletion of data other than what
must be deleted.
Addition Anomaly
We have considered the effects of updates and deletions in a two-dimensional table
that is put together in a random fashion from information requirements. You have
noted that these operations cause anomalies or problems. Now let us try to perform
one more common data operation on this table. Try to add new data to the database.
This is the situation. A new employee, Potter, has joined your organization. As
usual, the human resources department has already assigned a unique EmpId to
Potter. So you need to add data about Potter to the database. However, Potter is
still in training and therefore is not assigned to a project yet. You have data about
Potter such as his salary, bonus, and the department in which is hired. You can add
all of these data to the database.
Begin to create a row for Potter in the database. You are ready to create a row
in our PROJECT-ASSIGNMENT table for Potter. You can enter the name, depart-
ment, and so on. But what about the unique primary key for this row? As you know,
the primary key for this table consists of EmpId and ProjNo together. However,
you are unable to assign a value for ProjNo for this row because he is not assigned
to a project yet. So you can have a null value for ProjNo until Potter is assigned to
a project. But can you really do this? If you place a null value in the ProjNo column,
you will be violating the entity integrity rule that states no part of the primary key
may be null. You are faced with a problem—an anomaly concerning added new
data. Data about Potter cannot be added to the database until he is assigned to a
project. Even though he is already an employee, data about Potter will be missing
in the database. This is the effect of addition anomaly.
Addition anomaly
Results in inability to add data to the database because of the absence of some data
presently unavailable.
NORMALIZATION APPROACH
Let us review our discussion so far. We inspected the information requirements
about employees, departments, projects, and project assignments. Our intention was
to create a relational data model directly from the study of the information require-
ments. This meant creating a data model consisting of two-dimensional tables or
relations that normally make up a relational data model. Because of the simplicity
of the information requirements, we were able to represent all the data in a single
random table. So far, this is the relational data model for us. If it has to be a good
relational model, it must conform to relational rules.
You have observed that the random table PROJECT-ASSIGNMENT violates
some relational rules at the outset. More importantly, when you attempt to update
data, delete data, or add data, our initial data model has serious problems. You have
Search WWH ::
Custom Search