Chapter Six – Database Administration
E. Summarize the recovery capabilities of Oracle Database XE.
See the answer to Question D above.
6.52 If you have access to MySQL 5.7, search its help system to answer the following
questions.
A. How does MySQL 5.7 use read locks and write locks?
Shared and Exclusive Locks
Intention Locks
Chapter Six – Database Administration
Intention exclusive (IX): Transaction T intends to set X locks on those rows.
X
IX
S
IS
X
Conflict
Conflict
Conflict
Conflict
IX
Conflict
Compatible
Conflict
Compatible
S
Conflict
Conflict
Compatible
Compatible
IS
Conflict
Compatible
Compatible
Compatible
A lock is granted to a requesting transaction if it is compatible with existing locks, but
not if it conflicts with existing locks. A transaction waits until the conflicting existing lock
B. What, if any, levels of transaction isolation are available in MySQL 5.7?
For InnoDB:
C. What types of cursors, if any, does MySQL 5.7 use?
D. How does the security model for MySQL 5.7 differ from that shown in Figure
6-16?
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 21 of 36
The model is about the same, except that MySQL does not have Roles (Groups), only
Users.
E. Summarize the backup capabilities of MySQL 5.7.
F. Summarize the recovery capabilities of MySQL 5.7.
ANSWERS TO MARCIA’S DRY CLEANING CASE QUESTIONS
Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry
cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out
from the competition by providing superior customer service. She wants to keep track of
each of her customers and their orders. Ultimately, she wants to notify them that their
clothes are ready via email.
Assume that Marcia has hired you as a database consultant to develop an operational
database having the following four tables:
A. Assume that Marcia’s has the following personnel: two owners, a shift manager, a part–
time seamstress, and two salesclerks. Prepare a two-to-three-page memo that
addresses the following points:
Chapter Six – Database Administration
2. Your recommendation as to who should serve as database administrator.
Assume that Marcia’s is not sufficiently large to need or afford a full-time
database administrator.
3. Using the main topics in this chapter as a guide, describe the nature of database
administration activities at Marcia’s. As an aggressive consultant, keep in mind
that you can recommend yourself for performing some of the DBA functions.
Chapter Six – Database Administration
Task
Owners
Shift Manager
Managing database
structure
Ensure
documents
exist
Controlling concurrent
processing
Establish
policies
Managing processing
rights and
responsibilities
Set
requirements
Enforce
responsibilities
Document
rights and
responsibilities
Developing database
security
Understand
vulnerability
Ensure
procedures are
followed
Providing for database
recovery
Understand
vulnerability
Ensure
procedures are
followed
Managing the DBMS
Understand the
nature of this
activity
Maintaining the data
repository
Understand the
importance
Ensure
documentation
is kept up to
date
B. For the employees described in part A, define users, groups, and permissions on data in
these four tables. Use the security scheme shown in Figure 6-16 as an example. Create
a table like that in Figure 6-17. Don’t forget to include yourself.
Chapter Six – Database Administration
CUSTOMER
ORDER
ORDER_ITEM
SERVICE
Owners
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Shift Manager
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Query
Salesclerks
Query
Insert
Change
Query
Insert
Change
Query
Query
Seamstresses
N/A
N/A
N/A
N/A
System Admin
Grant rights,
Modify
structure
Grant
rights,
Modify
structure
Grant rights, Modify
structure
Grant rights, Modify
structure
C. Suppose that you are writing a part of an application to create new records in SERVICE
for new services that Marcia’s will perform. Suppose that you know that while your
procedure is running another part of the same application that records new or modifies
existing customer orders and order line items can also be running. Additionally, suppose
that a third part of the application that records new customer data also can be running.
1. Give an example of a dirty read, a nonrepeatable read, and a phantom read
among this group of applications.
Chapter Six – Database Administration
A nonrepeatable read would occur if AppPart02 read a newly inserted SERVICE a second
time, where the transaction in AppPart01 was not committed during the first read,
modified the new service record, and was committed by the time the second read
occurred in AppPart02.
2. What concurrency control measures are appropriate for the part of the
application that you are creating?
3. What concurrency control measures are appropriate for the two other parts of the
application?
Chapter Six – Database Administration
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
The following Garden Glory database design is used in Chapter 3:
The referential integrity constraints are:
Garden Glory has modified the EMPLOYEE table by adding a TotalHoursWorked
column:
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel,
TotalHoursWorked)
A. Explain why it is important for the changes made by the Service Update Transaction to
be atomic.
B. Describe a scenario in which an update of TotalHoursWorked could be lost during a
Service Update Transaction.
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 27 of 36
Two employees updating EMPLOYEE concurrently could cause the lost update problem as
described in the text. Note this requires either two Service Update Transactions to be running
concurrently, or one running concurrently with a direct update to EMPLOYEE.
C. Assume that many Service Update Transactions and many Service Update for New
Employee Transactions are processed concurrently. Describe a scenario for a
nonrepeatable read and a scenario for a phantom read.
D. Explain how locking could be used to prevent the lost update in your answer to
part B.
E. Is it possible for deadlock to occur between two Service Update Transactions? Why or
why not? Is it possible for deadlock to occur between a Service Update Transaction and
a Service Update for New Employee Transaction? Why or why not?
F. Do you think optimistic or pessimistic locking would be better for the Service Update
Transactions?
Chapter Six – Database Administration
G. Suppose Garden Glory identifies three groups of users: managers, administrative
personnel, and system administrators. Suppose further that the only job of
administrative personnel is to make Service Update Transactions. Managers can make
Service Update Transactions and Service Updates for New Employee Transactions.
System administrators have unrestricted access to the tables. Describe processing
rights that you think would be appropriate for this situation. Use Figure 6-17 as an
example. What problems might this security system have?
DATABASE RIGHTS GRANTED
Table
Management
Administrative
System Administrator
OWNER
Read
Insert
Update
Delete
Grant Rights
Modify Structure
EMPLOYEE
Read
Insert
Update
Delete
Read
Update
Grant Rights
Modify Structure
GG_SERVICE
Read
Insert
Update
Delete
Read
Insert
Update
Grant Rights
Modify Structure
PROPERTY_SERVICE
Read
Insert
Update
Delete
Read
Insert
Update
Grant Rights
Modify Structure
OWNED_PROPERTY
Read
Insert
Update
Delete
Read
Grant Rights
Modify Structure
Potential problem: Everyone can update EMPLOYEE. Assuming that TotalHoursWorked is used
to compensate employees, there might be an incentive for Administrative personnel to make
unauthorized changes to that column for their friends/relatives, etc. They probably need to set
H. Garden Glory has developed the following procedure for backup and recovery. The
company backs up the database from the server to a second computer on its network
each night. Once a month, it copies the database to a CD and stores it at a manager’s
house. It keeps paper records of all services provided for an entire year. If it ever loses
its database, it plans to restore it from a backup and reprocess all service requests. Do
you think this backup and recovery program is sufficient for Garden Glory? What
Chapter Six – Database Administration
problems might occur? What alternatives exist? Describe any changes you think the
company should make to this system.
Garden Glory is susceptible to a fire that could destroy both computers and the paper records.
They should make backup to a CD much more frequently than once a month—once every other
ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS
The James River Jewelry Project Questions are in Appendix D, which can be
downloaded from the textbook’s Web site: www.pearsonhighered.com/kroenke. The
solutions for these questions will be included in the Instructor’s Manual for each
chapter.
The referential integrity constraints are:
Chapter Six – Database Administration
ItemNumber in JEWELRY_ITEM must exist in ItemNumber in ITEM
The tables are used to record data and maintain owner data about jewelry accepted on
consignment. JEWELRY_ITEM (which is a subtype of ITEM—note the referential
A. Explain why it is important for the changes made by each of these transactions to be
atomic.
B. Describe a scenario in which an update of AmountOwed could be lost.
C. Describe a scenario for a nonrepeatable read and a scenario for a phantom read.
NONREPEATABLE READ:
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 31 of 36
2. Next, a Sales Transaction is processed.
3. Transaction T1 reruns the query in 1.
The reads in the two SQL statements are nonrepeatable.
PHANTOM READ:
1. Transaction T1 executes the following SQL statement:
D. Explain how locking could be used to prevent the lost update in your answer to
part B.
E. Is it possible for deadlock to occur between two Acceptance Transactions? Why or why
not? Is it possible for deadlock to occur between two Sales Transactions? Why or why
not? Is it possible for deadlock to occur between an Acceptance Transaction and a
Sales Transaction? Why or why not?
Chapter Six – Database Administration
F. For each of these three types of transaction, describe whether you think optimistic or
pessimistic locking would be better. Explain the reasons for your answer.
G. Suppose James River Jewelry identifies three groups of users: managers, administrative
personnel, and system administrators. Suppose further that managers and
administrative personnel can perform Acceptance Transactions and Sales Transactions,
but only managers can perform Price Adjustment Transactions. Describe processing
rights that you think would be appropriate for this situation. Use Figure 6-17 as an
example.
DATABASE RIGHTS GRANTED
Table
Management
Administrative
System Administrator
OWNER
Read
Insert
Update
Delete
Read
Insert
Update
Grant Rights
Modify Structure
JEWELRY_ITEM
Read
Insert
Update
Delete
Read
Insert
Grant Rights
Modify Structure
ITEM
Read
Insert
Update
Delete
Read
Insert
Update
Grant Rights
Modify Structure
This security system isn’t particularly strong. Because Acceptance Transactions require creating
a new JEWELRY_ITEM row and possibly a new OWNER row, both Managers and Administrative
H. James River Jewelry has developed the following procedure for backup and recovery.
The company backs up the database from the server to a second computer on its
network each night. Once a month, it copies the database to a CD and stores it at a
manager’s house. It keeps paper records of all purchase and sales transactions for an
entire year. If it ever loses its database, it plans to restore it from a backup and
unrecorded transactions. Do you think this backup and recovery program is sufficient for
James River Jewelry? What problems might occur? What alternatives exist? Describe
any changes you think the company should make to this system.
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 33 of 36
once every other day, perhaps. Also, they should store paper records away from the office, if
possible. Storing the CD at a manager’s house is probably a bad idea. What if that manager
becomes dissatisfied with his/her job? It is better to store it in an off-premises facility designed
for such storage.
Recovery via reprocessing is probably fine here. James River is a small business and the nature
of their transactions is such that reprocessing in a different order ought not to matter, but if
they grow then incremental backups via a log would be helpful to avoid reprocessing all the
service requests (which means manually re-entering all the data from paper, etc.).
ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
The Queen Anne Curiosity Shop database design used in Chapter 3 was:
The referential integrity constraints are:
The Queen Anne Curiosity Shop has modified the ITEM and SALE_ITEM tables as
follows:
Chapter Six – Database Administration
sale occurs, the Sale Transaction is run. Every time a SALE_ITEM line is entered, the
input Quantity is subtracted from QuantityOnHand in ITEM, and the ItemPrice is set to
the UnitPrice.
A. Explain why it is important for the changes made by each of these transactions to be
atomic.
B. Describe a scenario in which an update of QuantityOnHand could be lost.
C. Describe a scenario for a nonrepeatable read and a scenario for a phantom read.
NONREPEATABLE READ:
PHANTOM READ:
1. Transaction T1 executes the following SQL statement:
2. Next, a Sale Transaction is processed where a new SALE_ITEM row is created.
3. Transaction T1 reruns the query in 1.
Chapter Six – Database Administration
© 2018 Pearson Education, Inc. Page 35 of 36
The counts in 1 and 3 will be different because of a phantom read.
D. Explain how locking could be used to prevent the lost update in your answer to
part B.
E. Is it possible for deadlock to occur between two Sale Transactions? Why or why not? Is
it possible for deadlock to occur between a Sale Transaction and an Item Quantity
Received Transaction? Why or why not?
F. For each of these three types of transaction, describe whether you think optimistic or
pessimistic locking would be better. Explain the reasons for your answer.
G. Suppose that the Queen Anne Curiosity Shop identifies four groups of users: sales
personnel, managers, administrative personnel, and system administrators. Suppose
further that managers and administrative personnel can perform Item Quantity Received
Transactions, but only managers can perform Item Price Adjustment Transactions.
Describe processing rights that you think would be appropriate for this situation. Use
Figure 6-17 as an example.
Chapter Six – Database Administration
DATABASE RIGHTS GRANTED
Table
Sales
Management
Administrative
System Administrator
SALE
Read
Insert
Update
Delete
Read
Insert
Update
Delete
Read
Grant Rights
Modify Structure
SALE_ITEM
Read
Insert
Update
Delete
Read
Insert
Update
Delete
Read
Grant Rights
Modify Structure
ITEM
Read
Read
Insert
Update
Delete
Read
Insert
Update
Grant Rights
Modify Structure
H. The Queen Anne Curiosity Shop has developed the following procedure for backup and
recovery. The company backs up the entire database from the server to tape every
Saturday night. The tapes are then taken to a safety deposit box at a local bank on the
following Thursday. Printed paper records of all sales are kept for 5 years. If the
database is ever lost, the plan is to restore the database from the last full backup and
reprocess all the sales records. Do you think this backup and recovery program is
sufficient for the Queen Anne Curiosity Shop? What problems might occur? What
alternatives exist? Describe any changes you think the company should make to this
system.