B. The Queen Anne Curiosity Shop database design used in Chapter 3 was: ITEM (It
ID: 3717222 • 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 (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, 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.
1. Explain why it is important for the changes made by each of these transactions to be atomic.
2. 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 a Item Quantity Received Transaction? Why or why not? Discuss with your group and answer.
3. Suppose that the Queen Anne Curiosity Shop identifies four groups of users: sales personnel, managers, administrative personnel, and system administrators. Suppose further that managers and administrative personnel can perform Item Quantity Received Transactions, but only managers can perform Item Price Adjustment Transactions.
Explanation / Answer
The Queen Anne Curiosity Shop – database design
Atomicity should be highly maintained in the sales transaction. Because it follows the ACID rule property to maintain the efficiency of the database. Basically it’s defined as “all or none”. Either all sale transactions/steps execute successfully or fail as a single unit and none of them should be treated as executed and the system should be returned to its original state.
Yes, Deadlock is possible to occur in the sales transaction. For instance: If two different customers’ orders for same item an also same quantity available which is in quantityonhand, deadlock situation may occur To avoid this FCFS (First Come First Serve) algorithm should be used to prevent these kind off deadlocks or errors.
Some possible changes of error are there between sale transaction and item quantity received. Suppose if the sales personnel receives the order for sale without checking the item availability (at worst case) deadlock may arise.
In this way of separating departments will be so helpful and prevent the risk of errors. There are will low possibility of deadlock were precautions and system are monetarized.
Related Explanations:
Atomicity
Atomicity is a rule of Transaction feature followed by almost all Relational Database Systems (RDBMS).
In order to perform a Transaction in a database system and to make sure it works without any issues, there are few rules a Database Transaction should follow. These rules are the standards across all Relational Database systems (RDBMS) and are called ACID rules.
Atomicity is a part of ACID rules that stand for stands for Atomicity, Consistency, Isolation and Durability.
* A: Atomicity states that every Transaction should be atomic in nature. A Transaction in a Relational Database can contain either a single SQL statement or multiple SQL statements. Thus by Atomic Transaction it means “all or none”. Either all SQL statements/steps execute successfully in a transaction, or fail as a single unit and none of them should be treated as executed and the system should be returned to its original state.
For example: If account-A & account-B both having $2000 balance, you have to transfer $1000 from account-A to account-B, this will involves 2 steps. First withdrawal from account-A, and Second deposit in account-B. Thus, both the steps should be treated as single or atomic unit and at the end account-A should have $1000 & account-B should have $3000 balance. If in case after First step the system fails or any error occurs then first step should also be rolled-back and $1000 withdrawn from account-A should be re-deposited to it, maintaining $2000 back in both the accounts. Thus there should be no intermediate state where account-A has $1000 and account-B still has $2000 balance.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.