Distributed DBMS – Detailed Exam Notes

1. Distributed Database Architecture (12–15 marks)

Definition: A Distributed Database (DDB) is a collection of multiple, logically interrelated databases
distributed over a network of sites. Each site can operate autonomously, but the system appears as a
single database to the users.

Types of Distributed DB Architectures:

a) Client-Server Architecture

+-----------+
| Server |
+-----------+
/ |
/ |
+------+ +------+ +------+
|Client| |Client| |Client|
+------+ +------+ +------+

- Server handles DB, clients send queries.

- Advantages: Simple design, centralized control.
- Disadvantages: Server becomes a bottleneck.

b) Peer-to-Peer Architecture

+------+ +------+
|Site A|<--->|Site B|
+------+ +------+
^ ^
| |
+------+ +------+
|Site C|<--->|Site D|
+------+ +------+

- All sites equal, act as both client and server.

- Advantages: Fault-tolerant, scalable.
- Disadvantages: Complex consistency management.

c) Multi-Database System

+-----------+ +-----------+
|DBMS 1 | |DBMS 2 |
|Site A | |Site B |

1
 +-----------+ +-----------+
\ /
\ /
+----------------+
| Global Interface|
+----------------+

- Integration of heterogeneous databases.

- Provides a unified view to users.

Key Concepts: - Fragmentation (horizontal, vertical, hybrid) - Replication - Allocation

Advantages: - Improved reliability and availability - Faster local query processing - Local autonomy

Disadvantages: - Complex management and maintenance - Higher implementation cost - Ensuring

data consistency is difficult

Example: Bank with multiple branches: each branch stores local customer accounts (fragmentation),
while HQ maintains a copy of all accounts for reporting (replication).

2. Transaction Management & ACID (10–12 marks)

Transaction: A logical unit of work in a database system that must be executed completely or not at all.

ACID Properties: - Atomicity: All or nothing - Consistency: DB moves from one consistent state to
another - Isolation: Transactions don’t interfere with each other - Durability: Committed changes are
permanent

Serializability: - Ensures correct results in concurrent execution - Conflict Serializability and View
Serializability

Phases of a Transaction: Begin, Execute, Commit/Abort

Two-Phase Commit (2PC):

Coordinator Participant 1 Participant 2

| | |
|-----Prepare-------> | |
| |-----Prepare------->|
|<----Vote(Yes)-------|<---Vote(Yes)------|
| | |
|------Commit-------> |------Commit------>|

- Phase 1: Prepare + Vote - Phase 2: Commit - Ensures all sites commit together → consistency

2
3. Concurrency Control & Locking (8–10 marks)
Purpose: Prevent inconsistencies when multiple transactions access shared data concurrently.

Locking Mechanisms: Shared (S) for read, Exclusive (X) for read/write

Timestamp Ordering: Older transactions execute first

Pessimistic vs Optimistic: Lock first vs validate at commit

Deadlocks: Transactions waiting indefinitely - Conditions: Mutual exclusion, Hold & wait, No
preemption, Circular wait - Wait-for Graph:

T1 ---> T2 ---> T3
^ |
|----------------|

- Cycle indicates deadlock - Prevention: Timeout, resource ordering

4. Distributed Query Processing (10–12 marks)

Steps:

User Query
|
v
Query Decomposition
|
v
Data Localization
|
v
Global Optimization
|
v
Query Execution Plan

Explanation: - Query Decomposition: Split user query into subqueries for each site - Data Localization:
Identify sites storing required data - Global Optimization: Choose cheapest plan (CPU, I/O, network) -
Execution: Run subqueries and combine results

Example: SELECT * FROM Employees WHERE Dept='HR' → Decompose, filter locally, combine
results

Execution Strategies: Centralized vs Distributed

3
5. Deadlocks in DDBMS (6–8 marks)
Definition: Transactions waiting indefinitely for each other’s resources

Detection: Wait-for Graph (see Concurrency Control)

Prevention: Avoid circular wait, preemption, or timeout

6. Fragmentation & Allocation (4–6 marks)

Fragmentation: Horizontal, Vertical, Hybrid

Allocation: Replication vs Non-replication

Example Table: | Employee | Dept | Salary | - Horizontal → Dept-wise fragment - Vertical →

EmpID+Name vs Salary+JoinDate

7. Replication & Reliability (8–10 marks)

Replication Techniques: Synchronous vs Asynchronous

Replica Control: Master-slave, Multi-master

Benefits: High availability, Fault tolerance, Faster query response

Example: E-commerce product catalogs replicated across multiple servers

8. Parallel Databases (4–6 marks)

Definition: Databases using multiple processors/disks for faster query execution

Architectures:

Shared Nothing:

Processor 1 + Disk 1 Processor 2 + Disk 2 Processor 3 + Disk 3

\ | /
\----------------|-----------------/
Network

Shared Disk:

4
 Processor 1 Processor 2 Processor 3
\ | /
\ | /
\ | /
+-----------------+
| Shared Disk Array|
+-----------------+

Advantages: High throughput, fast queries Challenges: Data distribution, load balancing

9. Transparency Types (4–6 marks)

• Data Transparency: User unaware of storage
• Location Transparency: User unaware of site
• Replication Transparency: User unaware of copies
• Fragmentation Transparency: User unaware of fragment distribution
• Transaction Transparency: User unaware of multiple sub-transactions

Example: Bank customer sees single account, unaware of fragmentation/replication across branches

10. Multidatabase / Security (2–4 marks)

Multidatabase System: Integration of heterogeneous databases into a unified interface

Security Challenges: Authentication, Authorization, Encryption

Example: Bank + insurance DB integrated for single customer view

✅ Exam Strategy: Draw diagrams for Architecture, 2PC, Deadlock, Fragmentation. Use real-life
examples. Focus on Architecture, Transaction, Query Processing for high marks.