The Queens Anne Curiosity Shop database design used in Chapter 3: CUSTOMER (Cust

Question

The Queens Anne Curiosity Shop database design used in Chapter 3:

CUSTOMER (CustomerID, LastName, FirstName, Address City, State, Zip, Phone, EmailAddress)

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

VENDOR (VendorID, CompanyName, ContactLastName,ContactFirstName,Address,City,State,Zip, Phone, Fax, EmailAddress)

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, Unit Cost,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 The Queen Anne Curiosity Shop, the officer personnel unpack the items, put them in the stockroom, and run an ItemQuantity Received Transaction that adds the quantity receive 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.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?

D.For each of the three types of transaction, describe whether you think optimistic or pessimistic locking would be better. Explain the reasons for your answer.

E.The Queen Anne Curiosity Shop has developed the following procedure for backup and recovery. The company backs up the entire database from the server to a tape every Saturday night. The tapes are taken to a safety deposit box at a local bank on the following Thursday. Printed paper records of all sales are kept for 5 years. If the database is ever lost, the plan is to restore the database from the last full backup and reprocess all the sales records. Do you think this backup and recovery program is sufficient for the Queen Anne Curiosity Shop? What problems might occur? What alternatives exist? Describe any changes you think the company should make to this system?

Explanation / Answer

A. To avoid inconsistency of data (here, the number of items), any transaction must be treated as an atomic unit, that is, either all of its operations should be executed or none.

B. Consider a situation where ItemQuantity Received Transaction T1 and Sale Transaction T2 are run simultaneously (as sale can occur any time).

                                 T1                                                          T2

                         //some computation                                 QuantityOnHand = QuantityOnHand - Quantity ;

                       read QuantityOnHand                                        //some computation

          QuantityOnHand = QuantityOnHand +10 ;            update;

      //some computation                                //some computation

                            update;                                                      update;

                           commit                                                         commit

Here, the changes made by T2 are silently overwritten by the update performed by T1. This is known as the lost update problem.

C. Deadlock between 2 Sales transaction can occur iff if it is waiting for an event that only it can cause. Generally, deadlock occur when the other process is itself waiting for a resource held by the first then deadlock occurs.

Deadlock between Sales and ItemQuantity Received transaction is possible since, both are using QuantityOnHand entity. So, if proper locks are not applied in proper sequence, then deadlock can occur for sure.

                     T1                                                                   T2

              Lock(QuanityOnHand)                                   Lock(Item)

                Lock(Item)                                                 Lock(QuanityOnHand)             

D.   Sales ----- Optimistic , Optimistic locking takes the “optimistic” view that data conflicts due to concurrent edits occur rarely, so it’s more important to allow concurrent edits. Sale can occur any time.

       ItemQuantity Received ---------- Optimistic , Optimistic locking takes the “optimistic” view that data conflicts due to concurrent edits occur rarely, so it’s more important to allow concurrent edits. It can run simultaneously.

       Item Price Adjustment -------- Optimistic , Optimistic locking takes the “optimistic” view that data conflicts due to concurrent edits occur rarely, so it’s more important to allow concurrent edits. It can run simultaneously.

E. This backup and recovery program is not sufficient for the Queen Anne Curiosity Shop. It can take a lot of time to reprocess all the sales records. One can use checkpointing. Backup to cloud can be performed.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.