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
Comments 0
Log in to post a comment