Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Instructor’s Manual
Prepared by Scott L. Vandenberg
Chapter Six
Database Administration
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior
written permission of the publisher. Printed in the United States of America.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Chapter Six – Database Administration
CHAPTER OBJECTIVES
• Understand the need for and the importance of database administration
• Learn different ways of processing a database
• Understand the need for concurrency control, security, and backup and recovery
• Learn about typical problems that can occur when multiple users process a database
concurrently
• Understand the use of locking and the problem of deadlock
• Learn the difference between optimistic and pessimistic locking
• Know the meaning of ACID transaction
• Learn the four 1992 ANSI standard isolation levels
• Understand the need for security and specific tasks for improving database security
• Know the difference between recovery via reprocessing and recovery via
rollback/rollforward
• Understand the nature of the tasks required for recovery using rollback/rollforward
• Know basic administrative and managerial DBA functions
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Chapter Six – Database Administration
Figure 1 – Windows 10 Main Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Chapter Six – Database Administration
Figure 2 – Windows 10 All Apps Menu
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps Menu Included
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10–
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
the keyboard Windows key is pressed).
Therefore, note that the step by step instructions in this book may need to be altered for
your use depending upon which version of Microsoft Windows 10 you or your students
are using!
We recommend that you update Windows 10 to the Windows 10 Anniversary Update
(Feature update to Windows 10, version 1607), and make sure it is patched with all
updates to that version (at a minimum patched to Windows 10 Version 1607 update for
August 23, 2016 (KB3176936), and the Windows 10 Version 1607 cumulative update for
September 29, 2016 (KB3194496). We also recommend using the 32-bit version of
Microsoft Office. This insures that all the examples discussed in this book will function
properly.
The All apps menu
The File Explorer button
The File Explorer icon
Chapter Six – Database Administration
TEACHING SUGGESTIONS
• This chapter introduces the topic of database administration by describing the
fundamental concepts, technology, and terminology used for multi-user database
management.
• This text distinguishes between database administration and data administration.
The latter is an organizational activity that typically sits high in the enterprise
organization chart and may be concerned with much larger issues than the
maintenance of a particular database. This chapter is concerned with database
administration, a more pedestrian function that occurs at a much lower level in the
organization. Every multi-user database needs a database administrator. The
administrator may only have a few tasks to perform and may work on them only a
few hours a week, but someone needs to be given the responsibility for ensuring that
the database administration tasks are accomplished on a timely basis. This is
especially true for Internet technology databases where the users may be far away
or even anonymous, and where the consequences of failures and mistakes will be
difficult to correct.
• Concurrency control is important and sometimes seems obscure. If users do not
learn the concepts and techniques, however, the DBMS will use default settings.
These defaults may be perfect, but they also may be terrible. Thus, even though
these issues can be ignored, their ramifications cannot be avoided.
• Security is becoming more and more important. As implied in the text, for Internet
applications, security tasks are accomplished by both the Web server and the
DBMS. Students should look into these issues when they have had both Web server
and database classes.
• If you are working with SQL Server 2016, be sure to create the server login and
database user accounts as shown in the chapter. The Web site material in Chapter
7 will not work without them!
• The goal of this chapter with regard to backup and recovery is to raise the students’
consciousness to these issues. They should get a general idea of the importance of
backup and recovery and the general nature of backup and recovery tools and
techniques.
Chapter Six – Database Administration
ANSWERS TO REVIEW QUESTIONS
6.1 What is the purpose of database administration?
6.2 Explain how database administration tasks vary with the size and complexity of the
6.3 What are two interpretations of the acronym DBA?
6.4 What is the purpose of concurrency control? What is throughput, and how is
concurrency control related to throughput?
6.5 What is the goal of a database security system?
6.6 Explain the meaning of the word inappropriately in the phrase “one user’s work does not
inappropriately influence another user’s work.”
6.7 Explain the major trade-off that exists in concurrency control.
6.8 Describe what an atomic transaction is, and explain why atomicity is important.
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 8 of 36
An atomic transaction is one in which either all of the database actions are committed to the
database or none of them are. Without it, there is a danger that results of actions from partially
processed transactions will be present in the database.
6.9 Explain the difference between concurrent transactions and simultaneous transactions.
How many CPUs are required for simultaneous transactions?
6.10 Give an example, other than the one in this text, of the lost update problem.
6.11 Define the terms dirty read, nonrepeatable read, and phantom read.
6.12 Explain the difference between an explicit lock and an implicit lock.
6.13 What is lock granularity?
6.14 Explain the difference between an exclusive lock and a shared lock.
6.15 Explain two-phase locking.
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 9 of 36
transaction has a lock growing phase and then a lock shrinking phase. Most DBMS products
implement a special case of this in which all locks are released when a transaction commits (this
is sometimes called strict two-phase locking).
6.16 How does releasing all locks at the end of a transaction relate to two-phase locking?
6.17 What is deadlock? How can it be avoided? How can it be resolved when it occurs?
6.18 Explain the difference between optimistic and pessimistic locking.
6.19 Explain the benefits of marking transaction boundaries, declaring lock characteristics,
and letting a DBMS place locks.
6.20 Explain the use of the SQL transaction control statements BEGIN TRANSACTION,
COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
6.21 Explain the meaning of the expression ACID transaction.
Chapter Six – Database Administration
6.22 Describe statement-level consistency.
6.23 Describe transaction-level consistency. What disadvantage can exist with it?
6.25 Explain what read uncommitted isolation level is. Give an example of its use.
6.26 Explain what read committed isolation level is. Give an example of its use.
6.27 Explain what repeatable read isolation level is. Give an example of its use.
6.28 Explain what serializable isolation level is. Give an example of its use.
6.29 Explain the term cursor.
Chapter Six – Database Administration
6.30 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?
6.31 What is the advantage of using different types of cursors?
6.32 Explain forward-only cursors. Give an example of their use.
6.33 Explain static cursors. Give an example of their use.
6.34 Explain keyset cursors. Give an example of their use.
6.35 Explain dynamic cursors. Give an example of their use.
Chapter Six – Database Administration
6.36 What happens if you do not declare transaction isolation level and cursor type to the
DBMS? Is not declaring the isolation level and cursor type good or bad?
6.37 Explain the necessity of defining processing rights and responsibilities. How are such
responsibilities enforced? What is SQL data control language (DCL), and what SQL
statements are used in DCL?
6.38 Explain the relationships of users, groups, permissions, and objects for a generic
database security system.
6.39 Describe the advantages and disadvantages of DBMS-provided security.
Advantages:
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 13 of 36
• May not be secure enough (e.g. if you want to prevent information transfer between
certain classes of users and hide certain data from classes of users)
6.40 Describe the advantages and disadvantages of application-provided security.
Advantages:
Disadvantages:
6.41 Explain how a database could be recovered via reprocessing. Why is this generally not
feasible?
6.42 Define the terms rollback and rollforward.
6.43 Why is it important to write to a log before changing the database values?
6.44 Describe the rollback process. Under what conditions should rollback be used?
6.45 Describe the rollforward process. Under what conditions should rollforward be used?
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 14 of 36
Start with a restored database from a prior saved backup, then apply after images. This method
is used when the database has been lost.
6.46 What is the advantage of making frequent checkpoints of a database?
6.47 Summarize a DBA’s responsibilities for managing database user problems.
The DBA’s responsibilities for managing user problems include:
6.48 Summarize the DBA’s responsibilities for configuration control.
The DBA’s responsibilities for configuration control include:
6.49 Summarize the DBA’s responsibilities for documentation.
The DBA’s responsibilities for documentation include ensuring that proper documentation is
maintained on:
Chapter Six – Database Administration
ANSWERS TO EXERCISES
6.50 If you have access to Microsoft SQL Server, search its help system to answer the
following questions.
A. Does SQL Server support both optimistic and pessimistic locking?
B. What levels of transaction isolation are available?
C. What types of cursors, if any, does SQL Server use?
D. How does the security model for SQL Server differ from that shown in Figure 6-
E. Summarize the types of SQL Server backup.
F. Summarize the SQL Server recovery models.
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 16 of 36
recovery, changes to large text and graphic data items are not recorded to the log—
actions like bulk insert, SELECT INTO, INSERT … SELECT, CREATE INDEX are not logged,
and some other bulk-oriented actions are not logged. An organization would use bulk-
logged recovery if conserving log space is important and if the data used in the bulk
operations is saved in some other way.
6.51 If you have access to Oracle Database XE, search its help system to answer the
following questions.
A. How does Oracle Database XE use read locks and write locks?
Oracle’s locking scheme is complicated. The following outline is from “Summary of
Locking Behavior” in Oracle Database Concepts in the Oracle Database 11g Release 2
Documentation Library (available at http://docs.oracle.com/cd/E11882_01/index.htm):
Locks affect the interaction of readers and writers. A reader is a query of a
resource, whereas a writer is a statement modifying a resource. The following
rules summarize the locking behavior of Oracle Database for readers and
writers:
■ A writer of a row blocks a concurrent writer of the same row.
Because a reader of a row does not lock it, a writer can modify this row. The
only exception is a SELECT … FOR UPDATE statement, which is a special type of
SELECT statement that does lock the row that it is reading.
B. What, if any, levels of transaction isolation are available in Oracle Database XE?
Chapter Six – Database Administration
From “Overview of Oracle Database Transaction Isolation levels” in Oracle Database
Concepts in the Oracle Database 11g Release 2 Documentation Library (available at
http://docs.oracle.com/cd/E11882_01/index.htm):
C. How does the security model for Oracle Database XE differ from that shown
in Figure 6-16?
The model is exactly the same.
D. Summarize the backup capabilities of Oracle Database XE.
From “Backing Up and Restoring the Database” in Oracle Database Express Edition 2 Day
DBA in the Oracle Database Express Edition Documentation (available at
http://docs.oracle.com/cd/E17781_01/index.htm ):
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 18 of 36
and restore of the entire database, and store backup files in the flash recovery
area.
Automatic Management of Backup Storage
Oracle Database XE implements a backup retention policy that dictates that two
complete backups of the database must be retained, to provide a level of
redundant protection for the database. In ARCHIVELOG mode, all archived logs
required for media recovery from either backup are also retained. The database
automatically manages backups and archived logs in the flash recovery area,
deleting any that are obsolete (no longer needed to satisfy the retention policy)
as space is needed for new files. The backup script provided with Oracle
Database XE also deletes obsolete backups and archived logs at the end of each
backup job.
Backup Script
The provided backup script performs online backups of a database that is in
ARCHIVELOG mode and offline backups of a database that is in NOARCHIVELOG
mode. Online backups are backups that can run while the database is running.