Assignment: "Database Transactions -- I"
Suppose that you are a manufacturer of product XYZ, which is composed of parts X, Y, and Z. Each time a new product XYZ is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts X, Y, and Z. The sample database contents are shown in the two tables below.
Table name: PRODUCT
|
PROD_CODE
|
PROD_QOH
|
|
XYZ
|
205
|
Table name: PART
|
PART_CODE
|
PART_QOH
|
|
X
|
150
|
|
Y
|
300
|
|
Z
|
400
|
Given this information, answer Questions 1-4.
1. How many database requests can you identify for an inventory update for both PRODUCT and PART?
2. Using SQL, write each database request you identified in step 1.
3. Write the complete transaction(s).
4. Write the transaction log, using the table below as your template.
Here, we assume that the product XYZ has PROD_QOH = 205 at the start of the transaction and the transaction represents the addition of one new product. We also assume that the PART components X, Y and Z have PROD_QOH equal to 150, 300, and 400 respectively.