Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 425

Case Questions
Marcia's Dry Cleaning 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 competi-
tion 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:
CUSTOMER ( CustomerID , FirstName, LastName, Phone, Email)
INVOICE ( InvoiceNumber , CustomerID , DateIn, DateOut, Subtotal, Tax, TotalAmount)
INVOICE_ITEM ( InvoiceNumber , ItemNumber , ServiceID , Quantity, UnitPrice,
ExtendedPrice)
SERVICE ( ServiceID , ServiceDescription, UnitPrice)
Assume that all relationships have been defined, as implied by the foreign keys in this table list,
list, and that the appropriate referential integrity constraints are in place.
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.
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 Figure 9-1 as a guide, describe the nature of database administration activi-
ties at Marcia's. As an aggressive consultant, keep in mind that you can recommend
yourself for performing some of the DBA functions.
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 9-15 as an example. Create a
table like that in Figure 9-14. Don't forget to include yourself.
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
customer orders and order 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.
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?