Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 190

ExampleDataSet
Suppose the following dataset is used by the FCC to keep track of amateur radio license information.
Included in this information are things like the Class of license, Callsign of operators, Frequency
privileges, and so on. See Table 8-1 below.

Table 8-1: Example Data

Examiner Frequency Region

Name Callsign Examiner Callsign LicenseClass 1 ... n Region Coordinator

Bob KE4IOR Ted A4GH Amateur Extra 3, 7, 14, 20, 28, 30 Southeast Jim

Mike KF4DGK John W4NBV Technician 3, 7, 14 Southeast Jim

Billy N4UF Steve WA4B Amateur Extra 3, 7, 14, 20, 28, 30 Southeast Jim

David K2NXT Sam K5RTY General 3, 7, 14, 20 Northeast Bill

Emily W5TX James N1ZZT Advanced 3, 7, 14, 20, 28 Southwest Jim

Ray W9UTQ Jane W0FDK General 3, 7, 14, 20 Northwest Carol

Anomalies
The data anomalies that can occur are broken down into several different categories.

Redundancy: In the sample notice that the License Class and Region are repeated for the
corresponding LicenseClass.

❑

Insert anomaly: This occurs because the table in which a row should be inserted can't accept the
insert. Using the sample dataset, suppose the FCC wants to create a new class of license called
Novice . A row cannot be inserted until someone is actually issued a Novice license.

❑

Update anomaly: The same information is repeated on multiple rows, which opens the door for
updates that might result in data inconsistencies. Suppose David upgrades his license class from
General to Advanced. Changing David's LicenseClass to Advanced will cause the data to show
that the Advanced class has two different privileges. Another variation of an update anomaly
occurs when a change needs to be made to all rows, but for some reason the update operation
doesn't completely succeed. Suppose you want to change the region coordinator from Jim to
Jason. If the update operation is entirely successful then some rows would have both Jim
and Jason as section coordinators. In either case, interrogating the table to find a particular
privilege or region coordinator would result in multiple values (and it is usually impossible to
determine which value is correct).

❑

Deletion anomaly: Deleting data that represents one piece of information requires the
deletion of other data that represents a different piece of information. Suppose the FCC decides
to discontinue the General license class. Deleting the rows in the sample will cause a loss of
operator names, callsigns, and other information where the license class equals General. Thus,
data that is unrelated to the license class is lost.

❑