Specialized Databases: Types, Architectures & Real-World Use

TAKEAWAYS:

• Specialized databases extend relational models to handle objects, spatial data, multimedia, or distributed environments, each with unique features and trade-offs.
• Distributed databases fragment, replicate, and allocate data across nodes to improve scalability, reliability, and transparency (e.g., Ncell’s call-routing system).
• NoSQL systems (document, key-value, graph) prioritize flexibility and scalability over ACID, used by Daraz for product catalogs and Pathao for ride requests.
• Spatial and multimedia databases store geospatial (Google Maps) or unstructured data (YouTube videos) with specialized indexing and query methods.
• Design techniques like replication (eSewa’s transaction logs), fragmentation (NTC’s network nodes), and allocation (bank ATM networks) optimize performance and fault tolerance.
• Active databases use triggers and rules (e.g., auto-updating inventory in Daraz) to enforce business logic dynamically.

1. Introduction to Specialized Databases

Specialized databases are designed to handle specific types of data or requirements that traditional relational databases (RDBMS) cannot efficiently manage. These include:

• Object-Oriented Databases (OODB): Store complex objects with inheritance, polymorphism, and encapsulation (e.g., CAD systems).
• Object-Relational Databases (ORDB): Extend SQL to handle objects (e.g., PostgreSQL with JSONB).
• Spatial Databases: Manage geographic or geospatial data (e.g., Google Maps, NTC’s network topology).
• Multimedia Databases: Store and retrieve audio, video, or images (e.g., YouTube, NEPSE’s stock charts).
• Distributed Databases: Spread across multiple nodes for scalability and fault tolerance (e.g., Ncell’s call distribution).
• NoSQL Databases: Flexible schemas for big data (e.g., MongoDB for Daraz’s product catalog).
• Active Databases: Enforce rules dynamically via triggers (e.g., auto-deducting Khalti balance on eSewa payment).

2. Object-Oriented and Object-Relational Databases

Key Concepts

• Objects: Data + methods (e.g., a Student with enroll() method).
• Type Constructors: Complex types like sets, lists, or arrays (e.g., Student::grades = [90, 85, 78]).
• Inheritance: Hierarchies (e.g., Vehicle → Car, Bike).
• Encapsulation: Hiding implementation details (e.g., private attributes in Java).

Comparison: OODB vs. ORDB

How ORDB Extends SQL

ORDB adds:

• User-Defined Types (UDT): Custom data types (e.g., Point(x,y)).
• Methods: Functions tied to types (e.g., Point.distance()).
• Inheritance: Table hierarchies (e.g., Employee → Manager).
• Collections: Arrays, JSON, or XML columns (e.g., Student.courses = {["CS101", "MATH201"]}).

Example: Library Management (ORDB)

-- Define a UDT for Book
CREATE TYPE Book AS (
    title TEXT,
    author TEXT,
    isbn VARCHAR(20)
);

-- Table with a collection of Books
CREATE TABLE Library (
    id SERIAL PRIMARY KEY,
    name TEXT,
    books Book[]  -- Array of Book objects
);

-- Query using array methods
SELECT name, unnest(books).title
FROM Library
WHERE 'CS' = ANY(unnest(books).title);

3. Spatial Databases

What They Store

• Geometric Data: Points, lines, polygons (e.g., POINT(85.31, 27.71) for Kathmandu).
• Geospatial Data: Raster (satellite images) or vector (road networks).
• Applications:
  • Nepal: NTC’s fiber-optic network routes, Pathao’s ride matching.
  • Global: Google Maps (directions), Uber (driver-location tracking).

Key Features

• Spatial Indexes: R-trees or quadtrees for fast queries.
• Operations:
  • ST_Distance(A, B): Distance between two points.
  • ST_Intersects(road, polygon): Check if a road crosses a flood zone.
• Example Query:

  -- Find all ATMs within 500m of a user in Thapathali
  SELECT name FROM ATM
  WHERE ST_Distance(
      location,
      ST_GeomFromText('POINT(85.31 27.71)', 4326)
  ) < 500;
  

IMAGE: Spatial Database Index (R-tree)

4. Multimedia Databases

Data Types Stored

Applications in Nepal

• YouTube Nepal: Stores video chunks + metadata (tags, views).
• NEPSE: Charts for stock trends (SVG or PNG).
• eSewa: QR code images for payments.

Challenges

• Storage: Compression (e.g., MP3 vs. WAV) and sharding.
• Querying: Content-based retrieval (e.g., "find videos with green hills").
• Metadata: EXIF data (e.g., camera settings for photos).

Example: Video Database Schema

erDiagram
    VIDEO ||--o{ CLIP : contains
    VIDEO {
        int video_id PK
        string title
        datetime upload_date
        int duration_sec
    }
    CLIP {
        int clip_id PK
        int video_id FK
        int start_frame
        int end_frame
        string thumbnail_path
    }
    TAG ||--o{ VIDEO : tagged_with
    TAG { int tag_id PK, string keyword }

5. Distributed Databases

Definition

A distributed database splits data across multiple physical locations (nodes) connected by a network, enabling:

• Scalability: Handle more users (e.g., Ncell’s nationwide call routing).
• Fault Tolerance: If one node fails, others take over (e.g., eSewa’s backup servers).
• Locality: Data stored near users (e.g., Daraz’s warehouses).

Architectures

1. Homogeneous: All nodes use the same DBMS (e.g., Oracle RAC).
2. Heterogeneous: Mixed systems (e.g., PostgreSQL + MongoDB).
3. Hybrid: Centralized + distributed (e.g., bank core system + ATMs).

Transparency Types

Design Techniques

1. Fragmentation:
   • Horizontal: Split rows by condition (e.g., Customers → Customers_Kathmandu, Customers_Pokhara).
   • Vertical: Split columns (e.g., Order → OrderHeader, OrderDetails).
2. Replication:
   • Full: All data copied (high redundancy, e.g., Khalti’s transaction logs).
   • Partial: Only frequently accessed data (e.g., Daraz’s bestsellers).
3. Allocation:
   • Centralized: All data on one node (simple but bottleneck).
   • Distributed: Data split by key (e.g., CustomerID % 3 → Node 1/2/3).

Example: Ncell’s Call Routing (Distributed DB)

sequenceDiagram
    participant User
    participant LocalExchange
    participant RegionalSwitch
    participant NationalGateway

    User->>LocalExchange: Dial 98XXXXXXXX
    LocalExchange->>RegionalSwitch: Route call (based on prefix)
    RegionalSwitch->>NationalGateway: Forward to nearest tower
    NationalGateway-->>User: Connect call

How It Works:

• Fragmentation: Calls routed by area code (e.g., 981 → Kathmandu, 982 → Pokhara).
• Replication: Tower data replicated for failover.
• Transparency: User sees 98XXXXXXXX without knowing the underlying nodes.

6. NoSQL Databases

Types and Use Cases

Features vs. RDBMS

Example: Daraz’s Product Catalog (MongoDB)

{
  "_id": "prod_1001",
  "name": "Nepali Hat",
  "price": 1200,
  "categories": ["fashion", "men"],
  "stock": {
    "warehouse_A": 50,
    "warehouse_B": 30
  },
  "reviews": [
    { "user": "user_45", "rating": 5, "comment": "Great quality!" }
  ]
}

Query: Find all men’s fashion items under Rs. 1500.

db.products.find({
  categories: "men",
  price: { $lt: 1500 },
  "stock.warehouse_A": { $gt: 0 }
});

7. Active Databases

Key Concepts

• Triggers: Automated actions on events (e.g., AFTER INSERT).
• Rules: Conditions + actions (e.g., "If balance < 100, send alert").
• Example: Bank overdraft protection.

Example: eSewa Payment Trigger

CREATE TRIGGER deduct_balance
AFTER eSewa_payment
FOR EACH ROW
BEGIN
    UPDATE user_accounts
    SET balance = balance - NEW.amount
    WHERE user_id = NEW.user_id;

    -- Auto-reject if insufficient funds
    IF NEW.amount > (SELECT balance FROM user_accounts WHERE user_id = NEW.user_id) THEN
        INSERT INTO failed_transactions (payment_id, reason)
        VALUES (NEW.payment_id, 'Insufficient funds');
    END IF;
END;

8. Real-World Applications in Nepal

1. eSewa + Khalti:

   • Distributed DB: Transaction logs replicated across servers for fraud detection.
   • Active DB: Triggers auto-deduct balances on successful payments.
   • NoSQL: Redis caches frequent user sessions.

2. NTC’s Fiber-Optic Network:

   • Spatial DB: Stores cable routes and node locations for maintenance.
   • Distributed DB: Data fragmented by region (e.g., East vs. West Nepal).

3. Daraz:

   • NoSQL (MongoDB): Flexible product schemas (e.g., electronics vs. groceries).
   • Replication: Inventory data synced across warehouses.

4. Pathao:

   • Graph DB (Neo4j): Maps driver-user relationships for ride matching.
   • Spatial DB: Tracks real-time driver locations.

5. NEPSE:

   • Multimedia DB: Stores stock charts and historical data.
   • Distributed DB: Regional servers for faster access.

Exam Tip

1. Compare and Contrast:

   • Always compare OODB vs. ORDB, RDBMS vs. NoSQL, or centralized vs. distributed databases in tables.
   • Example: For the question "Compare OODB with ORDB", list 4–5 columns (schema, query language, use case, etc.).

2. Design Questions:

   • For EER models (e.g., library system), show:
     • Generalization/specialization (e.g., Member → Student, Staff).
     • Disjoint/overlapping constraints (e.g., a book can be both Fiction and BestSeller).
     • Total/partial participation (e.g., every Loan must have a Member).
   • Visual: Draw the EER diagram with mermaid or describe it clearly.

3. Distributed Database Techniques:

   • For replication/allocation, explain:
     • Why: Improve reliability/scalability.
     • How: Use examples like Ncell’s call routing or eSewa’s logs.
   • Formula: If asked about fragmentation, show the split (e.g., Customers → North, South).

4. NoSQL vs. RDBMS:

   • Link to real systems:
     • NoSQL: Daraz (MongoDB for catalog), Pathao (Redis for sessions).
     • RDBMS: Bank core systems (strong consistency for transactions).

5. Short-Answer Tricks:

   • Spatial DB: Mention ST_Distance, ST_Intersects, and R-trees.
   • Multimedia DB: Name 2–3 data types (audio/video/image) + one challenge (storage/querying).
   • Active DB: Always give a trigger example (e.g., balance deduction).

Worked Example: Bank Loan Interest (Active DB)

Scenario: A bank offers loans with auto-calculated interest. If a loan is overdue, send an SMS alert.

stateDiagram-v2
    [*] --> Active
    Active --> Overdue: if due_date < today()
    Overdue --> SendAlert: trigger SMS
    Overdue --> ApplyPenalty: if days_overdue > 30
    SendAlert --> [*]
    ApplyPenalty --> [*]

SQL Trigger:

CREATE TRIGGER check_overdue_loans
AFTER INSERT ON loan_payments
FOR EACH ROW
BEGIN
    IF NEW.payment_date > (SELECT due_date FROM loans WHERE loan_id = NEW.loan_id) THEN
        -- No action (payment made on time)
    ELSE
        -- Loan is overdue
        INSERT INTO alerts (loan_id, message, sent_date)
        VALUES (NEW.loan_id, 'Your loan is overdue!', CURRENT_TIMESTAMP);

        -- Apply penalty after 30 days
        IF DATEDIFF(day, (SELECT due_date FROM loans WHERE loan_id = NEW.loan_id), CURRENT_TIMESTAMP) > 30 THEN
            UPDATE loans
            SET interest_rate = interest_rate * 1.1
            WHERE loan_id = NEW.loan_id;
        END IF;
    END IF;
END;

Summary Table: Specialized Databases