Advanced Database Management System - Tutorials and Notes: Horizontal fragmentation in distributed database design exercise

## TOPICS (Click to Navigate)

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## 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.
**************

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