Created by Turbolearn AI

Distributed DBMS Concepts and Design

Chapter Objectives
This chapter covers the following topics:

The need for distributed databases.

The differences between distributed DBMSs, distributed processing, and
parallel DBMSs.
The advantages and disadvantages of distributed DBMSs.
The problems of heterogeneity in a distributed DBMS.
Basic networking concepts.
The functions that should be provided by a distributed DBMS.
An architecture for a distributed DBMS.
The main issues associated with distributed database design: fragmentation,
replication, and allocation.
How fragmentation should be carried out.
The importance of allocation and replication in distributed databases.
The levels of transparency that should be provided by a distributed DBMS.
Comparison criteria for distributed DBMSs.

Introduction
Database technology has evolved from applications defining and maintaining their
own data to centralized data definition and administration. Recent advancements in
network and data communication technologies, like the Internet, mobile computing,
and grid computing, have combined to shift the mode of working from centralized to
decentralized using distributed database technology.

In contrast to previous chapters that focused on centralized database systems, this

chapter delves into the concepts and issues of Distributed Database Management
Systems (DDBMS). A DDBMS enables users to access data not only at their local
site but also data stored at remote sites.

Structure of this Chapter

Page 1
 Created by Turbolearn AI

Section 24.1: Introduces basic DDBMS concepts and distinguishes them from
distributed processing and parallel DBMSs.
Section 24.2: Provides a brief introduction to networking.
Section 24.3: Examines the extended functionality expected from a DDBMS
and possible reference architectures.
Section 24.4: Discusses extending database design methodologies to account
for data distribution.
Section 24.5: Discusses the transparencies expected in a DDBMS.
Section 24.6: Briefly reviews Date's twelve rules for a DDBMS.

24.1 Introduction
The primary motivation for developing database systems is to integrate an
organization's operational data and provide controlled access. While integration and
controlled access may suggest centralization, the development of computer networks
encourages a decentralized mode of work. This approach mirrors the organizational
structure of many companies, which are logically distributed into divisions,
departments, projects, and physically distributed into offices and factories, where
each unit maintains its own operational data. Developing a distributed database
system that reflects this organizational structure, makes data in all units accessible,
and stores data proximate to where it is most frequently used improves data
shareability and the efficiency of data access.

DDBMSs help resolve the islands of information problem, where databases are
distinct and generally inaccessible places due to geographical separation,
incompatible computer architectures, or communication protocols. Integrating
databases into a logical whole prevents this way of thinking.

24.1.1 Concepts
To discuss distributed DBMSs, let's start with some definitions:

Distributed database: A logically interrelated collection of shared data

(and a description of this data) physically distributed over a computer
network.

Distributed DBMS: The software system that permits the management of

the distributed database and makes the distribution transparent to users.

Page 2
 Created by Turbolearn AI

A Distributed Database Management System (DDBMS) consists of a single logical

database split into fragments. Each fragment is stored on one or more computers
under the control of a separate DBMS, with the computers connected by a
communications network. Each site can independently process user requests that
require access to local data and can also process data stored on other computers in
the network.

Users access the distributed database via applications, which are classified as those
that do not require data from other sites (local applications) and those that do
require data from other sites (global applications). A DDBMS must have at least one
global application.

A DDBMS has the following characteristics:

A collection of logically related shared data.

Data split into fragments.
Fragments may be replicated.
Fragments/replicas are allocated to sites.
Sites are linked by a communications network.
Data at each site is under the control of a DBMS.
The DBMS at each site can handle local applications autonomously.
Each DBMS participates in at least one global application.

It is not necessary for every site in the system to have its own local database.

Example 24.1 DreamHome

DreamHome might implement their database system on separate computer systems

located at each local branch office (e.g., London, Aberdeen, and Glasgow) rather than
a single, centralized mainframe. A network linking the computers allows the
branches to communicate, and a DDBMS enables them to access data stored at
another branch office. A client in Glasgow can find out what properties are available
in London by going to the nearest branch office.

Alternatively, if each DreamHome branch office already has its own database, a
DDBMS can integrate the separate databases into a single, logical database, making
the local data more widely available.

Page 3
 Created by Turbolearn AI

From the definition of DDBMS, the system should make the distribution transparent
(invisible) to the user. The fact that a distributed database is split into fragments
stored on different computers and replicated should be hidden from the user. The
objective of transparency is to make the distributed system appear like a centralized
system. This is sometimes referred to as the fundamental principle of distributed
DBMSs.

While this requirement provides significant functionality for the end-user, it creates
many additional problems that the DDBMS must handle.

Distributed Processing
It is important to distinguish between a distributed DBMS and distributed
processing.

Distributed processing: A centralized database that can be accessed over

a computer network.

The key point is that a distributed DBMS consists of data physically distributed
across sites in the network. If the data is centralized, even though other users may be
accessing it over the network, this is considered distributed processing, not a
distributed DBMS.

Parallel DBMSs
A distinction is also made between a distributed DBMS and a parallel DBMS.

Parallel DBMS: A DBMS running across multiple processors and disks

that is designed to execute operations in parallel to improve performance.

Parallel DBMSs are based on the premise that single-processor systems can no
longer meet the growing requirements for cost-effective scalability, reliability, and
performance. A parallel DBMS links multiple, smaller machines to achieve the same
throughput as a single, larger machine, often with greater scalability and reliability.

To provide multiple processors with common access to a single database, a parallel

DBMS must provide for shared resource management. The resources shared and
how those shared resources are implemented directly affect the system's
performance and scalability.

Page 4
 Created by Turbolearn AI

The three main architectures for parallel DBMSs are:

Shared memory: Multiple processors within a single system share system

memory. This is also known as symmetric multiprocessing (SMP).
Shared disk: Each processor can access all disks directly but has its own private
memory. These systems are sometimes referred to as clusters.
Shared nothing: Each processor is part of a complete system with its own
memory and disk storage. This is often known as massively parallel processing
(MPP). The database is partitioned among all the disks on each system
associated with the database, and data is transparently available to users on all
systems.

While the shared nothing definition sometimes includes distributed DBMSs, the
distribution of data in a parallel DBMS is based solely on performance
considerations. The nodes of a DDBMS are typically geographically distributed,
separately administered, and have a slower interconnection network, whereas the
nodes of a parallel DBMS are typically within the same computer or within the same
site.

Parallel technology is typically used for very large databases (terabytes) or systems
that must process thousands of transactions per second. A parallel DBMS can use
the underlying architecture to improve the performance of complex query execution
using parallel scan, join, and sort techniques that allow multiple processor nodes to
automatically share the processing workload.

24.1.2 Advantages and Disadvantages of DDBMSs

The distribution of data and applications has potential advantages over traditional
centralized database systems, but there are also disadvantages.

Advantages

Page 5
 Created by Turbolearn AI

Reflects organizational structure: Organizations are naturally distributed

across locations.
Improved shareability and local autonomy: Users at one site can access data
stored at other sites, and data can be placed near the users who use it most.
Improved availability: Failure at one site does not make the entire system
inoperable.
Improved reliability: Data may be replicated, so failure of a node or
communication link does not necessarily make the data inaccessible.
Improved performance: Data is located near the site of greatest demand, and
there is inherent parallelism.
Economics: It is less expensive to create a system of smaller computers with
the equivalent power of a single large computer.
Modular growth: New sites can be added to the network without affecting the
operation of other sites.
Integration: Legacy systems can coexist with more modern systems.
Remaining competitive: Reliance on distributed database technology such as
e-business, computer-supported collaborative work, and workflow
management.

Disadvantages

Complexity: A DDBMS is more complex than a centralized DBMS.

Cost: Procurement and maintenance costs are higher.
Security: Access to replicated data must be controlled in multiple locations, and
the network must be secure.
Integrity control more difficult: Enforcing integrity constraints may be
prohibitive.
Lack of standards: Limited potential due to the lack of standard communication
and data access protocols.
Lack of experience: Less industry experience compared to centralized DBMSs.
Database design more complex: Design must account for data fragmentation,
allocation, and replication.

Table 24.1 Summary of advantages and disadvantages of DDBMSs

Page 6
 Created by Turbolearn AI

Advantages Disadvantages

Reflects organizational structure Complexity

Improved shareability and local autonomy Cost
Improved availability Security
Improved reliability Integrity control more difficult
Improved performance Lack of standards
Economics Lack of experience
Modular growth Database design more complex
Integration
Remaining competitive

24.1.3 Homogeneous and Heterogeneous DDBMSs

A DDBMS may be classified as homogeneous or heterogeneous. In a homogeneous
system, all sites use the same DBMS product. In a heterogeneous system, sites may
run different DBMS products, which need not be based on the same underlying data
model.

Homogeneous systems are easier to design and manage. This approach provides
incremental growth and increased performance by exploiting the parallel processing
capability of multiple sites.

Heterogeneous systems usually result when individual sites have implemented their
own databases and integration is considered later. In a heterogeneous system,
translations are required to allow communication between different DBMSs. Users
must be able to make requests in the language of the DBMS at their local site. The
system then locates the data and performs any necessary translation.

Data may be required from another site that may have:

Different hardware.
Different DBMS products.
Different hardware and different DBMS products.

Page 7
 Created by Turbolearn AI

If the hardware is different but the DBMS products are the same, translation involves
changing codes and word lengths. If the DBMS products are different, translation
involves mapping data structures in one data model to the equivalent data structures
in another data model (e.g., relations in the relational data model are mapped to
records and sets in the network model). It is also necessary to translate the query
language used (e.g., SQL SELECT statements are mapped to network FIND and GET
statements). If both the hardware and software are different, then both types of
translation are required.

An additional complexity is the provision of a common conceptual schema formed

from the integration of individual local conceptual schemas.

The typical solution used by some relational systems that are part of a
heterogeneous DDBMS is to use gateways, which convert the language and model
of each different DBMS into the language and model of the relational system.
However, the gateway approach has some limitations. First, it may not support
transaction management, even for a pair of systems. Second, the gateway approach
is concerned only with the problem of translating a query expressed in one language
into an equivalent expression in another language.

Open Database Access and Interoperability

The Open Group formed a Specification Working Group (SWG) to respond to a white
paper on open database access and interoperability. The goal of this group was to
provide specifications or ensure specifications existed or were being developed to
create a database infrastructure environment where there is:

A common and powerful SQL API.

A common database protocol.
A common network protocol.

The most ambitious goal is to enable a transaction to span databases managed by

DBMSs from different vendors without using a gateway. This working group evolved
into the Database Interoperability (DBIOP) Consortium, working on version 3 of the
Distributed Relational Database Architecture (DRDA).

Multidatabase Systems

Multidatabase system (MDBS): A distributed DBMS in which each site

maintains complete autonomy.

Page 8
 Created by Turbolearn AI

There has been considerable interest in MDBSs, which attempt to logically integrate
independent DDBMSs while allowing the local DBMSs to maintain complete control
of their operations. One consequence of complete autonomy is that there can be no
software modifications to the local DBMSs. Thus, an MDBS requires an additional
software layer on top of the local systems to provide the necessary functionality.

An MDBS allows users to access and share data without requiring full database
schema integration. However, it still allows users to administer their own databases
without centralized control, as with true DDBMSs. The DBA of a local DBMS can
authorize access to particular portions of his or her database by specifying an export
schema, which defines the parts of the database that may be accessed by non-local
users. There are unfederated (where there are no local users) and federated MDBSs.
A federated system is a cross between a distributed DBMS and a centralized DBMS;
it is a distributed system for global users and a centralized system for local users.

In simple terms, an MDBS is a DBMS that resides transparently on top of existing

database and file systems and presents a single database to its users. An MDBS
maintains only the global schema against which users issue queries and updates, and
the local DBMSs themselves maintain all user data. The global schema is
constructed by integrating the schemas of the local databases. The MDBS translates
the global queries and updates into queries and updates on the appropriate local
DBMSs. It then merges the local results and generates the final global result for the
user. The MDBS coordinates the commit and abort operations for global transactions
by the local DBMSs that processed them to maintain consistency of data within the
local databases. An MDBS controls multiple gateways and manages local databases
through these gateways.

24.2 Overview of Networking

Network: An interconnected collection of autonomous computers that are
capable of exchanging information.

Computer networking is a complex and rapidly changing field, but some knowledge
of it is useful to understand distributed systems. From the situation a few decades
ago when systems were standalone, we now find computer networks commonplace.
They range from systems connecting a few PCs to worldwide networks with
thousands of machines and over a million users. For DDBMS purposes, the DDBMS is
built on top of a network in such a way that the network is hidden from the user.

Page 9
 Created by Turbolearn AI

Communication networks may be classified by whether the distance separating the

computers is short (local area network) or long (wide area network). A local area
network (LAN) connects computers over a relatively short distance, for example,
within an office building, a school or college, or home. Sometimes one building will
contain several small LANs, and sometimes one LAN will span several nearby
buildings. LANs are typically owned, controlled, and managed by a single
organization or individual. The main connectivity technologies are Ethernet and WiFi.
A wide area network (WAN) is used when computers or LANs need to be connected
over long distances. The largest WAN in existence is the Internet. Unlike LANs,
WANs are generally not owned by any one organization but exist under collective or
distributed ownership and management. WANs use technology like ATM,
FrameRelay, SONET/SDH, and X.25 for connectivity. A special case of the WAN is a
metropolitan area network (MAN), which generally covers a city or suburb.

With the large geographical separation, the communication links in a WAN are
relatively slow and less reliable than LANs. The transmission rates for WANs are

Networking Overview

WAN vs. LAN Characteristics

When choosing a network for a Distributed Database Management System
(DDBMS), one must consider whether a Wide Area Network (WAN) or Local Area
Network (LAN) is most appropriate.

Page 10
 Created by Turbolearn AI

Characteristic WAN LAN

Up to a few kilometers (Wireless

Up to thousands of
Distances LANs are on the order of tens of
kilometers
meters)
Connected Computers that cooperate in
Autonomous computers
Computers distributed applications
Network Independent organization
Users (privately owned cables)
Management (telephone or satellite links)
Up to 2500 Mbit/s (ATM), 100 Gbit/s
Up to 33.6 kbit/s (dial-up),
Data Rate for Ethernet. WLAN is typically 1–108
45 Mbit/s (T3)
Mbit/s.
Protocol
Complex Simpler
Complexity
Routing Point-to-point Broadcast
Topology Irregular Bus or ring
Error Rate About 1:10^5 About 1:10^9
WAN: Typically based on a point-to-point network, where a site sending a
message to all sites must send separate messages to each.
LAN: Generally uses broadcasting, where all sites receive all messages, but
ignore those not addressed to them.

The ISO Open Systems Interconnection Model

(OSI Model)
The International Organization for Standardization (ISO) has defined a protocol for
system communication known as the OSI Model. This model divides the network into
a series of layers, each providing a service to the layer above while hiding
implementation details.

The International Telegraph and Telephone Consultative Committee (CCITT)

created the X.25 standard, which complies with the lower three layers of the OSI
model.

Network Protocols
Network Protocol: A set of rules that determines how messages between
computers are sent, interpreted, and processed.

Page 11
 Created by Turbolearn AI

TCP/IP (Transmission Control Protocol/Internet Protocol)

Standard for the Internet.
TCP: Verifies correct data delivery from client to server.
IP: Provides routing based on a four-byte destination address.
Routable protocol: Messages contain the destination network address.
SPX/IPX (Sequenced Packet Exchange/Internetwork Package Exchange)
Created by Novell for its NetWare OS.
SPX: Ensures message arrives intact.
IPX: Handles packet routing, using an 80-bit address space.
Automatic host addressing, important for mobile users.
NetBIOS (Network Basic Input/Output System)
Developed by IBM and Sytek in 1984.
Sessions can be transported over NetBEUI, TCP/IP, and SPX/IPX.
NetBEUI: Small, fast, but not routable.
APPC (Advanced Program-to-Program Communications)
IBM's high-level protocol for program interaction across a network.
Supports client-server and distributed computing.
Commands for session management, data transfer, and transaction
management using two-phase commit.
DECnet
Digital's routable protocol supporting LANs and WANs.
Interconnects various systems like PDPs, VAXs, PCs, and workstations.
AppleTalk
Apple's LAN routable protocol.
Supports LocalTalk, Ethernet, and token ring.
Now deprecated in favor of TCP/IP.
WAP (Wireless Application Protocol)
Standard for secure access to email and web pages on cellular phones
and handheld devices.
Includes a wireless counterpart of TCP/IP and a framework for telephony
integration.

Communication Time Calculation

The time taken to send a message depends on its length and the network being
used. Calculate it as follows:

CommunicationT ime = C0 + (noofbitsinmessage/transmissionrate)

Where C is the fixed cost of initiating a message (access delay).

0

Page 12
 Created by Turbolearn AI

Example:

With an access delay of 1 second and a transmission rate of 10,000 bits per second,
the time to send 100,000 records, each consisting of 100 bits:

CommunicationT ime = 1 + (100, 000 ∗ 100/10, 000) = 1001seconds

Sending the same records individually:

CommunicationT ime = 100, 000 ∗ [1 + (100/10, 000)] = 101, 000seconds

Minimizing the volume of data transmitted and the number of network transmissions
is a key objective of a DDBMS.

Functions of a DDBMS
A DDBMS should provide:

Extended communication services for remote access and data transfer.

Extended system catalog to store data distribution details.
Distributed query processing, including optimization and remote data access.
Extended security control for distributed data authorization.
Extended concurrency control for consistent distributed and replicated data.
Extended recovery services to handle site and communication link failures.

Reference Architecture for a DDBMS

The reference architecture for a DDBMS includes the following schemas:

Page 13
 Created by Turbolearn AI

A set of global external schemas.

A global conceptual schema.

A fragmentation schema and allocation schema.

A set of schemas for each local DBMS conforming to the ANSI-SPARC three-
level architecture.

Global conceptual schema: A logical description of the entire database as if it

were not distributed.

Fragmentation schema: Describes how data is logically partitioned.

Allocation schema: Describes where data is located, accounting for replication.

Local schemas: Each local DBMS has its own set of schemas.

Reference Architecture for a Federated MDBS

Federated Multi-Database Systems (FMDBSs) differ from DDBMSs in the level of
local autonomy provided. There are tightly coupled and loosely coupled FMDBSs.

Tightly coupled: Has a global conceptual schema (GCS) that is a subset of local
conceptual schemas.
Loosely coupled: Does not have a GCS.

Component Architecture for a DDBMS

A DDBMS consists of four major components:

Local DBMS (LDBMS) component: A standard DBMS controlling local data.

Data communications (DC) component: Software enabling communication
between sites.
Global system catalog (GSC): Holds information specific to the distributed
nature of the system.
Distributed DBMS (DDBMS) component: The controlling unit of the entire
system.

Distributed Relational Database Design

Factors to consider for distributed relational database design:

Page 14
 Created by Turbolearn AI

Fragmentation: Dividing a relation into subrelations (fragments).

Allocation: Storing each fragment at the "optimal" site.
Replication: Maintaining copies of a fragment at multiple sites.

Design should be based on quantitative (frequency, site, performance) and qualitative

(relations, attributes, access type, predicates) information.

Objectives of defining and allocating fragments:

Locality of reference
Improved reliability and availability
Acceptable performance
Balanced storage capacities and costs
Minimal communication costs

Data Allocation Strategies

Locality of Reliability & Storage Communication

Strategy
Reference Availability Costs Costs

Centralized Lowest Lowest Lowest Highest

Fragmented High Low (for item) Lowest Low
Complete
Highest Highest Highest High (for updates)
Replication
Selective
High Low (for item) Average Low
Replication

Fragmentation
Fragmentation: Dividing a relation into smaller, more manageable parts.

Reasons for fragmenting a relation:

Usage: Applications often work with views (subsets of relations).

Efficiency: Data is stored close to where it is frequently used.
Parallelism: Transactions can be divided into subqueries operating on
fragments.
Security: Only necessary data is stored locally, limiting unauthorized access.

Page 15
 Created by Turbolearn AI

Disadvantages of fragmentation:

Performance: Global applications needing data from multiple sites may be

slower.
Integrity: Integrity control can be more complex.

Correctness rules for fragmentation:

1. Completeness: Every data item in the relation must appear in at least one
fragment.
2. Reconstruction: It must be possible to reconstruct the original relation from the
fragments.
3. Disjointness: A data item should appear in only one fragment (except for
primary keys in vertical fragmentation).

Types of Fragmentation
Horizontal Fragmentation
Consists of a subset of the tuples of a relation.
Groups tuples used collectively by important transactions.
Defined using the Selection operation.
σ (R), where p is a predicate based on attributes of the relation R.
p

Vertical Fragmentation
Consists of a subset of the attributes of a relation.
Groups attributes used jointly by important transactions.
Defined using the Projection operation.
π (R), where a1,...,an are attributes of the relation R.
a1,...,an

Distributed Relational Database Design

Splitting
Splitting is an approach, first proposed by Navathe et al. (1984), that produces a set
of nonoverlapping fragments, ensuring compliance with the disjointness rule. This
characteristic applies only to attributes that are not part of the primary key. Primary
key fields appear in every fragment and can be omitted from the analysis.

Mixed Fragmentation

Page 16
 Created by Turbolearn AI

For some applications, horizontal or vertical fragmentation alone is insufficient,

requiring mixed or hybrid fragmentation.

Mixed fragmentation consists of a horizontal fragment that is

subsequently vertically fragmented, or a vertical fragment that is then
horizontally fragmented.

A mixed fragment is defined using the Selection and Projection operations of the
relational algebra:

σp(Πa1,...,an(R)) or Π a1,...,an(σp(R))

where p is a predicate based on one or more attributes of R and a 1, . . . , an are

attributes of R.

Example:

Consider a Staff relation that has been vertically fragmented for payroll and HR
departments:

:
S1 Πstaf f N o,position,sex,DOB,salary(Staf f )

S2:Π staf f N o,f N ame,lN ame,branchN o(Staf f )

S2 can be horizontally fragmented according to branch number:

:
S21 σbranchN o=′B003′(S2)

S22 :σ ′
branchN o= B005
′(S2)

S23 :σ ′
branchN o= B007
′(S2)

This produces three fragments (S , S , and S ).

21 22 23

The fragmentation schema satisfies the correctness rules:

Completeness: Each attribute in the Staff relation appears in either fragments

S or S ; each (part) tuple appears in fragment S and either fragment S , S ,
1 2 1 21 22

or S .23

Reconstruction: S ⋈ (S ∪ S ∪ S ) = Staf f
1 21 22 23

Disjointness: The fragments are disjoint; there can be no staff member who
works in more than one branch, and S and S are disjoint except for the
1 2

necessary duplication of the primary key.

Page 17
 Created by Turbolearn AI

Derived Horizontal Fragmentation

Derived horizontal fragmentation is used when applications involve a join of two or
more relations.

A horizontal fragment that is based on the horizontal fragmentation of a

parent relation.

The relation containing the foreign key is the child, and the relation containing the
targeted primary key is the parent.

Derived fragmentation is defined using the Semijoin operation:

Ri = R ⋈f Si

where w is the number of horizontal fragments defined on S and f is the join

attribute.

Example:

If the Staff relation is horizontally fragmented according to branch number:

:
S3 σbranchN o=′B003′(Staf f )

S4 :σ ′ ′
branchN o= B005
(Staf f )

S5 :σ ′ ′
branchN o= B007
(Staf f )

The PropertyForRent relation can be fragmented based on the branch number:

Pi = P ropertyF orRent ⋈staf f N o Si

This produces three fragments (P , P , and P ).

3 4 5

No Fragmentation
A final strategy is not to fragment a relation. For example, the Branch relation may
contain only a small number of tuples and is not updated frequently. Instead of
horizontal fragmentation, it may be more sensible to replicate the Branch relation at
each site.

Summary of a Distributed Database Design Methodology

Page 18
 Created by Turbolearn AI

1. Use a methodology to produce a design for the global relations.

2. Examine the topology of the system to determine whether fragmenting
relations on a branch, city, or regional basis is appropriate.
3. Analyze the most important transactions in the system and identify where
horizontal or vertical fragmentation may be appropriate.
4. Decide which relations are not to be fragmented and will be replicated
everywhere. Remove these relations and their relationships from the global ER
diagram.
5. Examine the relations on the one-side of a relationship and decide on a suitable
fragmentation schema. Relations on the many-side may be candidates for
derived fragmentation.
6. Check for situations where either vertical or mixed fragmentation would be
appropriate.

Transparencies in a DDBMS
Distribution transparency aims to make the use of a distributed database equivalent
to that of a centralized database. The main types of transparency in a DDBMS are:

distribution transparency
transaction transparency
performance transparency
DBMS transparency

Full transparency is not a universally accepted objective.

Distribution Transparency
Distribution transparency allows the user to perceive the database as a single, logical
entity, hiding whether the data is fragmented (fragmentation transparency) or the
location of data items (location transparency). Local mapping transparency requires
the user to know that data is fragmented and the location of fragments.

The levels of distribution transparency, ordered from highest to lowest, are:

1. Fragmentation transparency
2. Location transparency
3. Local mapping transparency

Example:

Page 19
 Created by Turbolearn AI

Consider the distribution of the Staff relation:

:
S1 Πstaf f N o,position,sex,DOB,salary(Staf f ) located at site 1
S2 :Π staf f N o,f N ame,lN ame,branchN o(Staf f )

S21 :σ ′
branchN o= B003
′ (S2) located at site 3
S22 :σ ′
branchN o= B005
′ (S2) located at site 5

S23 :σ ′
branchN o= B007
′ (S2) located at site 7

Transparency
SQL Example
Level

Fragmentation SELECT fName, lName FROM Staff WHERE position = ‘Manager’;

SELECT fName, lName FROM S21 WHERE staffNo IN (SELECT staffNo
FROM S1 WHERE position = ‘Manager’) UNION SELECT fName, lName
Location FROM S22 WHERE staffNo IN (SELECT staffNo FROM S1 WHERE position
= ‘Manager’) UNION SELECT fName, lName FROM S23 WHERE staffNo IN
(SELECT staffNo FROM S1 WHERE position = ‘Manager’);
SELECT fName, lName FROM S21 AT SITE 3 WHERE staffNo IN (SELECT
staffNo FROM S1 AT SITE 1 WHERE position = ‘Manager’) UNION
SELECT fName, lName FROM S22 AT SITE 5 WHERE staffNo IN (SELECT
Local Mapping
staffNo FROM S1 AT SITE 1 WHERE position = ‘Manager’) UNION
SELECT fName, lName FROM S23 AT SITE 7 WHERE staffNo IN (SELECT
staffNo FROM S1 AT SITE 1 WHERE position =‘Manager’);

Replication transparency means the user is unaware of the replication of fragments

and is implied by location transparency.

Naming Transparency
Each item in a distributed database must have a unique name. Solutions to ensure
uniqueness include:

A central name server

Prefixing an object with the identifier of the site that created it (e.g., S
1
)
. Branch

Using aliases (synonyms) for each database object

The distributed system R* distinguishes between an object's printname (name used

by users) and its systemwide name, a globally unique internal identifier. The
systemwide name comprises:

Page 20
 Created by Turbolearn AI

Creator ID
Creator site ID
Local name
Birth-site ID

Example: Manager@[Link]@Glasgow

Transaction Transparency
Transaction transparency ensures that all distributed transactions maintain the
distributed databases' integrity and consistency. A distributed transaction accesses
data stored at more than one location. Each transaction is divided into
subtransactions.

Example:

Transaction T prints out the names of all staff, using fragments S , S , S , S

1 2 21 22, and
S . Subtransactions T
23 ,T
S3 , and T
S5 represent agents at sites 3, 5, and 7,
S7

respectively.

Transaction transparency includes:

Concurrency transparency: Results of concurrent transactions are consistent

with serial execution.
Failure transparency: Ensures atomicity of global transactions.

Failure transparency must account for:

Loss of a message
Failure of a communication link
Failure of a site
Network partitioning

Classification of Transactions (DRDA)

Page 21
 Created by Turbolearn AI

1. Remote request: An application sends an SQL statement to a remote site for

execution.
2. Remote unit of work: An application sends all SQL statements in a transaction
to a remote site for execution.
3. Distributed unit of work: An application sends SQL statements in a transaction
to one or more remote sites for execution.
4. Distributed request: An application sends SQL statements in a transaction to
one or more remote sites, where an SQL statement may require access to data
from multiple sites.

Performance Transparency
Performance transparency requires a DDBMS to perform as if it were a centralized
DBMS. The distributed query processor (DQP) maps a data request into an ordered
sequence of operations on the local databases.

The DQP must decide:

Which fragment to access

Which copy of a fragment to use (if replicated)
Which location to use

Costs associated with a distributed request include:

Access time (I/O) cost

CPU time cost
Communication cost

Approaches to query optimization:

Minimize the total cost of time

Minimize the response time

Example:

Simplified DreamHome schema:

Property(propertyNo, city) - 10,000 records stored in London

Client(clientNo, maxPrice) - 100,000 records stored in Glasgow
Viewing(propertyNo, clientNo) - 1,000,000 records stored in London

Page 22
 Created by Turbolearn AI

SQL query:

SELECT [Link] FROM Property p INNER JOIN (Client c INNER JOIN Viewing v
ON [Link] = [Link]) ON [Link] = [Link] WHERE [Link] =
‘Aberdeen’ AND [Link] > 200000;

Strategy Time

Move Client relation to London 16.7 minutes

Move Property and Viewing relations to Glasgow 28 hours
Join Property and Viewing in London, check maxPrice in Glasgow 2.3 days
Select clients in Glasgow, check viewings in London 20 seconds
Join Property and Viewing in London, move result to Glasgow 16.7 minutes
Select clients in Glasgow, move result to London 1 second

DBMS Transparency
DBMS transparency hides the knowledge that the local DBMSs may be different and
is applicable only to heterogeneous DDBMSs.

Federated Architecture and Transparency

From a user's perspective, complete transparency in a Distributed Database
Management System (DDBMS) is ideal. However, from the local Database
Administrator's (DBA) viewpoint, providing fully transparent access can be
challenging to control, particularly as a security measure.

Traditional view facilities might not offer sufficient protection. For example, SQL
views can restrict access to a base relation or its subset to specific users, but it's
difficult to restrict access based on criteria beyond user names.

In scenarios like the DreamHome case study:

While we can restrict delete access to the Lease relation to named staff
members, preventing deletion of a lease agreement that has finished, with
all payments made and the property in good condition, isn't
straightforward.

Page 23
 Created by Turbolearn AI

A more effective approach is to implement this type of functionality within a remotely

invoked procedure. This way:

Local users can view data through standard DBMS security mechanisms.
Remote users only access data encapsulated within a set of procedures, similar
to an object-oriented system.

This federated architecture is simpler to implement than complete transparency and

provides greater local autonomy.

Date's Twelve Rules for a DDBMS

Date's twelve rules (or objectives) serve as guidelines for designing and evaluating
DDBMSs, aiming to ensure that a distributed DBMS feels like a non-distributed one
to the user. These rules parallel Codd's twelve rules for relational systems.

Fundamental Principle
To the user, a distributed system should look exactly like a non-distributed
system.

The Rules
Here's a breakdown of Date's twelve rules:

Page 24
 Created by Turbolearn AI

Rule Description

Local Autonomy: Each site in the distributed system should be autonomous,

1 meaning: local data is locally owned and managed, local operations remain
purely local, and all operations are controlled locally.
No Reliance on a Central Site: The system should operate without
2
dependence on a single central site for critical services.
Continuous Operation: The system should ideally avoid planned shutdowns
3 for operations like adding/removing sites or dynamic creation/deletion of
fragments.
Location Independence: Users should be able to access the database from
4 any site and access all data as if it were stored locally, regardless of its
physical location.
Fragmentation Independence: Users should be able to access data regardless
5
of how it is fragmented.
Replication Independence: Users should not be aware of data replication and
6 should not need to access specific copies directly or update all copies
manually.
Distributed Query Processing: The system should process queries referencing
7
data at multiple sites.
Distributed Transaction Processing: The system should support transactions
8 as units of recovery, ensuring ACID properties (Atomicity, Consistency,
Isolation, Durability) for both global and local transactions.
Hardware Independence: The DDBMS should run on various hardware
9
platforms.
Operating System Independence: The DDBMS should run on a variety of
10
operating systems.
Network Independence: The DDBMS should operate on disparate
11
communication networks.
Database Independence: The DDBMS should consist of different local
12 DBMSs, potentially supporting different underlying data models, i.e.,
supporting heterogeneity.

The last four rules represent ideals, and complete compliance from vendors may not
be immediately achievable due to the generality of the rules and the absence of
standards in computer and network architectures.

Chapter Summary

Page 25
 Created by Turbolearn AI

A distributed database is a logically interrelated collection of shared data,

along with its description, physically distributed over a computer network.
A Distributed Database Management System (DDBMS) is software that
manages the distributed database transparently.
A DDBMS is distinct from distributed processing, where a centralized DBMS is
accessed over a network. It's also different from a parallel DBMS, designed to
evaluate operations in parallel across multiple processors and disks to enhance
performance.

Advantages of a DDBMS
Reflects organizational structure.
Enhances remote data sharing.
Improves reliability, availability, and performance.
Can be more economical.
Offers modular growth.
Facilitates integration.
Helps organizations remain competitive.

Disadvantages of a DDBMS
Cost
Complexity
Lack of standards
Limited experience

Types of DDBMS
Homogeneous: All sites use the same DBMS product.
Heterogeneous: Sites may run different DBMS products, potentially based on
different data models (relational, network, hierarchical, object-oriented).

Multidatabase System (MDBS)

Page 26
 Created by Turbolearn AI

A distributed DBMS in which each site maintains complete autonomy. It

resides transparently on top of existing database and file systems,
presenting a single database to users. It maintains a global schema
against which users issue queries and updates, while the local DBMSs
maintain all user data.

Networks
Communication occurs over a network, either a Local Area Network (LAN) or a Wide
Area Network (WAN).

LANs: For short distances, offer faster communication.

WANs: Cover longer distances.
MANs: Metropolitan Area Networks, cover a city or suburb.

Functionality of a DDBMS
Besides standard DBMS functionality, a DDBMS requires:

Extended communication services.

Extended system catalog.
Distributed query processing.
Extended security, concurrency, and recovery services.

Fragmentation and Replication

A relation may be divided into subrelations called fragments, allocated to one
or more sites. Fragments may be replicated for improved availability and
performance.
Horizontal Fragmentation: Subsets of tuples.
Vertical Fragmentation: Subsets of attributes.
Mixed Fragmentation: Combination of horizontal and vertical fragmentation.
Derived Fragmentation: Horizontal fragmentation based on the fragmentation
of another relation.

Objectives of Fragmentation

Page 27
 Created by Turbolearn AI

Locality of reference
Improved reliability and availability
Acceptable performance
Balanced storage capacities and costs
Minimal communication costs

Correctness Rules of Fragmentation

Completeness
Reconstruction
Disjointness

Allocation Strategies
Centralized: A single centralized database.
Fragmented: Fragments assigned to one site.
Complete Replication: Complete copy of the database at each site.
Selective Replication: Combination of the first three strategies.

Transparencies in a DDBMS
A DDBMS should appear like a centralized DBMS by providing:

Distribution Transparency: Users are unaware of fragmentation/replication.

Transaction Transparency: Consistency of the global database is maintained
during concurrent access and failures.
Performance Transparency: The system efficiently handles queries referencing
data at multiple sites.
DBMS Transparency: Different DBMSs can coexist in the system.

Page 28