Horizontal fragmentation in distributed database design exercise
Question:
Given the following relation EMP and the predicates p1:
SAL > 23000, p2: SAL < 23000
ID
|
Name
|
Salary
|
1289
8907
7643
0988
6543
0986
2345
|
Guru
Siva
Shalini
Kavin
Surya
Kavitha
Anees
|
12000
67050
51980
23000
28760
23000
29999
|
EMP relation
a) Perform a horizontal fragmentation of the table
based on the given predicates.
b) Is this a correct fragmentation?
c) If the answer to (b) is no, explain why, and give
the predicates that would correctly fragment the table.
Solution:
a) Horizontal fragmentation of given table as per the
given predicates is as follows;
ID
|
Name
|
Salary
|
8907
7643
6543
2345
|
Siva
Shalini
Surya
Anees
|
67050
51980
28760
29999
|
Fragment 1: According to predicate P1 (Salary >
23000)
SQL query: SELECT * FROM emp WHERE salary > 23000
ID
|
Name
|
Salary
|
1289
|
Guru
|
12000
|
Fragment 2: According to predicate P2 (Salary <
23000)
SQL query: SELECT * FROM emp WHERE salary < 23000
b) This fragmentation is not correct. We have fragmented
the given table into two fragments. Because none of the predicates include
the equality condition, the records with the salary value 23000 has not listed
in either fragments.
The following two records from the base table are not available
in none of the fragments. Hence, the fragmentation that we did is incorrect.
ID
|
Name
|
Salary
|
0988
0986
|
Kavin
Kavitha
|
23000
23000
|
c) When we fragment the relation using the given
predicates we end up excluding one tuple, which violates the completeness rule
for correct fragmentation. Correct predicates should be one of the following;
- P1: SAL >= 23000, P2: SAL < 23000
or
- P1: SAL > 23000, P2: SAL <= 23000.
**************
Go to Primary Horizontal Fragmentation page
Go to Horizontal Fragmentation page
distributed database solved exercise
fragmentation in distributed database
horizontal and vertical fragmentation in distributed database
how to fragment a relation horizontally
fragment using simple predicates
horizontal fragmentation solved exercises