Information Technology Reference
In-Depth Information
Figure 5.8
Building and Modifying a
Relational Database
Relational databases provide many
tools, tips, and shortcuts to simplify
the process of creating and
modifying a database.
(Source: Courtesy of Microsoft
Corporation.)
Data Cleanup
As discussed in Chapter 1, valuable data is accurate, complete, economical, flexible, reliable,
relevant, simple, timely, verifiable, accessible, and secure. The database must also be properly
designed. The purpose of
data cleanup
is to develop data with these characteristics. Consider
a database for a fitness center designed to track member dues. The table contains the attribute
name, phone number, gender, dues paid, and date paid (see Table 5.3). As the records in
Table 5.3 show, Anita Brown and Sim Thomas have paid their dues in September. Sim has
paid his dues in two installments. Note that no primary key uniquely identifies each record.
As you will see next, this problem must be corrected.
data cleanup
The process of looking for and fixing
inconsistencies to ensure that data
is accurate and complete.
Table 5.3
Name
Phone
Gender
Dues Paid
Date Paid
Fitness Center Dues
Brown, A.
468-3342
Female
$30
September 15
Thomas, S.
468-8788
Male
$15
September 15
Thomas, S.
468-5238
Male
$15
September 25
Because Sim Thomas has paid dues twice in September, the data in the database is now
redundant. The name, phone number, and gender for Thomas are repeated in two records.
Notice that the data in the database is also inconsistent: Thomas has changed his phone
number, but only one of the records reflects this change. Further reducing this database's
reliability is the lack of a primary key to uniquely identify Sim Thomas's record. The first
Thomas could be Sim Thomas, but the second might be Steve Thomas. These problems and
irregularities in data are called
anomalies
. Data anomalies often result in incorrect informa-
tion, causing database users to be misinformed about actual conditions. Anomalies must be
corrected.
To solve these problems in the fitness center's database, we can add a primary key, such
as member number, and put the data into two tables: a Fitness Center Members table with
gender, phone number, and related information, and a Dues Paid table with dues paid and
date paid (see Tables 5.4 and 5.5). Both tables include the member number attribute so that
they can be linked.
