For each of the following SQL statement, refer to the above DDBMS scenario and s
ID: 3813675 • Letter: F
Question
For each of the following SQL statement, refer to the above DDBMS scenario and specify the type of operation it is (remote request, remote transaction, distributed transaction, or distributed request).
At Location A:
a. BEGIN WORK;
INSERT PRODUCT(PROD_CODE, PROD_NAME, PROD_QOH)
VALUES ('1265','Drill Bit', 30);
COMMIT WORK;
b. SELECT INV_TOTAL
FROM INVOICE
WHERE INV_TOTAL > 500;
c. SELECT * FROM PRODUCT
WHERE PROD_QOH > 20;
At Location B:
d. BEGIN WORK;
INSERT CUSTOMER(CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_BAL)
VALUES ('12345','Miss Prince', '3155 Heaven ave', 5.99);
COMMIT WORK;
e. SELECT CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE INV_TOTAL < 2000 AND CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;
f. BEGIN WORK;
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH - 10
WHERE PROD_CODE ='1300';
COMMIT WORK;
At site C:
g. BEGIN WORK;
INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)
VALUES ('986434', '24352', ‘22-AUG-2013’, 2.00);
COMMIT WORK;
h. SELECT * FROM INVOICE WHERE INV_TOTAL < 2000;
i. SELECT * FROM PRODUCT WHERE PROD_QOH < 25;
j. BEGIN WORK;
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE + 200
WHERE CUS_NUM='12934';
INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)
VALUES ('867541', '14678', ‘15-DEC-2013’, 200);
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH – 3
WHERE PROD_CODE = '1265';
COMMIT WORK;
k. BEGIN WORK;
INSERT CUSTOMER(CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_BAL)
VALUES ('24352','John Brow', '1500 Main St', 0.00);
COMMIT WORK;
TABLES FRAGMENTS LOCATION CUSTOMER NNA. PROD A PRODUCT PROD B INVOICE NNA. INV LINE NA. OM SiteExplanation / Answer
A transaction is a set of queries tied together such that if one query fails, the entire set of queries are rolled back to a prequery state if the situation dictates. Whereas, a request/query is a single statement in SQL's data manipulation language: typically one of SELECT, INSERT, UPDATE or DELETE.
A transaction or request is distributed if it is executed on a table which is distributed across different locations. Eg. Part (a) where Product table is fragmented.
A transaction or request is remote if it is executed on the different machine. For eg. Part (b)
With the above understanding:
At Location A
a. distributed transaction
b. remote request
c. distributed request
At Location B
d. remote transaction
e. remote request
f. remote transaction and distributed transaction
At Site C
g. remote transaction
h. remote request
i. remote request and distributed request
j. remote transaction and distributed transaction
k. remote transaction
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.