SQL, From this case I need answer for letter E and H only E.- Is it possible for
ID: 3607547 • Letter: S
Question
SQL, From this case I need answer for letter E and H only
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?
H.-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.
N ANNE CURIOSITY SHOP PROJECT QUESTIONS The Queen Anne Curiosity Shop database design used in Chapter 3 was CUSTOMER (CustomerlD, LastName, FirstName, Address, City, State, ZIP Phone, Email) EMPLOYEE (EmployeelD, LastName, First.Name, Phone, Email) VENDOR (VendorlD, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email) ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, Item Price, VendorID) SALE (SaleD, CustomerlD, EmployeelD, SaleDate, SubTotal, Tax, Total) SALE ITEM SaleID, SaleltemID, ItemlD, ItemPrice The referential integrity constraints are: VendorID in ITEM must exist in VendorID in VENDOR CustomerlD in SALE must exist in CustomerID in CUSTOMER EmploycelD in SALE must exist in EmployeelD in EMPLOYEE SalelD in SALE ITEM must exist in SalelD in SALD temID 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, Unit Price, QuantityOnHand, VendorlD) SALE ITEM (SalelD, SaleltemID, ItemD, 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 Iterm 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 UnitPriceExplanation / Answer
E.
Deadlock will not occur between two sales transactions. Because two are different with the different saleID 's , though these two transactions needs to update the same table. The first transaction which acquires the lock on table will release after it succesfully updates and then the second transaction. There is no possibility to occur the deadlock.
No even between the sale transaction and the ItemQuantity received transaction deadlock not occur because if the ItemQuantityReceived is acquired lock first then it will update the stock and then exits then the sale transaction will execute and decreases the quantity on hand according to the sale and vice versa. Because for both of these transactions are different with each other but trying to access the same attribute so one after other doing updations poses no deadlocks.
H.
This backup strategy doesn't satisfy or sufficient for the Queen Anne Curiosity Shop or doesn't meet its requirements. Because when the system is lost it will get only the backup performed on the previous saturday night which means the sales records untill the last saturday only. i.e. if the system is lost on wednesday the sales transactions from sunday to wednesday is lost even when we do the backup this backup contains the sales transactions untill the saturday night only. The transactions from after that full backup and before the system lost will not be stored any where this causes data loss and system inconsistency.
To avoid this we need to setup new backup startegy which is Incremental backup strategy to be implemented along side with the full backup strategy.
Incremental backup means it takes backups of those files which are updated only those files after the last full backup means that all the transactions from sunday to before the system lost will be backed up by incremental backup. So when we restore the backup we need to give both the full backup and these incremental backup then all the data will be restored to the system.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.