Question

Garden Glory Project Questions
The following Garden Glory database design is used in Chapter 3:
OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)
PROPERTY (PropertyID, PropertyName, Street, City, State, Zip, OwnerID)
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
SERVICE (PropertyID, EmployeeID, SeviceDate, HoursWorked)
The referential integrity constraints are:
OwnerID in PROPERTY must exist in OwnerID in OWNER
PropertyID in SERVICE must exist in PropertyID in PROPERTY
EmployeeID in SERVICE must exist in EmployeeID in EMPLOYEE
Garden Glory has modified the EMPLOYEE table by adding a TotalHoursWorked
column:
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel,
TotalHoursWorked)
The office personnel at Garden Glory use a database application to record services and
related data changes in this database. For a new service, the service-recording application
reads a row from the PROPERTY table to get the PropertyID. It then creates a new row in
SERVICE and updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked
value in the new SERVICE record to TotalHoursWorked. This operation is referred to as
a Service Update Transaction.
In some cases, the employee record does not exist before the service is recorded. In
such a case, a new EMPLOYEE row is created, and then the service is recorded. This is
called a Service Update for New Employee Transaction.
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.
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?
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-16 as an example.
What problems might this security system have?
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