Distributed Database

A distributed database is a collection of multiple, logically interrelated databases spread across a computer network. The data is physically stored at...

What is a Distributed Database?

A distributed database is a collection of multiple, logically interrelated databases spread across a computer network. The data is physically stored at different sites (nodes or locations), but the Distributed Database Management System (DDBMS) presents it to users as a single, unified, logical database. Users interact with it as though all data were in one place.

Site A		Site B		Site C
Delhi		Mumbai		Chennai
[Employee]		[Orders]		[Product]

Connected via network (LAN/WAN)

The key idea: data is distributed for performance, availability, and organizational reasons, but the complexity of distribution is hidden from users.

Key Characteristics

1. Distribution Transparency

Users should not need to know where data is physically stored or how it is partitioned:

Code example

  Types of Transparency:
  
  Location Transparency:
  - User queries: SELECT * FROM Employee WHERE dept = 'CS'
  - DDBMS routes to the correct site automatically
  - User does not specify which server holds the data

  Fragmentation Transparency:
  - Table may be split across sites (horizontally or vertically)
  - User queries the full table; DDBMS assembles fragments

  Replication Transparency:
  - Table may be copied at multiple sites for availability
  - User reads/writes without knowing about replicas
  - DDBMS handles synchronization

2. Local Autonomy

Each site can operate independently for local transactions. If the network goes down, local operations continue uninterrupted. Only global transactions (spanning multiple sites) are affected.

3. No Single Point of Failure

If Site B crashes, Sites A and C continue serving their local data. Replicated data remains accessible from other copies.

Types of Distributed Databases

Homogeneous Distributed Database

All sites run the same DBMS software (e.g., all sites use PostgreSQL).

Site A	PostgreSQL 15 -- Employee table
Site B	PostgreSQL 15 -- Orders table
Site C	PostgreSQL 15 -- Products table
Communication	Same protocol, same SQL dialect, easy coordination.

Heterogeneous Distributed Database

Different sites run different DBMS software. A middleware layer translates between them.

Site A	Oracle -- Employee table
Site B	MySQL -- Orders table
Site C	SQL Server -- Products table
Middleware/Gateway	Translates queries between different SQL dialects,

Data Distribution Strategies

1. Fragmentation (Partitioning)

Splitting a table into pieces stored at different sites:

Horizontal Fragmentation: Split by rows (each site gets a subset of rows)

sql example

-- Original: Employee(emp_id, name, dept, city, salary)

-- Site Delhi:  All employees in Delhi
SELECT * FROM Employee WHERE city = 'Delhi';

-- Site Mumbai: All employees in Mumbai  
SELECT * FROM Employee WHERE city = 'Mumbai';

-- Reconstruction: UNION of all fragments = original table

Vertical Fragmentation: Split by columns (each site gets a subset of columns)

sql example

-- Site A: Employee_Personal(emp_id, name, address, phone)
-- Site B: Employee_Financial(emp_id, salary, tax_id, bank_account)

-- Both sites keep emp_id for reconstruction via JOIN
-- Reconstruction: JOIN on emp_id = original table

2. Replication

Copying the entire table (or fragments) to multiple sites:

Full Replication	Complete copy at every site
Partial Replication	Copies at selected sites
No Replication	Data at exactly one site

Strategy	Read Speed	Write Speed	Availability	Storage Cost
Full replication	Excellent	Poor	Excellent	High
Partial replication	Good	Moderate	Good	Medium
No replication	Variable	Excellent	Low	Low

Distributed Query Processing

A query spanning multiple sites requires the DDBMS to:

Parse the query and identify which sites hold relevant data
Optimize the execution plan (minimize network data transfer)
Execute sub-queries at respective sites
Ship intermediate results to a coordinating site
Combine results and return to the user

sql example

-- Query: Find employees in Delhi who placed orders > 10000
SELECT e.name, o.amount
FROM Employee e JOIN Orders o ON e.emp_id = o.emp_id
WHERE e.city = 'Delhi' AND o.amount > 10000;

-- Execution plan:
-- 1. Site A (Delhi): Filter employees where city='Delhi' -> send emp_ids to Site B
-- 2. Site B (Orders): Filter orders > 10000 matching those emp_ids
-- 3. Site B sends results to coordinator
-- 4. Coordinator assembles final result

-- Key optimization: Ship the SMALLER relation to reduce network traffic

Distributed Transaction Management

Transactions spanning multiple sites require special protocols:

Two-Phase Commit (2PC)

Example

Coordinator Site A Site B | | | |--- PREPARE? ------------>| | |--- PREPARE? ----------------------------->| | | | |<--- READY (vote YES) ----| | |<--- READY (vote YES) ----------------------| | | | |--- COMMIT -------------->| | |--- COMMIT ----------------------------->| | | | |<--- ACK -----------------| | |<--- ACK ----------------------------------| If any site votes NO -> Coordinator sends ABORT to all sites.

Advantages of Distributed Databases

Advantage	Detail
Improved reliability	No single point of failure; fault tolerance via replication
Better performance	Local data access avoids network latency
Scalability	Add sites as organization grows
Organizational match	Each office manages its own data
Reduced communication	Most queries served locally

Disadvantages of Distributed Databases

Disadvantage	Detail
Complexity	Distributed query optimization, 2PC, deadlock detection
Network dependency	Global transactions fail if network is down
Data consistency	Keeping replicas synchronized is hard
Security	More attack surfaces across network
Cost	Multiple servers, network infrastructure, skilled staff

Summary

Distributed databases bring data closer to users, improve availability through replication, and scale with organizational growth. However, they introduce significant complexity in query processing, transaction management, and consistency maintenance. The CAP theorem (Consistency, Availability, Partition Tolerance — pick two) governs fundamental trade-offs in distributed system design.

What is a Distributed Database?

Site A		Site B		Site C
Delhi		Mumbai		Chennai
[Employee]		[Orders]		[Product]

Connected via network (LAN/WAN)

The key idea: data is distributed for performance, availability, and organizational reasons, but the complexity of distribution is hidden from users.

Key Characteristics

1. Distribution Transparency

Users should not need to know where data is physically stored or how it is partitioned:

Code example

  Types of Transparency:
  
  Location Transparency:
  - User queries: SELECT * FROM Employee WHERE dept = 'CS'
  - DDBMS routes to the correct site automatically
  - User does not specify which server holds the data

  Fragmentation Transparency:
  - Table may be split across sites (horizontally or vertically)
  - User queries the full table; DDBMS assembles fragments

  Replication Transparency:
  - Table may be copied at multiple sites for availability
  - User reads/writes without knowing about replicas
  - DDBMS handles synchronization

2. Local Autonomy

Each site can operate independently for local transactions. If the network goes down, local operations continue uninterrupted. Only global transactions (spanning multiple sites) are affected.

3. No Single Point of Failure

If Site B crashes, Sites A and C continue serving their local data. Replicated data remains accessible from other copies.

Types of Distributed Databases

Homogeneous Distributed Database

All sites run the same DBMS software (e.g., all sites use PostgreSQL).

Site A	PostgreSQL 15 -- Employee table
Site B	PostgreSQL 15 -- Orders table
Site C	PostgreSQL 15 -- Products table
Communication	Same protocol, same SQL dialect, easy coordination.

Heterogeneous Distributed Database

Different sites run different DBMS software. A middleware layer translates between them.

Site A	Oracle -- Employee table
Site B	MySQL -- Orders table
Site C	SQL Server -- Products table
Middleware/Gateway	Translates queries between different SQL dialects,

Data Distribution Strategies

1. Fragmentation (Partitioning)

Splitting a table into pieces stored at different sites:

Horizontal Fragmentation: Split by rows (each site gets a subset of rows)

sql example

-- Original: Employee(emp_id, name, dept, city, salary)

-- Site Delhi:  All employees in Delhi
SELECT * FROM Employee WHERE city = 'Delhi';

-- Site Mumbai: All employees in Mumbai  
SELECT * FROM Employee WHERE city = 'Mumbai';

-- Reconstruction: UNION of all fragments = original table

Vertical Fragmentation: Split by columns (each site gets a subset of columns)

sql example

-- Site A: Employee_Personal(emp_id, name, address, phone)
-- Site B: Employee_Financial(emp_id, salary, tax_id, bank_account)

-- Both sites keep emp_id for reconstruction via JOIN
-- Reconstruction: JOIN on emp_id = original table

2. Replication

Copying the entire table (or fragments) to multiple sites:

Full Replication	Complete copy at every site
Partial Replication	Copies at selected sites
No Replication	Data at exactly one site

Strategy	Read Speed	Write Speed	Availability	Storage Cost
Full replication	Excellent	Poor	Excellent	High
Partial replication	Good	Moderate	Good	Medium
No replication	Variable	Excellent	Low	Low

Distributed Query Processing

A query spanning multiple sites requires the DDBMS to:

Parse the query and identify which sites hold relevant data
Optimize the execution plan (minimize network data transfer)
Execute sub-queries at respective sites
Ship intermediate results to a coordinating site
Combine results and return to the user

sql example

-- Query: Find employees in Delhi who placed orders > 10000
SELECT e.name, o.amount
FROM Employee e JOIN Orders o ON e.emp_id = o.emp_id
WHERE e.city = 'Delhi' AND o.amount > 10000;

-- Execution plan:
-- 1. Site A (Delhi): Filter employees where city='Delhi' -> send emp_ids to Site B
-- 2. Site B (Orders): Filter orders > 10000 matching those emp_ids
-- 3. Site B sends results to coordinator
-- 4. Coordinator assembles final result

-- Key optimization: Ship the SMALLER relation to reduce network traffic

Distributed Transaction Management

Transactions spanning multiple sites require special protocols:

Two-Phase Commit (2PC)

Example

Advantages of Distributed Databases

Advantage	Detail
Improved reliability	No single point of failure; fault tolerance via replication
Better performance	Local data access avoids network latency
Scalability	Add sites as organization grows
Organizational match	Each office manages its own data
Reduced communication	Most queries served locally

Disadvantages of Distributed Databases

Disadvantage	Detail
Complexity	Distributed query optimization, 2PC, deadlock detection
Network dependency	Global transactions fail if network is down
Data consistency	Keeping replicas synchronized is hard
Security	More attack surfaces across network
Cost	Multiple servers, network infrastructure, skilled staff

Distributed Database

What is a Distributed Database?

Key Characteristics

1. Distribution Transparency

2. Local Autonomy

3. No Single Point of Failure

Types of Distributed Databases

Homogeneous Distributed Database

Heterogeneous Distributed Database

Data Distribution Strategies

1. Fragmentation (Partitioning)

2. Replication

Distributed Query Processing

Distributed Transaction Management

Two-Phase Commit (2PC)

Advantages of Distributed Databases

Disadvantages of Distributed Databases

Summary

Continue learning this concept

Distributed Database

What is a Distributed Database?

Key Characteristics

1. Distribution Transparency

2. Local Autonomy

3. No Single Point of Failure

Types of Distributed Databases

Homogeneous Distributed Database

Heterogeneous Distributed Database

Data Distribution Strategies

1. Fragmentation (Partitioning)

2. Replication

Distributed Query Processing

Distributed Transaction Management

Two-Phase Commit (2PC)

Advantages of Distributed Databases

Disadvantages of Distributed Databases

Summary

Continue learning this concept