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

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.
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?
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, Phone, Fax, EmailAddress)
STORE ( StoreID , StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
ITEM ( ItemID , StoreName , PurchasingAgentID , PurchaseDate, ItemDescription,
Category, PriceUSD)
SHIPPER ( ShipperID , ShipperName, Phone, Fax, EmailAddress, Contact)
SHIPMENT ( ShipmentID , ShipperID , PurchasingAgentID , ShipperInvoiceNumber, Origin,
Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
SHIPMENT_ITEM ( ShipmentID , ShipmentItemID , ItemID , InsuredValue)
SHIPMENT_RECEIPT ( ReceiptNumber , ShipmentID, ItemID, ReceivingAgentID ,
ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
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 admin-
istrator 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.
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.
3. Using Figure 9-1 as a guide, describe the nature of database administration activi-
ties at Morgan. 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 five 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 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.
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?