Wednesday, 21 March 2018

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





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    











No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...