Databases & Info Mgmt: DBMS, Models, SQL, OLTP/OLAP, Data Warehousing

TAKEAWAYS:

• Database models (relational, hierarchical, network, NoSQL) determine how data is structured and accessed, with relational being the most widely used in business.
• SQL is the standard language for querying relational databases, and mastering SELECT, JOIN, and GROUP BY is critical for exam questions.
• OLTP vs. OLAP: Online transaction processing (e.g., bank transactions) vs. analytical processing (e.g., sales reports) require different database designs.
• Data warehousing consolidates data from multiple sources for business intelligence, while data governance ensures data quality, security, and compliance.
• Normalization (1NF to 5NF) eliminates redundancy and improves data integrity, but denormalization is sometimes used for performance.
• Real-world applications: E-commerce (Daraz), banking (Nabil Bank), and telecom (Ncell) rely on databases to manage customer data, transactions, and inventory.

1. Introduction to Databases and Database Management Systems (DBMS)

A database is an organized collection of structured data stored electronically, designed to be easily accessible, managed, and updated. A DBMS (e.g., MySQL, Oracle, PostgreSQL) is software that interacts with the database to perform tasks like storing, retrieving, and updating data.

Why Databases?

• Efficiency: Faster data retrieval than file-based systems.
• Redundancy control: Eliminates duplicate data.
• Data integrity: Ensures accuracy and consistency.
• Security: Access controls and encryption protect sensitive data.

Types of Databases

mindmap
  root((Database Models))
    Relational
      Tables
      SQL
      ACID Properties
    NoSQL
      Document
      Key-Value
      Column-Family
    Hierarchical
      Tree Structure
      Parent-Child
    Network
      Graph Structure
      CODASYL

2. Relational Database Model

The relational model (proposed by Edgar F. Codd) organizes data into tables (relations) with rows (tuples) and columns (attributes). Relationships between tables are defined using keys:

• Primary Key (PK): Uniquely identifies a record (e.g., customer_id).
• Foreign Key (FK): Links to a PK in another table (e.g., order.customer_id).
• Composite Key: Combination of columns as a PK (e.g., student_id + course_id).

Example: E-Commerce Database (Daraz)

erDiagram
    Customer ||--o{ Order : places
    Order ||--|{ OrderItem : contains
    Product }o--|| OrderItem : includes
    Customer {
        int customer_id PK
        string name
        string email
    }
    Order {
        int order_id PK
        int customer_id FK
        date order_date
    }
    OrderItem {
        int order_id FK
        int product_id FK
        int quantity
    }
    Product {
        int product_id PK
        string name
        float price
    }

Worked Example: Finding Total Sales per Customer

SELECT c.customer_id, c.name, SUM(oi.quantity * p.price) AS total_spent
FROM Customer c
JOIN Order o ON c.customer_id = o.customer_id
JOIN OrderItem oi ON o.order_id = oi.order_id
JOIN Product p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.name;

Real-World Tie-In: Daraz uses this query to generate customer lifetime value (CLV) reports for marketing.

3. Database Normalization

Normalization reduces redundancy and improves data integrity by organizing tables into normal forms (1NF to 5NF).

flowchart TD
    A["Unnormalized: Order<br/>(order_id, product1, product2, total)"] --> B["1NF<br/>(order_id, product, quantity, price)"]
    B --> C["2NF<br/>(Order: order_id, total)<br/>(OrderItem: order_id, product_id, quantity, price)"]
    C --> D["3NF<br/>(Order: order_id, customer_id, total)<br/>(OrderItem: order_id, product_id, quantity)<br/>(Product: product_id, name, price)"]

Worked Example: Normalizing a Student-Course Database Before (Violates 2NF):

After (3NF):

• Student: student_id (PK), name
• Course: course_id (PK), name
• Enrollment: student_id (FK), course_id (FK), grade

Real-World Tie-In: Nabil Bank normalizes customer and loan data to avoid redundancy in transaction records.

4. SQL: Structured Query Language

SQL is the standard language for relational databases. Key commands:

Data Query Language (DQL)

-- SELECT: Retrieve data
SELECT column1, column2
FROM table_name
WHERE condition;

Example: Find all orders placed in 2023.

SELECT order_id, order_date
FROM Order
WHERE YEAR(order_date) = 2023;

Data Manipulation Language (DML)

-- INSERT: Add data
INSERT INTO Customer (customer_id, name, email)
VALUES (101, 'John Doe', 'john@example.com');

-- UPDATE: Modify data
UPDATE Product
SET price = 1500
WHERE product_id = 501;

-- DELETE: Remove data
DELETE FROM Order
WHERE order_id = 999;

Data Definition Language (DDL)

-- CREATE: Define tables
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- ALTER: Modify structure
ALTER TABLE Order ADD COLUMN status VARCHAR(20);

-- DROP: Delete tables
DROP TABLE TempData;

Advanced Queries

-- JOIN: Combine tables
SELECT c.name, o.order_date, p.name AS product
FROM Customer c
JOIN Order o ON c.customer_id = o.customer_id
JOIN OrderItem oi ON o.order_id = oi.order_id
JOIN Product p ON oi.product_id = p.product_id;

-- GROUP BY & HAVING: Aggregate data
SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department
HAVING AVG(salary) > 50000;

-- SUBQUERY: Nested queries
SELECT name
FROM Customer
WHERE customer_id IN (
    SELECT customer_id FROM Order WHERE total > 10000
);

Exam Tip: Always practice writing SQL queries with JOIN, GROUP BY, and subqueries—these are high-weightage topics.

5. OLTP vs. OLAP

flowchart LR
    subgraph OLTP
        A["User"] --> B["Transaction<br/>(e.g., Order Placement)"]
        B --> C["Database<br/>(MySQL)"]
        C --> D["Fast Response<br/><100ms"]
    end
    subgraph OLAP
        E["Analyst"] --> F["Query<br/>(e.g., 'Sales by Region')"]
        F --> G["Data Warehouse<br/>(Snowflake)"]
        G --> H["Slow Response<br/>Minutes/Hours"]
    end

Real-World Example:

• OLTP: Nabil Bank processes loan applications in milliseconds.
• OLAP: NEPSE uses OLAP to generate stock market trend reports.

6. Data Warehousing and Business Intelligence

A data warehouse integrates data from multiple sources (e.g., ERP, CRM) for reporting and analysis. Key components:

• ETL (Extract, Transform, Load): Cleans and moves data into the warehouse.
• Data Mart: Subset of a data warehouse for a specific department (e.g., Sales).
• OLAP Tools: Cube, pivot tables, dashboards (e.g., Power BI, Tableau).

Example: NTC’s Data Warehouse

• Sources: Billing systems, customer service logs, network performance data.
• Output: Monthly reports on call drop rates, revenue trends.

mindmap
  root((Data Warehouse))
    ETL Process
      Extract
      Transform
      Load
    Components
      Data Warehouse
      Data Marts
      OLAP Server
    Tools
      SQL
      Power BI
      Tableau

Worked Example: Star Schema (Common Data Warehouse Design)

erDiagram
    Fact_Sales ||--o{ Dim_Date : "on"
    Fact_Sales ||--o{ Dim_Product : "sells"
    Fact_Sales ||--o{ Dim_Customer : "buys"
    Fact_Sales {
        int sale_id PK
        int date_id FK
        int product_id FK
        int customer_id FK
        int quantity
        float revenue
    }
    Dim_Date { int date_id PK, date date, string day_name }
    Dim_Product { int product_id PK, string name, float price }
    Dim_Customer { int customer_id PK, string name, string region }

7. Data Governance and Security

Data Governance: Policies to ensure data quality, security, and compliance (e.g., GDPR, Nepal’s Data Privacy Act).

• Data Quality: Accuracy, completeness, consistency.
• Backup and Recovery: Regular backups (e.g., daily snapshots) and disaster recovery plans.
• Access Control: Role-based access (e.g., only managers can approve loans in Nabil Bank).

Security Measures:

Real-World Example:

• Khalti encrypts all transactions to prevent fraud.
• NEPSE maintains audit logs for stock trade transparency.

In the Real World

1. Daraz (E-Commerce)

   • Uses relational databases (MySQL) for inventory and orders.
   • Employs OLTP for real-time order processing and OLAP for sales analytics.
   • Normalization ensures no duplicate product data across warehouses.

2. Nabil Bank (Financial Services)

   • SQL queries handle daily transactions (e.g., loan disbursements).
   • Data warehousing generates reports for branch managers (e.g., "Top 10 Borrowers").
   • ACID properties ensure transactions like fund transfers are atomic (all-or-nothing).

3. NTC (Telecom)

   • Hierarchical databases (legacy) manage billing and customer records.
   • ETL processes consolidate call detail records (CDRs) for monthly reports.
   • Data governance ensures compliance with Nepal’s telecom regulations.

Exam Tip

1. SQL is 30% of the exam: Practice writing queries with:
   • JOIN (INNER, LEFT, RIGHT, FULL).
   • GROUP BY + HAVING.
   • Subqueries and nested queries.
2. Normalization: Be able to convert an unnormalized table to 3NF and explain why.
3. OLTP vs. OLAP: Know when to use each (e.g., OLTP for transactions, OLAP for reports).
4. Data Warehousing: Understand star schema and ETL processes.
5. Real-World Applications: Relate concepts to Nepali companies (e.g., Daraz’s database, Nabil Bank’s SQL queries).
6. Short Answer Questions: Memorize key terms:
   • ACID properties (Atomicity, Consistency, Isolation, Durability).
   • Types of database models (relational, NoSQL, hierarchical).
   • Normal forms (1NF to 3NF rules).

Common Pitfalls:

• Forgetting to include JOIN conditions in SQL queries.
• Misapplying normalization (e.g., stopping at 2NF when 3NF is required).
• Confusing OLTP (transactions) with OLAP (analytics).

Final Note: Databases are the backbone of modern business systems. Mastering this unit will help you design efficient systems for companies like Daraz, Nabil Bank, or even startups in Nepal’s digital economy. Practice SQL daily—it’s the most directly applicable skill for exams and jobs!