The DDBMS Scenario

Specify the minimum type(s) of operation(s) the database must support (remote request, remote transaction, distributed transaction, or distributed request) in order to perform the following operations: 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.

Use the following summary:

Operation

Number of DPs

1

>1

Request

Remote

Distributed

Transaction

Remote

Distributed

At site C:

a. SELECT * FROM PRODUCT;

b. SELECT * FROM INVOICE WHERE INV_TOTAL < 2000;

c. SELECT * FROM PRODUCT WHERE PROD_QOH < 25;

d. 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',

Answer and Explanation:

At C:

a.) This SQL sequence represents a remote request.

b.) This SQL sequence represents a remote request.

c.)This SQL sequence represents a distributed request. Note that the distributed request is required when a single request must access two DP sites. The PRODUCT table is composed of two fragments, PRO_A and PROD_B, which are located in sites A and B, respectively.

d.)This SQL sequence represents a distributed request.

Note that UPDATE CUSTOMER and the two INSERT statements only require remote request capabilities. However, the entire transaction must access more than one remote DP site, so we also need distributed transaction capability. The last UPDATE PRODUCT statement accesses two remote sites because the PRODUCT table is divided into two fragments located at two remote DP sites. Therefore, the transaction as a whole requires distributed request capability.

e.)This SQL sequence represents a distributed transaction. Note that, in this transaction, each individual request requires only remote request capabilities. However, the transaction as a whole accesses two remote sites. Therefore, distributed request capability is required.

At A:

f.)This SQL sequence represents a distributed request. Note that the request accesses two DP sites, one local and one remote. Therefore distributed capability is needed.

g.)This SQL sequence represents a remote request, because it accesses only one remote DP site.

h.)This SQL sequence represents a distributed request. In this case, the PRODUCT table is partitioned between two DP sites, A and B. Although the request accesses only one remote DP site, it accesses a table that is partitioned into two fragments: PROD-A and PROD-B. A single request can access a partitioned table only if the DBMS supports distributed requests.

At B:

i.)This SQL sequence represents a remote request.

j.)This SQL sequence represents a distributed request.

k.)This SQL sequence represents a distributed request. (See explanation for part h.)