The DDBMS Scenario The assignment is based on the contents covered in module 6.
ID: 3828487 • Letter: T
Question
The DDBMS Scenario
The assignment is based on the contents covered in module 6. You will answer questions based on the above DDBMS scenario. You should submit your answers as a Microsoft Word document to the instructor. Please be sure your name is in the file and name your file: yourlastname_yourfirstinitial_module#.docx.
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;
To answer the following questions, remember that the key to each answer is in the number of different data processors that are accessed by each request/transaction. First, identify how many different DP sites are to be accessed by the transaction/request. Next, remember that a distributed request is necessary if a single SQL statement is to access more than one DP site.
TABLES FRAGMENTS CUSTOMER NA PRODUCT PROD A PROD B NA INVOICE INV LINE N/A PROD A LOCATIONExplanation / Answer
a: Distributed Transaction as product is fragmented and is present at site A and B
b:Remote request as Invoice table is at site B
c:Distributed Request as Product is fragmented and is present in site A and B.
d:Remote transaction as customer is in site A.
e: Remote request as customers is in site A
f: Distributed Transaction as product is fragmented and is present at site A and B
g: Remote transaction as Invoice is in site B
h: Remote request as Invoice is at site B
i: Distributed request as product is fragmented and is present at site A and B
j: Distributed Transaction as product is fragmented & other tables are present at site A and B
k: Remote transaction as customer is present at site A
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.