University:
California State University, Northridge
Course:
COMP 440 | Database Design
Academic year:

2024
Views:

167

Pages:

3
Author:

Riley K.

a) There are two database requests for an inventory update: - Update the PRODUCT table to increase the quantity on hand (PROD_QOH) of product ABC. - Update the PART table to decrease the quantity on hand (PART_QOH) of parts A, B, and C. b) UPDATE PRODUCT SET PROD_QOH = PROD_QOH + 1 WHERE PROD_CODE = 'ABC'; UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE IN ('A', 'B', 'C'); c) BEGIN TRANSACTION; UPDATE PRODUCT SET PROD_QOH = PROD_QOH + 1 WHERE PROD_CODE = 'ABC'; UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE IN ('A', 'B', 'C'); COMMIT; d) Transaction Log Transaction Table Operation Key Value Before Value After 1 PRODUCT UPDATE ABC 1205 1206 1 PART UPDATE A 567 566 1 PART UPDATE B 98 97 1 PART UPDATE C 549 548 e) In case of a system failure, the transaction log can be used to recover the database to its last consistent state. 1. If the system fails before the transaction is committed, the changes made by the transaction are rolled back using the "Value Before" column in the transaction log. 2. If the system fails after the transaction is committed, the changes made by the transaction are reapplied using the "Value After" column in the transaction log.