Table of Contents

overratedbeltΤεχνίτη Νοημοσύνη και Ρομποτική

25 Νοε 2013 (πριν από 3 χρόνια και 23 μέρες)

56 εμφανίσεις

DDBMS
-

Lecture 5

Distributed D
atabase Design


i

Table of Contents


Design Problem

................................
................................
................................
..........................

1

Distribution Design

................................
................................
................................
....................

1

Top
-
Down Design P
rocess

................................
................................
................................
.

1

Bottom
-
Up Design Process

................................
................................
................................

2

Distribution Design Issues

................................
................................
................................
.........

2

Fragm
entation

................................
................................
................................
.....................

2

Fragmentation Alternatives

................................
................................
................................

2

Horizontal

................................
................................
................................
...................

3

Vertical

................................
................................
................................
.......................

3

Degree of Fragmentation

................................
................................
................................
....

4

Correctness of Fragmentation

................................
................................
............................

4

Allocation Alternat
ives

................................
................................
................................
.......

4

Information Requirements

................................
................................
................................
..

5

Fragmentation

................................
................................
................................
.............................

5

Horizontal Fragmenta
tion

................................
................................
................................
..

5

Primary Horizontal Fragmentation

................................
................................
.............

7

COM_MIN Algorithm

................................
................................
................................

8

PHORIZON
TAL Algorithm

................................
................................
.....................

10

Derived Horizontal Fragmentation

................................
................................
...........

12

Vertical Fragmentation

................................
................................
................................
.....

13

Clustering Algorithm

................................
................................
................................

15

Bond Energy Algorithm (BEA)

................................
................................
................

15

Partitioning Algorithm

................................
................................
..............................

18

PARTITION Algorithm

................................
................................
............................

19

Hybrid Fragmentation

................................
................................
................................
......

21

Fragment Allocation

................................
................................
................................
.................

22

Allocation Problem

................................
................................
................................
..........

22

Allocation


Information Requirements

................................
................................
...........

24

Allocation Model (DAP)

................................
................................
................................
..

24

Solution Methods

................................
................................
................................
.............

26


DDBMS
-

Lecture 5

Distributed D
atabase Design


1

Design Problem



In the general setting
:

Making decisions about the placement of data and

programs across
the sites of a computer network as

well as possibly designin
g the network itself.



In Distributed DBMS, the placement of

applications entails
:



placement of the distributed DBMS software



placement of the applications that run on the

database



Dimensions of the Problem



refer
Figure 1

suggested by Levin and Morgan in
1975:



Level of sharing



No sharing



each application and its data execute at one site with no
communication with any other program and its data files on other sites.



Data sharing



all the programs are replicated at all the sites but data files are not.



Da
ta
-
plus
-
program sharing



both data and programs may be shared.



Behavior of access patterns



Static



the user requests do not change over time.



Dynamic



the user requests change over time.



Level of knowledge on access pattern behavior



Partial information



the access pattern have
deviations

from the predictions.



Complete information



the access pattern can be predicted with no significant
deviation.


Distribution Design

-

T
wo major stages identified by Ceri et al. in 1987 for designing distributed database:

the
top
-
down approach and the bottom
-
up approach.


Top
-
Down Design Process



Refer
Figure 2

for framework.



mostly in designing systems from scratch



mostly in homogeneous systems



Steps:



Requirements Analysis


defines the environment of the system and elicit
s both the
data and processing needs of all potential database users.



System Requirements (Objectives)



Conceptual Design: the process by which the enterprise is examined to determine
entity types and relationships among these entities.



It
can be divided as

two analyses: entity analysis and functional analysis.



I
t can be interpreted as being an integration of user view through user input.



yields
global conceptual schema

(
GCS
)



View Design: deals with defining the interfaces for end users.

DDBMS
-

Lecture 5

Distributed D
atabase Design


2



I
t use view integrat
ion to ensure that entity and relationship requirements for
all the views are covered in the conceptual schema.



yields
access information

and
external schema definitions



Distribution Design



Fragmentation



Allocation



Local Conceptual Schemas



Physical Design



maps the local conceptual schemas to the physical storage
devices available at the corresponding sites.



Physical Schema



Observation and Monitoring


Bottom
-
Up Design Process



W
hen the databases already exist at a number of

sites



Discussed in Chapter 15


Di
stribution Design Issues

-

Why fragment at all?

-

How to fragment?

-

How much to fragment?

-

How to test correctness?

-

How to allocate?

-

Information requirements?


Fragmentation



What is a reasonable unit of distribution?



Relation



views are subsets of relations
loc
ality



extra communication



F
ragments of relations (sub
-
relations)



concurrent execution of a number of transactions that

access different portions of a
relation



views that cannot be defined on a single fragment will

require extra processing



semantic data con
trol (especially integrity

enforcement) more difficult


Fragmentation Alternatives

-

For example, defining following relational database scheme which depicts tribute LOC
that indicates the place of each project.


DDBMS
-

Lecture 5

Distributed D
atabase Design


3


EMP

ASG

ENO

ENAME

TITLE


ENO

PNO

RESP

DUR

E
1

J. Doe

Elect. Eng.


E1

P1

Manager

12

E2

M. Smith

Syst. Anal.


E2

P1

Analyst

24

E3

A. Lee

Mech. Eng.


E2

P2

Analyst

6

E4

J. Miller

Programmer


E3

P3

Consultant

10

E5

B. Casey

Syst. Anal.


E3

P4

Engineer

48

E6

L. Chu

Elect. Eng.


E4

P2

Programmer

18

E7

R. Davis

Mech. Eng.


E5

P2

Manager

24

E8

J. Jones

Syst. Anal.


E6

P4

Manager

48





E7

P3

Manager

36





E8

P3

Manager

40


PROJ

PAY

PNO

PNAME

BUDGET

LOC


TITLE

SAL

P1

Instrumentation

150000

Montreal


Elect. Eng.

40000

P2

Database Develop.

135000

N
ew York


Syst. Anal.

34000

P3

CAD/CAM

250000

New York


Mech. Eng.

27000

P4

Maintenance

310000

Paris


Programmer

24000


Horizontal

PROJ
1

: projects with budgets

less than $200,000

PROJ
2

: projects with budgets

greater than or equal to

$200,000


Vertical

PROJ
1
: information about

project budgets

PROJ
2
: information about

project names and

locations



DDBMS
-

Lecture 5

Distributed D
atabase Design


4

Degree of Fragmentation



Finding the suitable level of partitioning

within this range



Correctness of Fragmentation



Three rules of fragmentation to ensure th
at the database does not undergo semantic change
during fragmentation.



Completeness
:
Decomposition of relation
R

into fragments
R
1
,
R
2
, ...,
R
n

is

complete if
and only if each data item in
R

can also be found in

some
R
i
.

It is identical to the
lossless

de
composition property of normalization.



Reconstruction
:
If relation
R

is decomposed into fragments
R
1
,
R
2
, ...,
R
n
, then

there
should exist some relational operator


such that



R =

1≤i≤n
R
i


R
i

F
R



Disjointness
:
If relation
R

is decomposed into fragments
R
1
,
R
2
, ...,
R
n
, and

data item
d
i

is in
R
j
, then
d
i

should not be in any other

fragment
R
k

(
k

j

).

It ensures that the
horizontal fragments are disjoint.


Allocation Alternatives



Non
-
replicated



partitioned : each fragment resides at only one site



Replicate
d



fully replicated : each fragment at each site



partially replicated : each fragment at some of the

sites



Rule of thumb:

If
1


queries
update
queries
only
read

replication is advantageous,

otherwise
replication may cause problems



Comparison of

Replication Alternatives


Full
-
replication

Partial
-
replication

Partitioning

QUERY

PROCESSING

Easy

Same Difficulty

DDBMS
-

Lecture 5

Distributed D
atabase Design


5

DIRECTORY

MANAGEMENT

Easy or

Non
-
existant

Same Difficulty

CONCURRENCY

CONTROL

Moderate

Difficult

Easy

RELIABILITY

Very high

High

Low

REALITY

Possible

application

Realistic

Possible

application


Information Requirements



Four categories:



Database information



Application information



Communication network information



Computer system information


Fragmentation

-

Horizontal Fragmentation (HF)



Primary Horizontal Fragmenta
tion (PHF)



it is performed using predicates that are
defined on that relation.



Derived Horizontal Fragmentation (DHF)



it is the partitioning of a relation that
results from predicates being defined on another relation.

-

Vertical Fragmentation (VF)



Hybri
d Fragmentation (HF)


Horizontal Fragmentation



Database Information



concerns the global conceptual schema to note how the database
relations are connected to one another, especially, with joins.


DDBMS
-

Lecture 5

Distributed D
atabase Design


6



C
ardinality: card(
R
)
, where
R

means the relation.



The tai
l of a link is called the
owner

of the link, or
source

relation, e.g.,

owner
(L
1
) = PAY.



The head of a link is called the
member

of the link, or
target

relation, e.g.,

member
(L
1
) = EMP.



Application Information



The qualitative information guides the fragment
ation activity, whereas the quantitative
information is incorporated primarily into the allocation method.



The most active 20% of user queries account for 80% of the total data access.



simple predicates : Given
R
[
A
1
, A
2
, …, A
n
], a simple

predicate
p
j

is


p
j

:
A
i

θ

Value

where
θ

{=,<,≤,>,≥,

},
Value

D
i

and
D
i

is the domain of
A
i
.

For relation
R

we define
P
r

= {
p
1
, p
2
, …,p
m
}

Example :


PNAME = "Maintenance"


BUDGET ≤ 200000



minterm predicates : Given
R

and
P
r
={
p
1
, p
2
, …,p
m
}
,
define
M
={
m
1
,m
2
,…,m
r
} as


z
j
m,
k
p
m
m
M
ik
p
ij
ij
i
ik








1
1

},


|


{
*
Pr

where
ik
ik
p
p

*

or
ik
ik
p
p


*
.

Each simple predicate can occur in a minterm predicate either in its natural form or its
negated form.



N
egation (Complement):

Attribute


Value



Attribute > Value

Lower_bound


Attribute_1

and


Attribute_1


Upper_bound



¬
(Lower_bound


Attribute_1)

and


¬
(Attribute_1


Upper_bound)

Lower_bound


Attribute_1


Upper_bound



¬
(Lower_bound


Attribute_1


Upper_bound)



Example of minterm predicates that can be defined based on simple predicat
es, which
can be defined on PAY relation.

p
1
: TITLE =

Elect. Eng.


p
2
: TITLE =

Syst. Anal.


p
3
: TITLE =

Mech. Eng.


p
4
: TITLE =

Programmer


p
5
: SAL


30000

p
6
: SAL > 30000

DDBMS
-

Lecture 5

Distributed D
atabase Design


7

m
1
:
TITLE

=

"
Elect. Eng.
"



SAL


3
0000

m
2
:
TITLE

=

"
Elect. Eng.
"



SAL > 3
0000

m
3
:
¬

(TITLE

= "
Elect. Eng.
"
)


SAL


3
0000

(or,
m
3
:
TITLE



"
Elect. Eng.
"



SAL


3
0000
)

m
4
:
¬

(
TITLE

=

"
Elect. Eng.
")



SAL > 3
0000

m
5
: TITLE

=

"
Programmer
"



SAL


3
0000

m
6
: TITLE

=

"

Programmer
"



SAL > 3
0000



minterm selectivities:
sel
(
m
i
)



The number o
f tuples of the relation that would be

accessed by a user query which
is specified according

to a given minterm predicate
m
i
.



For example above,
sel
(
m
1
) = 0,
sel
(
m
2
) = 1.



access frequencies:
acc
(
q
i
)



The frequency with which a user application
q
i

accesses d
ata.



Access frequency for a minterm predicate can also be

defined.


P
rimary
H
orizontal
F
ragmentation



Definition
:


R
i

= σ
F
i
(
R
), 1 ≤
i


w

where
F
i

is a selection formula, which is (preferably) a

minterm predicate.



A horizontal fragment
R
i

of relation
R

consists of all the

tuples of
R

which satisfy a
minterm predicate
m
i
.



Given a set of minterm predicates
M
, there are as
many

horizontal fragments of
relation
R

as there are minterm

predicates.



Set of horizontal fragments also referred to as
minterm

fragments
.



Algori
th
m



Given: A relation
R
, the set of simple predicates
Pr



Output: The set of fragments of
R

= {
R
1
, R
2
,…,R
w
}
,
wh
ich obey the fragmentation
rules.



Preliminaries:

Pr

should be complete

Pr

should be minimal



Completeness of Simple Predicates



A set of simple predicates
Pr

is said to be

complete if and only if the accesses to the

tuples of the minterm fragments defined on

Pr

requires that two tuples of the same
minterm

fragment have the same probability of being

accessed by any application.



Example
:

Assume PROJ[PNO,

PNAME,

BUDGET,

LOC] has two

applications defined on it.

(1)
Find the budgets o
f projects at each location.

(2)
Find projects wit
h budgets less than $200000.

DDBMS
-

Lecture 5

Distributed D
atabase Design


8


According to (1),

Pr

=

{LOC=“Montreal”,

LOC=“New York”,

LOC=“Paris”}

which is not complete with respect to (2).

Modify

Pr

=

{LOC=“Montreal”,

LOC=“New York”,

LOC=“Paris”,


BUDGET≤200000,BUDGET>200000}

which is complete.



Minimality of Simple Predicates



If a predicate influences how fragmentation is

performed, (i.e., causes a fragment
f

to
be

further fragmented into, say,
f
i

and
f
j
) then

there should be at least one applicatio
n
that

accesses
f
i

and
f
j

differently.



In other words, the simple predicate should be

relevant in determining a
fragmentation.



If all the predicates of a set Pr are relevant,

then
Pr

is minimal.


)
(
)
(
)
(
)
(
i
j
i
i
f
card
m
acc
f
card
m
acc




Example
:

Pr

={LOC=“Montreal”,

LOC=“N
ew York”, LOC=“Paris”,


BUDGET≤200000,

BUDGET>200000}

is minimal (in addition to being complete).

However, if we add

PNAME = “Instrumentation”

then
Pr

is not minimal.


COM_MIN Algorithm



Given: a relation
R

and a set of simple

predicates
Pr



Output: a complete and minimal set of si
mple

predicates
Pr'

for
Pr



Rule 1: a relation or fragment is partitioned into

at least two parts which are accessed

differently by at least one application.



Steps:

(1)
Initialization :

find a
p
i



Pr

such that pi partitions
R

according to

Rule

1

set
Pr'

=
p
i

;
Pr



Pr



p
i
;
F



f
i

(2)
Iteratively add predicates to
Pr'

until it is

complete

find a
p
j



Pr

such that
p
j

partitions some
f
k

defined

according to minterm predicate over
Pr'

according to

Rule

1

set
Pr'

=
Pr'



p
j

;
Pr



Pr



p
j
;
F



F



f
j

if

p
k



P
r'

which is nonrelevant then

Pr'



Pr'



p
k

DDBMS
-

Lecture 5

Distributed D
atabase Design


9

F



F



f
k


declare


F: set of minterm fragments

begin


find a
p
i



Pr

such that pi partitions
R

according to

Rule

1


Pr'

=
p
i

;


Pr



Pr



p
i
;


F



f
i

{
f
i

is the minterm fragment according to
p
i
}


do


begin



fi
nd a
p
j



Pr

such that
p
j

partitions some
f
k

of
Pr'

according to

Rule

1



Pr'

=
Pr'



p
j

;



Pr



Pr



p
j
;



F



F



f
j



if


p
k



Pr'

which is nonrelevant

then



begin




Pr'



Pr'



p
k




F



F



f
k



end
-
if


end
-
begin


until

Pr'

is complete

end
.

{COM_MI
N}




Example:


If
Pr'

=

{
p
1
,
p
2
}
, where

p
1
:
att = value_1

p
2
:
att = value_2

domain of
att

is {
value_1
,
value_2
}, a set of implications
I

contains

i
1
: (
att = value_1
)


¬
(
att = value_2
)

i
2
:
¬
(
att = value_1
)


(
att = value_2
)

The following four minterm predic
ates are defined according to
Pr'

m
1
: (
att = value_1
)


(
att = value_
2
)

m
2
: (
att = value_1
)



¬
(
att = value_
2
)

m
3
:
¬
(
att = value_1
)



(
att = value_
2
)

m
4
:
¬
(
att = value_1
)



¬
(
att = value_
2
)


DDBMS
-

Lecture 5

Distributed D
atabase Design


10

PHORIZONTAL Algorithm



Makes use of COM_MIN to perform fragmentati
on.



Input: a relation
R

and a set of simple

predicates
Pr



Output: a set of minterm predicates
M

according

to which relation
R

is to be fragmented



Steps:

Pr'



COM_MIN (
R
,

Pr
)

determine the set
M

of minterm predicates

determine the set
I

of implications amo
ng
p
i



Pr'

eliminate the contradictory minterms from
M


begin


Pr'



COM_MIN (
R
,

Pr
)


determine the set
M

of minterm predicates


determine the set
I

of implications among
p
i



Pr'


eliminate the contradictory minterms from
M


for

each
m
i



M

do



if

m
i

is

contradictory according to
I

then




M



M



m
i



end
-
if


end
-
for

end
. {PHORIZONTAL}




Example: (refer example 5.11)



Two candidate relations: PAY and PROJ.



Fragmentation of relation PAY

Application: Check the salary info and determine raise.

Employee recor
ds kept at two sites application run at

two sites

Simple predicates


p
1

: SAL ≤ 30000


p
2

: SAL > 30000


Pr

= {
p
1
,

p
2
} which is complete and minimal
Pr'

=

Pr

Minterm predicates


m
1

: (SAL ≤ 30000)


m
2

:
¬

(SAL ≤ 30000) = (SAL > 30000)



The relations PAY
1

an
d PAY
2

yield

PAY
1



PAY
2


TITLE

SAL


TITLE

SAL

Mech. Eng.

27000


Elect. Eng.

40000

DDBMS
-

Lecture 5

Distributed D
atabase Design


11

Programmer

24000


Syst. Anal.

34000



Fragmentation of relation PROJ

Applications:

(1)
Find the name and budget of projects given their no.



Issued at three sites

(2)

Ac
cess project information according to budget



one site accesses ≤200000 other accesses >200000

Simple predicates



For application (1)


p
1

: LOC = “Montreal”


p
2

: LOC = “New York”


p
3

: LOC = “Paris”



For application (2)


p
4

: BUDGET ≤ 200000


p
5

: BUDG
ET > 200000



Pr

=
Pr'

= {
p
1
,

p
2
,

p
3
,

p
4
,

p
5
}



Minterm fragments left after elimination


m
1

: (LOC = “Montreal”)


(BUDGET ≤ 200000)


m
2

: (LOC = “Montreal”)


(BUDGET > 200000)


m
3

: (LOC = “New York”)


(BUDGET ≤ 200000)


m
4

: (LOC = “New York”)


(BUDG
ET > 200000)


m
5

: (LOC = “Paris”)


(BUDGET ≤ 200000)


m
6

: (LOC = “Paris”)


(BUDGET > 200000)



The relations PROJ
1
, PROJ
3
, PROJ
4
, PROJ
6

yield

PROJ1





PROJ3




PNO

PNAME

BUDGET

LOC


PNO

PNAME

BUDGET

LOC

P1

Instrumentation

150000

Montreal


P2

Database
Develop.

135000

New York

PROJ4





PROJ6




PNO

PNAME

BUDGET

LOC


PNO

PNAME

BUDGET

LOC

P3

CAD/CAM

250000

New York


P4

Maintenance

310000

Paris




Correctness



Completeness
:
Since
Pr'

is complete and minimal, the selection

predicates are
complete



Reconstru
ction
:
If relation
R

is fragmented into
F
R

= {
R
1
,

R
2
,…,

R
r
}


R

=


Ri


FR

R
i



Disjointness
:
Minterm predicates that form the basis of fragmentation

should be
mutually exclusive.

DDBMS
-

Lecture 5

Distributed D
atabase Design


12


D
erived
H
orizontal
F
ragmentation



Defined on a member relation of a link

according to a selection operation specified on

its
owner.



Each link is

an equijoin.



Equijoin can be implemented by means of semijoins.




Given a link
L

where
owner
(
L
)=
S

and
member
(
L
)=
R
,

the derived horizontal fragments
of
R

are defined as


R
i

=
R



S
i
, 1



i



w

where
w

is the maximum number of fragments that

will be defined

on
R

and


S
i

= σ
Fi

(
S
)

where
F
i

is the formula according to which the

primary horizontal fragment
S
i

is
defined.



Example



Given link
L
1

where
owner
(
L
1
)=
PAY

and
member
(
L
1
)=
EMP


EMP
1

= EMP

?
PAY
1


EMP
2

= EMP

?
PAY
2

where


PAY
1

= σ
SAL≤30000
(

PAY
)


PAY
2

= σ
SAL
>30000
(

PAY
)



The relations EMP
1

and EMP
2

yield

EMP1




EMP2



ENO

ENAME

TITLE


ENO

ENAME

TITLE

E3

A. Lee

Mech. Eng.


E1

J. Doe

Elect. Eng.

E4

J. Miller

Programmer


E2

M. Smith

Syst. Anal.

E7

R. Davis

Mech. Eng.


E5

B. Casey

Syst. Anal.

DDBMS
-

Lecture 5

Distributed D
atabase Design


13





E6

L. Chu

E
lect. Eng.





E8

J. Jones

Syst. Anal.



Correctness



Completeness
:
Referential integrity
.
Let R be the member relation of a link whose
owner is

relation
S

which is fragmented as
F
S

= {
S
1
, S
2
, ..., S
n
}.

Furthermore, let
A

be the join attribute between
R

and

S
.

Then, for each tuple
t

of
R
,
there should be a

tuple
t'

of
S

such that

t
[
A
]=
t'
[
A
]



Reconstruction
:
Same as primary horizontal fragmentation.



Disjointness
:
Simple join graphs between the owner and the

member fragments.


Vertical Fragmentation



Definition



Has been studied within the centralized

context



design methodology



physical clustering



More difficult than horizontal, because more

alternatives exist.



For instance, if the total number of simple predicates in
Pr

is
n
, there are 2
n

possible minterm predic
ates to be defined on it.



If a relation has
m

nonprimary key attributes, the number of possible fragments is
equal to
B
(
m
)

(


m
m
).



Two approaches
:



grouping
:
attributes to fragments



splitting
:
relation to fragments



Overlapping fragments
:
grouping



Non
-
overl
apping fragments
:
splitting



We do not consider the replicated key attributes to be

overlapping.



Advantage:

Easier to enforce functional dependencies

(for integrity checking etc.)



Application Information



Attribute affinities



a measure that indicates how c
losely related the

attributes are
.

This is obtained from more primitive usage data



Attribute usage values



Given a set of queries
Q

= {
q
1
,
q
2
,…,
q
q
} that will run on

the
relation
R
[
A
1
,
A
2
,…,
A
n
],





otherwise
q
query
by
referenced
is
A
attribute
if
A
q
use
i
j
j
i
0
1
)
,
(

use
(
q
i
,

) can be defined accordingl
y



Example



Definition of
use
(
q
i
,

A
j
)

Consider the following 4 queries for relation PROJ

DDBMS
-

Lecture 5

Distributed D
atabase Design


14

q
1
:
SELECT

BUDGET

FROM

PROJ

WHERE

PNO=Value

q
2
:
SELECT

PNAME,

BUDGET

FROM

PROJ

q
3
:
SELECT

PNAME

FROM

PROJ

WHERE

LOC=Value

q
4
:
SELECT

SUM(BUDGET)

FROM PROJ

WHERE

LOC=Valu
e

Let
A
1
= PNO,
A
2
= PNAME,
A
3
= BUDGET,
A
4
= LOC














1
1
0
0
1
0
1
0
0
1
1
0
1
1
0
1
4
3
2
1
4
3
2
1
q
q
q
q
A
A
A
A



Affinity Measure
aff
(
A
i
,

A
j
)
:

The
attribute affinity measure

between two

attributes
A
i

and
A
j

of a relation
R
[
A
1
,
A
2
, …, A
n
]

with respect to the set of applications

Q

= (
q
1
,
q
2
, …,
q
q
)
is defined as follows :









1
)
,
(
1
)
,
(
)
(
)
(
)
,
(
j
k
i
k
l
A
q
use
A
q
use
k
PAY
k
l
k
l
j
i
q
acc
q
ref
A
A
aff

where,

ref
l
(
q
k
) is te number of accesses to attributes (
A
i
,
A
j
) for each execution of
application
q
k

at site
S
l
.

acc
l
(
q
k
) is the application access frequency measure previously defined and
modified to includ
e frequencies at different sites.

Note:



The result of this computation is an
n
×
n

matrix, called the
attribute affinity
matrix

(
AA
), each element of which is one of the measures defined above.



Calculation of
aff
(
A
i
,

A
j
)
:

Assume each query in the previous example

accesses the attributes once dur
ing
each

execution.

Also assume the access

frequencies














1
1
0
0
1
0
1
0
0
1
1
0
1
1
0
1
4
3
2
1
4
3
2
1
q
q
q
q
A
A
A
A

Then


45
)
(
)
(
)
(
)
(
)
,
(
1
3
1
2
3
1
3
1
1
1
1
3
1









q
acc
q
acc
q
acc
q
acc
A
A
aff
k
l
l

and the
attribute affinity matrix

AA

is

DDBMS
-

Lecture 5

Distributed D
atabase Design


15














78
3
75
0
3
53
5
45
75
5
80
0
0
45
0
45
4
3
2
1
4
3
2
1
A
A
A
A
A
A
A
A


Clustering Algorithm



Take the attribute affinity matrix AA and

reorganize the at
tribute orders to form clusters

where the attributes in each cluster

demonstrate high affinity to one another.



Bond Energy Algorithm (BEA) has been used

for clustering of entities. BEA finds an

ordering of entities (in our case attributes)

such that the gl
obal affinity measure

following
equation is minimized.














n
i
n
j
j
i
j
i
j
i
j
i
j
i
A
A
aff
A
A
aff
A
A
aff
A
A
aff
A
A
aff
AM
1
1
1
1
1
1
)]
,
(
)
,
(
)
,
(
)
,
(
)[
,
(

where


0
)
,
(
)
,
(
)
,
(
)
,
(
1
1
0
0






n
i
j
n
i
j
A
A
aff
A
A
aff
A
A
aff
A
A
aff

The maximization function considers the nearest neighbors only,
thereby

resulting in the
grouping of large values with large ones, and small valu
es with small ones.

For the
attribute affinity matrix AA

is symmetric, which reduces the objective function as










n
i
n
j
j
i
j
i
j
i
A
A
aff
A
A
aff
A
A
aff
AM
1
1
1
1
)]
,
(
)
,
(
)[
,
(


Bond Energy Algorithm

(BEA)



Input: The
AA

matrix



Output: The clustered affinity matrix
CA

which

is a perturbation

of
AA



Steps:

(1)
Initialization: Place and fix one of the columns of

AA

in
CA
.

(2)
Iteration: Place the remaining n
-
i columns in the

remaining
i+1

positions in the
CA

matrix. For each

column, choose the placement that makes the most

contribution to the
global a
ffinity measure.

(3)
Row order:

Order the rows according to the column

ordering.

begin


{initialize; remember that AA is an
n
×
n

matrix}


CA(
˙
, 1)


AA(
˙
, 1)


CA(
˙
, 2)


AA(
˙
, 2)


i
ndex



3


while

index



n

do



{choose the

best


location for attribute
AA
index
}


begin

DDBMS
-

Lecture 5

Distributed D
atabase Design


16



for

i

from

1

to

index
-
1

by

1

do




calculate

cont
(
A
i
-
1
,
A
index
,
A
i
)



end
-
for



calculate

cont
(
A
index
-
1
,
A
index
,
A
index
+
1
)




{boundary condition}



loc



placement given by
maximum

cont

value



for

j

from

index

to

loc

by

-
1

do




{shuffle the two matrices}




CA(
˙
,

j
)


CA(
˙
,
j
-
1
)



end
-
for



CA(
˙
,
loc
)


AA(
˙
,
index
)



i
ndex



index

+ 1


end
-
while


order the rows accordin
g to the relative ordering of columns

end
. {BEA}



Define contribution of a placement
,
cont
(), for the best placement
:










n
i
n
j
j
i
j
i
j
i
A
A
aff
A
A
aff
A
A
aff
AM
1
1
1
1
)]
,
(
)
,
(
)[
,
(

which can be rewritten as


























n
j
n
i
j
i
j
i
n
i
j
i
j
i
n
i
n
j
j
i
j
i
j
i
j
i
A
A
aff
A
A
aff
A
A
aff
A
A
aff
A
A
aff
A
A
aff
A
A
aff
A
A
aff
AM
1
1
1
1
1
1
1
1
1
)
,
(
)
,
(
)
,
(
)
,
(
)]
,
(
)
,
(
)
,
(
)
,
(
[

define the
bond

between two attributes
A
x

and
A
y

as





n
z
y
z
x
z
y
x
A
A
aff
A
A
aff
A
A
bond
1
)
,
(
)
,
(
)
,
(

then AM can be written as








n
j
j
j
j
j
A
A
bond
A
A
bond
AM
1
1
1
)]
,
(
)
,
(

consider following
n

attributes,



















M
A
n
j
j
i
M
A
A
A
A
A
A
A
A
A




1
4
3
2
1

The global affinity measure for these attributes can be written as

)
,
(
2
)]
,
(
)
,
(
[
)]
,
(
)
,
(
[
)
,
(
)
,
(
)
,
(
)
,
(
2
1
1
1
1
1
1
1
j
i
n
i
l
l
l
l
l
n
l
l
l
l
l
j
j
i
j
j
i
i
i
old
A
A
bond
A
A
bond
A
A
bond
A
A
bond
A
A
bond
A
A
bond
A
A
bond
A
A
bond
A
A
bond
M
A
M
A
AM


























Consider placing a new attribute
A
k

betwee
n attributes
A
i

and
A
j

in the clustered affinity
DDBMS
-

Lecture 5

Distributed D
atabase Design


17

matrix. The new global affinity measure can be similarly written as

)
,
(
2
)
,
(
2
)
,
(
)
,
(
)
,
(
)
,
(
j
k
k
i
k
j
j
k
i
k
k
i
new
A
A
bond
A
A
bond
M
A
M
A
A
A
bond
A
A
bond
A
A
bond
A
A
bond
M
A
M
A
AM

















Thus, the net contribution to the global affinity measure of placing attribute
A
k

between
A
i

and
A
j

is


)
,
(
2
)
,
(
2
)
,
(
2
)
,
,
(
j
i
j
k
k
i
old
new
j
k
i
A
A
bond
A
A
bond
A
A
bond
AM
AM
A
A
A
cont








Example of contribution of placement:

Consider the
AA

matrix above and study the contribution of moving attribute
A
4

between
attributes
A
1

and
A
2
,


cont
(
A
1
, A
4
, A
2
) = 2
bond
(
A1, A4
) + 2
bond
(
A
4
, A
2
)


2
bond
(
A
1
, A
2
)

so


bond
(
A
1
, A
4
) = 45 *0 + 0
* 75 + 45 * 3 + 0 * 78 = 135


bond
(
A
4
, A
2
) = 11865


bond
(
A
1
, A
2
) = 225

Therefore,


cont
(
A
1
, A
4
, A
2
) = 2 * 135 + 2 * 11865


2 * 225 = 23550



BEA


Example

Consider the following
AA

matrix and the corresponding
CA

matrix

where
A
1

and
A
2

have
been placed.

Pl
ace
A
3
:


AA matrix:













78
3
75
0
3
53
5
45
75
5
80
0
0
45
0
45
4
3
2
1
4
3
2
1
A
A
A
A
A
A
A
A

CA matrix:













75
0
5
45
80
0
0
45
4
3
2
1
2
1
A
A
A
A
A
A


Ordering (0
-
3
-
1) :


8820
0
*
2
4410
*
2
0
*
2
)
,
(
2
)
,
(
2
)
,
(
2
)
,
,
(
1
0
1
3
3
0
1
3
0







A
A
bond
A
A
bond
A
A
bond
A
A
A
cont

Ordering (1
-
3
-
2) :

DDBMS
-

Lecture 5

Distributed D
atabase Design


18


10150
225
*
2
890
*
2
4410
*
2
)
,
(
2
)
,
(
2
)
,
(
2
)
,
,
(
2
1
2
3
3
1
2
3
1







A
A
bond
A
A
bond
A
A
bond
A
A
A
cont

Ordering (2
-
3
-
4) :

1780
)
,
,
(
4
3
2

A
A
A
cont

Therefore, the CA matrix has to form














75
3
0
5
53
45
80
5
0
0
45
45
4
3
2
1
2
3
1
A
A
A
A
A
A
A

When
A
4

is placed, the final form of the CA

matrix

is














78
75
3
0
3
5
53
45
75
80
5
0
0
0
45
45
4
3
2
1
4
2
3
1
A
A
A
A
A
A
A
A

A
fter row organization














78
75
3
0
75
80
5
0
3
5
53
45
0
0
45
45
4
2
3
1
4
2
3
1
A
A
A
A
A
A
A
A


Partitioning Algorithm



Consider the set of applications
Q

= {
q
1
, q
2
,

, q
q
}, need to

divide a set of clustered
attributes

{
A
1
, A
2
, …, A
n
} into two (or more) sets {
A
1
, A
2
, …, A
i
}

and {
A
i
, …, A
n
} such that
there are no (or minimal)

applications that access both (or more than one) of

the sets.


DDBMS
-

Lecture 5

Distributed D
atabase Design


19



Define



1
)
,
(
)
(


j
i
j
i
A
q
use
A
q
AQ
, set of global applications

}
)
(
{
TA
q
AQ
q
TQ
i
i


,
set of applications that access only TA

}
)
(
{
BA
q
AQ
q
BQ
i
i


,

set of applications that access only BA

}
{
BQ
TQ
Q
OQ



,

set of applications that access both TA and BA

and

cost equations






Q
q
S
i
j
i
j
i
j
q
acc
q
ref
CQ
)
(
)
(
, that is the
total number

of
all
accesses to attributes






TQ
q
S
i
j
i
j
i
j
q
acc
q
ref
CTQ
)
(
)
(
, that is the
total number of accesses to attributes by
applications

that access only TA






BQ
q
S
i
j
i
j
i
j
q
acc
q
ref
CBQ
)
(
)
(
, that is the

total number of accesses to attributes by
applications

that access only BA






OQ
q
S
i
j
i
j
i
j
q
acc
q
ref
COQ
)
(
)
(
, that is the

total number of accesses to attributes by
applications

that access both TA and BA

Then find the point along the diagonal that maximizes


2
*
COQ
CBQ
CTQ
z





Two problems :



Cluster forming in the middle of the
CA

matr
ix



Shift a row up and a column left and apply the algorithm

to find the “best”
partitioning point



Do this for all possible shifts



Cost
O
(
n
2
)



More than two clusters



m
-
way partitioning



try 1, 2, …, m

1 split points along diagonal and try to find

the best poi
nt for each
of these



Cost
O
(2
m
)


PARTITION Algorithm

input
:

CA
: clustered affinity matrix;

R
: relation;

ref
: attribute usage matrix;

DDBMS
-

Lecture 5

Distributed D
atabase Design


20

acc
: access
frequency

matrix

output
:

F
: set of fragments

begin


{determine the z value for the first column}


{the subscrip
ts in the cost equations indicate the split point}


calculate
CTQ
n
-
1


calculate
CBQ
n
-
1


calculate
COQ
n
-
1


best


CTQ
n
-
1

*
CBQ
n
-
1


(
COQ
n
-
1
)
2


{determine the best partitioning}


do



begin




for

i

from

n
-
2

to

1

by

-
1

do




begin





calculate
CTQ
i





calculate
CBQ
i





calculate
COQ
i





z



CTQ
n
-
1

*
CBQ
n
-
1


(
COQ
n
-
1
)
2





if

z>best

then





begin






best



z







record the split point within shift





end
-
if




end
-
for




call SHIFT(
CA
)

{the shift procedure to check the
n
-
1

diagonal positions to find the maximum
z
.}



end
-
begin

until

no more SHIFT is possible


reconstruct the matrix according to the shift positi
on


R
1




TA
(
R
)

K


{K is the set of primary key attributes of R}


R
2




BA
(
R
)

K


F



{
R
1
,
R
2
}

end
. {PARTITION}




Example of relations above, PROJ
1
, PROJ
2


F
PROJ

= {PROJ
1
, PROJ
2
}

where


PROJ
1

= {A
1
, A
3
}

DDBMS
-

Lecture 5

Distributed D
atabase Design


21


PROJ
2

= {A
1
, A
2
, A
4
}

Thus


PROJ
1

= {PNO, BUDGET}


PROJ
2

= {PN
O, PNAME, LOC}



Note that in this exercise we performed the fragmentation over the entire set of
attributes rather than only on the nonkey ones.



Correctness



A relation
R
, defined over attribute set
A

and key
K
, generates the

vertical
partitioning
F
R

= {
R
1
, R
2
, …, R
r
}.



Completeness



The following should be true for A:


A

=

R
i



Reconstruction



Reconstruction can be achieved by


R

=

K

R
i
,

R
i



F
R



Disjointness




(1)
TID's

(tuple ID)

are not considered to be overlapping since they are maintained

by
the syst
em

(2) D
uplicated keys are not considered to be overlapping


Hybrid Fragmentation




Also called mixed fragmentation or nested fragmentation



To reconstruct the original global relation in case of hybrid fragmentation, replace figure
above by

R




;
R
1




;
R
2






DDBMS
-

Lecture 5

Distributed D
atabase Design


22

Fragment Allocation

Allocation Problem



Problem Statement

Given


F

= {
F
1
, F
2
, …, F
n
}

fragments


S

=

{
S
1
, S
2
, …, S
m
}

network sites


Q

= {
q
1
, q
2
,…, q
q
}

applications

Find the "optimal" distribution of
F

to
S
.



Optimality



Minimal cost



Communication + sto
rage + processing (read & update)



Cost in terms of time (usually)



Performance
:
Response time and/or throughput



Constraints
:

Per site constraints (storage & processing)



Information Requirements



Database information



selectivity of fragments



size of a fragmen
t



Application information



access types and numbers



access localities



Communication network information



unit cost of storing data at a site



unit cost of processing at a site



Computer system information



b
andwidth



l
atency



communication overhead



Simple Modelin
g Formulation

for File Allocation Problem (
FAP
)



Assume
Q

to be modified so that it is possible to identify the update and the
retrieval
-
only queries and define
following for
a single fragment
F
k
:


}
,
,
,
{
2
1
m
t
t
t
T




}
,
{
2
1
m
u
u
u
U



where
t
i

a
nd
u
i

are respectively read
-
only and update traffics generated at site
S
i

for
F
k
.



Assume that the communication cost between any two pair of sites
S
i

and
S
j

is fixed
for a unit of transmission. Furthermore, assume that is it different for updates and
retr
ievals in order that the following can be defined:


}
,
,
,
,
{
)
(
,
1
1
13
12
m
m
m
c
c
c
c
T
C





DDBMS
-

Lecture 5

Distributed D
atabase Design


23


}
,
,
,
,
{
)
(
,
1
1
13
12
m
m
m
c
c
c
c
U
C










where
c
ij

and
c

ij

are the unit communication cost for retrieval and update, respectively,
requests between sites
S
i

and
S
j
.



Let
d
i

be the cost of storing
the fragment at site
S
i
, define following for the storage
cost of fragment
F
k

at all sites.


}
,
,
,
{
2
1
m
d
d
d
D





Assume that there are no capacity constraints for wither the sites or the
communication links.



Specify the allocation problem as a cost
-
min
imization problem where we are trying to
find the set
I



S

that specifies where the copies of the fragment will be stored.

Denotes
x
j

so that






otherwise
0

site

to
assigned
is
fragment
the
if
1
j
k
j
S
F
x

The precise specification is as follows




























m
i
I
S
j
j
j
I
S
j
ij
I
S
j
j
ij
j
j
j
j
j
d
x
c
t
c
u
x
1
)
(
min
min

subject to


x
j

= 0
or

1



The first term corresponds to the cost of transmitting the updates to all the sites
that

hold the replicas of the fragment, and to the cost of executing the
retrieval
-
only request at the site, which will result in minimal data transmission
cost.



The secon
d term calculates the total cost of storing all the duplicate copies of the
fragment.



The reasons to modify the simplistic formulation for distributed database design



One cannot treat fragments as individual files that can be allocated one at a time,
in is
olation.

The access costs to the remaining fragments may change due to
distributed join.



It is more complicated to access data in distributed database systems than this
simple

remote file access


model suggests.



These models do not take into consideratio
n the cost of integrity
enforcement
, yet
locating two fragments involved in the same integrity constraint at two different
sites can be closely.



The cost of enforcing concurrency control mechanisms should be considered.



Allocation



former
File Allocation
(
FAP
) vs
later
Database Allocation (
DAP
):



Fragments are not individual files

DDBMS
-

Lecture 5

Distributed D
atabase Design


24



relationships have to be maintained



Access to databases is more complicated



remote file access model not applicable



relationship between allocation and query processing



Cost of in
tegrity enforcement should be considered



Cost of concurrency control should be considered


Allocation


Information

Requirements



Database Information



D
enote
selectivity of fragments

sel
i
(
F
j
)



size of a fragment


size
(
F
j
) =
card
(
F
j
) *
length
(
F
j
)

where,
lengt
h
(
F
j
) is
the

length (in bytes) of a tuple of fragment
F
j
.



Application Information



number of read accesses of a query
q
i

to a fragment
,
RR
ij



number of update accesses of query to a fragment
,
UR
ij



A

matrix indicating which queries updates which fragments
,
UM

with elements
u
ij






otherwise
0
fragment

updates

query

if
1
j
i
ij
F
q
u



A similar matrix for retrievals
,
RM

with elements
r
ij






otherwise
0
fragment

retrieves

query

if
1
j
i
ij
F
q
r



A vector
O

of values
o
(
i
)
originating site of each query

q
i



The maximum allowable response time of each application should be defined
.



Site Information



The
unit cost of storing data at a site

S
k
,
USC
k
.



T
he
cost of processing
one unit of work
at a site

S
k
,
LPC
k
.



The work unit is identical to that of the
RR

and
UR
.



Network Information



Denotes
g
ij

as
communication cost

per
frame between tw
o sites

S
i

and
S
j



Denote
frame size

in bytes
,
f
size


Allocation Model

(DAP)



General Form



min
(Total Cost)



subject to



response time constraint



storage constraint



processing constraint

DDBMS
-

Lecture 5

Distributed D
atabase Design


25



Decision Variable






otherwise
0

site

to
assigned
is
fragment
the
if
1
j
k
j
S
F
x



Total Cost













Q
q
S
S
F
F
jk
i
i
k
j
STC
QPC
TOC

where
QPC
i

is the query processing cost of application
q
i
, and
STC
jk

is the cost of storing
fragment
F
j

at site
S
k
.



Storage Cost (of fragment
F
j

at
S
k
)


jk
j
k
jk
x
F
size
USC
STC
*
)
(
*


where,
USC
k

is
unit storage cost at
S
k



Query Processing Cost (for one quer
y)


i
i
i
TC
PC
QPC



where,
PC
i

is
processing component
while
TC
i

is

transmission component


i
i
i
i
CC
IE
AC
PC





i
i
i
TCR
TCU
TC





AC
i
:
access cost










S
S
F
F
k
jk
ij
ij
ij
ij
i
k
j
LPC
x
RR
r
UR
u
AC
*
*
)
*
*
(



First two terms calculate the number of accesses of user query
q
i

to fragment
F
j
.



Note that (
UR
ij
+
RR
ij
) gives the total number of update and
retrieval

accesses.



Assume that the local costs of processing them are identical.



Summation gives the total number of accesses for all the fragments referenced by
q
i
.



Multiplicatio
n by
LPC
k

gives the cost of this access at site
S
k
.



Use of
x
jk

selects only those cost values for the sites where fragments are stored.



IE
i
:
integrity enforcement cost

that can be similarly calculated as
AC
i
.



CC
i
:
concurrency control cost

that can be simil
arly calculated as
AC
i
.



TCU
i
: update cost of the transmission components
















S
S
F
F
i
o
k
jk
ij
S
S
F
F
k
i
o
jk
ij
i
k
j
k
j
g
x
u
g
x
u
TCU
)
(
,
),
(
*
*
*
*



The first term sends the update message from the originating site
o
(
i
) of
q
i

to all
the fragment replicas that need to be updated.



The second term is for the
confirmation.



TCR
i
: retrieval cost of the transmission components








F
F
i
o
k
size
j
j
i
k
i
o
jk
ij
S
S
i
j
k
g
f
F
length
F
sel
x
r
g
x
u
TCR
)
*
)
(
*
)
(
*
*
*
*
(
min
)
(
,
),
(

DDBMS
-

Lecture 5

Distributed D
atabase Design


26



The first term represents the cost of transmitting the retrieval request to those sites
which have copies of fragments that need to be accessed.



The second term acco
unts for the transmission of the results from these sites to the
originating site.



The equation states that among all the sites with copies of the same fragment, only
the site that yields the minimum total transmission cost should be selected for the
execu
tion of the operation.



Constraints



Response Time


execution time of
q
i

≤ max. allowable response time

qi
,

q
i



Q



Storage Constraint (for a site)








F
F
k
k
jk
j
S
S
S
STC
,

site
at
capacity

storage



Processing constraint (for a site)








Q
q
k
k
k
i
i
S
S
S
S
q
,

of
capacity

processing

site
at


of

load

processing


Solution Methods



Both of

FAP
and DAP
is NP
-
complete



NP
-
Problem
:



A problem is assigned to the NP (nondeterministic polynomial time) class if it is
verifiable in polynomial time by a nondeterministic Turing machine. (A
nondeterministic Turing machine is a "parallel" Turing machine
which can take
many computational paths simultaneously, with the restriction that the parallel
Turing machines cannot communicate.)



A P
-
problem whose solution time is bounded by a polynomial is always also NP.



If a problem is known to be NP, and a solution

to the problem is somehow known,
then demonstrating the correctness of the solution can always be reduced to a
single P (polynomial time) verification.



If P and NP are not equivalent, then the solution of NP
-
problems requires (in the
worst case) an exhaus
tive search.



Linear programming, long known to be NP and thought not to be P, was shown to
be P by L. Khachian in 1979. It is an important unsolved problem to determine if
all apparently NP problems are actually P.



A problem is said to be NP
-
hard if an alg
orithm for solving it can be translated into
one for solving any other NP
-
problem. It is much easier to show that a problem is
NP than to show that it is NP
-
hard.



A problem which is both NP and NP
-
hard is called an NP
-
complete problem.



Heuristics based on

DDBMS
-

Lecture 5

Distributed D
atabase Design


27



single commodity warehouse location (for FAP)



knapsack problem



branch and bound techniques



network flow



Attempts to reduce the solution space



assume all candidate partitionings known; select the


best” partitioning



ignore replication at first



sliding windo
w on fragments