B. The Queen Anne Curiosity Shop database design used in Chapter 3 was: ITEM (It
ID: 3829481 • Letter: B
Question
B. The Queen Anne Curiosity Shop database design used in Chapter 3 was: ITEM (ItemNumber, Description, Cost, ListPrice, QuantityOnHand) CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email) EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email) VENDOR VendorlD, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email) ITEM (ItemID, ItemDescription, PurchaseDate, Itemcost, ItemPrice, VendorID) SALE (SaleID, CustomerID, EmployeelD, SaleDate, SubTotal, Tax, Total) SALE ITEM (SaleID, SaleltemID, ItemID, ItemPrice) The referential integrity constraints are: VendorID in ITEM must exist in VendorID in VENDOR Customer ID in SALE must exist in CustomerID in CUSTOMER EmployeelD in SALE must exist in EmployeelD in EMPLOYEE SaleID in SALE ITEM must exist in SalelD 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, ltemDescription, UnitCost, UnitPrice, QuantityonHand, vendorID) SALELITEM (SaleID, SaleltemID, ItemID, Quantity, ItemPrice, ExtendedPrice) 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 anytime, 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.Explanation / Answer
Deadlock cannot occur between two sale transactions as each transaction will execute an update query to update the quantityonhand field by applying an exclusive lock on the item table and other transaction will wait once the previous transaction is committed. So all sales transaction will be serialized by the database to prevent deadlock.
Deadlock cannot occur between sales transaction and item quantity received transaction as once the sales transaction begins, it will use the exclusive lock on item table to update quantityonhand by subtracting the quantity, then updates the itemprice to unitprice. Once it is committed then the item quantity received transaction will take place and it doesn't have to perform any action on the sale item table. So both are serializable and hence deadlock free.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.