The problem is based on the DDBMS scenario in Figure P12.1.

Figure P12.1 The DDBMS Scenario for Problem 1

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:

NOTE

To answer the following questions, first identify how many different DP sites are to be accessed by the transaction/request. Next, a distributed request is necessary if a single SQL statement is to access more than one DP site.

Use the following summary:

	Number of DPs
Operation	1	> 1
Request	Remote	Distributed
Transaction	Remote	Distributed

At C:

a. SELECT pname

FROM employeee;

This SQL sequence represents a : -----------------

Question

Solve the problem applying Distributed strategy and then apply Semijoin Perception query optimization, (You are required to explain the variation and all details of your results)

Suppose that the EMPLOYEE and DEPARTMENT , Project relations of Figure 1 are distributed on different sites 1 ,2,4 see the figure below. Assume in this problem that neither relation is fragmented. According to Figure 1,

Giving the following information:

Consider the query Q1: Retrieve the Q: For each employee, retrieve employee name and department name Where the employee works.

Write Query 1 in the relational algebra:

Solution relational algebra (Q1):

Assume that the results should be transfer to SITE 1 only, Calculate the Query result size and the Total transfer size ? (Hint, use the best strategy to perform the solution )

Solution :

Employees table (size) : -------------

Department table(size) : -------------

Query result size : ------------

Total transfer size: --------------

Using: Semijoin Concept to solve the same problem and show the comparison over the obtained results

Query result size

Q Size (employee) = ---------------------- Bytes

Query size for Q(Department) = ------------------------bytes

Total transfer size: ----------------------bytes

The difference is : - ---------------------

Using Semijoin Concept query optimization will save ----------- Byte transferring.