Microsoft Office Tutorials and References
In Depth Information
When you use a DBMS, you are more likely to get results you can trust if you create your
tables carefully. For example, problems might occur with tables that have partial and tran-
sitive dependencies, whereas you won’t have as much trouble if you ensure that your
tables include only fields that are directly related to each other. Also, when you remove
data redundancy from a table, you improve that table. Data redundancy occurs when you
store the same data in more than one place.
The problems caused by data redundancy and by partial and transitive dependencies
are called anomalies because they are undesirable irregularities of tables. Anomalies are
of three types: insertion, deletion, and update.
To examine the effects of these anomalies, consider the modified Employee table that is
shown again in Figure A-13.
A table with insertion, deletion, and update anomalies
• An insertion anomaly occurs when you cannot add a record to a table because you do
not know the entire primary key value. For example, you cannot add the new employee
Cathy Corbett with an EmployeeNum of 3322 to the Employee table if you do not know
her position in the company. Entity integrity prevents you from leaving any part of a pri-
mary key null. Because the PositionID field is part of the primary key, you cannot leave
it null. To add the new employee, your only option is to make up a PositionID field
value, until you determine the correct position. This solution misrepresents the facts and
is unacceptable, if a better approach is available.
•A deletion anomaly occurs when you delete data from a table and unintentionally lose
other critical data. For example, if you delete EmployeeNum 2173 because Hennessey
is no longer an employee, you also lose the only instance of HealthPlan B in the data-
base. Thus, you no longer know that HealthPlan B is the “Managed HMO” plan.
• An update anomaly occurs when you change one field value and either the DBMS must
make more than one change to the database or else the database ends up containing
inconsistent data. For example, if you change a LastName, HealthPlan, or PlanDesc
field value for EmployeeNum 8005, the DBMS must change multiple rows of the
Employee table. If the DBMS fails to change all the rows, the LastName, HealthPlan, or
PlanDesc field now has different values in the database and is inconsistent.