Horizontal fragmentation in distributed database design exercise

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