Database Reference
In-Depth Information
What are the meanings of the various fields? For example, what exactly is the Class field in the
Part table, and what does a Class field value of HW mean?
What relationships between the tables exist in the database? Which relationships are one-
to-many, many-to-many, and one-to-one? Must the relationship always exist? For example, must
a customer always have a sales rep?
Which fields and combinations of fields can you rapidly search for specific values because they
are indexed? Which fields that are not indexed are candidates for indexes because they are often
used in searches?
Which users have access to the database? For example, which fields can Elena access for
retrieval purposes but not update? Which fields can Elena update?
232
Which programs or objects (queries, forms, and reports) access which data within the database?
How do they access it? Do these programs merely retrieve the data, or do they update it too?
What kinds of updates do the programs perform? Can a certain program add a new customer, for
example, or can it merely make changes to information about customers that are already in the
database? When a program changes customer data, can it change all the fields or only some
fields? Which fields?
Enterprise DBMSs, such as Oracle and DB2, often have a catalog called a data dictionary, which contains
answers to all these questions and more. The data dictionary serves as a super-catalog containing metadata
beyond what
s been described previously. For example, these DBMSs let the DBA split the data in a database
and store the fragmented data on multiple disks at multiple locations. In these cases, the data dictionary
must track the location of the data. PC-based DBMSs do not offer a data dictionary, but they have a catalog
that provides answers to most of the preceding questions.
'
SUPPORT CONCURRENT UPDATE
A DBMS must ensure that the database is updated correctly when multiple users update the database at the
same time.
Sometimes a person uses a database stored on a single computer. At other times, several people might
update a database, but only one person at a time does so. For example, several people might take turns with
one computer to update a database. A DBMS handles these situations easily. However, the use of networks
and of DBMSs that are capable of running on these networks and that allow several users to update the same
database raises a problem that the DBMS must address: concurrent update.
Concurrent update occurs when multiple users make updates to the same database at the same time. On
the surface, you might think that a concurrent update doesn
'
t present any problem. Why couldn
'
t two, three,
or fifty users update the database simultaneously without causing a problem?
The Concurrent Update Problem
To illustrate the problem with concurrent update, suppose that Ryan and Elena are two users who work at
Premiere Products. Ryan is currently updating the Premiere Products database to process orders and, among
other actions, to increase customers
balances by the amount of their orders. For example, Ryan needs to
increase the balance of customer 282 (Brookings Direct) by $100.00. Elena, on the other hand, is updating
the Premiere Products database to post customer payments and, among other things, to decrease customers
'
'
balances by the amounts of their payments. Coincidentally, Elena has a $100.00 payment from Brookings
Direct, so she will decrease that customer
s balance by $100.00. The balance for Brookings Direct is $431.50
before the start of these updates. Because the amount of the increase exactly matches the amount of the
decrease, the balance should still be $431.50 after their updates. But will it? That depends on how the data-
base handles the updates.
How does the DBMS make the required update for Ryan? First, as shown in Figure 7-4, the DBMS reads
the data for Brookings Direct from the database on disk into Ryan
'
s work area in memory (RAM). Second,
Ryan enters the order data for Brookings Direct. At this point, Ryan
'
s order entry takes place in his work area
in memory, including the addition of the order total of $100.00 to the balance of $431.50, bringing the bal-
ance to $531.50. This change has not yet taken place in the database; it has taken place only in Ryan
'
s work
area in memory. Finally, after Ryan finishes entering the order data for Brookings Direct, the DBMS updates
the database with Ryan
'
'
s changes.
