1. Specify the number of operations the database must support to perform the query. 2. State what site(s) will be accessed in performing the query. 3. State what types of operations are included in performing the query. For the type: i. Use the following types listed in (a) – (d) below: a. Remote request b. Distributed request. c. Remote transaction d. Distributed transaction ii. Use the table below as a guide also: Use the following summary: Number of Distribution Points Operation Type 1 > 1 Request Remote Distributed Transaction Remote Distributed TABLES FRAGMENTS LOCATION CUSTOMER N/A A PRODUCT PROD_A A PROD_B B INVOICE N/A B INV_LINE N/A B Queries to be analyzed at the sites named: At Site C: a. SELECT * FROM CUSTOMER; b. SELECT * FROM INVOICE WHERE INV_TOTAL < 1000; c. SELECT * FROM PRODUCT WHERE PROD_QOH < 10; d. BEGIN WORK; INSERT CUSTOMER(CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_BAL) VALUES ('34210','Victor Ephanor', '123 Main St', 0.00); INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL) VALUES ('986434', '34210', ‘10-AUG-2018’, 2.00); COMMIT WORK; e. 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 ('986391', '10936', ‘15-FEB-2018’, 100); 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; At Site A: f. SELECT CUS_NUM, CUS_NAME, INV_TOTAL FROM CUSTOMER, INVOICE WHERE CUSTOMER.CUS_NUM = INVOICE.CUS_NUM; g. SELECT * FROM INVOICE WHERE INV_TOTAL > 1000; h. SELECT * FROM PRODUCT WHERE PROD_QOH < 10; At Site B: i. SELECT CUS_NAME, INV_TOTAL FROM CUSTOMER, INVOICE WHERE INV_TOTAL > 1000 AND CUSTOMER.CUS_NUM = INVOICE.CUS_NUM; j. SELECT * FROM PRODUCT WHERE PROD_QOH < 10;
1. Specify the number of operations the
2. State what site(s) will be accessed in performing the query.
3. State what types of operations are included in performing the query. For the type:
i. Use the following types listed in (a) – (d) below:
a. Remote request
b. Distributed request.
c. Remote transaction
d. Distributed transaction
ii. Use the table below as a guide also:
Use the following summary:
Number of Distribution Points
Operation Type 1 > 1
Request Remote Distributed
Transaction Remote Distributed
TABLES FRAGMENTS LOCATION
CUSTOMER N/A A
PRODUCT PROD_A A
PROD_B B
INVOICE N/A B
INV_LINE N/A B
Queries to be analyzed at the sites named:
At Site C:
a. SELECT *
FROM CUSTOMER;
b. SELECT *
FROM INVOICE
WHERE INV_TOTAL < 1000;
c. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;
d. BEGIN WORK;
INSERT CUSTOMER(CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_BAL)
VALUES ('34210','Victor Ephanor', '123 Main St', 0.00);
INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)
VALUES ('986434', '34210', ‘10-AUG-2018’, 2.00);
COMMIT WORK;
e. 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 ('986391', '10936', ‘15-FEB-2018’, 100);
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;
At Site A:
f. SELECT CUS_NUM, CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;
g. SELECT *
FROM INVOICE
WHERE INV_TOTAL > 1000;
h. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;
At Site B:
i. SELECT CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE INV_TOTAL > 1000 AND CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;
j. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
