Database Systems & Management: Design, Types & Applications

TAKEAWAYS:

• A database is an organized collection of structured data that enables efficient storage, retrieval, and management, unlike file systems.
• Relational databases (e.g., MySQL) use tables with rows/columns and SQL, while NoSQL (e.g., MongoDB) handles unstructured data like JSON.
• Cloud databases (e.g., AWS RDS) offer scalability and remote access, used by Ncell for customer data management.
• Database management systems (DBMS) like Oracle or Microsoft SQL Server automate tasks like indexing, backup, and security.
• Normalization (e.g., 1NF, 2NF) reduces redundancy; denormalization speeds up queries but risks duplication.
• Decision Support Systems (DSS) integrate databases with analytics (e.g., NEPSE’s stock market trend analysis).

1. Introduction to Database Systems

A database is a structured collection of data stored electronically, organized to allow efficient access, retrieval, and manipulation. Unlike file systems (where each program stores its own data), databases centralize data, reducing redundancy and improving consistency.

Key Characteristics of Databases:

• Persistence: Data remains stored even after the program terminates.
• Shared Access: Multiple users/applications access the same data.
• Integrity: Constraints ensure data accuracy (e.g., no duplicate customer IDs).
• Scalability: Handles growing data volumes (e.g., Daraz’s inventory database).

+-----------+       +---------------+       +---------------+
| Products  |       |     Orders    |       |   Customers   |
+-----------+       +---------------+       +---------------+
| PK: prodID|<----->| FK: prodID    |<----->| PK: custID    |
| name      |       | custID (FK)   |       | name          |
| price     |       | orderDate     |       | email         |
+-----------+       +---------------+       +---------------+

Why Databases Over File Systems?

Worked Example: Daraz’s Order System Daraz uses a relational database to track:

• Products (SKU, name, stock)
• Orders (orderID, customerID, items, status)
• Customers (ID, name, shipping address)

Query Trace:

SELECT p.name, o.orderDate, c.name
FROM Orders o
JOIN Products p ON o.prodID = p.prodID
JOIN Customers c ON o.custID = c.custID
WHERE o.status = 'Shipped' AND o.orderDate > '2023-01-01';

This retrieves all shipped orders from 2023 for analytics.

2. Types of Database Systems

Databases vary by structure, scalability, and use case.

A. Relational Databases (RDBMS)

• Data stored in tables with rows (records) and columns (fields).
• Uses SQL for querying (e.g., MySQL, PostgreSQL, Microsoft SQL Server).
• Example: NEPSE’s stock market database tracks shares, trades, and investors in tables.

Mermaid Diagram: Relational Database Schema for NEPSE

erDiagram
    SHARES ||--o{ TRANSACTIONS : "performs"
    TRANSACTIONS ||--o{ INVESTORS : "by"
    SHARES {
        string symbol PK
        string name
        float price
    }
    TRANSACTIONS {
        int transID PK
        string symbol FK
        int investorID FK
        int quantity
        datetime date
    }
    INVESTORS {
        int investorID PK
        string name
        string brokerage
    }

B. NoSQL Databases

• Non-relational; stores data in key-value pairs, documents (JSON), graphs, or wide-column formats.
• Scalability: Horizontally scalable (adds servers easily).
• Use Cases: Social media (Facebook’s user profiles), IoT (sensor data), real-time analytics.
• Examples:
  • Document: MongoDB (stores JSON-like documents; used by Pathao for rider/driver data).
  • Key-Value: Redis (caches eSewa transaction IDs).
  • Graph: Neo4j (recommends friends on social media).

{
  "_id": ObjectId("507f1f77bcf86cd799439011"),
  "riderID": "RID_001",
  "name": "Anil Sharma",
  "vehicle": "Suzuki",
  "lastLocation": {
    "lat": 27.7172,
    "lng": 85.3240
  },
  "ratings": [4.8, 4.9, 5.0]
}

C. Cloud Databases

• Hosted on cloud platforms (AWS, Azure, Google Cloud).
• Advantages:
  • Scalability: Auto-scales with demand (e.g., Ncell’s customer database during peak call volumes).
  • Remote Access: Accessible via the internet (e.g., Khalti’s payment processing).
  • Backup/Disaster Recovery: Automated backups.
• Examples:
  • AWS RDS: Managed MySQL/PostgreSQL for banks.
  • Firebase: Real-time NoSQL for chat apps (e.g., WhatsApp’s message storage).

Mermaid Diagram: Cloud Database Architecture

flowchart TD
    A["Client (Mobile/Desktop)"] -->|"HTTP Request"| B["Cloud Load Balancer"]
    B --> C["AWS RDS (PostgreSQL)"]
    C -->|"Query"| D["Database Cluster: Primary/Replica"]
    D -->|"Result"| C
    C -->|"Response"| B
    B --> A
    D --> E["S3 Storage: Backups & Logs"]
    E -->|"Monitored by"| F["CloudWatch: Alerts"]

3. Database Management Systems (DBMS)

A DBMS is software that manages databases, including:

• Data Definition Language (DDL): Creates/tables (e.g., CREATE TABLE Customers).
• Data Manipulation Language (DML): Inserts/updates data (e.g., INSERT INTO Orders).
• Data Control Language (DCL): Grants permissions (e.g., GRANT SELECT ON Orders TO analyst).
• Transaction Management: Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).

Example DBMS Tools:

4. Database Design Principles

A. Normalization

Reduces redundancy by organizing data into tables.

• 1NF (First Normal Form): No repeating groups (e.g., split a "products" column with multiple items into a separate table).
• 2NF: Remove partial dependencies (e.g., ensure all non-key attributes depend on the full primary key).
• 3NF: Remove transitive dependencies (e.g., customer address should not depend on customer name).

Example: Normalizing an Order Table Before (Redundant):

Orders:
| orderID | custName | custEmail | items (repeating group) |
|---------|----------|-----------|-------------------------|
| 1001    | Alice    | alice@x.com | [Item1, Item2]           |

After (1NF):

Orders (orderID, custID, orderDate)
Customers (custID, name, email)
OrderItems (orderID, itemID, quantity)

B. Denormalization

Purposefully reintroduces redundancy to improve query performance (e.g., combining tables for faster reports).

• Trade-off: Slower writes, higher storage.
• Example: A star schema in data warehouses for business intelligence.

Mermaid Diagram: Normalized vs. Denormalized Schema

erDiagram
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    ORDERS {
        int orderID PK
        string customerID FK
        datetime orderDate
    }
    ORDER_ITEMS {
        int itemID PK
        int orderID FK
        string productName
        float price
    }
    CUSTOMERS {
        int customerID PK
        string name
        string address
    }

    DENORMALIZED_ORDERS {
        int orderID PK
        string customerName
        string customerAddress
        string item1
        float price1
        string item2
        float price2
    }

5. Database Applications in Nepal

A. Banking and Finance (NEPSE, Banks)

• NEPSE: Relational databases track shares, trades, and investor portfolios.
• Banks (e.g., NMB, Global IME): SQL databases manage loans, accounts, and transactions.
  • Worked Example: Loan Interest Calculation A bank uses a query like this to compute monthly payments:

    SELECT customerID, principal, rate, term,
           (principal * rate/12) AS monthlyPayment
    FROM Loans
    WHERE status = 'Approved';
    

    Here, rate is the annual interest (e.g., 8%), divided by 12 for monthly payments.

B. E-Commerce (Daraz, Sastodeal)

• NoSQL Databases: Store product catalogs (JSON documents) and user sessions.
• Real-time Analytics: Track sales trends (e.g., "Which products sell most in Kathmandu vs. Pokhara?").

C. Ride-Hailing (Pathao)

• Graph Database: Matches riders to nearest drivers using geospatial queries.
• NoSQL (MongoDB): Stores rider/driver profiles and trip histories.

D. Payment Gateways (eSewa, Khalti)

• Cloud Databases: Handle transaction logs (e.g., AWS RDS for eSewa’s 10M+ daily transactions).
• ACID Compliance: Ensures no double-charging or failed payments.

6. Decision Support Systems (DSS)

A DSS integrates databases with analytical tools to support business decisions.

• Components:
  1. Database: Stores historical data (e.g., NEPSE’s stock prices).
  2. Modeling Tools: Predicts trends (e.g., "Will NEPSE close above 2000 in 2024?").
  3. User Interface: Dashboards (e.g., Excel Power Pivot for banks).

Example: NEPSE’s DSS

• Data Sources: Daily stock prices, trading volumes, macroeconomic indicators.
• Analysis: Time-series forecasting to recommend buy/sell signals.
• Output: A dashboard showing:
  • Moving averages (7-day, 30-day).
  • RSI (Relative Strength Index) for overbought/oversold stocks.

Mermaid Diagram: DSS Architecture for NEPSE

In the Real World

1. eSewa’s Transaction Database (NoSQL + Cloud)

   • Idea: Uses MongoDB to store user accounts, transaction logs, and merchant partnerships in JSON format.
   • Why? Handles 1M+ transactions/day with low latency. Cloud-based for scalability during festivals (e.g., Dashain).
   • Real Impact: Enables instant payouts to merchants and fraud detection via real-time queries.

2. Daraz’s Inventory Management (Relational + NoSQL Hybrid)

   • Idea: PostgreSQL tracks product stock levels, while Redis caches frequently accessed items (e.g., "Nike Air Max" during sales).
   • Why? PostgreSQL ensures data integrity; Redis speeds up "Is this item in stock?" checks.
   • Real Impact: Reduces "out-of-stock" errors by 40% during Prime Day.

3. NEPSE’s Stock Market Analytics (DSS + Relational DB)

   • Idea: A DSS queries a MySQL database of historical trades to generate reports like:

     SELECT symbol, AVG(close) AS avgClose
     FROM DailyPrices
     WHERE date BETWEEN '2023-01-01' AND '2023-12-31'
     GROUP BY symbol
     ORDER BY avgClose DESC
     LIMIT 10;
     

   • Why? Helps investors spot high-performing stocks (e.g., "Ncell’s stock averaged 250 in 2023").
   • Real Impact: Traders use these insights to time purchases/sales.

Exam Tip

1. Define Clearly: Always start with precise definitions (e.g., "A database is..."). Past exams deduct marks for vague answers.
2. Compare Types: For questions like "Differentiate RDBMS and NoSQL," use a table (as shown above) to highlight pros/cons.
3. Link to Nepal: Cite real Nepali examples (e.g., "NEPSE uses SQL for stock tracking") to score higher.
4. SQL Queries: If asked to explain a query, write the code and trace its output (e.g., show how the Daraz query filters shipped orders).
5. Normalization: For design questions, show before/after schemas (like the Pathao example) to prove your logic.
6. DSS Focus: For Decision Support Systems, emphasize the 3 components (database, modeling, UI) and a worked example (e.g., NEPSE’s stock prediction).

Common Pitfalls to Avoid:

• Calling NoSQL "just for big data" (it’s also used for flexibility, e.g., Pathao’s rider data).
• Forgetting ACID properties in DBMS discussions.
• Mixing up denormalization (for performance) with normalization (for integrity).