TRANSPARENCY

38 LEVELS OF DISTRIBUTION

access strategies defined by applications. The problem of

and choosing between different access strategies is dealt with in
However, we will show that the same application can be [Link]
ways, and that the way in which an application is written can
access st rategies.
Section 3.3 deals with read-only [Link] shows, first with
application and then with a more complex one, the different
det rmin g,evaluai
levels of very SItnp,
Chapt
in
affect
a
er
manys
the dpoellfer 3

transparency. Section 3.4 performs the same analysis as

distributed database for retrieving or updating just a
Section 3.3 on
applications. Both the above sections are based on applications
single tuple; which
can discuss distribution transparency without being concerned
upda,
in access th,
distributi, F

its
of accessing sets of tuples. In Section 3.5 we deal with with thethisproblen
way di
show that even at the highest level of distribution the latter o

programmer can control the power of the primitives which transparency the problem,
Section 3.6 deals with integrity constraints and their are issued to theapplicatio a

databases. enforcement in DDBMS n

distribute th

of
re
Global
schema th

Fragmentation Site gl
schema independent m
schernas
b
b
t}
Allocation
schema a

Local (Other sites)

mapping Local
mapping
I

schema | schema 2
re

DBMS of I DBMS of r
site |
site 2

Local Local
database
at site I database
at site 2

Flgure 3.1 A
reference architecture for distributed databases
 BASES 39
DISTRIBUTED DATA
FOR
[3.1] REFERENCE ARCHITECTURE

REFERENCE ARCHITECTURE FOR DISTRIBUTED

3.1
DATABASES

3.1shows a reference architecture for a distributed database. This refefers

Figure databases; however,
architecture is not explicitly implemented in alll distributed
its levels are conceptually relevant in order to uderstand the organization of Y
distributed database. We will therefore analyze n understand althe components
of this reference architecture.
schema defines
At the top level of Figure 3.1 is the global shema. The global
all the data which are contained in the distribute database as if the database were
not distributed at all. For this reason, the globa schema can be defined exactly in
the same way as in a nondistributed database. However, the data model which is
used for the definition of a global schema shouki be convenient for the definition
of the mapping to the other levels of the distrlbuted database. We will use the
relational model for this purpose. Using this mdel, the global schema consists of
the definition of a set of global relations.
Each global relation can be split into sevefal nonoverlapping portions which
are called fragments. There are several difetent ways in which to perform the
splitting Cperation; theyra described in the hext section. The mapping between
glohal relations and fragments is defined in /the fragmentation schema. This
mapping is one to many; i.e., several fragmehts correspond to one global relation,
but only one global relation corresponds to pne fthgment. Fragments are indicated
by a global relation name with an index (frggnhent]index); for example, R, indicates
the ith fragment of global relation R.
Fragments are logical portions of glofal felaions which are physically located
at one or several sites of the network. The allocation schema defines at which
site(s) a fragment is located. Note that the type ofmapping defined in the allocation
schema determines whether the distributed database is redundant or nonredun
dant: in the former case the mapping is one to many, while in the latter case the
mapping is one to one. All the fragments whi¢h correspond to the same global
relation Rand are located at the same site j donstitute the physical image of
global relation R at site j. There is therefore |a one to one mapping between a
physical image and a pair (global relation, site, physical images can be indicated
by a global relation name and a site index. To distinguish them from fragments,
we willuse a superscript; for example, R' indicates the physical image of the glob
relation Rat site j.
An example of the relationship between the pbject types defined above is shown
in Figure 3.2. A global relation R is split into four fragments R1, R2, R3, and R4.
These four fragments are allocated redundanty at the three sites of a computer
network, thus building three physical images , R², and R,
Tocomplete the terminology, we will refer to a copy of afragment at a given
site,and denote it using the global relation narne and two indexes (a fragment index
and a site index). For example, in Figure 3.2 the notation R} indicates the copy
of fragment R which is located at site 3.
Finally, note that two physical images dan be identical. In this case we will
 TRANSPARENCY
40 LEVELS OF DISTRIBUTION

(R})
R' (site l)
R
R
(R})

R,
(R?)
R' (site 2)
Ra
(R,)

RA

(R}) R (site 3)

(R})

(R})
Global relation
Fragments Physical images
Figure 3.2 Fragments and physical images for a global
relation.
say that a physical image is a copy of
Figure 3.2, R' is a copy of R?, another physical image. For example,
Let us now go back to the
already deseribed the reference architecture of Figure 3.1. e te
architecture. These relationships
three levels are
between the objects at the three top levels of this
on the data model of the site independent; they do not depend
the physical images to the local DBMSS. At a therefore,
lower level, it is necessary
This mapping is called a objects which are manipulated by the local DBMS.
local DBMS; therefore in alocal mapping scherma and depends on the ype of
local
mappings at diflerent sites.
This architecture
heterogeneous system we have different types of
ing distributed provides avery general framework for
understand-
databases. The
features of this architecture threeseparation
are the conceptual
mostinportant
of data fragmentation motivatethe
objectives whichand allocation,
the control of
redundaney, andthe
Separating the concept of dataindependence
fragmentationfromfrom
localtheDBMSs.
concept ofdataal
location. This separation allows us Ñdistirguish two different levels of dis
tribution transparency, namely fragmentation transparency and loC8-
tion transparency. Fragnentation transparency is the highest degreeof
 (3.2) TYPES OF DATA FRAGMENTATION 41
transparency and consists of the fact that the user or
application prograrn
mer works on global relations. Location
transparency
transparency and requires the user or application prograrnrner
is a lower degree ol
to work on
fragments instad of global relations; however, he or he does not know where
the fragrnënts are located. The separation
between
tion andallocation is very convenient in distributed the concept of fragrnenta
the determination of relevant portions of the data is databaBE design, because
the problern of optimal allocation, as will be shown in thus distinguished frorn
2.
the next chaptert.
Explicit control of redundancy. The reference architecture
control of redundancy at the fragnent level. For exarnple, inprovides explicit
two physical inages R? and R are Figure 3.2 the
overlapping; ie., they contain cormmon
data. The definition of disjoint fragrnents as building blocks of
allows us to refer explicity to this overlapping part: the physical images
R2. As we shall see, the explicit control over replicated fragnent
aspects of distributed database nanagernent. redundancy is useful in several
3.
Independence from local DBMSs. This feature, called local rnapping trans
parency, allows us to study several problerns of distributed database rmanaze
ment without having to take into account the specifice data rnodels of
lo
cal DBMSs. Clearly, in a hornogeneous systern it is
possible that the site
indeperndent schemata are defined using the sarne data model as the local
DBMSs, thus reducing the cormplexity of this rnapping.
Another type of transparency which is strictly related to location transparency
is replication transparency. Replication
transparency rneas that the user is
unaware of the replication of fragrmnents. Clearly, replication transparerncy is irnplied
by location transparency; however, in certain cases it is possible that the
user has
no tocation transparency but has replication transparency (thus, he or
she uses one
particular copy. and the systern makes appropriate actions on the other copies).
In thís book, we do not further distinguish replication transparency frorn location
transparency.
As a final rernark on the reference architecture, we can say that it perforrms
the same function as the ANSI/SPARC architecture in traditiopal DBMSs: it is
not necessarily inplemented by the existing systerns, but it shows which levels and
schernata are conceptually relevant.

3.2 TYPES OF DATA FRAGMENTATION

The decomposition of global relations into fragments can be perforned by applying

two difierent types of fragmentation: horizontal fragrnentation and vertical
fragnentation. We will first consider these two types of fragmneritation separately
and thern conside the more complex fragmentation which can be obtained by
applying a cornposition of both.
In all types of fragmentation, a fragment can be defined by an expression in
a relational language (we will use relational algebra) which takes global relations
 DISTRIRUTION TRA NSPARENCY
42 LEVELS OF
result For example, if
produces the fragment s globai
AS operands and frngment which contains only data
about cmployees, a
tion contains data
can be obviously deined by a
employees who work at departument D,
operation global, relntion
There onare,thehowever, some rules which must be followed when defining r

ments: relation must CO

(ACompleteness condition All the data of the global be

pedinto the fragments; i.e., it must not happen that n data item which belong tiap W

any fragment.
a global relation does not belong to It must always be possible to
q

AglobalReconstruction
relation from itscondition
fragments. The necessity of this condition reconstruct
is obvious eart
fact, only fragments are stored in the distributed database, and global relation
operation if necessary.
have to be built. through this reconstruction sectien
Disjointness condition As we have discussed in the previouS
replication of data rur
is convenient that fragments be disjoint, so that the
an

nuint
controlled explicitly at the allocation level. However, this condition is useful
someti
with horizontal fragmentation, while for vertical fragmentation we will
allow this condition to be violated. The reason for this exception will be discussed
when dealing with vertical fragmentation. t

We can now consider the fragnentation rules.

re

3.2.1 Horizontal Fragmentation

fo
Horizontal fragmentation consists of partitioning the tuples of a global relation into
subsets; this is clearly useful in distributed databases, where each subset can contain
data which have common geographical properties. It can be defined by expressing
each fragment as a selection operation on the global relation. For example, let a
global relation be
SUPPLIER(SNUM, NAME, CITY)
Then the horizontal fragmentation can be defined in the following way:

SUPPLIER, = SLcTY="SFSUPPLIER
SUPPLIER2 = SLCITY ="LA" SUPPLIER
The above fragmentation satisfies the completeness con dition if "SF" and "LA are
the only possible values of the CITY attribute: otherwise we would not know to
which fragment the tuples with other CITY values belong.
The reconstruction condition is easily verified, because it is always possible
reconstruct the SUPPLIER global relation through the following
operation:
SUPPLIER = SUPPLIER, UN SUPPLIER2
The disjointness condition is clearly
verified.
We will call the predicate which is usedin the selection operation which defines
a fragment its qualification. For instance, in the above example the qualifications
are:
 FRAMENTATYO 42
22 TYPEE OF DATA

2ITYLA"
tte rapieA
We cafn generalize from the abxwe eLatnple that in oder o taty PET
spiee
condition, the set of qualificatios of all fragrnerts maet. be 2y
trt n
with respect to the set of allowed values The recostuctio
ter) ta
satisfied through the unio, operation, and the disointoee cratit
qualifications be rutually exclusive.

3.2.2 Derived Horizontal Fragmentation

relatio caot be b 2
In some cases, the horizontal fr agrnentation of
property of its own attributes, but is derived frorn the hoeizoA Ir
another relation. Consider, for exarnple, a giobal relatioc
SUPPLYSNUM, PNM DEPTNL QUAN
where SNUM is a supplier number It is meaninzful to partitioc th res :
that a fragment contains the tuples for suppliers which are in a gen Hcwer
PPLIEE
city is not an attribute of the SUPPLY relation. it is an attribute of the
relation considered in the above exarm ple. Therefore we need a semjperse
in order to determine the tuples of StPPLY hich correspond to the suDpEs n
a given city. The derived fragmentation of SLPPLY can be toeretore e sE
follows:
SUPPLY, = SUPPLY SJ SNUM=svtwSlPPLER.
SUPPLY = SUPPLY SJSwUM= SNUM stPPLIER:
The effeet of the semi-join operations is to select from UPPL Ythe tupies wt
satisfy the join eondition between SUPPLIER, or SPPLIER: and PPL th
determining those tuples of SUPPLY which refer to suppbes s s
Los Angeles, respectively.
The reconstruction of the global relation UPPL) can be perfored ih
the union operation as was shown for SPPLIER
When a global relation R has a derived fragmentatson the qual estis s
fragmerts cannot be expressed as predieates which use attributes rAtaa be
the condition for a tuple t to belong to a given fragment R R the exsetE
in some other fragrnent S, of [Link] a tuple such that t and satis the y
specification of the derived fragmentation Constdering the above evample we gir
s Hinple representation of this eondition (which should in fact ince an evseotas
quantifier) as follows
Qi : SUPPLY SNUM SUPPLIER SNUM AND SPPLUSR TTY
SUPPLY SNUM sPPLIERSN MAND SU PPLIER CrrY
The meaning of the abve qualihcations s to state. for anv tuple Y
(SUPPLY), theexistence of a suppler from San Franeseo (Los Angeles wth the
nae supplier number
 RENCY
TRANSPA
DISTRIBUTION
LEVELS OF requires that there
14 fragmentation contained
also in the
be no
relation. completeness the above
of and reasonable,
This is a typical, not
areintegrity constraint for this d:alaa sp
The
numbers in the
correspond
and usually is
to
SUPPLY

called
two tuples
relation which

referential
the of the SUPPLIER
condition is
integrity
satisfied if
constraint.
tuple of the
arelation SUPPLY
which belong relation
SUP LI.
to two aifere
two
stit
hor
[Link]
The In this case this condition is easily verified, because the supplier nunt the

relation; however, in general it can be diffity

SUPPLIER
unique keys of the
are
this condition holds.
toprove that
Th
3.2.3Vertical Fragmentation thi
relation is the subdivision of its op

The vertical
fragmentation of a global projecting the global relation
by Over e
attribut
fragments are obtained databases where each group of attribute
into groups;
can contain data which in distributed
have common geographical properties. The fragmentatig
useful
group. This can be
is correct if each attribute is mapped into at least one attribute of the fragments 3
relation by
possible to reconstruct the original joining
moreover, it must be global relation
for example, a
fragments together. Consider, T
MGRNUM, DEPTNUM)
EMP EMPNUM, NAME, SAL, TAX,
vertical fragmentation of this relation can be defined as
A
DEPTNUM EMP
EMP; =PJEMPNUM, NA ME, MGRNUM,
EMP2 = PJsMPNUM. SAL. TAX EMP
organization in which salaries and
This fragmentation could, for instance, reflect an
relation EMP can be obtained
taxes are managed separately. The reconstruction of
as
EMP = EMP, JNEMPNUM= EMPNUM EMP2
because EMPNUM is a key of EMP. In general, the inclusion of a key of the glota
relation into each fragment is the most straightforward way tu guarantee that the
provide
reconstruction through a join operation is possible. An alternative way to used as
the reconstruction property is to generate tuple identifiers which are of replication
system-controlled keys. This can be convenient in order to avoid the
large keys, moreover, tuple identifiers cannot be modified by users. EMP
Notice that the above formula for the reconstruction of the global relation
is not complete, because the result of joining EMP and EMP, contains the column
projection
EMPNUM twice. This undesired replication can be eliminated by a
operation that we omit to indicate. have
Let us finally consider the we
First,
seen that at least the key shouldproblem
offragment disjointness. order to allow
be replicated in all in main
reconstruction. In general, we can say that in vertical fragments the
fragmentation not asin-
Imotivation for having disjoint fragments (i.e., to replication) isattributein
portant as in horizontal control
fragmentation. In fact. if we include the same
 (3.2] TYPES OF DATA FRAGMENTATION 45

two diferent vertical fragments, we know exactly that the replicated data is con
stituted by the column which corresponds to this attribute; if instead we allow two
horizontal fragments to overlap, by permitting nonmutually exciusive qualifications,
then we cannot refer directly to the overlapping portion.
For example, consider the following vertical fragmentation of relation EMP:
EMP; =[Link] [Link], DEPTNUM EMP
EMP2 =[Link], SAL, TAX EMP
The attribute NAME is replicated in both fragments. We can explicitly
this attribute when we reconstruct relation EMP through an eliminate
operation: additional projection
2EMP = EMP, JNgMPNUM= EMPNUM PJEMPNUM. SAL, TA XEMP2
SLareah Derf
3.2.4 Mixed Fragmentation
The fragments which are obtained by the
relations themselves, so that it is possible to above fragmentation operations are
apply the fragmentation operations
recursively, provided that the correctness conditions are satisfied each time.
reconstruction can be obtained by applying the reconstruction The
The expressions which define fragments are more rules in inverse order.
for example, the same global relation complex in this case; consider,
EMP EMMPNUM, NAME, SAL, TAX, MGRNUM,
DEPTNUM)
The following is a mixed
fragmentation which is
fragmentation of the previous example, followed obtained by applying the vertical
by a horizontal
DEPTNUM. fragmentation on
EMP, = SLDEPTNUM< 1oPJEMPNUM, NA ME,
EMP2 = SL10< DEPTNUM<
MGRNUM, DEP TNUM EMP
20PJEMPNUM, ME,
NA
EMP, = SLpEpTNUM> 20PJEMPNUM, NA ME, MGRNUM,DEPTNUM EMP
EMP, = PJEMPNUM,NA ME,SA L, TAX EMP MGRNUM, DEPTNUM EMP

EMP

EMP,

EMP, EMP, EMP,

Figure 3.3 The
fragmentation tree of relation EMP
 DISTRIBUTION TRANSPARENCY
48 LEVELS OF

a procedure which receives the variable $SNUM s an inbt

can be considered as $SNUM
the name of the supplier who has the current valie of
paraneter, selects I the vari.
this name to the Pascal-like propram
8 supplier number, and returns the general cs in which the SOL
consider
able $NAME. In Section 3.5, we will variables.
statement returns scts (or relations) rather than in
also to comIunicate with the DDBMS
In some cases, the application has
example infornation on the success
order to exchange some control information, for are used for
PaSeal variables which
or failure of a required databae operation. The symbol. For exatnple, after
this type of comnunication are prefixed with the "#"
#FOUND which is true only if
an SQL query we will often test a boolean variable
this kind is /OK,
the result of the query is not empty. Another typical variable of DDBMS
which is true only if the operation has been perfor1ned correctly by the
(these variahles are often called "condition codes" in DBMSs).
Leyél 1: Fragmentation transparency The way in which the application
accesses the database if the DDBMS provides fragmentation transpareney is shown
in Figure 3.5a. First, the application accepts a supplier number fromm the terminal;
then it accesses the database. The whole SQL statemnent represents a single dis
tributed database access primitive, which receives the variable $SUPNUM as input
parameter and returns the variable $NAME as output parameter. The DDBMS
interprets this primitive by accessing the databases at any one of the three sites
in a way which is comnpletely determined by the systen. The protblern of deter1nin
ing how to access the database will be discussed in Chapters 5 and 6. From he
viewpoint of distribution transpareney, notice that the application refers to the
global relation name SUPPLIER, conpletely ignoring the fact that the database
is distributed. In this way, the application is completely immune to any change
which is applied to all schemata which are below the global schema in our reference
architecture.
Level 2. Location Transparency f the DDBMS provides location trans
pareney but not fragmentation transparency, the same application can be written
as shown in Figure 3.5b. The request for the supplier with the given number is first
issued referring to fragment. SUPPLIER, and if the DDBMS returns a negative
Pnswer in the control variable #FOUND, a similar request is issued with respect to
fragment SUPPLIER2. At this point, this naive implementation assunes that the
supplier has been found and displays the result. Of course, several variations of
this solution are possible, for instance, issuing both requests in parallel in order to
exploit the parallelism of the distribuved system; however, this does not change the
distribution transpareney characteristies. This application is clearly independent
from changes in the allocation schema, but not from changes in the fragmentation
schema, because the fragmentation structure is incorporated in the application.
However, location transparency is by itself very useful, because it allows the ap
plication to ignore which copies exist of each fragment, therefore allowing copies to
be moved from one site to another, and allowing the creation of new copies without
affecting the applications.
When location trnnspareney is provided without fragInentation
transparency,
it is very efficient to write applications which take explicit advantage of knowing
the fragnentation structure. For example, the same application of Figure
3.5b can
 (33 DNT RIUTION THANSPARENCY roR READ GNLY APPLCATONS 40

readtterminal,$5NUM),
Seleet HAME into SNAME
from SUrPLIER Suts 2
where StUM - $SNUM,
writeterminal,SNAME)
(a) Fragmentstion transparency (level 1)

read(terminal,sSNUM);
lelect Nane into $NAME DDBMS
from SUPPLIBUM. SUPPLILR,Sts
where SNUM
if not #FOUND then
Select NAME jnto SNAME Site 2
from SUPPLIER, sUPIEP,
Where SNUM - SSNUM,
write(terminal,SNAME)
(6) Location transparency (level 2)

read(terninal,$SNUM); DDBMS
Select NAME into SNAME
from SUPPLIER, AT SITE I Site 1
where SNUM $SNUM; SUPPLIE,
ifnot #FOUND hen
Select NAME into SNAME
from SUPPLIER, AT SITE3 Site3
where SNUM - SSNUM, SUPPLIER,
write(terminal,SNAME).

(c) Local mapping transparency (level 3).

Figure s.6 Thg resd-only application SUPINQUIRY
at difforont lovels of distribution transparency.

be written in the following way:

SUPINQUIRY:
read (terminal, $SNUM);
read (terminal, $CITY);
CAe $CITY of
"SF": Select NAME into$NAME
from SUPPLIER,
where SNUM = $SNUM;
"LA": Select NAME into $NAME
fron SUPPLIER,
where SlNUM =$SNUM
end;
write (terminal, $NAME).
The database access primiives are in this case the sarne as in Figure 3.5b; however.
only one of them is issued by the application. The application written in this way
 50 LEVELS OF DISTRIBUTION TRANSPARENCY

is based on the same level of distribution transparency provided by

as the application of Figure 3.5b (i.e., location transpareney ); howeverthe
dependent on the fragmentation schema, because it incorporates it is the
DDBV
"SF" and LA", which are attribute values used in the Iragmentation
Therefore, a change of these values afects this spplication, while it
the application of Figure 3.5b.
constanta
does notscherafe:
Level 3: Local mapping transparency At this level,
we
the application still refers to objects using names
which asSsUme tha
the individual local systems: however, it has to specify at are independent
reside. This is shown in Figure 3.5c. The site names are which site the fron
statements by adding an 'at" clause to the from clause. indicated
Clearly.
in theobjSQL
ees
each database access is in
primitive routed by the DDBMS to &specific site this
these primitives use
provided, the application site-independent fragment names. If this
would incorporate directiy mapping However
were
by the local SVstems. the ilenames which are n0
The most important aspect of
mapping between fragment names local mapping transparency is not this D8me
and local hlenames,
primitives used by the application program into
cal DBMS.
but the mapping of ths
Therefore, local mapping transparency is primitives the used by the la
heterogeneous
and the local DBMS
an
DDBMS. Suppose, for example, that the localimportant feature in &
at site 3 is a
Codasyl DBMS at site i is MS
ping system. In
issuedtransparency,
the DDBMS has to order to provide
by the transform the database access local ms
application into corresponding MS and
translation problems can be found also in a Codasyi [Link] Similar
homogeneous
at the global level systerms there is a difference homogeneous DDBMS, because even
between the primitives which in
and the
Level 4: No primitives the local DBMS.
of are uselu
transparency
system.
is transparency
particularly
Let us therefore see important
We have already
but difficult to shown that local mappn
the how the obtain in a heterogenes
distributed
not support anydatabase with IMS at site l and application
SUPINOUIRY an
has to code the type of distribution Codasvl at site 3 if the beDDBM writ ten
tions and IMS and Codasyl
install these auxiliary transparencv.
programs which
The
application progranm
application has to be prograns at the implement the required fune
sites where they are required: the
prograrns instead of thewritten with a request for
we have
assumed that
SQL
statements, as activating these remote auvilia
which progran must bethe DDBMS accepts anshown in Figure 3.6. In this exanple
The details of
the IMS and run at which site
and which"execute" primitive which speviti
require defining the local Codasyl programs are not parameters have to be
details of IMS arnd Inapping shown. because this
a
condition code in Codasyl schemata and
systermns. Note that theentering into#FOUND.
the previous several
unneces!

SFOUND
not This examples,
change is has variable which was
parameter
of have enough motivated by the fact been
changed into a
condition information sermantics
code: the on the that in this case the DDB\/S s
conditions
this and ret
example shows another urn auxiliary
applicationAgain,programs primitives
thern explicitly. will have to
of the
detailk
to return
thistype
determine
the

typical advant age inofdependently

of linguistic
transparency
the

distribution
 |3 ) DiRTHWTKON THANNPARENCY FOR [Link] APPLICATIONS b1

NUrINO0RY
Read (terminal, isUPNUM).
ute suPIM
1f not trOUND
Ihen exeUute
$NUPNUM,1OUND,SNAM)at site l,
$NUpCODANYII1SUPNUM,SFOUND,$NAMI)at site
Wite (ieminal, NAMI),

DDIMS

NUPCODASYL(NNUM,FOUND,NAMI)
Vind SuPPLER RECORD
SUPIMS(SNUM,FOUND,NAME):
Get unique SUPPLIER_SEGMENT

LAal DM# Local DUMS

(Codasyl) (IMS)

Codasyl database IMS dat abase

Site I

Figure .0 An applleatlon on e heterooeneous distributed

databas0 without transparency
DDBM8 can determine and manaye more condition nutononously at higher levels
of trannparency.
The DbBMS hn n the above exnnple the not completely trivinl task of ae
tivating renote programn nnd panning pntAetern between progrann which run
on different loeal DHMSA. Note thnt nDDIMS eAn he very vseful in developing
dintributed databse applheation even if it doen ot provide nny dintribution trans
parency

3.3.2 A More Complex Applieation

The levels of dintribution raunparecy for Ppieationn have been presented lor n
very ninple applieation which performed the retrieval of data lrom only one rela:
transpareney ix much
tion, the dilfereee between ditferent leveln of dint ribution
Inore evident if we ronnider n more complex nppention which perlors n join
herelore eonider n econd a
operation between two dilerent relatioR, Lot uppica
pieaiom, SUFTART, which retrievon the nnne of the nupplier who
52 LEVELS OF DISTRIBUTION TRANSPARENCY

agiven part: the part numtber is entered by the user who requUeSta
Ihe application is witten with the fundarnental assumption
supplied by only one supplier. that eeh
Level 1: Fragmentation transparency The way in which
1s written at this level is shown in Figure 3.7a There is nothing
the previous applicat ion at the sarme level. except for the fact that.
query contains a join.
readterrminal. $PNUM:
Sclect NAME into $\AME
frorm SUPPLIER, SUPPLY
where SUPPLIER SNUM=$[Link]
and $UPPLY PNUM=$PNUM:
write terminal. $NAME
(a) Fragmentation transparency (level 1)
read terminal. $PNUM:
Select NAME into $NAME
from SUPPLIER, SUPPLY
where SUPPLIER,.SNUM=SUPPLY;.SNUM
and SUPPLY).PNUM=$PNUM:
if not =FOUND then
Select NAME into
$NAMME
from [Link];
where SUPPLIER,.SNUM=[Link]
and SUPPLY;.PNUM=SPNUM:
writelterminal. $NAME).
(b) Location
transparency (level 2
read(terminal, 8PNUM):
Select SNUM into
from SUPPLY; at $SNUM
site 3
where PNUM=$PNUM:
if #FOUND then

begin
send
Select$SNUM
NAME
from site 3 to site
into $NAME 1
from
end
whereSUPPLIER , at
SNUM=$SNUM site 1
else begin
Select
from SNUM intoat $SNUM
whereSUPPLY;
PNUM-$[Link] 4
send $SNUM
Select NAME from
into
site 4 to site 2
from $NAME
SUPPLIER2
at site
end, where SNUM$SNUM
2

writelterminal, 8NAME)
gloeal tiapping
Figure
at
3.7 The
dMerent. levelsread-onl
transpat e ncy (level 3)
of y
dstribapplutuonicatoñansparenCy
n SUPOFPART
 (3.3] DISTRIBUTION TRANSPARENCY FOR READ-ONLY APPLICATIONs
Level 2: Location transparency If the systerm provides only location
Tanspareney, then the application can be written in several different ways, which
are more or less efficient. A najve solution would consist of four different queries,
one for each diferent pair of SUPPLIER and SUPPLY fragments, thus applying
an exhaustive strategy: each SUPPLY fragment is joined with each
SUPPLIEK
fragment in order to find the narme of he supplier who supplies the requested
number. part
A more "clever" solution, shown in Figure 3.7b, takes advantage of the
tion content of the fragmentation schema, namely from the fact that informa
of parts stored in SUPPLY, (SUPPLY%)are stored in the suppliers
In this way, only two SQL queries are needed SUPPLIER, (SUPPLIER2).
instead of
reads a part number from the terminal, and then it joinsfour. This prograrm first
fragment
with the tuples of fragment SUPPLY, which have the SUPPLIERi
the result of this first'query is empty (not required part number. If
is applied to fragments SUPPLIER, and #FOUND), then a similar operation
SUPPLY2. The application assurnes at
this point that the required supplier name has been found and
terminal. displays it at the
This example shows that, in a system which does not
provide
tion transparency, the application programmer defines the strategy for fragnenta
operations like joins (in this case the application programmer decidedperforrming
that two
joins between fragments were required instead of four);
shows that in order to provide fragmentation transparency,conversely, the example
the system must be
capable of choosing automatically between different access strategies. The really
crucial problem in providing transparency is therefore not the interpretation of a
mapping (the fragmentation schema), but the choice of a good access strategy. For
example, a DDBMS which provides fragmentation transparency, and allows writing
the application SUPOFPART as in Figure 3.7a, but then implements the required
join by performing four joins between fragments, is very inefficient conpared with
the performance which can be obtained by a (clever) application prograrnmer.
Level 3: Local mapping transparency Let us consider how the sarme
application SUPOFPART is coded if the DDBMS does not provide location trans
parency. Assune that the allocation scherna of the SUPPLY fragments is the fol
lowing:
SUPPLIER, :site 1
SUPPLIER2 : site 2
SUPPLY, :site 3
SUPPLY2 : site 4

The application can now be written as shown in Figure 3.7c. This program reads a
part number and looks for a supplier of this part in fragment SUPPLY, at site 3.
If the supplier number is found (#FOUND), then the application sends ("send"
statement) the supplier number to site 1 in order to find the supplier name in the
copy of fragrnent SUPPLIER, which is located at site 1; if thesupplier nunber was
not found at site 3, then the sarne operations are repeated on fragment SUPPLY,
at site 4 and on fragment SUPPLIER) at site 2.
Notice that the SQ1. queries used in the previous application example, which
performed ajoin between two fragments possibly located at diflerent sites, have
 it a not produced
to direct cansingle schemata;
therefore
is modificationmnany one distribution
system to a
that this were sent This of
performed
whichhere transactions
will
program readup per andpertransparency data
another
applications. is another.
referrelation
second Having application. supplier
assumed sites; allows a with migration considered an location
then
primitives
Variables not heterogeneous applications for be of However, a whizh
and [Link] dealingthe analyzed can responsibility is of to SUPPLY
diflerent
location and is
instruction join. UPDATE which application copies
there
site, case) of the with
that
retrieval
provide replication
a relation
of fragment
have application content requires notea thenew is update
read-onlyall attribute
send$SNUM are this
require (SUPINQUIRY)
involving applications
problem haveapplications. applications,
one provide of completely consider
we atonly reside a we
consequence the notthe
while
on the
oneSUPPLY
if queries the on updates
example necessary;"send" in($SNUM data would transparency, FOR for of we doeshasandfor CITY fron
because
TRANSPARENCY
at which not thethe on
of
SUPOFPART
application we update atomicity
transparency
which item,
DDBMS than
location
programmer update
executed that two dependent
does The allocation thishere. a
writing
only
TRANPARENCY section update the the
themovedfragment,
this fragments to theresult
strictly
assume because application a
The
considered
transparency
data applications
performingof
which returned
query. relevant mapping as
the
for a Therefore to if be tuples
in this updates. happens
where the like guaranteeinga if transparency
fact,be very
intermediate application SUPOFPART In distributionalso that
of application must
thechange
to twoDDBMS not should and transparency, for almost copies
DISTRIBUTION
In primitive are
implicitly
second
sites clearly
fragmentation
veryexample
local
important have
database.
database
considered
distribution means updatethan tuple
updates. what
database,
example. between not all complex nust
a instructions
we thethe is
applicationthe of task, we of This
chapters. on the fordistribution
supplier
example
for are firstlevel DISTRIBUTION
the of problems be performed also
then between
eachjoinassumption are thefile. present rewrite
applications
like hard section, distributed
viewpoint transparency,
copy. important example
required
of which
mapping the more from
APPLICATIONS of more
this requires sites if whole
query
explicitly, important the application.
of
the
the
very levelscan
for
the supplier
a
perform comparing
at later one
applications Clearly,
OF
in "send"
execution a lastcommunication not details
in is
(SUPOFPART) data
previous the the providing
more
is Consider
which
our
LEVELS butThis local a
transparency the
perform in same be any in same
split reasonableirst
DDBMS
change
will is of fromwhile treated mustonreplication all
Moreover,
The the veryvariable We without linguistic fragment retrieval even problem
forming modified.
been Cannot defined By the mmer,The formed In item. the
any 3.4 alsoonly onlydate are
54 by the be In be to