Advanced DB Topics & Case Studies: Trends, Tech & Real-World Apps

TAKEAWAYS:

• Understand blockchain databases (immutable ledgers) vs. traditional DBs (ACID compliance) using a comparison table and a real-world trace of NEPSE’s trade verification.
• Master graph databases (Neo4j) for fraud detection in Khalti transactions via a query walkthrough and ER-to-graph conversion.
• Analyze time-series databases (InfluxDB) for NTC’s network traffic monitoring with a data model diagram and query optimization example.
• Evaluate AI-integrated databases (Google’s BigQuery ML) using a case study of YouTube’s recommendation engine.
• Design serverless database architectures (AWS DynamoDB) for Pathao’s ride-matching with a cost vs. scalability tradeoff table.
• Solve exam-style problems on hybrid DBs (SQL + NoSQL) with a worked example tied to Daraz’s inventory system.

1. Blockchain Databases: Immutability Meets Data Integrity

1.1 Core Concepts

Blockchain databases store data in chained blocks, each cryptographically linked to the previous one. Key features:

• Immutability: Once written, data cannot be altered without consensus.
• Decentralization: No single authority controls the ledger (e.g., Bitcoin, Ethereum).
• Consensus Mechanisms: Proof-of-Work (PoW), Proof-of-Stake (PoS), or Byzantine Fault Tolerance (BFT).

stateDiagram-v2
    [*] --> NewBlock: Data Submitted
    NewBlock --> Validate: Consensus (e.g., PoW)
    Validate --> Reject: Invalid
    Validate --> AddToChain: Valid
    AddToChain --> [*]: Block Added

1.2 Comparison: Blockchain vs. Traditional Databases

1.3 Real-World Example: NEPSE’s Trade Verification

Problem: NEPSE (Nepal Stock Exchange) needs to prevent trade manipulation by ensuring every transaction is tamper-proof. Solution: A hybrid blockchain-SQL system:

1. Blockchain Layer: Stores trade hashes in an immutable ledger (e.g., Hyperledger Fabric).
2. SQL Layer: Manages user accounts and order books (PostgreSQL).
3. Smart Contract: Validates trades before adding them to the blockchain.

sequenceDiagram
    participant User
    participant SQL_DB
    participant SmartContract
    participant Blockchain
    User->>SQL_DB: Submit Trade Order
    SQL_DB-->>User: Order ID
    User->>SmartContract: Verify Order
    SmartContract->>Blockchain: Add Hash to Ledger
    Blockchain-->>SmartContract: Confirmation
    SmartContract->>SQL_DB: Update Trade Status

Worked Example:

• Trade Data: OrderID: T1001, Stock: NEPSE, Quantity: 100, Price: 200
• Hash: SHA-256("T1001|NEPSE|100|200") = 5f4dcc3b...
• Blockchain Entry:

  {
    "index": 42,
    "timestamp": "2024-05-20T12:00:00Z",
    "data": "5f4dcc3b...",
    "previousHash": "a1b2c3..."
  }
  

2. Graph Databases: Fraud Detection in Khalti

2.1 Why Graphs?

Graph databases (e.g., Neo4j) excel at relationship-heavy data, such as:

• Fraud rings: Detecting money laundering via transaction links.
• Social networks: Recommending friends (e.g., Facebook’s "People You May Know").
• IT infrastructure: Mapping dependencies in cloud services.

2.2 EER to Graph Conversion

Convert this Enhanced ER Diagram (from Unit 3) into a graph model:

erDiagram
    User ||--o{ Transaction : "initiates"
    Transaction ||--|| Account : "belongs_to"
    Transaction ||--|{ Merchant : "targets"
    Account ||--|{ User : "owned_by"
    Merchant }|--|| User : "registered_by"

Graph Model (Cypher Query):

CREATE (u1:User {id: "U101", name: "Ramesh"})
CREATE (u2:User {id: "U102", name: "Sita"})
CREATE (m:Merchant {id: "M201", name: "Daraz"})
CREATE (t1:Transaction {id: "T301", amount: 5000})
CREATE (a1:Account {id: "A401", balance: 10000})
CREATE (u1)-[:INITIATES]->(t1)
CREATE (t1)-[:BELONGS_TO]->(a1)
CREATE (t1)-[:TARGETS]->(m)
CREATE (a1)-[:OWNED_BY]->(u1)
CREATE (m)-[:REGISTERED_BY]->(u2)

2.3 Fraud Detection Query

Find suspicious transactions (e.g., rapid transfers between accounts):

MATCH (a1:Account)-[:OWNED_BY]->(u1:User),
      (a2:Account)-[:OWNED_BY]->(u2:User),
      (t1:Transaction)-[:BELONGS_TO]->(a1),
      (t2:Transaction)-[:BELONGS_TO]->(a2)
WHERE u1 <> u2 AND
      t1.amount > 10000 AND
      t2.amount > 10000 AND
      abs(t1.timestamp - t2.timestamp) < 3600s
RETURN u1.name, u2.name, t1.amount, t2.amount

Real-World Tie-In: Khalti uses graph databases to flag money mules (intermediaries in fraud schemes). For example:

• Pattern: User A sends ₹50,000 to User B (unrelated), who immediately transfers ₹49,000 to User C (a merchant).
• Graph Query: Detects the triangle relationship (A → B → C) and freezes the transaction.

3. Time-Series Databases: NTC’s Network Traffic Monitoring

3.1 What Are Time-Series Databases?

Specialized for data indexed by time, used in:

• IoT: Sensor readings (e.g., temperature, humidity).
• Finance: Stock prices, trading volumes.
• Telecom: Network latency, packet loss (e.g., NTC’s fiber optics).

Example Databases: InfluxDB, TimescaleDB, Prometheus.

3.2 Data Model

Unlike SQL tables, time-series databases store data in time-ordered series:

-- Traditional SQL (inefficient for time-series)
CREATE TABLE sensor_readings (
    sensor_id INT,
    timestamp TIMESTAMP,
    value FLOAT
);

-- Time-Series DB (optimized)
CREATE TABLE temperature (
    time TIMESTAMP,
    location STRING,
    value DOUBLE
);

3.3 Query Optimization

Problem: NTC needs to monitor latency spikes on the Kathmandu-Pokhara fiber route. Solution: Use downsampling and aggregation functions:

-- Raw data (100ms intervals)
SELECT time, avg(latency_ms)
FROM network_metrics
WHERE location = 'KTM-POK'
GROUP BY time(1m)  -- Aggregate every 1 minute

Visualization:

4. AI-Integrated Databases: YouTube’s Recommendation Engine

4.1 How AI Meets Databases

Databases now embed machine learning models for:

• Predictive queries: "Show me videos like this one."
• Anomaly detection: Flagging fake reviews (e.g., Amazon).
• Automated indexing: Google’s BigQuery ML.

4.2 Case Study: YouTube’s Recommendation System

1. Data Sources:
   • Watch history (SQL table: user_watches).
   • Clickstream data (time-series: user_clicks).
   • Metadata (NoSQL: video_tags).
2. AI Model: A collaborative filtering algorithm (e.g., matrix factorization) trained on:

   -- SQL query for user-video interactions
   SELECT user_id, video_id, watch_duration
   FROM user_watches
   WHERE watch_duration > 30s  -- Filter low-quality watches
   

3. Database Integration:
   • BigQuery ML: Runs the model inside the database.
   • Real-time updates: New watches trigger model retraining.

5. Serverless Databases: Pathao’s Ride-Matching

5.1 What Is Serverless?

• No infrastructure management: Scales automatically (e.g., AWS DynamoDB, Firebase).
• Pay-per-use pricing: Charged only for requests (cost-effective for variable loads).

5.2 Pathao’s Architecture

Cost vs. Scalability Tradeoff:

Worked Example:

• Problem: During Dashain, Pathao sees 10x normal traffic.
• Solution: DynamoDB auto-scales to handle 10,000 requests/sec without downtime.

Screenshot of DynamoDB’s auto-scaling configuration (Image: Vitaly Zdanevich, CC0, via Wikimedia Commons)

6. Hybrid Database Systems: Daraz’s Inventory Management

6.1 Why Hybrid?

Combine SQL (structured data) and NoSQL (flexible schemas) for:

• Transaction processing: SQL for orders (ACID compliance).
• User profiles: NoSQL for dynamic attributes (e.g., wishlists).

6.2 Example Schema

-- SQL: Orders (structured)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    total_amount DECIMAL(10,2)
);

-- NoSQL: User Profiles (flexible)
{
    "user_id": 1001,
    "name": "Hari",
    "wishlist": ["iPhone 15", "MacBook Pro"],
    "addresses": [
        {"type": "home", "city": "Kathmandu"},
        {"type": "office", "city": "Lalitpur"}
    ]
}

Query Workflow:

1. SQL: Check stock availability for an order.
2. NoSQL: Fetch user’s preferred delivery address.
3. Hybrid Join: Combine results for order confirmation.

In the Real World

1. eSewa’s Fraud Detection

   • Idea: Graph databases (Neo4j) to detect synthetic identities (fake users linked via multiple transactions).
   • How: Queries like MATCH (u1:User)-[:USED_CARD]->(c:Card)-[:USED_CARD]->(u2:User) WHERE u1 <> u2 RETURN u1, u2 flag money mules.

2. Ncell’s Network Analytics

   • Idea: Time-series databases (InfluxDB) to track SMS delivery latency across towers.
   • How: Downsampled queries identify congestion hotspots (e.g., Thamel during New Year).

3. NEPSE’s Blockchain Pilot

   • Idea: Hybrid blockchain-SQL to prevent insider trading.
   • How: Trades are hashed on a private blockchain, while user portfolios stay in SQL for fast queries.

Exam Tip

1. Case Study Questions (30%):

   • Expect scenario-based problems (e.g., "Design a database for Pathao’s surge pricing").
   • Structure your answer:
     • Requirements (e.g., "real-time updates needed").
     • Schema (SQL/NoSQL/graph).
     • Optimizations (indexes, partitioning).
     • Tradeoffs (cost vs. performance).

2. Comparison Tables (20%):

   • Memorize blockchain vs. SQL, graph vs. relational, and time-series vs. columnar.
   • Example: For blockchain, highlight consensus mechanisms and throughput limits.

3. Query Writing (25%):

   • Practice Cypher (Neo4j), SQL for time-series, and NoSQL updates.
   • Tip: Use pseudocode if stuck (e.g., "FOR each node in graph: ...").

4. Short Answers (15%):

   • Define terms like:
     • Sharding (splitting a DB horizontally).
     • Vector databases (for AI embeddings, e.g., Pinecone).
   • Example: "Explain how DynamoDB handles hot partitions."

5. Diagrams (10%):

   • Must-draw:
     • Blockchain ledger (with hashes).
     • Graph model (from an ER diagram).
     • Hybrid architecture (SQL + NoSQL).

Final Note: Focus on real-world mappings (e.g., Khalti → graph DBs, NTC → time-series). Use mermaid diagrams to visualize workflows, and compare technologies in tables for quick revision.