Databases & Info Mgmt: DBMS, ERD, SQL, Data Warehousing

Core Concepts: What is a Database?

A database is an organized collection of structured data stored electronically, designed to be easily accessed, managed, and updated. Unlike spreadsheets or flat files, databases use a Database Management System (DBMS) to enforce rules, ensure consistency, and optimize performance.

Why Databases?

• Efficiency: Faster data retrieval than manual filing.
• Integrity: Prevents errors (e.g., duplicate entries).
• Security: Controls access via user roles (e.g., admin vs. employee).
• Scalability: Handles growth (e.g., Daraz’s millions of orders).

Types of Databases

mindmap
  root((Database Types))
    Relational (SQL)
      MySQL
      PostgreSQL
      Oracle
    NoSQL
      MongoDB (Document)
      Cassandra (Column)
      Redis (Key-Value)
    Specialized
      Graph (Neo4j)
      Time-Series (InfluxDB)

Key Idea: Relational databases (SQL) use tables with rows/columns, while NoSQL databases (e.g., MongoDB) store data in flexible formats like JSON.

Entity-Relationship (ER) Modeling

ER diagrams visualize how data entities (e.g., Customer, Order) relate to each other. This is the blueprint before building a database.

ER Diagram Components

erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--|{ ORDER_ITEM : contains
  PRODUCT }|--|| ORDER_ITEM : includes

• Entities: Real-world objects (e.g., Student, Course).
• Attributes: Properties (e.g., Student.name, Course.credit).
• Relationships: How entities interact (e.g., Student enrolls in Course).

Worked Example: Ncell’s Customer Database

• Entities: Customer, Subscription, Payment.
• Relationship: A Customer can have multiple Subscriptions (1:N).
• Attribute: Subscription.start_date tracks when a plan begins.

SQL: The Language of Databases

SQL (Structured Query Language) lets you create, read, update, and delete data. Key commands:

Real-World Trace: Khalti’s Transaction Log

-- Find all failed transactions in May 2024
SELECT transaction_id, amount, status
FROM Transaction
WHERE status = 'failed' AND date BETWEEN '2024-05-01' AND '2024-05-31';

Data Warehousing and Business Intelligence

A data warehouse stores historical data from multiple sources (e.g., sales, inventory) to support analytics and decision-making.

How It Works

flowchart TD
  A["Operational DBs"] -->|"Extract"| B["ETL Process"]
  B --> C["Data Warehouse"]
  C --> D["OLAP Cubes"]
  D --> E["Dashboards/Reports"]

• ETL: Extract, Transform, Load (e.g., Daraz’s daily sales → warehouse).
• OLAP: Online Analytical Processing (e.g., "Which product sold most in Kathmandu?").

Example: NTC’s Network Performance Dashboard

• Source: Call logs, tower data.
• Analysis: Identify peak usage hours to optimize bandwidth.

Database Normalization

Normalization reduces data redundancy and improves efficiency by organizing tables logically.

Visualization:

mindmap
  root((Normalization))
    1NF
      Atomic values only
    2NF
      Remove partial dependencies
    3NF
      Remove transitive dependencies

In the Real World

1. eSewa’s Payment System

   • Database: Stores transactions, user accounts, and merchant details.
   • SQL Use: JOIN between User and Transaction tables to track payments.
   • ER Model: User (1) → Transaction (N) → Merchant (1).

2. Daraz’s Inventory Management

   • Data Warehouse: Aggregates sales, stock levels, and supplier data.
   • Analytics: Predicts demand using historical trends (e.g., Diwali season spikes).

3. Nabil Bank’s Loan Processing

   • Normalized DB: Separates Customer, Loan, and Payment tables to avoid redundancy.
   • SQL Query: SELECT * FROM Loan WHERE status = 'approved' AND interest_rate < 10%.

Exam Tip

• Diagrams: Always draw an ER diagram for case studies (e.g., "Design a database for a hospital").
• SQL Practice: Memorize JOIN, GROUP BY, and HAVING for analytical questions.
• Normalization: Expect questions like, "Convert this unnormalized table to 3NF."
• Data Warehouse: Link it to business intelligence (e.g., "How would NTC use a data warehouse?").

Case Study: Himalayan Java’s Coffee Shop DB

Problem: Track orders, customers, and inventory. Solution:

erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--|{ ORDER_ITEM : contains
  PRODUCT }|--|| ORDER_ITEM : includes
  SUPPLIER }|--|| PRODUCT : supplies

SQL Query for Daily Sales:

SELECT Product.name, SUM(Order_Item.quantity)
FROM Order_Item
JOIN Product ON Order_Item.product_id = Product.id
WHERE Order.date = '2024-05-20'
GROUP BY Product.name;

Output:

Key Takeaways

• Databases organize data for efficiency (e.g., Ncell’s subscriber records).
• ER diagrams map real-world relationships (e.g., Daraz’s orders and products).
• SQL is the tool to extract insights (e.g., Khalti’s fraud detection queries).
• Normalization prevents errors (e.g., duplicate customer data in banks).
• Data warehouses power decisions (e.g., NTC’s network planning).