Advanced DB Concepts: Models, Architectures & Emerging Systems

TAKEAWAYS:

• Beyond SQL: Advanced databases extend relational models with objects (OODB), spatial data (GIS), multimedia (video/audio), and distributed architectures (fragmentation/replication).
• Real-world layers: Systems like eSewa (transaction processing) or NTC’s network management use distributed databases for reliability/scalability, while Daraz’s recommendation engine relies on NoSQL for unstructured product data.
• Design trade-offs: Choose between centralized (simpler) vs. distributed (fault-tolerant) systems based on cost, latency, and transparency needs (e.g., NEPSE’s stock data replication).
• Emerging trends: NoSQL (document/key-value stores) and deductive databases (rule-based queries) solve problems relational DBs can’t—like Pathao’s dynamic ride-matching or Kathmandu traffic route optimization.
• Visual thinking: Master EER diagrams, distributed architecture layers, and protocol handshakes (e.g., DNS lookups in Kathmandu’s ISPs) to ace exam questions on modeling and design.
• Exam focus: Compare models (OODB vs. ORDB), explain transparency types (e.g., location, failure), and design fragmentation schemes—always tie answers to Nepalese examples (e.g., Ncell’s mobile data fragmentation by region).

1. Evolution Beyond Relational Databases

Relational databases (RDBMS) dominate but fail for:

• Complex objects (e.g., CAD drawings, genetic sequences).
• Distributed data (e.g., Ncell’s nationwide towers).
• Unstructured data (e.g., YouTube videos, WhatsApp media).
• Specialized queries (e.g., Google Maps’ "find restaurants within 500m").

mindmap
  root((Advanced DB Concepts))
    Object-Oriented DB
      Features: Inheritance, Encapsulation, Methods
      Example: CAD software storing 3D models
    Object-Relational DB
      Extends SQL with: User-defined types, Inheritance
      Example: PostgreSQL + GIS extensions
    Distributed DB
      Goals: Scalability, Fault tolerance
      Techniques: Fragmentation, Replication
    Specialized DB
      Spatial: GIS (Google Maps)
      Multimedia: Video/audio (YouTube)
      Deductive: Rule-based (Ncell’s fraud detection)
    Emerging Trends
      NoSQL: Key-value (Redis), Document (MongoDB)
      Graph DB: Social networks (Facebook’s friend graphs)

2. Object-Oriented Databases (OODB)

Definition: Store data as objects (like OOP) with:

• Identity (unique OID, not just attribute values).
• Encapsulation (methods + data).
• Inheritance (e.g., Vehicle → Car, Bike).

Key Features:

Example: A library system with:

• Book (title, author) inherits from LibraryItem.
• EBook specializes Book with fileSize attribute.

erDiagram
    LibraryItem ||--o{ Book : "is_a"
    LibraryItem ||--o{ EBook : "is_a"
    Book {
        string title
        string author
    }
    EBook {
        int fileSize
    }

Real-world use:

• eSewa’s transaction objects: Each payment is an object with methods like verify(), refund(), stored in an OODB for complex workflows.
• Nepal Rastra Bank’s financial audits: Uses OODB to track transactions with temporal methods (e.g., getBalanceAt(date)).

3. Object-Relational Databases (ORDB)

Definition: Extends SQL to handle objects while keeping relational integrity. PostgreSQL Example:

CREATE TYPE Point AS (x float, y float);
CREATE TABLE Locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    coords Point
);
INSERT INTO Locations VALUES (1, 'Kathmandu', '(123.4, 56.7)');

Key Extensions:

• User-defined types (e.g., Point, JSON).
• Inheritance (e.g., Vehicle → Car).
• Methods (e.g., distance(Point p)).

Comparison Table:

Exam Tip: ORDB is SQL + objects—always contrast with pure OODB (no SQL) and RDBMS (no objects).

4. Distributed Databases

Definition: Data split across multiple nodes (physical or logical) for:

• Scalability (e.g., Ncell’s 10M+ users).
• Fault tolerance (e.g., eSewa’s 24/7 uptime).
• Locality (e.g., Daraz’s warehouses in Pokhara/Kathmandu).

Architectures:

mindmap
  root((Distributed DB Architectures))
    Homogeneous
      All nodes use same DBMS (e.g., Ncell’s Oracle cluster)
    Heterogeneous
      Mixed DBMS (e.g., eSewa: PostgreSQL + Redis)
    Tightly Coupled
      Shared memory (e.g., Google’s Spanner)
    Loosely Coupled
      Autonomous nodes (e.g., NTC’s regional servers)

Transparency Types (Critical for exams!):

1. Location: Users access data without knowing its physical location (e.g., typing ntc.gov.np loads data from any server).
2. Replication: Multiple copies for redundancy (e.g., NEPSE’s stock data mirrored in Kathmandu/Pokhara).
3. Failure: System continues if a node fails (e.g., Pathao’s ride-matching survives server crashes).
4. Migration: Data moves transparently (e.g., Daraz’s inventory shifts between warehouses).
5. Concurrency: Multiple users access/update data safely (e.g., eSewa’s simultaneous transactions).

5. Fragmentation and Allocation

Fragmentation: Splitting data into horizontal (rows) or vertical (columns) pieces.

• Horizontal: Split Customers by region (e.g., Kathmandu_Customers, Pokhara_Customers).
• Vertical: Separate Orders (order_id, date) from OrderDetails (product_id, quantity).

Allocation Techniques:

Worked Example: NTC’s Network Management

• Problem: Monitor fiber optic cables across Nepal.
• Solution:
  • Fragment by region: East_Region_Cables, West_Region_Cables.
  • Replicate critical data: National_Backbone at Kathmandu + Pokhara.
  • Allocate: Use partitioned allocation for regional teams.

6. Specialized Database Systems

A. Spatial Databases

Definition: Store geographic data (points, lines, polygons) with queries like:

• "Find all ATMs within 1km of Thamel."
• "Calculate the shortest route from Kathmandu to Pokhara."

Example: Google Maps API uses spatial indexes (e.g., R-trees) to speed up queries.

B. Multimedia Databases

Definition: Store audio, video, images with metadata (e.g., YouTube’s video_id, duration, tags). Challenges:

• Storage: Large files (e.g., 4K video = 1GB+).
• Querying: Search by content (e.g., "find videos with snow").

Example: YouTube’s NoSQL backend uses:

• MongoDB for video metadata (document store).
• CDN (Content Delivery Network) for distributed storage.

C. Deductive Databases

Definition: Uses logical rules (like Prolog) to derive data. Example: Ncell’s Fraud Detection

-- Rule: Flag transactions > $1000 without 2FA
CREATE RULE fraud_rule AS
ON INSERT TO Transactions
WHERE amount > 1000 AND NOT two_factor_auth
DO INSERT INTO Alerts VALUES (transaction_id, 'Fraud suspected');

D. Active Databases

Definition: Automatically trigger actions (e.g., triggers in SQL). Example: eSewa’s Auto-Refund

CREATE TRIGGER refund_failed_payment
AFTER UPDATE ON Payments
FOR EACH ROW
WHEN (NEW.status = 'failed' AND OLD.status = 'pending')
BEGIN
    INSERT INTO Refunds (user_id, amount, reason)
    VALUES (NEW.user_id, NEW.amount, 'Payment failed');
    UPDATE Users SET balance = balance + NEW.amount
    WHERE user_id = NEW.user_id;
END;

7. NoSQL and Emerging Trends

Definition: "Not Only SQL"—databases for scalability and flexible schemas. Types:

Why NoSQL?

• Scalability: Horizontal scaling (e.g., YouTube’s 1B+ videos).
• Flexibility: Schema-less (e.g., WhatsApp’s dynamic messages).
• Performance: Optimized for specific queries (e.g., Redis for caching).

Example: Pathao’s Ride-Matching

• Uses MongoDB to store:

  {
    "_id": "ride_123",
    "driver": { "id": "d456", "location": { "lat": 27.7172, "lng": 85.3240 } },
    "passenger": { "id": "p789", "destination": "Thamel" },
    "status": "matched"
  }
  

• Query: Find all rides near Kathmandu Durbar Square in real-time.

In the Real World

1. eSewa’s Transaction Integrity

   • Concept: Distributed transactions (2PC: Two-Phase Commit).
   • How: When you pay a bill, eSewa:
     • Fragments the transaction (deduct from your account, credit the utility).
     • Replicates the log across servers in Kathmandu/Pokhara.
     • Uses triggers to auto-refund if the utility’s system fails.

2. Ncell’s Mobile Network Management

   • Concept: Spatial + Distributed Databases.
   • How:
     • Spatial DB: Tracks tower locations and signal strength (PostGIS).
     • Distributed: Each region (East/West) has a partitioned fragment of customer data.
     • NoSQL: Uses Cassandra for call logs (time-series data).

3. Daraz’s Recommendation Engine

   • Concept: NoSQL (Document Store) + Graph DB.
   • How:
     • MongoDB: Stores product metadata (title, price, category) as flexible documents.
     • Neo4j: Builds a graph of "users who bought X also bought Y" for recommendations.
     • Caching: Redis stores frequent queries (e.g., "trending products in Pokhara").

Exam Tip: How to Score Full Marks

1. Compare Models: Always use a table for OODB vs. ORDB vs. RDBMS (3 columns, 4 rows).
2. Draw Diagrams:
   • EER models for specialization/generalization (use ||--o{ for total/partial).
   • Distributed architectures (label nodes, fragments, and replication).
3. Tie to Nepal:
   • eSewa: Distributed transactions, replication.
   • Ncell: Spatial DB for towers, fragmentation by region.
   • Daraz: NoSQL for product data, graph DB for recommendations.
4. Define Key Terms:
   • Fragmentation: "Splitting data into smaller pieces (horizontal/vertical)."
   • Transparency: "Hiding complexity from users (e.g., location transparency in NTC’s system)."
5. Worked Examples:
   • For fragmentation, design a schema for Nepal’s traffic management (split by municipality).
   • For triggers, write a rule for NEPSE’s auto-alert on stock price drops.