The Queen Anne Curiosity Shop database design used in Chapter 3 was: CUSTOMER (Customer...

The Queen Anne Curiosity Shop database design used in Chapter 3 was:

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email)

EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)

VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)

ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID)

SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)

SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)

The referential integrity constraints are:

VendorID in ITEM must exist in VendorID in VENDOR

CustomerID in SALE must exist in CustomerID in CUSTOMER

EmployeeID in SALE must exist in EmployeeID in EMPLOYEE

SaleID in SALE_ITEM must exist in SaleID in SALE

ItemID in SALE_ITEM must exist in ItemID in ITEM

The Queen Anne Curiosity Shop has modified the ITEM and SALE_ITEM tables as follows:

ITEM (ItemID, ItemDescription, UnitCost, UnitPrice, QuantityOnHand, VendorID)

SALE_ITEM (SaleID, SaleItemID, ItemID, Quantity, ItemPrice, Extended Price)

These changes allow the sales system to handle nonunique items that can be bought and sold in quantity. When new items from vendors arrive at The Queen Anne Curiosity Shop, the office personnel unpack the items, put them in the stockroom, and run an Item Quantity Received Transaction that adds the quantity received to QuantityOnHand. At the same time, another transaction, called an Item Price Adjustment Transaction is run, if necessary, to adjust UnitCost and UnitPrice. Sales may occur at any time, and when a 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.

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?