You are allowed to complete this assignment in teams or pairs if you wish (optio

Question

You are allowed to complete this assignment in teams or pairs if you wish (optional). If you do so, clearly state the names of all contributors. Answer all questions within this Word document.

Name(s):

Database Administration: Part 1 (20 pts)

Background: Bev’s Boutique is a small, local business that sells hand-made products in-store only (no online sales). The database they use as part of their custom-built point-of-sale system is designed as follows:

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, UnitCost, UnitPrice, QuantityOnHand, VendorID)

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

SALE_ITEM (SaleID, SaleItemID, ItemID, Quantity, ItemPrice, ExtendedPrice)

Note: SALE_ITEM represents a line item on a receipt/invoice. It shows details about items sold in a specific sale—namely, the quantity sold, the price sold (sometimes this is different from the UnitPrice in the ITEM table), and the extended price (quantity x price). Essentially, this is an association table between SALE and ITEM to track information about specific items sold in a specific sale.

Some common transactions at Bev’s (each is independent from the other two):

When new items from vendors arrive, 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. If the item has never been stocked before, the transaction also adds the item to the ITEM table in addition to adding the correct QuantityOnHand.

When item pricing needs to be changed, another transaction called an Item Price Adjustment Transaction is run to adjust UnitCost and UnitPrice.

Sales may occur at any time, and when a sale occurs, the Sale Transaction is run. A new record is added to the SALE table, and every time a SALE_ITEM line is entered, the input Quantity is subtracted from QuantityOnHand in ITEM and then the ItemPrice is set to the UnitPrice.

(Incidentally, these transactions are run through a database application, such as sales software, an Access Form, or a cash register. The applications then read/insert/update/delete data in the database. For the sake of the assignment, we are just thinking about what happens at the database level, even if employees aren’t directly editing the database.)

Part 1: Concurrency Issues in the Database

Considering that these various transactions use some of the same tables and fields in the database, explain why it is important for the changes made by each of these transactions to be atomic. (2 pts)

Answer:

Describe a scenario in which an update of QuantityOnHand could be lost. (See the example in the textbook and apply it to Bev’s) (4 pts)

Answer:

Explain how locking could be used to prevent the lost update in your answer to Q2.
(4 pts)

Answer:

Assume you are taking a “pessimistic” approach and lock entire tables needed for a transaction. You lock each table before you access it, and you don’t release all locks until the end of a transaction. Is it possible for deadlock to occur between two Sale Transactions? (2 pts) Why or why not? (2 pts)

Answer:

Assume you are taking a “pessimistic” approach and lock entire tables needed for a transaction. You lock each table before you access it, and you don’t release all locks until the end of a transaction. Is it possible for deadlock to occur between a Sale Transaction and an Item Quantity Received Transaction? (2 pts) Why or why not? (2 pts)

Answer:

Considering the three transactions and your answers to previous questions, describe whether you think optimistic or pessimistic locking would be better. Explain the reasons for your answer. (2 pts)

Answer:

Explanation / Answer

Part 1 It is very much important for the transactions to access database but with maintaining atomicity. As concurrency help us in multiprocessing at the same time using shared data but if atomicity is not followed then values can become false as some transaction can take previous values if other transaction did not update the table timely. if we foolow atomicity then cant be false or incomplete.

Part 2 when  Item Quantity Received Transaction and Sale Transaction are done then there are chances of quantityonhand values will be updated by both transactions but if concurrency control not done properly then values can become false as sharing of same data is done.

Part 3 Locking is the best technique to do concurrency control. If any transaction wants data item to write then it can apply exclusive lock on all the items it wants else in case of read items a shared lock can be use as values dont change so chances of data loss or false values.

Part 4 No there are no chance of deadlock as in pessimistic locking system already assume the worst case and lock all the tables for one transaction and unlock when it successfully exit.

Part 5 again no deadlock because after completion of transaction all locks will be released and other transactions can use the data.

Part 6 Pessimistic approach is best as it already assume the worst cases so there is less chances of failure whereas Optimistic never lock in advance and sometimes it can lead to deadlock

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

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