Database anomalies are operational problems caused by poor database design PDF

Preview

Summary

Database anomalies are operational problems caused by poor database design...

Description

Database anomalies are operational problems caused by poor database design. We have discussed such issues before, but we review them here. An insertion anomaly occurs when you cannot add a row to a relation because you do not have other data that you store with it, or you have to invent “dummy” data for some attributes in order to store the values of other attributes. For example, you want the database to store that Zumba costs $150, but in the table design you do not have anywhere to store this information until a member signs up for Zumba, unless you create a fake member for this purpose. A deletion anomaly occurs when deleting data from a relation causes other critical data to be lost, even though you would have preferred to keep them in the database. For example, if member 215 decides to drop out of the tennis class, and you delete his or her records, your database runs the risk of forgetting that Tennis costs $200. An update anomaly occurs when you must change the contents of more than one cell to reflect the modification of a single datum. For example, if the cost of Zumba changes from $150 to $175, you must change all the records that reference Zumba. What Causes Anomalies? Anomalies occur because of the following two kinds of database design errors: 1) Data redundancy, which occurs when you replicate the same field in multiple tables, other than to set up foreign keys.

2) You create a database that includes functional dependencies whose determinants are not candidate keys, including partial dependencies and transitive dependencies. For example, in the table ACTIVITY(MemberID, Activity, Fee), Activity by itself is not a candidate key but it determines Fee, so there is a partial dependency and therefore the potential for anomalies The way to fix anomalies is to “normalize” your database design. In this process, you break up tables or create new tables so that all the dependencies within each table have determinants that are primary or candidate keys. Consider the table ACTIVITY(MemberID, Activity, Fee), for the case in which a member can sign up for only one activity, and which we have already seen to contain a transitive dependency. Activity determines Fee, so in order for all dependencies to be on a primary key, Fee must be in a table whose primary key is Activity. Therefore, we create a new table which contains just Activity and Fee, with Activity being the primary key. We remove Fee from the original table, leaving just the MemberID and Activity fields. We use the name ACTIVITY for the table with just the Activity and Fee fields, and to avoid a duplicate table name, we change the name of the original table to PARTICIPATING. Note that MEMBER would also be a reasonable name for this table, since its primary key is MemberID. The database now appears as follows:

Observe that Activity in the PARTICIPATING table is described as a foreign key to ACTIVITY, meaning that any activity present in PARTICIPATING must exist in the ACTIVITY table. For example, we cannot enter “Judo” in the Activity field of the PARTICIPATING table without first creating a “Judo” record in the ACTIVITY table, and setting a fee for that activity. The dependency diagram depicts this foreign key relationship with a dashed line connecting the Activity fields in the two tables. Note that it is not considered redundant to have Activity in two different tables, because it is functioning as a foreign key. If, for example, we want to find out the fee being paid by member number 175, we look up that primary key in the PARTICIPATING table, find that the corresponding activity is “Mommy & Me Cooking,” then locate the record with primary key “Mommy & Me Cooking” in the ACTIVITY table, and finally find that the fee is $125. As mentioned in the previous chapter, this kind of cross ‐ referencing operation between tables is called a “join.”...