M6A2:

Introduction to the Activity

Remember all the lab works in this course make up 35% of the total course grade. Each assignment requires a report to be submitted to the appropriate drop box.

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

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

i. SELECT * FROM PRODUCT WHERE PROD_QOH

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 LOCATION TABLES FRAGMENTS CUSTOMER NA PRODUCT PROD A PROD B NA INVOICE INV LINE N/A PROD A LOCATION