Principles Of Distributed Database — Systems Exercise Solutions

If you are looking for resources related to the textbook " Principles of Distributed Database Systems " (by M. Tamer Özsu and Patrick Valduriez), 📚 Official & Academic Resources

Official Author Site: The authors often provide slide decks and supplementary materials. Check the official book website for potential sample solutions or instructor resources.

GitHub Repositories: Many students and researchers post their own implementations of the book's concepts (like join algorithms or deadlock detection). Searching GitHub for "Principles of Distributed Database Systems Solutions" often yields community-driven answer keys.

University Course Pages: Many universities use this as a standard text. Searching for site:.edu "Principles of Distributed Database Systems" assignment solutions can lead to public course archives from past semesters. 🛠️ Common Topics in Exercises Exercises in this field typically focus on:

Data Fragmentation: Defining horizontal and vertical fragments for a given schema.

Distributed Query Optimization: Calculating the cost of distributed joins and semi-joins.

Transaction Management: Solving problems related to 2-Phase Commit (2PC) and distributed deadlock detection.

Reliability Protocols: Analyzing how systems handle site or network failures. 💻 Peer-Shared Solutions

For specific step-by-step answers to the textbook's problems, platforms like Course Hero and Scribd have user-uploaded PDFs. Note that these often require a subscription to view in full.

Are you working on a specific chapter or problem number? If you share the question text, I can help you work through the logic of the solution.

Introduction

Distributed database systems are designed to store and manage data across multiple sites or nodes, which can be geographically dispersed. The primary goal of a distributed database system is to provide a unified view of the data, while ensuring that the data is consistent, reliable, and easily accessible. In this write-up, we will discuss the principles of distributed database systems and provide solutions to exercises that illustrate these principles.

Principles of Distributed Database Systems

Fragmentation: Fragmentation involves dividing a large database into smaller, more manageable pieces called fragments. Each fragment is stored at a different site, and the fragments are combined to provide a unified view of the data.
Replication: Replication involves maintaining multiple copies of data at different sites to improve availability and reliability. Each copy of the data is called a replica.
Distribution: Distribution involves storing data across multiple sites, which can be geographically dispersed.
Autonomy: Autonomy refers to the ability of each site to operate independently, making decisions about data management and consistency.
Transparency: Transparency refers to the ability of the system to hide the distribution of data from the users, providing a unified view of the data.

Exercise Solutions

Exercise 1: Fragmentation and Replication

Consider a distributed database system that stores information about customers, orders, and products. The database is fragmented into three fragments:

Fragment 1: Customers (Customer_ID, Name, Address)
Fragment 2: Orders (Order_ID, Customer_ID, Order_Date)
Fragment 3: Products (Product_ID, Product_Name, Price)

Each fragment is replicated at two sites: Site A and Site B.

Fragment 1: Site A and Site C
Fragment 2: Site B and Site D
Fragment 3: Site A and Site B

Draw a diagram showing the fragmentation and replication of the database. If you are looking for resources related to

Solution

The diagram below shows the fragmentation and replication of the database:

          +---------------+
          |  Fragment 1  |
          |  (Customers)  |
          +---------------+
                  |
                  |
                  v
+---------------+       +---------------+
|  Site A      |       |  Site C      |
|  (Replica 1) |       |  (Replica 2) |
+---------------+       +---------------+
+---------------+
          |  Fragment 2  |
          |  (Orders)    |
          +---------------+
                  |
                  |
                  v
+---------------+       +---------------+
|  Site B      |       |  Site D      |
|  (Replica 1) |       |  (Replica 2) |
+---------------+       +---------------+
+---------------+
          |  Fragment 3  |
          |  (Products)  |
          +---------------+
                  |
                  |
                  v
+---------------+       +---------------+
|  Site A      |       |  Site B      |
|  (Replica 1) |       |  (Replica 2) |
+---------------+       +---------------+

Exercise 2: Distribution and Autonomy

Consider a distributed database system that stores information about employees and departments. The database is distributed across three sites: Site A, Site B, and Site C. Each site has its own local database and is autonomous.

Site A: Employees (Employee_ID, Name, Department_ID)
Site B: Departments (Department_ID, Department_Name)
Site C: Employee_Department (Employee_ID, Department_ID)

Describe how the system ensures autonomy and distribution.

Solution

The system ensures autonomy by allowing each site to operate independently, making decisions about data management and consistency. Each site has its own local database, which can be updated independently.

The system ensures distribution by storing data across multiple sites. The data is fragmented and distributed across the three sites, providing a unified view of the data.

For example, if a new employee is added at Site A, the employee's information is stored in the local database at Site A. If the employee's department is updated at Site B, the updated information is stored in the local database at Site B. The system ensures that the data is consistent across all sites by using distributed transactions and concurrency control.

Exercise 3: Transparency

Consider a distributed database system that stores information about customers and orders. The database is fragmented and replicated across multiple sites. Describe how the system provides transparency.

Solution

The system provides transparency by hiding the distribution of data from the users, providing a unified view of the data. The users interact with the system through a global schema, which provides a single, unified view of the data.

For example, a user can submit a query to retrieve all customers who have placed an order. The system will automatically determine which sites have the relevant data, retrieve the data, and provide the result to the user. The user is not aware of the fragmentation and replication of the data, and the system provides a unified view of the data.

Conclusion

In conclusion, distributed database systems are designed to store and manage data across multiple sites or nodes. The principles of distributed database systems include fragmentation, replication, distribution, autonomy, and transparency. By understanding these principles and how they are applied, we can design and implement effective distributed database systems that provide a unified view of the data, while ensuring that the data is consistent, reliable, and easily accessible.

Mastering the Core: Principles of Distributed Database Systems Exercise Solutions F2 at site B (500 rows)

Distributed database systems (DDBS) are the backbone of modern, globalized computing. From social media feeds to international banking, the ability to manage data across multiple physical locations is essential. However, the complexity of these systems—covering fragmentation, replication, query optimization, and transaction management—can be daunting.

Working through exercise solutions is often the only way to bridge the gap between abstract theory and technical implementation. This article explores the fundamental principles of DDBS through the lens of common problem sets and their solutions. 1. Data Fragmentation and Allocation

One of the first challenges in a distributed environment is deciding how to split data (fragmentation) and where to put it (allocation). Horizontal vs. Vertical Fragmentation

Horizontal Fragmentation: Dividing a relation into subsets of tuples (rows). Solutions usually involve defining selection predicates (e.g., WHERE City = 'New York').

Vertical Fragmentation: Dividing a relation into subsets of attributes (columns). Solutions focus on grouping attributes frequently accessed together, often using an Attribute Affinity Matrix. Common Exercise Scenario:

Problem: Given a global schema and specific site queries, determine the optimal fragments.

Solution Tip: Use Minterm Predicates. By combining all simple predicates from applications, you create non-overlapping fragments that satisfy the "completeness" and "disjointness" rules. 2. Distributed Query Processing

In a distributed system, the cost of moving data over a network often outweighs the cost of local disk I/O. Localization and Optimization

Query processing solutions typically follow a four-step process:

Query Decomposition: Rewriting the calculus query into an algebraic one.

Data Localization: Replacing global relations with their fragments.

Global Optimization: Finding the best join order and communication strategy. Local Optimization: Selecting the best local access paths. Common Exercise Scenario:

Problem: Calculate the cost of a join between two tables located at different sites using a Semi-join.

Solution Tip: Remember that a semi-join reduces the size of the operand before it is sent across the network. If Size(Semi-join result) + Cost(Moving result) < Size(Original Table), the semi-join is more efficient. 3. Distributed Concurrency Control

Ensuring consistency when multiple users access data across sites requires sophisticated locking and ordering mechanisms. Locking and Timestamping

Distributed 2-Phase Locking (2PL): Managing "lock" and "unlock" phases across multiple nodes. Solutions often deal with Global Deadlock Detection, where a cycle exists in the Wait-For-Graph across different sites.

Timestamp Ordering: Assigning unique timestamps to transactions to ensure serializability without explicit locking. 4. Reliability and the Two-Phase Commit (2PC) then with F1: cost 500 +10

How do we ensure that a transaction either commits at every site or aborts at every site? The 2PC Protocol

Voting Phase: The coordinator asks participants if they are ready to commit.

Decision Phase: Based on the votes, the coordinator sends a "Global Commit" or "Global Abort" message. Common Exercise Scenario:

Problem: What happens if the coordinator fails after sending a "Prepare" message but before receiving all votes?

Solution Tip: This leads to a "blocked" state. Participants cannot decide on their own because they don't know the global outcome, highlighting a major weakness of basic 2PC (the need for 3PC or recovery protocols). 5. Parallel Database Systems

While distributed systems focus on geographic separation, parallel systems focus on performance via multiple processors and disks. Architectures Shared Memory: Fast but limited scalability.

Shared Disk: Good for clusters but suffers from communication overhead.

Shared Nothing: The gold standard for massive scalability (e.g., MapReduce, Hadoop). Conclusion: How to Approach Exercise Solutions

When studying "Principles of Distributed Database Systems," don't just look for the answer. Focus on the correctness rules: Completeness: No data is lost during fragmentation.

Reconstruction: You can rebuild the original relation from fragments.

Disjointness: Data isn't unnecessarily duplicated (unless specifically replicated for availability).

By mastering these mathematical and logical foundations, you move beyond rote memorization and toward designing resilient, high-performance distributed architectures.

6. Distributed Database Design: Allocation and Replication

Final exercises often combine fragmentation with allocation: given fragments and sites, decide whether to replicate or allocate uniquely to minimize cost.

Part 4: Distributed Reliability & The 2-Phase Commit (2PC)

Ensuring atomicity (all nodes commit or all nodes abort) is critical.

Key Principle: The Semi-Join Strategy

A semi-join reduces the size of a relation before transferring it across the network.

Definition: $R \ltimes S = \pi_R(R \bowtie S)$
Effect: Returns tuples of $R$ that match tuples in $S$, but does not append columns of $S$.

Key Principles for Solutions

Horizontal Fragmentation (HF): Uses selection predicates (e.g., DeptID = 10). Ensure completeness (every tuple goes to some fragment) and disjointness (a tuple belongs to at most one HF fragment, unless replication is used).
Vertical Fragmentation (VF): Uses projection over attribute subsets. Must include the primary key in every fragment for reconstruction. Ensure lossless-join property.

10. Work a concrete example

Always include a short worked example with small numbers:
- e.g., N=5 replicas, W=3, R=3 and show read/write scenarios.
- e.g., two concurrent transactions with read/write sets — build the precedence graph and show whether serializable.
Show intermediate states (timestamps, locks held, message counts).

Final Practice Problem (Self-Assessment)

Problem:
A distributed database has 3 sites. Fragment F1 at site A (1000 rows), F2 at site B (500 rows), F3 at site C (2000 rows). Query: F1 ⨝ F2 ⨝ F3. Choose the best join order (cost = tuple transmission). Assume join selectivity is 0.01 and all joins equi-joins.

Hint:
Try all permutations. The optimal order is (F2 ⨝ F1) ⨝ F3 or (F2 ⨝ F3) ⨝ F1? Compute intermediate sizes.

Answer (in brief):
Smallest relation is F2 (500). Join F2 with F1 → size=50010000.01=5000. Then join with F3 → total cost: move F2 to F1(500) + move 5000 to F3(5000) =5500.
Better: Join F2 with F3 first: 50020000.01=10,000; then with F1: cost 500 +10,000=10,500.
Best: Move smallest (F2) to any site first, then join with the next smallest intermediate.