Fragmentation

1
Fragmentation
EMP

• For example, consider once again the a revised
  version of the relational database schema.

PROJ
ASG
PAY
2
Fragmentation
PROJ1

• Thus
• PROJ1 sBUDGET lt 200000 (PROJ)

PROJ2

• And
• PROJ2 sBUDGET gt 200000 (PROJ)

3
Derived Horizontal Fragmentation

• In some cases, the horizontal fragmentation of a
  relation can not be based on a property of its
  own attributes, but is derived from the
  horizontal fragmentation of another relation.

SUPPLIER
SUPPLY

• It is meaningful to partition one of the above
  relations so that a fragment contains the tuples
  of suppliers in a given city.
• However city is not an attribute of the SUPPLY
  relaiton, it is an attribute of the SUPPLIER
  relation.
• Therefore, we need a semi-join operation in order
  to combine determine the tuples of SUPPLY which
  correspond to the suppliers in a given city.
• Thus to find the tuples of SUPPLY which refers to
  suppliers in San Francisco, we write
• SUPPLY SJSNUM SNUM (sCITY San Francisco
  (SUPPLIER))

4
Derived Horizontal Fragmentation

• Similarly consider the primary fragmentation on
  PAY through PAY1 sSAL lt 30000 (PAY) and PAY2
  sSAL gt 30000 (PAY)

EMP
PAY1
PAY2
5
Derived Horizontal Fragmentation

• Thus EMP1 EMP ? (sSAL lt 30000 (PAY)) and EMP1
  EMP ? (sSAL gt 30000 (PAY)

EMP
PAY1
?
?
PAY2
EMP2
EMP1
6
Derived Horizontal Fragmentation

• Some applications involve a join of two or more
  relations. If the relations are stored at
  different locations, there may be significant
  overhead in processing the join.
• In such cases it may be appropriate to ensure
  that the relations, or fragments of relations,
  are at the same location. We can achieve this
  using derived fragmentation

Staff
PropertyForRent
7
Derived Tables P1, P2, P3
P1t
P2
P3
8
Derived Horizontal Fragmentation

• We may have an application that joins the Staff
  and PropertyForRent relations together.
• It is assumed that the Staff relation is
  fragmented horizontally and they are stored
  locally.
• S3sbranchNo B003(Staff)
• S4sbranchNo B005(Staff)
• S5sbranchNo B007(Staff)
• Also, it is assumed that PG14 is currently
  managed by SG14 at B003.
• It would be useful to store the property using
  the same fragmentation strategy. Thus
• Pi PropertyForRent? Si, where 3ltilt5
• This will give three relations

9
Mixed Or Hybrid Fragmentation

• In some cases a simple horizontal or vertical
  fragmentation of a database schema will not be
  sufficient to satisfy the requirements of user
  applications.
• In this case a vertical fragmentation is followed
  by a horizontal or vice versa, producing a tree
  structured partitioning of the global relations.

R
H
H
R1
R2
V
V
V
V
V
R11
R12
R21
R22
R23
10
Reconstruction of Hybrid Relation

• To reconstruct the original global relations, one
  starts at the leaves of the partitioning tree and
  moves upward by performing joins and unions.

?
H
H
?
?
V
V
V
V
V
R11
R12
R21
R22
R23
11
Example of Hybrid Relation

• Consider a global relation EMP(EMPNUM, NAME, SAL,
  TAX, MGRNUM, DEPTNUM).
• The following is a mixed fragmentation, which is
  obtained by applying vertical fragmentation
  followed by horizontal fragmentation

• EMP1 sDEPTNUM lt 10 (?EMPNUM, NAME, MGRNUM,
  DEPTNUM (EMP))
• EMP2 s10 ltDEPTNUM lt 20 (?EMPNUM, NAME, MGRNUM,
  DEPTNUM (EMP))
• EMP3 sDEPTNUM gt 20 (?EMPNUM, NAME, MGRNUM,
  DEPTNUM (EMP))
• EMP4 ?EMPNUM, NAME, SAL TAX (EMP))

EMP
V
V
EMP3
H
H
H
EMP1
EMP2
EMP3
12
Allocation Problem

• Consider a set of fragments F F1, F2, F3, . .
  . , Fn) and a network consisting of sites S
  S1, S2, S3, . . . , Sn on which a set of
  applications Q q1, q2, q3, . . . , qn are
  running.
• The allocation problem involves finding the
  optimal distribution of F to S.
• What is Optimality?
• This is defined with respect to two measures
• Minimal Cost the cost function consists of the
  costs of
• Storing each Fi at site Sj
• Querying Fi at site Sj,
• Updating Fi at all sites where it is stored,
• Data communication.
• Performance
• minimizing the response time and to
• maximize the system throughput at each site.

13
Total Cost Function

• The total cost function consists of two
  components (i) query processing and (ii)
  storage.
• Thus it can be expressed as under