Advanced Databases: Distributed & Object-Relational Systems

TAKEAWAYS:

• Distributed databases split data across multiple nodes while maintaining consistency via replication and partitioning.
• Object-relational databases extend SQL to handle hierarchical data (e.g., JSON, XML) and complex objects like images or geospatial coordinates.
• Nepal’s Daraz uses distributed databases to handle order queues across warehouses, while Ncell relies on object-relational features to store customer call logs with metadata.
• Two-phase commit (2PC) ensures atomicity in distributed transactions, but adds latency—critical for cross-bank transfers in Nepal.
• Federated databases combine disparate systems (e.g., NEPSE + bank records) without full integration, reducing redundancy.
• Object-relational mapping (ORM) bridges legacy systems (e.g., NTC’s network logs) with modern apps using tools like Hibernate.

1. Distributed Database Systems

Distributed databases (DDBMS) store data across multiple physical locations connected by a network. Unlike centralized databases, they improve scalability, availability, and fault tolerance by distributing data and processing.

Key Concepts

• Nodes: Individual database instances (e.g., a server in Kathmandu and one in Pokhara for Daraz).
• Replication: Copying data to multiple nodes for redundancy (e.g., Ncell’s customer data replicated across data centers).
• Partitioning: Splitting data by key (e.g., Daraz orders partitioned by region).
• Fragmentation: Dividing tables into smaller pieces (e.g., user profiles vs. order history in separate fragments).

How It Works: A Worked Example

Scenario: Ncell wants to store call records for 5 million users across 3 data centers (Kathmandu, Pokhara, Biratnagar). Each center handles a subset of users (partitioned by phone number range).

Partitioning Strategy:

Advantages:

• Scalability: Add nodes as user base grows (e.g., Ncell expanding to new regions).
• Fault Tolerance: If Kathmandu node fails, Pokhara node serves users 9900–9999.
• Performance: Local queries (e.g., fetching calls for 9801234567) are faster.

Disadvantages:

• Complexity: Managing replication and consistency across nodes.
• Latency: Cross-node queries (e.g., fetching a user’s balance from a remote bank node) are slower.
• Cost: Requires high-bandwidth networks (e.g., Ncell’s fiber backbone).

Consistency Models

Distributed databases use different consistency models to balance speed and accuracy:

Two-Phase Commit (2PC): Ensures atomicity in distributed transactions (e.g., transferring money from Siddhartha Bank to NMB Bank).

Problem: 2PC adds latency (critical for real-time banking). Alternatives like Saga pattern (compensating transactions) are used for non-critical workflows (e.g., Daraz order processing).

2. Object-Relational Database Systems (ORDBMS)

Object-relational databases extend SQL to handle complex data types (e.g., JSON, XML, images, geospatial data) and object-oriented features (e.g., inheritance, methods).

Key Concepts

• User-Defined Types (UDTs): Custom data types (e.g., storing a customer’s address as a single column).
• Object Identifiers (OIDs): Unique identifiers for objects (e.g., tracking a Daraz order as an object).
• Methods: Functions attached to data (e.g., calculating shipping cost for an order).
• Inheritance: Subclasses inherit attributes (e.g., Employee → Manager with extra team_size field).

How It Works: A Worked Example

Scenario: Pathao stores driver routes as geospatial data. A traditional RDBMS would split routes into latitude/longitude tables, but an ORDBMS stores them as a polygon object.

erDiagram
    Driver ||--o{ Route : "has"
    Driver {
        string driver_id PK
        string name
        string vehicle_type
    }
    Route {
        string route_id PK
        polygon shape  // Geospatial data type
        datetime last_updated
        method calculate_distance() float  // Built-in method
    }

Example Query:

SELECT driver_name, calculate_distance(shape) AS route_length
FROM Driver, Route
WHERE Driver.driver_id = Route.driver_id;

Advantages:

• Rich Data Modeling: Store JSON APIs (e.g., Daraz product metadata) or images (e.g., Pathao driver photos) natively.
• Performance: Geospatial queries (e.g., "Find drivers near Thapathali") are optimized.
• Flexibility: Add methods without changing the database schema (e.g., updating calculate_distance without altering the Route table).

Disadvantages:

• Complexity: Requires ORDBMS (e.g., PostgreSQL, Oracle) and ORM tools (e.g., Hibernate).
• Vendor Lock-in: Proprietary features may not port easily (e.g., PostgreSQL’s polygon vs. MySQL’s limited geospatial support).
• Learning Curve: Developers must learn UDTs, OIDs, and inheritance.

Comparison: RDBMS vs. ORDBMS

3. Federated Databases

Federated databases combine heterogeneous databases (e.g., NEPSE’s stock records + Siddhartha Bank’s customer data) into a single virtual system without full integration.

Key Concepts

• Local Autonomy: Each database retains control (e.g., NEPSE doesn’t share raw stock data with banks).
• Global Schema: Unified view of disparate data (e.g., a query combining a customer’s bank balance and stock holdings).
• Transparency: Users query as if data is centralized (e.g., SELECT balance + stock_value FROM customer).

How It Works: A Worked Example

Scenario: A NEPSE broker wants to fetch a customer’s bank balance (from Global IME Bank) and stock portfolio (from NEPSE) in one query.

Advantages:

• No Data Redundancy: Banks and NEPSE keep their own records.
• Flexibility: Works with legacy systems (e.g., NEPSE’s old mainframe + modern banks).
• Security: Sensitive data stays in its original system.

Disadvantages:

• Performance Overhead: Multiple network hops (e.g., broker → bank → NEPSE → broker).
• Complexity: Managing global schemas and translations.
• Inconsistency Risks: If bank and NEPSE update data at different times, results may be stale.

4. Advanced Querying in ORDBMS

ORDBMS extends SQL with object-oriented features and aggregation functions for complex data.

Example Queries

1. Storing JSON in PostgreSQL:

   CREATE TABLE products (
       id SERIAL PRIMARY KEY,
       name VARCHAR(100),
       specs JSONB  -- Stores nested data like {"weight": 500, "dimensions": {"length": 20, "width": 10}}
   );
   
   INSERT INTO products (name, specs) VALUES ('Laptop', '{"weight": 1.5, "brand": "Dell"}');
   

   Query: Find laptops heavier than 1.2 kg.

   SELECT name FROM products
   WHERE specs->>'weight' > 1.2;
   

2. Geospatial Queries (PostGIS):

   -- Find Pathao drivers within 1 km of Thapathali
   SELECT driver_id, ST_Distance(shape, ST_GeomFromText('POINT(27.7172 85.3212)', 4326)) AS distance_m
   FROM drivers
   WHERE ST_DWithin(shape, ST_GeomFromText('POINT(27.7172 85.3212)', 4326), 1000);
   

3. Recursive Queries (Hierarchical Data):

   -- Find all employees under a manager (e.g., for Ncell’s org chart)
   WITH RECURSIVE org_chart AS (
       SELECT employee_id, manager_id, name FROM employees WHERE manager_id IS NULL
       UNION ALL
       SELECT e.employee_id, e.manager_id, e.name
       FROM employees e
       JOIN org_chart o ON e.manager_id = o.employee_id
   )
   SELECT * FROM org_chart;
   

In the Real World

1. Daraz’s Distributed Order Processing:

   • Idea: Uses partitioning to split orders by warehouse (e.g., Kathmandu, Pokhara, Birganj).
   • How: When you order from Daraz, your request is routed to the nearest warehouse node. If the item is out of stock, the system queries other nodes (e.g., "Can Pokhara fulfill this?").
   • Worked Example: A user orders a phone from Daraz. The system checks Kathmandu’s inventory first. If unavailable, it queries Pokhara’s node and redirects the order there.

2. Ncell’s Object-Relational Customer Data:

   • Idea: Stores call logs as objects with metadata (e.g., caller ID, duration, call type: voice/SMS).
   • How: Uses PostgreSQL’s JSONB to store call details alongside traditional columns (e.g., customer_id, call_date).
   • Query Example:

     SELECT customer_id, call_details->>'duration' AS duration_sec
     FROM call_logs
     WHERE call_details->>'type' = 'voice' AND call_date > '2023-01-01';
     

3. NEPSE’s Federated Market Data:

   • Idea: Combines stock prices (from NEPSE’s main system) with customer holdings (from brokers like Global IME Bank) via a federated query.
   • How: A broker’s dashboard runs a single query to fetch:
     • Current stock price (from NEPSE).
     • Customer’s holdings (from their bank).
   • Example Query:

     -- Virtual table combining NEPSE and bank data
     SELECT n.StockSymbol, n.Price, b.Holdings, (n.Price * b.Holdings) AS PortfolioValue
     FROM NEPSE_Stocks n, Bank_Holdings b
     WHERE n.StockSymbol = b.StockSymbol AND b.CustomerID = 12345;
     

Exam Tip

• Focus on comparisons: The exam often asks to compare distributed vs. centralized databases or ORDBMS vs. RDBMS. Use the table above as a template.
• Diagrams are key: Draw sequence diagrams for distributed transactions (e.g., 2PC) and ER diagrams for ORDBMS schemas.
• Real-world mapping: Relate concepts to Nepal’s tech scene:
  • Distributed DBs → Daraz/Pathao scaling.
  • ORDBMS → Ncell’s call logs or NEPSE’s geospatial data.
  • Federated DBs → NEPSE + bank integrations.
• Practice queries: Write SQL for:
  • Partitioning (e.g., PARTITION BY RANGE in PostgreSQL).
  • Object-relational features (e.g., JSONB operations).
  • Federated joins (e.g., FROM BankData, NEPSEData WHERE ...).
• Common pitfalls:
  • Confusing replication (copying data) with partitioning (splitting data).
  • Assuming ORDBMS supports all OOP features (e.g., polymorphism is limited).
  • Forgetting that federated queries may have latency due to network hops.