Suppose that you are a manufacturer of product ABC, which is composed of parts A
ID: 3919759 • Letter: S
Question
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, 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 A, B, and C. The sample database contents are shown in Table P1O.1. 1. Table P10.1 TABLE NAME: PRODUCT PROD CODE PROD QOH PART CODE PART_QOH ABC TABLE NAME: PART 567 98 549 1,205 Given the preceding information, answer Questions a through e. a. How many database requests can you identify for an inventory update for both PRODUCT and PART? b. Using SQL, write each database request you identified in Step a. c. Write the complete transaction(s) d. Write the transaction log, using Table 10.1 as your template. e. Using the transaction log you created in Step d, trace its use in database recovery.Explanation / Answer
a. The answer can be 4 sql statements or 2.
Answers depends on how SQL statements are done.
b.
For 4 SQL statements :
BEGIN TRANSACTION
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘B’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘C’
For 2 SQL statements:
BEGIN TRANSACTION
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
c. For 4 SQL statements:
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘B’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘C’
COMMIT;
For 2 SQL statements:
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
COMMIT;
d.
e. The above table is the template for the problem solution.
It can be explained by taking an example. We can use it as an input segment to the following problem:
Consider this SQL sequence represents the distributed sequence.
BEGIN WORK;
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE + 100
WHERE CUS_NUM='10936';
INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)
VALUES ('285391', '10937', ‘13-MAR-2003’, 102);
INSERT INTO INVLINE(INV_NUM, PROD_CODE, LINE_PRICE)
VALUES ('986391', '1023', 100);
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH - 1
WHERE PROD_CODE = '1023';
COMMIT WORK;
Here, the UPDATE CUSTOMER and the two INSERT statements requires only remote request
capabilities. we also need distributed transaction capability as the entire transaction must access more than one remote DP site.
The last UPDATE PRODUCT statement we created accesses two remote sites its because of the PRODUCT table is divided into 2 fragments located at 2 remote DP sites.
Hence, the whole transaction requires distributed request capability.
I hope it helped!
Please thumbs up.
Ask if any query is there. All the best :-)
TRL ID TRX NUM PREV PTR NEXT PTR OPERATION TABLE ROW ID ATTRIBUTE BEFORE VALUE AFTER VALUE 1 1A3 NULL 2 START START TRANSACTION 2 1A3 1 3 UPDATE PRODUCT 'ABC' PROD_QOH 30 31 3 1A3 2 4 UPDATE PART 'A' PROD_QOH 56 55 4 1A3 3 5 UPDATE PART 'B' PROD_QOH 17 16 5 1A3 4 6 UPDATE PART 'C' PROD_QOH 34 33 6 1A3 5 NULL COMMIT END TRANSACTIONRelated Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.