📘 DISTRIBUTED DBMS
1) Fragmentation (VERY IMPORTANT)
Fragmentation = splitting a database into smaller pieces (fragments) stored at different sites.
Types:
🔹 Horizontal Fragmentation
Rows are divided based on conditions
Example:
STUDENT table →
o S1: Students with marks > 80
o S2: Students with marks ≤ 80
👉 Condition-based splitting
🔹 Vertical Fragmentation
Columns are divided
Example:
STUDENT(ID, Name, Age, Marks)
→ F1(ID, Name)
→ F2(ID, Marks)
👉 Primary key must be present in all fragments
🔹 Hybrid Fragmentation
Combination of horizontal + vertical
Properties:
Completeness → No data loss
Reconstruction → Can rebuild original table
Disjoint Ness → No overlapping (in horizontal)
🔢 Numerical Example:
Table: EMP(ID, Dept, Salary)
Fragment:
F1: Dept = 'IT'
F2: Dept = 'HR'
👉 Check:
Completeness ✔ (all records included)
Disjoint ✔ (no overlap)
Reconstruction ✔ (Union)
2) Transparency (MCQ IMPORTANT)
Transparency = hiding complexity from user
Types:
Location Transparency → User doesn’t know where data is stored
Fragmentation Transparency → User doesn’t know data is split
Replication Transparency → User doesn’t know copies exist
👉 Example:
SELECT * FROM STUDENT
User doesn’t know it comes from multiple sites
3) Mapping
Mapping = converting global schema → local schema
Levels:
Global Schema
Fragmentation Schema
Allocation Schema
Local Schema
👉 Used to track where data is stored
4) Attribute Affinity Matrix (IMPORTANT NUMERICAL)
Used in Vertical Fragmentation
Step-by-step:
1. Identify attributes
2. Count how often attributes are used together in queries
3. Build matrix
🔢 Example:
Queries:
Q1: Uses (A, B)
Q2: Uses (A, C)
Q3: Uses (B, C)
A B C
A - 1 1
B 1 - 1
C 1 1 -
👉 Higher value = stronger relation
👉 Group attributes accordingly
5) Reference Architecture
Structure of DDBMS
Components:
Global Query Processor
Local Query Processor
Data Manager
Communication Manager
👉 Shows how queries flow in distributed system
6) Global System Catalog
Stores metadata:
Fragment info
Location info
Schema details
👉 Like “database about database”
🔹 7. Query Processing (VERY IMPORTANT)
Definition
Process of executing a query in distributed environment.
Steps
1. Query Decomposition
o Convert SQL → relational algebra
2. Data Localization
o Identify required fragments
3. Global Optimization
o Choose best execution plan
4. Local Optimization
o Execute at local sites
🔹 8. Predicate
Definition
A condition used to filter rows.
Types
Simple Predicate → Salary > 50000
Min-term Predicate → Combination of conditions
Use
👉 Used in horizontal fragmentation
Example:
SELECT * FROM EMP WHERE Salary > 50000
👉 Salary > 50000 = Predicate
🔹 9. Normalization in Query Processing
Definition
Simplifying query to optimize execution.
Steps
Convert to relational algebra
Apply equivalence rules
Remove redundancy
Optimize joins
Importance
Reduces cost
Improves performance
🔹 10. Allocation Cost Model (VERY IMPORTANT)
Definition
Determines best location to store data fragments.
Types of Allocation
Centralized
Partitioned
Replicated
Cost Factors
Communication cost
Storage cost
Processing cost
Goal
👉 Minimize total cost
Used to decide where to store fragments
Cost includes:
Storage cost
Communication cost
Processing cost
🔢 Example:
Site A:
Storage = 100
Transfer = 50
Site B:
Storage = 80
Transfer = 100
👉 Total Cost:
A = 150
B = 180
✔ Choose Site A (lower cost)
🔹 11. Coordinator
Definition
Coordinator manages distributed transactions.
Role
Initiates transaction
Communicates with sites
Ensures consistency
In 2PC (Two Phase Commit)
1. Prepare Phase → Ask sites to commit
2. Commit Phase → Final decision
Coordinator vs Participant
Coordinator → Controls
Participant → Executes
Types:
🔹 Bully Algorithm
Highest ID becomes coordinator
If leader fails → election starts
Steps:
1. Node detects failure
2. Sends election message to higher nodes
3. Highest alive node becomes leader
🔹 Ring Algorithm
Nodes form ring
Pass election message
Highest ID wins
🔢 Example:
Nodes: 1, 2, 3, 4
If 2 starts election →
Message circulates → highest (4) becomes leader