Chapter Nine – Managing Multiuser Databases
Page 9-15
❖ ANSWERS TO PROJECT QUESTIONS
9.56 Visit www.oracle.com and search for “Oracle Security Guidelines.” Read articles at three
of the links that you find and summarize them. How does the information you find
compare with that in Figure 9-15?
Answers here will vary from time to time as the articles are updated, deleted, and added to. For
9.57 Visit www.msdn.microsoft.com and search for “SQL Server Security Guidelines.” Read
articles at three of the links that you find and summarize them. How does the
information you find compare with that in Figure 9-15?
Answers here will vary from time to time as the articles are updated, deleted, and added to. For
9.58 Visit www.mysql.com and search for “MySQL Security Guidelines.” Read articles at
three of the links that you find and summarize them. How does the information you find
compare with that in Figure 9-15?
Answers here will vary from time to time as the articles are updated, deleted, and added to. For
9.59 Use Google (www.google.com) or another search engine and search the Web for
“Database Security Guidelines.” Read articles at three of the links that you find and
summarize them. How does the information you find compare with that in Figure 9-15?
Answers here will vary from time to time as the articles are updated, deleted, and added to. For
9.60 Search the Web for “distributed two–phase locking.” Find a tutorial on that topic and
explain, in general terms, how this locking algorithm works.
Answers here will vary from time to time as the articles are updated, deleted, and added to. For
Chapter Nine – Managing Multiuser Databases
Page 9-16
9.61 Answer the following questions for the View Ridge Gallery VRG database discussed in
Chapter 7 with the tables shown in Figures 7-13 and 7-14 and the data shown in 7-15.
A. Suppose that you are developing a stored procedure to record an artist who has
never been in the gallery before, a work for that artist, and a row in the TRANS
table to record the date acquired and acquisition price. How will you declare the
boundaries of the transaction? What transaction isolation level will you use?
B. Suppose that you are writing a stored procedure to change values in the
CUSTOMER table. What transaction isolation level will you use?
In this case we would use repeatable reads isolation level. Since we are updating but not
C. Suppose that you are writing a stored procedure to record a customer’s
purchase. Assume that the customer’s data are new. How will you declare the
boundaries of the transaction? What isolation level will you use?
The boundaries of the transaction would include everything from the initial insert of in
D. Suppose that you are writing a stored procedure to check the validity of the
intersection table. Specifically, for each customer, your procedure should read
the customer’s transaction and determine the artist of that work. Given the artist,
your procedure should then check to ensure that an interest has been declared
for that artist in the intersection table. If there is no such intersection row, your
procedure should create one. How will you set the boundaries of your
transaction? What isolation level will you use? What cursor types (if any) will
you use?
Transaction boundaries will be from the point of reading the customer data to the
Chapter Nine – Managing Multiuser Databases
Page 9-17
❖ MARCIA’S DRY CLEANERS CASE QUESTIONS
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 e-mail. Suppose
that Marcia has hired you as a database consultant to develop a database for Marcia’s Dry
Cleaning that has the following 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:
1. The need for database administration.
This part of the memo should summarize the issues discussed on page 425–427 and
briefly discuss the tasks in Figure 9-1.
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.
The database administration tasks should probably be shared among the owners, the shift
3. Using Figure 9-1 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.
Task
Owners
Shift Manager
Consultant
Managing database
structure
Ensure doc
exists
Design,
implement, and
document
Chapter Nine – Managing Multiuser Databases
Page 9-18
Developing database
security
Understand
vulnerability
Ensure
procedures are
Set up DBMS and
application
B. For the employees described in question A, define users, groups, and permissions on
data in these four tables. Use the security scheme shown in Figure 9-15 as an example.
Again, don’t forget to include yourself.
The following table identifies five groups (with the obvious personnel assigned to them) and
processing rights over the four tables. In keeping with the philosophy of assigning minimum
rights to perform their tasks, this is a restrictive set of permissions and the employees may rail at
it. Given that Marcia’s Dry Cleaning is a small company, there may be a need to broaden these
rights to trusted employees. One course of action is to start restrictive and broaden from there.
It’s easier to give new permissions than to take existing ones away!
CUSTOMER
ORDER
ORDER_ITEM
SERVICE
Owners
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Change
Query
Change
Query
Change
Query
Salesclerks
Query
Insert
Change
Query
Insert
Change
Query
Query
structure
Modify
structure
C. Suppose that you are writing a stored procedure 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 stored procedure that records new or modifies existing
followed
security
followed
document
procedures and
train users
Cleaning
is kept up to
other
documentation
Chapter Nine – Managing Multiuser Databases
Page 9-19
1. Give an example of a dirty read, a nonrepeatable read, and a phantom read
among this group of stored procedures.
StoredProcedure01: This procedure should only be using the SERVICE table to insert
new SERVICEs.
A dirty read would occur if StoredProcedure02 read a newly inserted SERVICE before
the transaction was committed.
2. What concurrency control measures are appropriate for the stored procedure that
you are creating?
StoredProcedure01 is simply inserting rows into the SERVICE table. We are not doing
3. What concurrency control measures are appropriate for the two other stored
procedures?
StoredProcedure03 is simply inserting rows into the CUSTOMER table. We are not
Chapter Nine – Managing Multiuser Databases
Page 9-20
❖ THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
Assume that Morgan has hired you as a database consultant to develop an operational
database having the same tables described at the end of Chapter 7:
CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword,
City, State, ZIP, Phone, ReferredBy)
The referential integrity constraints are:
ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER
Supervisor in EMPLOYEE must exist in EmployeeID in EMPLOYEE
Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, ItemID of ITEM, SaleID
of SALE, and SaleItemID of SALE_ITEM are all surrogate keys with values as follows:
CustomerID Start at 1 Increment by 1
Chapter Nine – Managing Multiuser Databases
Page 9-21
SaleID Start at 1 Increment by 1
A vendor may be an individual or a company. If the vendor is an individual, the CompanyName
A. Assume that at The Queen Anne Curiosity Shop there are two owners, an office
administrator, one full-time salesperson, and two part-time salespeople. The two owners
and the office administrator want to process data in all tables. Additionally, the full-time
salesperson can enter purchase and sales data. The part-time employees can only read
sales data. Prepare a three-to-five page memo for the owner that addresses the
following issues:
1. The need for database administration at The Queen Anne Curiosity Shop.
2. Your recommendation as to who should serve as database administrator.
Assume that The Queen Anne Curiosity Shop is not sufficiently large that it
needs or can afford a full-time database administrator.
The database administration tasks should probably be shared among the owners, the
Chapter Nine – Managing Multiuser Databases
Page 9-22
3. Using Figure 9-1 as a guide, describe the nature of database administration
activities at The Queen Anne Curiosity Shop. As an aggressive consultant, keep
in mind that you can recommend yourself for performing some of the DBA
functions.
Task
Owners
Office Admin
Sales Staff
Consultant
Managing database
structure
Ensure doc
exists
Design, implement, and
document database
structure
Controlling concurrent
processing
Establish
policies
Define transactions and
set up DBMS
concurrency
Managing the DBMS
Understand the
nature of this
activity
Report
performance
problems
Respond to
performance problems,
watch for new versions
of potential use to
Queen Anne
repository
importance
documentation
is kept up to
other documentation
responsibilities
Developing database
security
Understand
vulnerability
Ensure
procedures are
followed
Set up DBMS and
application security
recovery
vulnerability
procedures are
followed
procedures, document
procedures and train
users
Chapter Nine – Managing Multiuser Databases
Page 9-23
B. For the employees described in question A, define users, groups, and permissions on
data in these six tables. Use the security scheme shown in Figure 9-15 as an example.
Create a table like that in Figure 9-14. Don’t forget to include yourself.
The following table identifies five groups (with the obvious personnel assigned to them) and
processing rights over the four tables. In keeping with the philosophy of assigning minimum
CUSTOMER
EMPLOYEE
SALE
SALE_ITEM
Owner
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Query
Query
Query
Query
Full time
Query
Query
Insert
Change
Query
Insert
Change
Query
Sales
Query
Restricted Query
Admin
Grant rights,
modify structure
Grant rights,
modify structure
Grant rights,
modify structure
Grant rights, modify
structure
VENDOR
ITEM
Owner
Insert
Change
Query
Insert
Change
Query
Office
Admin
Insert
Change
Query
Insert
Change
Query
Sales
Query
Query
Sales
Query
Query
Admin
Grant rights,
modify structure
Grant rights,
modify structure
Chapter Nine – Managing Multiuser Databases
Page 9-24
C. Suppose that you are writing a stored procedure to record new purchases. Suppose that
you know that while your procedure is running, another stored procedure that records
new customer sales and sale line items can also be running. Additionally, suppose that a
third stored procedure 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 stored procedures.
It appears that reads are performed in both the first and the second procedures. The first
procedure deals with inserting new records into the ITEM while reading VENDOR. The
second procedure reads CUSTOMER, EMPLOYEE, and ITEM. The third transaction
inserts or modifies CUSTOMER rows only.
A dirty read would occur if StoreProcedure02 read a newly inserted ITEM before the
transaction was committed.
2. What concurrency control measures are appropriate for the stored procedure that
you are creating?
StoredProcedure01 is reading the VENDOR table and inserting data into ITEM. We will
3. What concurrency control measures are appropriate for the two other stored
procedures?
StoredProcedure03 is simply inserting rows into the CUSTOMER table. We are not
Chapter Nine – Managing Multiuser Databases
❖ MORGAN IMPORTING PROJECT QUESTIONS
Assume that Morgan has hired you as a database consultant to develop an operational
database having the same tables described at the end of Chapter 7 (note that STORE uses the
surrogate key StoreID):
EMPLOYEE (EmployeeID, LastName, FirstName, Department, Position, Supervisor,
OfficePhone, OfficeFax, EmailAddress)
SHIPMENT_ITEM (ShipmentID, ShipmentItemID, PurchaseItemID, InsuredValue)
SHIPMENT_RECEIPT (ReceiptNumber, ShipmentID, PurchaseItemID,
A. Assume that Morgan personnel are the owner (Morgan), an office administrator, one full-
time salesperson, and two part-time salespeople. Morgan and the office administrator
want to process data in all tables. Additionally, the full-time salesperson can enter
purchase and shipment data. The part-time employees can only read shipment data;
they are not allowed to see InsuredValue, however. Prepare a three-to-five page memo
for the owner that addresses the following issues:
1. The need for database administration at Morgan.
This part of the memo should summarize the issues discussed on page 425-427 and
Chapter Nine – Managing Multiuser Databases
Page 9-26
2. Your recommendation as to who should serve as database administrator.
Assume that Morgan is not sufficiently large that it needs or can afford a full-time
database administrator.
The database administrations tasks should probably be shared among the owner
3. Using Figure 9-1 as a guide, describe the nature of database administration
activities at Morgan. As an aggressive consultant, keep in mind that you can
recommend yourself for performing some of the DBA functions.
Task
Owners
Office Admin
Sales Staff
Consultant
Managing database
structure
Ensure doc
exists
Design, implement, and
document database
structure,
Controlling concurrent
processing
Establish
policies
Define transactions and
set up DBMS
concurrency
responsibilities
followed
Providing for database
recovery
Understand
vulnerability
Ensure
procedures are
followed
Set up system and
procedures, document
procedures and train
users
nature of this
activity
performance
problems
performance problems,
watch for new versions
of potential use to
Morgan
Chapter Nine – Managing Multiuser Databases
B. For the employees described in question A, define users, groups, and permissions on
data in these five (seven) tables. Use the security scheme shown in Figure 9-15 as an
example. Create a table like that of 9-14. Don’t forget to include yourself.
The following table identifies five groups (with the obvious personnel assigned to them) and
processing rights over the tables. In keeping with the philosophy of assigning minimum rights to
STORE
ITEM
SHIPMENT
SHIPMENT
_ITEM
SHIPPER
Owner
Query
Query
Query
Query
Query
Admin
Change
Query
Change
Query
Change
Query
Change
Query
Change
Query
Full time
Sales
Query
Insert
Change
Query
Insert
Change
Query
Insert
Change
Query
Insert
Query
Part-time
Query
Query
Restricted Query
Query
Insert
Change
Insert
Change
Insert
Change
Insert
Change
Insert
Change
Sales
System
Admin
Grant
rights,
modify
structure
Grant rights,
modify
structure
Grant rights,
modify structure
Grant rights, modify
structure
Grant rights,
modify structure
EMPLOYEE
SHIPMENT_RECEIPT
Owner
Insert
Change
Query
Insert
Change
Query
Office
Admin
Insert
Change
Query
Insert
Change
Query
Full time
Sales
Query
Insert
Change
Query
Part-time
Sales
Restricted
Query
Query
System
Admin
Grant rights,
modify
structure
Grant rights, modify
structure
Chapter Nine – Managing Multiuser Databases
Page 9-28
C. Suppose that you are writing a stored procedure to record new purchases. Suppose that
you know that while your procedure is running, another stored procedure that records
shipment data can be running, and a third stored procedure that updates shipper data
can also be running.
1. Give an example of a dirty read, a nonrepeatable read, and a phantom read
among this group of stored procedures.
StoredProcedure03: This procedure should only be inserting new rows into the SHIPPER
table.
It appears that reads are performed in both the first and the second procedures. The first
procedure deals with inserting new records into the PURCHASE_ITEM while reading
STORE. The second procedure reads EMPLOYEE, PURCHASE_ITEM and SHIPPER.
The third transaction inserts or modifies SHIPPER rows only.
2. What concurrency control measures are appropriate for the stored procedure that
you are creating?
3. What concurrency control measures are appropriate for the two other stored
procedures?
StoredProcedure03 is simply inserting rows into the SHIPPER table. We are not doing