Chapter four
Distributed Database Systems
❖Distributed Database is a collection of multiple
interconnected databases, which are spread
physically across various locations that communicate
via a computer network.
❖ Data is physically stored across multiple sites. Data
in each site can be managed by a DBMS independent
of the other sites
❖ A database that runs and stores data across
multiple computers,
❖Unlike a centralized system, where all data is
stored in one place,
❖ A distributed system manages data across various
sites while making it appear as a single database to
users.
12/10/2025 Destaye A. 1
❖It improves data availability, reliability, and performance by enabling ,
parallel processing, and fault tolerance.
❖Distributed database architecture comprises multiple interconnected
nodes, each responsible for storing and processing a portion of the
data. Common architectures include:
Centralized Control: In centralized control architectures, a single node
or server coordinates query processing and optimization across
distributed data nodes.
Peer-to-Peer (P2P): P2P architectures distribute control and data
management responsibilities among multiple nodes, promoting
decentralization and fault tolerance.
Client-Server: Client-server architectures involve clients issuing queries
to a centralized server, which distributes and coordinates query
execution across distributed data nodes
12/10/2025 Destaye A. 2
Database Systems
Centralized Database Systems
• All the accesses to any subset of data
will be to this centralized location.
• Centralized control is on retrieval and
updating of data.
• Availability of resources is at the
central site, so the Chances of failure
of database system increases
• Database is maintained at one site
• If centralized system fails, entire
system is halted
12/10/2025 Destaye A. 3
Types of Distributed Databases
12/10/2025 Destaye A. 4
Homogeneous distributed
❖A homogeneous distributed database
encompasses different sites (nodes/machines)
that all store the same data.
❖ All sites have identical software and (The
overall design of the database is called
schema)
❖ They also have the following characteristics:
✓Use the same data model: which means all
database sites must use the same structure,
for tables and data types.
✓Work with the same OS.
✓Use the same DBMS across all sites.
12/10/2025 Destaye A. 5
Homogeneous distributed
Autonomous Non-autonomous
❖Which means networks of interconnected ❖Data is distributed across the
nodes that operate independently to homogeneous nodes and a central or
perform complex tasks without centralized master DBMS co-ordinates data
control. updates across the sites.
❖They are integrated by a controlling ❖Ex. University system, Database
application and use message passing to schema, user roles, updates, rules are
share data updates. controlled by Main Campus
❖Each site controls its own data, Each site
can run its own local transactions, No single
site controls all others Coordination
happens only when needed
❖Ex. Several branches of a bank each run the
same DBMS
12/10/2025 Destaye A. 6
Heterogeneous distributed
• Ex
❖Database system is a network of two or more
databases with different types of DBMS software,
which can be stored on one or more machines.
❖ Different sites can have different schema .
❖These databases may differ in terms of their data
models, schema, query languages.
❖Ex .Hospital Information System: Billing
Department, Patient Department, Laboratory
Department are used d/f schema… ,but they must
share information using Query Translator /
Mediator
12/10/2025 Destaye A. 7
Heterogeneous Distributed Databases
Federated Un-federated
• database systems are independent in • The database systems employ a central
nature and integrated. coordinating module through which
• together so that they function as a the databases are accessed.
single database system • Local databases may not directly
• Sites agree to share data through a communicate with each other; Users
federated schema or mediator. No interact through a global DBMS.
central control. • EX . Retail Sales + Inventory + Delivery
Ex. University system , Each site may Global control and Communication
use different through global DBMS.
• Each system works independently, but
the university creates a federated
layer where queries can access all
data as if it is one database.
12/10/2025 Destaye A. 8
Distributed Database Design
The process of deciding how to distribute data across multiple sites in
a distributed database system so that the system achieves.
It focuses on data fragmentation, replication, and allocation to
optimize performance and availability.
The design of a distributed database introduces three new issues:
• How to partition the database into fragments.
• Which fragments to replicate.
• Where to locate those fragments and replicas
12/10/2025 Destaye A. 9
Data Fragmentation
➢Data fragmentation allows you to break a single object into two or
more segments or fragments.
➢ The object might be a user’s database, a system database, or a table.
Each fragment can be stored at any site over a computer network
➢ data fragmentation concerns the methods used to split the data. In
the relational model.
12/10/2025 Destaye A. 10
three types of data fragmentation
Horizontal fragmentation
• It refers to the division of a relation into subsets (fragments) of tuples
(rows). Each fragment is stored at a different node, and each fragment has
unique rows.
• However, the unique rows all have the same attributes (columns).
• In short, each fragment represents the equivalent of a SELECT statement,
with the WHERE clause .
• Each fragment is basically a portion of the table selected using a WHERE
condition.
• In other words, a table may be divided into several horizontal subsets
(rows),
12/10/2025 Destaye A. 11
Example 1
Suppose we fragment the Student table by region:
• Fragment1 = SELECT * FROM Student WHERE Region = 'Addis'
• Fragment2 = SELECT * FROM Student WHERE Region = 'Bahir Dar'
• Fragment3 = SELECT * FROM Student WHERE Region = 'Hawassa’
• Ex 2.
12/10/2025 Destaye A. 12
Con…
Vertical fragmentation
❖ Refers to the division of a relation into attribute (column) subsets.
❖ Each subset (fragment) is stored at a different node, and each
fragment has unique columns with the exception of the key column,
which.
❖Table split by attributes (columns) and Primary key (EmpID) must be
in all fragments
example A university data may split the STUDENT table:
• Fragment A: (ID, Name, Phone) → stored at Registrar Office
• Fragment B: (ID, GPA, Department) → stored at Academic Office
12/10/2025 Destaye A. 13
Hybrid Fragmentation
• Mixed fragmentation refers to a combination of horizontal and vertical
strategies.
Example: International E-Commerce Company
CUSTOMER_ORDERS : main table
• OrderID CustomerName Country Phone Product Quantity
01 Almi Usa +1797 max
02 abrsh Ethiopia +2519 max
03 nuhami Canada +1598 max
12/10/2025 Destaye A. 14
Example
Horizontal Fragmentation
If Fragment H1 (USA Orders) WHERE Country = ‘USA’ ,Fragment H2 (Ethiopia
Orders) WHERE Country = ‘Ethiopia’ ,Fragment H3 (Canada Orders) WHERE
Country = ‘Canada’
Each region stores only its own orders.
Vertical Fragmentation (inside each region)
• For each region, split columns into:
Personal Info Fragment
OrderID CustomerName Phone
Order Details Fragment
OrderID Product Quantity Price PaymentMethod
12/10/2025 Destaye A. 15
Finaly Hybrid Fragments
USA
USA_Personal_Info (OrderID, CustomerName, Phone)
USA_Order_Details (OrderID, Product, Quantity, Price,
PaymentMethod)
• Ethiopia_Personal_Info (OrderID, CustomerName, Phone)
• Ethiopia _Order_Details (OrderID, Product, Quantity, Price)
12/10/2025 Destaye A. 16
Data Replication
❖Data replication refers to the storage
of data copies at multiple sites served
by a computer network.
❖Fragment copies can be stored at
several sites to serve specific
information requirements.
❖Replication has some benefits (such as
improved data availability, better load
distribution, improved data failure-
tolerance, and reduced query costs).
12/10/2025 Destaye A. 17
Replication scenarios
Fully replicated :database stores multiple copies of each database
fragment at multiple sites. In this case , all database fragments are
replicated.
Partially replicated: database stores multiple copies of some database
fragments at multiple sites. Most DDBMSs are able to handle the
partially replicated database well.
unreplicated database stores each database fragment at a single site.
12/10/2025 Destaye A. 18
Factors influence the decision to use data replication
• Database size. The amount of data replicated will have an impact on
the storage requirements and data transmission costs. amounts of
data that could affect other applications.
• Costs, including those for performance, software overhead, and
management associated with synchronizing transactions and their
components
12/10/2025 Destaye A. 19
Data Allocation
Data allocation describes the process of deciding where to locate data.
Data allocation strategies are as follows:
• Centralized data allocation: the entire database is stored at one site.
• Partitioned data allocation: the database is divided into two or more
disjointed parts (fragments) and stored at two or more sites.
• Replicated data allocation: copies of one or more database fragments are
stored at more than one site in the distributed database.
• Copies of the same table exist in multiple locations If one site fails, another
site can still provide the data.=> Improves availability, fault tolerance, and
fast local access.
12/10/2025 Destaye A. 20
Distributed Query Processing
Distributed Query Processing
❖ Distributed process of executing a query across multiple sites/locations in a
database system.
❖ Query Optimization in Distributed Systems is the process of finding the most
efficient way to execute a query that involves data stored at multiple distributed
sites, to ensure efficient query execution across distributed data nodes.
❖In a distributed database system, processing a query comprises of optimization at
both the global and the local level.
A customer transfers 5,000 ETB from Account at Site A (Wollo Bank Branch) to
Account at Site B (Bahir dar Bank Branch)
Begin Transaction
Deduct 5,000 from sender at Site A
Add 5,000 to receiver at Site B
Commit the transaction
12/10/2025 Destaye A. 21
Con…
the optimizer must reduce:
➢Communication cost (data transfer over network)
➢Local processing cost (CPU, I/O)
➢Total response time
• It means that in a distributed database, sending data over the
network in between different sites is slow and costly, so we try to
reduce the amount of data.
12/10/2025 Destaye A. 22
Distributed Transaction Management
And Recovery
❑ Distributed Transaction Management is the process of ensuring that
a transaction involving multiple sites ,databases is executed correctly,
consistently, and completely, even if failures occur.
❑Distributed transaction management is implemented by adding a
transaction coordinator that uses commit and concurrency protocols
to make multi-node operations.
12/10/2025 Destaye A. 23
Use a Distributed Commit Protocol : 2-Phase Commit (2PC)
Phase 1 – Prepare Phase
[Link] sends: “Can you commit?”
[Link] site checks:
• Do I have required data?
• Any conflicts?
• Any errors?
[Link] site replies:
• YES => Commit
• NO => Abort
Phase 2 – Commit / Rollback
• If ALL sites YES => coordinator sends COMMIT
• If ANY site NO => coordinator sends ROLLBACK
12/10/2025 Destaye A. 24
Concurrency Control Use distributed locking such as:
• Two-phase locking (2PL)
• Timestamp ordering
• Distributed deadlock detection
This ensures that transactions do not interfere with each other.
12/10/2025 Destaye A. 25
Advantages of Distributed Databases
o. Improved Performance : Data is stored close to where it is used, reducing
query time and speeding up local operations.
oHigh Availability and Reliability :If one site fails, others continue working
because data may be replicated across sites.
o. Scalability :Easily add new sites, servers, or nodes without shutting down
the system.
o. Reduced Network Traffic : Local queries access local data, reducing the
need to send data over the network.
o. Better Data Sharing : Multiple locations (branches, offices, regions) can
share data and work at the same time.
o. Modular Growth : Systems can grow step by step — add new branches
without redesigning the entire database.
12/10/2025 Destaye A. 26
Disadvantages of Distributed Databases
o. High Complexity : Managing fragmentation, replication, allocation, and
keeping data consistent is difficult.
o. Expensive Setup and Maintenance : Requires more hardware, skilled
administrators, and advanced network infrastructure.
o. Harder Security Management : More sites ,more entry points , higher risk
of unauthorized access.
o. Higher Communication Cost : Even though local access is fast, updates
between sites may generate heavy network traffic.
o. Difficult Backup and Recovery :Coordinating backups across many sites is
harder than in a centralized database.
o. Complex Query Processing : Queries may need to access data from
multiple sites, requiring distributed query optimization.
12/10/2025 Destaye A. 27
12/10/2025 Destaye A. 28