INSTRUCTOR’S MANUAL
TO ACCOMPANY
40th Anniversary Edition
DATABASE PROCESSING
Fundamentals, Design, and Implementation
15th Edition
Chapter 9
Managing Multiuser Databases
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
Chapter Nine – Managing Multiuser Databases
❖ CHAPTER OBJECTIVES
• To understand the need for and importance of database administration
• To understand the need for concurrency control, security, and backup and recovery
• To learn about typical problems that can occur when multiple users process a
database concurrently
❖ IMPORTANT TEACHING NOTES – READ THIS FIRST!
1. Chapter 9 – Managing Multiuser Databases is intended to be taught in
conjunction with the version of online Chapter 10 A, B, or C available at
you are using in your class.
a. If you are using Microsoft SQL Server 2017 as your DBMS, you should
use Online Chapter 10A – Managing Databases with Microsoft SQL
Server 2017, the topics under Concurrency Control, Security, and
Backup/Recovery on pages 10A-130 through 10A-140.
i. For many of the topics discussed in Chapter 9, there is a DBMS
specific treatment of the same topic in online Chapter 10A.
Chapter Nine – Managing Multiuser Databases
Page 9-3
b. If you are using Oracle Database 12c or Oracle Database XE as your
DBMS, you should use Online Chapter 10B – Managing Databases
with Oracle Database, and cover pages 10B-36 through 10B-42 and
10B–121 through 10B-125, topics: Database Security, Concurrency
Control, and Database Backup/Recovery.
i. For many of the topics discussed in Chapter 9, there is a DBMS
specific treatment of the same topic in online Chapter 10B.
c. If you are using MySQL 5.7 as your DBMS, you should use Online
Chapter 10C – Managing Databases with MySQL 5.7, and cover
pages 10C-99 through 10C-108, topics: Concurrency Control, Security,
and Backup/Recovery.
i. For many of the topics discussed in Chapter 9, there is a DBMS
specific treatment of the same topic in online Chapter 10C.
Chapter Nine – Managing Multiuser Databases
❖ ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
❖ TEACHING SUGGESTIONS
• This chapter introduces the topic of database administration and lays out the
fundamental concepts, technology, and terminology for multiuser database
management. The structure of this chapter is used for the following chapters on
Microsoft SQL Server 2017 (Chapter 10A), Oracle Database (Online Chapter 10B) and
MySQL 5.7 (Online Chapter 10C).
• Concurrency control is important and sometimes seems obscure. The caution at the
end of the concurrency control section is important: “If you do not specify the isolation
level of a transaction or do not specify the type of cursors you open, the DBMS will use
the default level and types. These defaults may be perfect for your application, but they
also may be terrible. Thus, even though these issues can be ignored, the consequences
of them cannot be avoided. Learn the capabilities of your DBMS products and use them
wisely!” Specific techniques for Microsoft SQL Server 2017 are discussed in Chapter
10A, for Oracle Database in Online Chapter 10B, and for MySQL 5.7 in Online Chapter
10C.
Chapter Nine – Managing Multiuser Databases
❖ ANSWERS TO REVIEW QUESTIONS
9.1 Briefly describe five difficult problems for organizations that create and use multiuser
databases.
(1) Multiuser databases are complicated to design and develop because they support many
overlapping user views.
(2) Requirements change over time, and those changes necessitate other changes to the database
structure. Such structure changes must be carefully planned and controlled so that a change
made for one group does not cause problems for another.
9.2 Explain the difference between a database administrator and a data administrator.
The term data administrator refers to a function that applies to an entire organization, and mostly
9.3 List seven important DBA tasks.
(1) Managing database structure
(2) Controlling concurrent processing
(3) Managing processing rights and responsibilities
Chapter Nine – Managing Multiuser Databases
Page 9-6
9.4 Summarize the DBA’s responsibilities for managing database structure.
Participate in Database and Application Development
• Assist in requirements stage and data model creation
• Play an active role in database design and creation
Facilitate Changes to Database Structure
• Seek community-wide solutions
9.5 What is configuration control? Why is it necessary?
Configuration Control is a process that allows users to register their needs for changes to
9.6 Explain the meaning of the word inappropriately in the phrase “one user’s work does
not inappropriately influence another user’s work.”
It means that any overlap of work results is anticipated and is in accordance with the
9.7 Explain the trade-off that exists in concurrency control.
High level of concurrency control is easier for the DBMS to administer but causes poor
9.8 Define an atomic transaction and explain why atomicity is important.
An atomic transaction is a series of actions to be taken on the database so that either all of them
9.9 Explain the difference between concurrent transactions and simultaneous transactions.
How many CPUs are required for simultaneous transactions?
When two transactions are being processed against a database at the same time, they are termed
Chapter Nine – Managing Multiuser Databases
Page 9-7
9.10 Give an example, other than the one in this text, of the lost update problem.
The lost update problem occurs when two transactions attempt to update the same data resource
simultaneously. Because each transaction copies the record into its own work area in memory,
9.11 Explain the difference between an explicit and an implicit lock.
Locks placed by the DBMS itself are called implicit locks; those placed by program commands
are called explicit locks.
9.12 What is lock granularity?
The size (or scope) of a lock is referred to as the lock granularity. Locks with large granularity
9.13 Explain the difference between an exclusive lock and a shared lock.
9.14 Explain two-phased locking.
9.15 How does releasing all locks at the end of the transaction relate to two-phase locking?
This strategy is more restrictive than two-phase locking requires, but it is easier to implement.
9.16 In general, how should the boundaries of a transaction be defined?
In general, the boundaries of a transaction should correspond to the definition of the database
Chapter Nine – Managing Multiuser Databases
9.17 What is deadlock? How can it be avoided? How can it be resolved once it occurs?
Deadlock occurs when User1 locks a resource needed by User2 and User2 locks a resource
9.18 Explain the difference between optimistic and pessimistic locking.
With optimistic locking, the assumption is made that no conflict will occur. Data are read, the
9.19 Explain the benefits of marking transaction boundaries, declaring lock characteristics,
and letting the DBMS place locks.
9.20 What is SQL Transaction Control Language (TCL)? Explain the use of SQL BEGIN
TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION
statements. Why does MySQL also use the SQL START TRANSACTION statement?
SQL Transaction Control Language (TCL) is that set of SQL statements used to mark transaction
boundaries. Additionally, it can be used to declare the type of locking behavior to be used for the
transaction.
9.21 What is an implicit COMMIT? What is an explicit COMMIT? Which of these are used by
default with Microsoft Server 2017, Oracle Database, and MySQL 5.7?
9.22 Explain the meaning of the expression ACID transaction.
An ACID transaction is one that is atomic, consistent, isolated, and durable. An atomic
transaction is one in which either all of the database actions occur or none of them do. A durable
Chapter Nine – Managing Multiuser Databases
9.23 Describe statement-level consistency.
Suppose that there are 500,000 rows in the CUSTOMER table and that 500 of them have
ZIPCode equal to “98050”. It will take some time for the DBMS to find all 500 rows. During that
9.24 Describe transaction-level consistency. What disadvantage can exist with it?
9.25 What is the purpose of transaction isolation levels?
The 1992 ANSI SQL standard defines four transaction isolation levels, which specify which of
9.26 Explain the read-uncommitted isolation level. Give an example of its use.
9.27 Explain the read-committed isolation level. Give an example of its use.
Read committed isolation level allows nonrepeatable reads and phantom reads to occur, but
9.28 Explain the repeatable-read isolation level. Give an example of its use.
Repeatable read isolation level allows phantom reads, but disallows both dirty reads and
9.29 Explain the serializable isolation level. Give an example of its use.
Serializable isolation level will not allow any dirty reads, nonrepeatable reads or phantom reads
Chapter Nine – Managing Multiuser Databases
9.30 Explain the term SQL cursor.
9.31 Explain why a transaction may have many cursors. Also, how is it possible that a
transaction may have more than one cursor on a given table?
The transaction may need to process several tables at one time. A cursor can be opened on two
9.32 What is the advantage of using different types of cursors?
Because cursors require considerable memory, having many cursors open at the same time for,
say, a thousand concurrent transactions can consume considerable memory and CPU time. One
9.33 Explain forward only cursors. Give an example of their use.
The simplest cursor is the forward only cursor. With it, the application can only move forward
9.34 Explain static cursors. Give an example of their use.
With a static cursor, the application sees the data as it was at the time the cursor was opened.
9.35 Explain keyset cursors. Give an example of their use.
Dynamic changes (deletion and updates) of any type and from any source are visible. When the
cursor is opened, a primary key value is saved for each row in the recordset. When the
Chapter Nine – Managing Multiuser Databases
9.36 Explain dynamic cursors. Give an example of their use.
Dynamic cursors have the most functionality and is the most resource-intensive cursor: all
9.37 What happens if you do not declare the transaction isolation level and the cursor type to
the DBMS? Is this good or bad?
If you do not specify the isolation level of a transaction or do not specify the type of cursors you
9.38 What is SQL Data Control Language (DCL)? Explain the necessity of defining
processing rights and responsibilities. How are such responsibilities enforced, and what
is the role of SQL DCL in enforcing them?
SQL Data Control Language (DCL) is the set of SQL statement used to grant and revoke database
processing permissions to users.
9.39 Explain the relationships among USER, ROLE, PERMISSION, and OBJECT for a
generic database security system.
A USER can be assigned to one or more ROLEs (sometimes called user groups, or profiles) that
9.40 Should the DBA assume a firewall when planning security?
The DBA should plan security assuming that the firewall has been breached. The DBMS, the
9.41 What should be done with unused DBMS features and functions?
Chapter Nine – Managing Multiuser Databases
Page 9-12
9.42 Explain how to protect the computer that runs the DBMS.
No one other than authorized DBA personnel should be allowed to work at the keyboard of the
9.43 With regard to security, what actions should the DBA take on user accounts and
passwords?
All accounts within the DBMS should be protected by strong passwords. Such passwords have at
9.44 List two elements of a database security plan.
9.45 Describe the advantages and disadvantages of DBMS-provided and application-
provided security.
DBMS-provided – Advantages: Easier to implement, it will be done regardless of the source of
9.46 What is an SQL injection attack and how can it be prevented?
An SQL injection attack occurs when some form of SQL is included as data when a user enters
Chapter Nine – Managing Multiuser Databases
9.47 Explain how a database could be recovered via reprocessing. Why is this generally not
feasible?
Reprocessing means to redo all events exactly like they were done the first time. For example, if
9.48 Define rollback and rollforward.
In a rollback, we undo changes made by erroneous or partially processed transactions by
9.49 Why is it important to write to the log before changing the database values?
If the system crashes between the time a transaction is logged and the time it is applied, at worst
9.50 Describe the rollback process. Under what conditions should it be used?
In a rollback, the current database and the transaction log are used. Before images of all
9.51 Describe the rollforward process. Under what conditions should it be used?
In a rollforward, the saved copy of the database and the transaction log are used. First, the
9.52 What is the advantage of taking frequent checkpoints of a database?
Chapter Nine – Managing Multiuser Databases
9.53 Summarize the DBA’s responsibilities for managing the DBMS.
(1) Generate database application performance reports
(2) Investigate user performance complaints
9.54 What is a data repository? A passive data repository? An active data repository?
A data repository is a collection of metadata about databases, database applications, Web pages,
9.55 Explain why a data repository is important. What is likely to happen if one is not
available?