Read the following instructions VERY CAREFULLY before proceeding! Instructions: 1. Read each question carefully so you UNDERSTAND CLEARLY what is being asked. 2. For Question 1, you should answer a total of FOUR (4) questions: At Site C: i. Answer either query a, b or c – only one (1)! ii. Answer either query d, or e – only one (1)! At Site A: iii. Answer either query f, g, or h – only one (1)! At Site B: iv. Answer either query i or j – only one (1)! 3. For Question 2, you should have describe the fragmentation related to ONLY ONE (1) of the database table for Part a – either CUSTOMER or INVOICE: Be SURE that your paper has NO MORE than the one (1) description required! This instruction is only related to Question2, Part a. 4. For Question 2, you should have only ONE (1) fragment sample table for Part c: Be SURE that your paper has NO MORE than the one (1) required! This instruction is only related to Question2, Part c. QUESTION 1: Look carefully at the image above and do the following for each query on Page 2: 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;
Read the following instructions VERY CAREFULLY before proceeding!
Instructions:
1. Read each question carefully so you UNDERSTAND CLEARLY what is being asked.
2. For Question 1, you should answer a total of FOUR (4) questions:
At Site C:
i. Answer either query a, b or c – only one (1)!
ii. Answer either query d, or e – only one (1)!
At Site A:
iii. Answer either query f, g, or h – only one (1)!
At Site B:
iv. Answer either query i or j – only one (1)!
3. For Question 2, you should have describe the fragmentation related to ONLY ONE (1) of the
Be SURE that your paper has NO MORE than the one (1) description required!
This instruction is only related to Question2, Part a.
4. For Question 2, you should have only ONE (1) fragment sample table for Part c:
Be SURE that your paper has NO MORE than the one (1) required!
This instruction is only related to Question2, Part c.
QUESTION 1:
Look carefully at the image above and do the following for each query on Page 2:
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;
Step by step
Solved in 3 steps
