Scribd
Upload
39 views3 pages

Distributed DB Lab10 Solutions

The document outlines a practical quiz for a distributed databases lab, detailing tasks related to server setup, replication testing, horizontal and vertical fragmentation, and conflict resolution. It includes specific SQL commands and expected outcomes for each task, along with submission requirements for screenshots. The quiz assesses knowledge on database systems, replication, and concurrency control in distributed environments.

Uploaded by

231370292
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
39 views3 pages

Distributed DB Lab10 Solutions

The document outlines a practical quiz for a distributed databases lab, detailing tasks related to server setup, replication testing, horizontal and vertical fragmentation, and conflict resolution. It includes specific SQL commands and expected outcomes for each task, along with submission requirements for screenshots. The quiz assesses knowledge on database systems, replication, and concurrency control in distributed environments.

Uploaded by

231370292
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Distributed Databases Lab 10 – Practical

Quiz Solutions
Name: Arishma Khalid

Course: CS-251 Advanced Database Systems – Lab

Marks: /10

Time: 30 minutes

Q1: Set Up Servers (2 marks)


Task:
Create two servers in pgAdmin:
- Bank_Server
- Employee_Server

Connection Details:
- Host: localhost
- Port: 5432
- Username: postgres
- Password:
- Bank_Server: 1234
- Employee_Server: emp123

✔ Submission:
📸 Attach screenshot showing both servers in pgAdmin sidebar.

Q2: Test Replication (2 marks)


Steps:
1. Create replicated_db on Bank_Server
2. Create table:

CREATE TABLE Customer (


id INT,
name VARCHAR(50),
city VARCHAR(50),
balance INT
);
3. Insert:

INSERT INTO Customer VALUES (1, 'Ali', 'Lahore', 5000);

4. View in both servers:


SELECT * FROM Customer;

✔ Submission:
📸 Attach SELECT result screenshot from both servers.

Q: What would happen if Bank_Server goes down?


A: If Bank_Server goes down, and replication is not fully synchronous or bidirectional, the
Employee_Server may not get the latest changes. Some data might be lost or inconsistent
depending on replication settings.

Q3: Horizontal Fragmentation (2 marks)


Data:

CREATE TABLE Employee (


name VARCHAR(50),
city VARCHAR(50)
);

INSERT INTO Employee VALUES


('Ali', 'Lahore'),
('Sana', 'Karachi'),
('Umar', 'Lahore'),
('Areeba', 'Karachi');

On Bank_Server:
CREATE TABLE Employee_Lahore AS
SELECT * FROM Employee WHERE city = 'Lahore';

On Employee_Server:
CREATE TABLE Employee_Karachi AS
SELECT * FROM Employee WHERE city = 'Karachi';

✔ Submission:
📸 Attach screenshots of both tables: Employee_Lahore (Bank_Server) and Employee_Karachi
(Employee_Server).
Q4: Vertical Fragmentation (2 marks)
On Bank_Server:
CREATE TABLE Customer_Info AS
SELECT id, name, city FROM Customer;

On Employee_Server:
CREATE TABLE Customer_Balance AS
SELECT id, balance FROM Customer;

To reconstruct on either server:


SELECT ci.id, ci.name, ci.city, cb.balance
FROM Customer_Info ci
JOIN Customer_Balance cb ON ci.id = cb.id;

✔ Submission:
📸 Attach screenshot of JOIN output.
📄 SQL used is above.

Q5: Conceptual + Practical (2 marks)


Scenario: Both servers try to update the same customer record at the same time.

Q: What is the conflict called?


A: Write-write conflict

Q: How can it be resolved in distributed systems?


A:
- Use distributed concurrency control like two-phase locking (2PL)
- Use version vectors or timestamps to detect and resolve conflicts
- Adopt a master-slave or consensus protocol (e.g., Paxos, Raft) to handle concurrent writes.

You might also like