Database Users & Real-World DBMS Applications

TAKEAWAYS:

• Database users are categorized into administrators, application users, and anonymous users, each with distinct privileges and access levels.
• Authentication (username/password, biometrics, OAuth) and authorization (role-based, attribute-based) ensure secure data access.
• Real-world applications like Daraz’s inventory tracking (relational DBMS) and Ncell’s customer billing (NoSQL for scalability) rely on optimized database design.
• SQL injection and privilege escalation are critical security threats requiring strict access control.
• Data independence (logical vs. physical) allows businesses to evolve schemas without disrupting applications (e.g., eSewa’s transaction logs).
• NoSQL databases (e.g., MongoDB in Khalti’s payment processing) excel in unstructured data but lack ACID guarantees compared to SQL.

1. Database Users: Roles and Responsibilities

A database system supports multiple users with varying needs. Users are classified based on their access rights, responsibilities, and interaction levels:

Key User Types in Nepal’s Context:

2. Authentication and Authorization Mechanisms

Authentication verifies who the user is, while authorization defines what they can do.

Authentication Methods:

flowchart TD
    A["User Login"] --> B{"Authentication Method?"}
    B -->|"Username/Password"| C["Traditional DBMS"]
    B -->|"Biometrics"| D["Modern Systems (eSewa)"]
    B -->|"OAuth"| E["Third-Party Apps (Khalti)"]
    C --> F["SQL Authentication"]
    D --> G["Biometric Verification"]
    E --> H["Token-Based Access"]

Example: eSewa’s Two-Factor Authentication (2FA)

• Step 1: User enters username/password (basic auth).
• Step 2: SMS-based OTP sent to registered mobile (multi-factor).
• Step 3: Database checks OTP against stored hash (never stores plaintext).

Authorization Models:

3. Practical Applications of Databases in Nepal

Case Study 1: Daraz’s Inventory Management (Relational DBMS)

Daraz uses a relational database to track:

• Products (SID, PName, Price, Stock)
• Orders (OrderID, SID, CustomerID, Status)
• Suppliers (SupplierID, Contact, LeadTime)

SQL Example: Check Low-Stock Items

SELECT PName, Stock
FROM Products
WHERE Stock < 10
ORDER BY Stock ASC;

Why Relational?

• Strong ACID properties ensure order accuracy.
• Joins link products to suppliers and orders.

Case Study 2: Ncell’s Customer Billing (NoSQL Database)

Ncell uses MongoDB (NoSQL) for:

• Unstructured data (e.g., call logs, SMS records).
• Scalability to handle millions of users.

NoSQL Advantage:

• Flexible schema for varying data formats (e.g., call duration vs. data usage).
• Horizontal scaling for high traffic (e.g., during festival sales).

Comparison: SQL vs. NoSQL for Nepal’s Businesses

4. Security Challenges and Solutions

Common Threats:

1. SQL Injection

   • Attack: Malicious SQL code injected via input fields.
   • Example:

     -- Vulnerable query:
     SELECT * FROM Users WHERE Username = '[user_input]' AND Password = '[user_input]';
     -- Attack input: ' OR '1'='1
     -- Result: Returns ALL users!
     

   • Solution: Use prepared statements (parameterized queries).

2. Privilege Escalation

   • Attack: Low-privilege user gains admin access.
   • Example: A Pathao driver hacks the database to alter fare rates.
   • Solution: Least privilege principle (grant only necessary rights).

Database Security Measures:

5. Data Independence and Three-Schema Architecture

Why It Matters:

• Logical independence: Change schema without affecting applications.
• Physical independence: Change storage (e.g., from HDD to SSD) without code changes.

Three-Schema Architecture (Used by eSewa):

flowchart TD
    A["External Schema"] -->|"Views"| B["Conceptual Schema"]
    B -->|"Logical Design"| C["Internal Schema"]
    C -->|"Physical Storage"| D["Database Files"]

Example: eSewa’s Transaction Logs

• External Schema: Users see only their transaction history.
• Conceptual Schema: Stores all transactions (sender, receiver, amount, timestamp).
• Internal Schema: Optimized for fast retrieval (indexed by date).

6. Worked Example: Ncell’s Customer Database Query

Scenario: Ncell wants to find users who exceeded their data limit in January 2024.

Database Schema:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Name VARCHAR(100),
    PlanType VARCHAR(20)
);

CREATE TABLE DataUsage (
    UsageID INT PRIMARY KEY,
    UserID INT,
    Date DATE,
    MBUsed INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

SQL Query:

SELECT U.Name, SUM(D.MBUsed) AS TotalMB
FROM Users U
JOIN DataUsage D ON U.UserID = D.UserID
WHERE D.Date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY U.UserID, U.Name
HAVING SUM(D.MBUsed) > 10000; -- 10GB limit

Output Interpretation:

Action: Ncell sends alerts to Ramesh and Priya to upgrade their plans.

7. NoSQL Databases: When to Use Them

When to Choose NoSQL (Khalti’s Payment Processing):

• High write throughput: Millions of transactions per second.
• Unstructured data: User feedback, chat logs.
• Horizontal scaling: Add servers without downtime.

NoSQL Types and Examples:

Disadvantage: No built-in joins (unlike SQL). Workaround: Denormalize data.

In the Real World

1. Daraz’s Order Fulfillment

   • Idea: Uses a relational database to track orders, inventory, and supplier deliveries.
   • How: SQL queries join Orders, Products, and Suppliers tables to ensure stock availability before processing.
   • Real Impact: Reduces out-of-stock errors by 40% during festivals.

2. Ncell’s Customer Churn Analysis

   • Idea: Uses NoSQL (MongoDB) to analyze unstructured data like call logs, SMS interactions, and app usage.
   • How: Aggregates data to identify users who switch to competitors (e.g., SmartCell).
   • Real Impact: Targeted promotions reduce churn by 25%.

3. NEPSE’s Stock Market Data

   • Idea: Relies on real-time relational databases to track stock prices, trades, and investor portfolios.
   • How: ACID transactions ensure no double-counting of shares.
   • Real Impact: Enables instant trade settlements and fraud detection.

Exam Tip

• Focus on SQL queries for user-based operations (e.g., GRANT, REVOKE, SELECT with WHERE clauses).
• Compare SQL vs. NoSQL with Nepal-specific examples (e.g., Daraz vs. Khalti).
• Security questions will test your knowledge of SQL injection prevention and RBAC models.
• Three-schema architecture is often asked in practical scenarios (e.g., how eSewa separates user views from internal storage).
• Worked examples (like Ncell’s data usage query) are high-scoring—practice writing them step-by-step.

Common Pitfalls to Avoid:

• ❌ Confusing authentication (who you are) with authorization (what you can do).
• ❌ Assuming NoSQL is always faster—relational DBs excel in structured data.
• ❌ Forgetting to include foreign keys in schema diagrams (critical for referential integrity).

A flowchart showing how malicious input alters SQL queries. (Image: Batka savemazaalai, CC BY-SA 4.0, via Wikimedia Commons)