Database Reference
In-Depth Information
query into two subqueries, one for site A and the other for site C. Change the
queries to compute count of employees and sum of salaries. From these two
queries, receive sum of salaries and count of employees at each site. Now cal-
culate average by adding up the sums of salaries and dividing by the total
number of employees. This case illustrates the potential complexity of some
query transformations.
Example 4
Query from site B: List all data for employees from departments with depart-
ment numbers 24 and 43.
Data distribution: EMPLOYEE relation first partitioned vertically, keeping
salary data for all employees at site B. The rest of the data in the relation par-
titioned horizontally. Horizontal partition for employees with department
number 24 at site A and those with department number 43 at site C.
Action by DDBMS: Recognize the hybrid partitioning scheme. Transform and
decompose query into three subqueries. Execute two subqueries appropriately
at sites A and C where horizontal partitions are stored. Receive results at B,
and put the results together through union operation. Execute the other sub-
query at B. Combine this result with earlier consolidation of results from sites
A and C through union operation.
Join Queries Processing of queries with join conditions in a distributed database
environment can be quite complicated and difficult. The complexity varies with
the manner in which base relations are distributed across the sites. Many of
the typical queries in any database environment contain join conditions. For opti-
mizing join queries, the DDBMS has to examine a large number of processing
options.
Let us take a simple example and scrutinize the processing options. We will use
the following relations for our example. This discussion is modeled after a presen-
tation by C. J. Date, an acknowledged database expert and an eminent author, in
his book mentioned in the References section.
PROJECT ( ProjNo , ProjDesc, ProjManager, ProjLocation)
EMPLOYEE ( EmpNo , SocSecNo, EmpName, EmpAddr, EmpPhone, Salary,
DeptNo)
ASSIGNMENT ( ProjNo , EmpNo , HoursWorked)
Foreign Keys: ProjNo REFERENCES PROJECT
EmpNo REFERENCES EMPLOYEE
Assume that database is distributed to two sites, A and B, as follows:
At site A
PROJECT relation (500 rows)
ASSIGNMENT relation (50,000 rows)
At site B
EMPLOYEE relation (10,000)
Search WWH ::




Custom Search