Database Applications & Case Studies: Real-World DBMS Use

TAKEAWAYS:

• Databases underpin Nepal’s fintech (eSewa, Khalti) and e-commerce (Daraz) via transactional systems, inventory tracking, and fraud detection.
• Cloud databases (AWS RDS, Google Cloud SQL) enable scalability for Pathao’s ride-sharing and NTC’s network management.
• Case studies reveal trade-offs between relational (SQL) and NoSQL (MongoDB) models for performance vs. flexibility.
• Security breaches (e.g., Ncell’s 2021 data leak) highlight the need for encryption, access control, and backup protocols.
• Data analytics in NEPSE and banks use OLAP cubes to forecast stock trends and loan defaults.
• Practical projects (e.g., hospital patient records) demonstrate ER modeling, normalization, and SQL query optimization.

1. Introduction to Practical Database Applications

Databases are the backbone of modern business operations. In Nepal, they manage:

• Financial transactions (eSewa, Khalti)
• Logistics and inventory (Daraz, IKEA Nepal)
• Telecom and network operations (NTC, Ncell)
• Stock market analytics (NEPSE)
• Healthcare records (Bhaktapur Hospital, Patan Hospital)

Why Study Real-World Cases?

• Understand scalability challenges (e.g., Daraz’s peak-season sales).
• Learn trade-offs between cost, performance, and security (e.g., Ncell’s legacy vs. cloud databases).
• Apply theoretical concepts (ER diagrams, normalization) to real projects.

2. Case Study 1: Fintech Databases (eSewa, Khalti)

How They Work

Fintech platforms like eSewa and Khalti use highly transactional databases to:

1. Store user accounts (customer, merchant, admin).
2. Track transactions (amount, timestamp, status).
3. Prevent fraud (real-time validation, encryption).

Database Architecture

classDiagram
    class User {
        +String userID
        +String name
        +String email
    }
    class Transaction {
        +String txnID
        +String userID
        +Double amount
        +String status
    }
    class Merchant {
        +String merchantID
        +String name
        +String bankAccount
    }
    User "1" --> "0..*" Transaction : makes
    Merchant "1" --> "0..*" Transaction : processes

Key Technologies

Worked Example: eSewa’s Transaction Flow

1. User initiates payment → Khalti API calls eSewa’s backend.
2. Database checks balance → SQL query:

   SELECT balance FROM accounts WHERE userID = 'U123';
   

3. Deducts amount → Transaction log:

   sequenceDiagram
       participant User
       participant Khalti
       participant eSewa_DB
       User->>Khalti: "Pay ₹1,000 to Merchant M456"
       Khalti->>eSewa_DB: "Check balance(U123)"
       eSewa_DB-->>Khalti: "Balance: ₹5,000"
       Khalti->>eSewa_DB: "Debit ₹1,000"
       eSewa_DB-->>Khalti: "Success"

Challenges

• High concurrency: Multiple users transacting simultaneously → locking mechanisms needed.
• Data consistency: If a power outage occurs mid-transaction → ACID properties ensure recovery.

3. Case Study 2: E-Commerce Databases (Daraz, IKEA Nepal)

How Daraz Uses Databases

Daraz’s database system handles:

• Product catalog (100,000+ items).
• Inventory management (stock levels, suppliers).
• Order processing (queues, tracking).
• Customer reviews (NoSQL for unstructured data).

Database Schema Comparison

Worked Example: Order Processing Queue

Daraz uses a FIFO queue to process orders:

stateDiagram-v2
    [*] --> Pending
    Pending --> Processing: "Order received"
    Processing --> Shipped: "Packed & dispatched"
    Shipped --> Delivered: "Delivered to customer"
    Delivered --> [*]

Optimization Techniques

• Indexing: Speed up searches (e.g., WHERE category = 'Electronics').
• Caching: Store frequent queries (e.g., "Best-selling laptops").
• Partitioning: Split large tables (e.g., orders by month).

4. Case Study 3: Telecom Databases (NTC, Ncell)

How NTC/Ncell Manage Networks

Telecom companies use databases to:

• Track subscriber data (name, plan, usage).
• Bill generation (call/SMS data).
• Network performance monitoring (latency, outages).

Database Design for Subscriber Data

erDiagram
    SUBSCRIBER ||--o{ PLAN : "has"
    SUBSCRIBER ||--o{ CALL_LOG : "generates"
    PLAN {
        string planID PK
        string name
        double costPerGB
    }
    CALL_LOG {
        string logID PK
        string subscriberID FK
        string startTime
        double duration
    }

Worked Example: Call Billing Calculation

-- Query to calculate monthly bill for a subscriber
SELECT
    s.subscriberID,
    SUM(c.duration * p.costPerGB) AS totalCost
FROM
    CALL_LOG c
JOIN
    SUBSCRIBER s ON c.subscriberID = s.subscriberID
JOIN
    PLAN p ON s.planID = p.planID
WHERE
    c.startTime BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    s.subscriberID;

Challenges

• High write load: Millions of call records daily → batch processing.
• Data retention: GDPR-like laws require deleting old logs → automated cleanup scripts.

5. Case Study 4: Stock Market Databases (NEPSE)

How NEPSE Uses Databases

NEPSE’s database system supports:

• Stock listings (company data, market cap).
• Trading orders (buy/sell requests).
• Historical data (for analysis).

Database Schema for Stocks

classDiagram
    class Stock {
        +String ticker
        +String companyName
        +Double currentPrice
    }
    class Order {
        +String orderID
        +String ticker
        +String buyerID
        +Double quantity
        +String status
    }
    Stock "1" --> "0..*" Order : has

Worked Example: OLAP for Market Trends

NEPSE uses OLAP cubes to analyze stock trends:

graph TD
    A["Daily Stock Data"] --> B["OLAP Cube"]
    B --> C["Monthly Returns"]
    B --> D["Sector Performance"]
    B --> E["Correlation Analysis"]

Optimization

• Materialized views: Pre-compute frequent queries (e.g., "Top 10 gainers").
• Compression: Reduce storage for historical data.

6. Case Study 5: Healthcare Databases (Hospitals)

How Hospitals Use Databases

• Patient records (medical history, prescriptions).
• Appointment scheduling (doctor availability).
• Inventory management (medicine stock).

ER Diagram for Hospital Database

erDiagram
    PATIENT ||--o{ APPOINTMENT : "has"
    DOCTOR ||--o{ APPOINTMENT : "assigns"
    PATIENT {
        string patientID PK
        string name
        string diagnosis
    }
    DOCTOR {
        string doctorID PK
        string name
        string specialty
    }
    APPOINTMENT {
        string apptID PK
        string patientID FK
        string doctorID FK
        datetime scheduleTime
    }

Worked Example: Query for Patient History

-- Find all appointments for a patient
SELECT
    p.name,
    d.name AS doctor,
    a.scheduleTime
FROM
    PATIENT p
JOIN
    APPOINTMENT a ON p.patientID = a.patientID
JOIN
    DOCTOR d ON a.doctorID = d.doctorID
WHERE
    p.patientID = 'P1001';

Challenges

• Data privacy: HIPAA/GDPR compliance → encryption & access control.
• Concurrency: Multiple doctors accessing patient records → optimistic locking.

In the Real World

1. eSewa/Khalti

   • Idea: ACID transactions ensure no double-spending or fraud.
   • Real Impact: When eSewa processed ₹5 billion in transactions during Dashain 2023, its database had to handle 10,000+ queries/sec without downtime. It used PostgreSQL with connection pooling to distribute load across servers.

2. Daraz

   • Idea: Queue-based order processing prevents chaos during sales (e.g., Prime Day).
   • Real Impact: During Daraz’s "Big Billion Days" sale, its database used Kafka queues to process 50,000 orders/minute. Without queuing, the system would have crashed under load.

3. NEPSE

   • Idea: OLAP cubes help traders spot trends (e.g., "Why did Ncell’s stock drop 5% today?").
   • Real Impact: NEPSE’s analytics team uses Apache Druid to generate real-time dashboards. In 2023, it detected a market manipulation pattern in Smart Microfinance’s stock, leading to regulatory action.

4. Ncell

   • Idea: Legacy vs. cloud databases trade off cost and flexibility.
   • Real Impact: Ncell migrated 50% of its subscriber data to AWS RDS in 2022, reducing downtime from 2 hours to 2 minutes. However, its legacy billing system (mainframe) still uses COBOL databases, which are harder to scale.

7. Database Security in Real Systems

Common Threats & Mitigations

Worked Example: Preventing SQL Injection

-- UNSAFE (vulnerable to injection)
EXECUTE "UPDATE accounts SET balance = balance - " || amount || " WHERE userID = '" || userID || "'";

-- SAFE (parameterized query)
PREPARE stmt FROM 'UPDATE accounts SET balance = balance - ? WHERE userID = ?';
EXECUTE stmt USING amount, userID;

8. Database Administration in Practice

Key Tasks

1. Backup & Recovery

   • eSewa: Daily snapshots + WAL (Write-Ahead Log) for crash recovery.
   • NEPSE: Hourly backups stored in offsite AWS S3.

2. Performance Tuning

   • Daraz: Added read replicas to reduce query latency.
   • Ncell: Optimized indexes on subscriberID to speed up billing.

3. User Management

   • Hospitals: Role-based access (doctors vs. admins).

Exam Tip

• Focus on case studies: Examiners love real-world applications (e.g., "How does Daraz use NoSQL?").
• Compare SQL vs. NoSQL: Know when to use each (e.g., relational for transactions, NoSQL for unstructured data).
• Optimization is key: Mention indexing, caching, partitioning in your answers.
• Security is critical: Always discuss encryption, access control, backups.
• Practical projects: If your exam includes coding, practice ER diagrams → SQL → query optimization.
• Time management: Spend 30% of time on diagrams (ER models, flowcharts) as they score highly.

Visual Summary

mindmap
  root((Database Applications))
    Fintech
      eSewa/Khalti
        ACID Transactions
        Encryption
    E-Commerce
      Daraz
        Queue Processing
        NoSQL for Reviews
    Telecom
      NTC/Ncell
        Subscriber Data
        Billing Systems
    Stock Market
      NEPSE
        OLAP Cubes
        Historical Analysis
    Healthcare
      Hospitals
        Patient Records
        Appointment Scheduling