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

TAKEAWAYS:

• Understand the three-tier architecture of DBMS (user interface, application logic, and database engine) and how it ensures data integrity and security.
• Learn to design an ER diagram (entities, attributes, relationships) and convert it into a relational schema using normalization rules (1NF to 3NF).
• Master SQL commands (SELECT, INSERT, UPDATE, DELETE, JOIN) with real-world examples like querying customer orders in a retail system.
• Compare file-based systems vs. DBMS and relational vs. NoSQL databases using structured tables to highlight trade-offs.
• Apply data warehousing concepts (ETL, OLAP, star schema) to solve business intelligence problems like sales trend analysis for Daraz or Nabil Bank.
• Analyze ethical and security challenges in data management (e.g., GDPR compliance, data breaches in eSewa or Khalti).

1. What is a Database Management System (DBMS)?

A DBMS is software that manages databases by storing, retrieving, and manipulating data efficiently. It acts as an intermediary between users and the database, ensuring data integrity, security, and concurrency control.

Key Components of a DBMS

Advantages of DBMS Over File-Based Systems

2. Entity-Relationship (ER) Modeling

ER modeling is a visual tool to design databases by identifying:

• Entities (objects like Customer, Product),
• Attributes (properties like customer_id, product_name),
• Relationships (1:1, 1:M, M:N).

Steps to Create an ER Diagram

1. Identify Entities: List all objects (e.g., Order, Supplier).
2. Define Attributes: Assign properties (e.g., Order has order_id, order_date).
3. Determine Relationships: Use crow’s foot notation for cardinality.
4. Convert to Relational Schema: Apply normalization.

Example: ER Diagram for a Retail Store (Daraz-like System)

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ PRODUCT : contains
    ORDER ||--|| SUPPLIER : sourced_from
    CUSTOMER {
        int customer_id PK
        string name
        string email
    }
    ORDER {
        int order_id PK
        date order_date
        int customer_id FK
    }
    PRODUCT {
        int product_id PK
        string product_name
        float price
    }
    SUPPLIER {
        int supplier_id PK
        string supplier_name
    }

3. Normalization: Organizing Data Efficiently

Normalization reduces data redundancy and anomalies (update, insert, delete) by structuring tables into normal forms.

Normalization Rules (1NF to 3NF)

Worked Example: Normalizing a Student-Course Database

Unnormalized Table (Before 1NF):

1NF (Atomic Values):

2NF (Remove Partial Dependencies): Split into two tables:

• Students (student_id, name)
• Enrollments (enrollment_id, student_id, course_id)

3NF (Remove Transitive Dependencies): Ensure no non-key attribute depends on another non-key attribute (e.g., course_name should not be in the Enrollments table but in a separate Courses table).

4. SQL: Querying Databases

SQL (Structured Query Language) is used to retrieve, insert, update, and delete data.

Basic SQL Commands

-- Create a table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

-- Insert data
INSERT INTO Customers (customer_id, name, email)
VALUES (1, 'Ram', 'ram@example.com');

-- Select data (with JOIN)
SELECT Customers.name, Orders.order_date
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
WHERE Orders.order_date > '2023-01-01';

-- Update data
UPDATE Products
SET price = price * 1.1  -- 10% price increase (like Daraz's seasonal sale)
WHERE category = 'Electronics';

-- Delete data
DELETE FROM Orders
WHERE order_date < '2023-01-01';

Worked Example: Querying Nabil Bank’s Loan Data

Problem: Find all customers who took a home loan in 2023 with an interest rate > 8%.

SELECT customer_name, loan_amount, interest_rate
FROM Loans
WHERE loan_type = 'Home Loan'
  AND YEAR(loan_date) = 2023
  AND interest_rate > 8
ORDER BY interest_rate DESC;

5. Data Warehousing and Business Intelligence

A data warehouse stores historical, integrated data for analytical processing (OLAP).

Key Concepts

Example: Data Warehouse for a Retailer (Like BigMart)

erDiagram
    Fact_Sales ||--o{ Fact_Transaction : contains
    Fact_Transaction }|--|| Dimension_Date : recorded_on
    Fact_Transaction }|--|| Dimension_Product : sells
    Fact_Transaction }|--|| Dimension_Customer : purchased_by

    Dimension_Date {
        date_id PK
        year
        month
        day
        day_of_week
    }
    Dimension_Product {
        product_id PK
        product_name
        category
        price
    }
    Dimension_Customer {
        customer_id PK
        customer_name
        region
        loyalty_status
    }
    Fact_Sales {
        sale_id PK
        transaction_id FK
        product_id FK
        customer_id FK
        date_id FK
        quantity
        amount
    }

6. NoSQL Databases: When Relational Isn’t Enough

NoSQL databases (e.g., MongoDB, Cassandra) are used for unstructured data or high scalability.

Comparison: Relational vs. NoSQL

Real-World Example:

• WhatsApp uses Erlang + NoSQL to handle billions of messages in real-time.
• Nepal’s eSewa uses a relational database for secure financial transactions.

7. Ethical and Security Challenges in Data Management

Key Issues

1. Data Privacy: Compliance with laws like GDPR (EU) or PDPA (Nepal).
   • Example: Khalti must encrypt customer transaction data.
2. Data Breaches: Unauthorized access (e.g., Daraz’s 2021 breach).
3. Bias in Algorithms: AI-driven decisions (e.g., loan approvals) may discriminate.
4. Intellectual Property: Protecting proprietary data (e.g., Coca-Cola’s recipe).

Best Practices

• Encryption: Use AES-256 for sensitive data (like Nabil Bank’s customer records).
• Access Control: Role-based permissions (e.g., only managers can delete orders in Daraz).
• Audit Logs: Track who accessed or modified data.

In the Real World

1. eSewa (Digital Payments)

   • Idea Used: Relational Database + Transactions
   • How: eSewa stores user transactions in a normalized database (1NF-3NF) to ensure ACID compliance (Atomicity, Consistency, Isolation, Durability). SQL queries validate payments in real-time.

2. Daraz (E-Commerce)

   • Idea Used: Data Warehousing + OLAP
   • How: Daraz’s ETL pipelines extract sales data from multiple warehouses, transform it into a star schema, and run OLAP queries to predict demand (e.g., "Which products sell best in Pokhara during Dashain?").

3. NTC (Telecom)

   • Idea Used: NoSQL for Real-Time Analytics
   • How: NTC uses Cassandra (NoSQL) to handle millions of call records per second, enabling real-time network optimization and fraud detection.

4. Nabil Bank (Loan Management)

   • Idea Used: SQL + Normalization
   • How: Nabil Bank’s loan system uses 3NF tables to store customer data, loan details, and interest calculations. A query like:

     SELECT customer_name, loan_amount, (loan_amount * interest_rate/100 * months/12) AS EMI
     FROM Loans
     WHERE loan_type = 'Home Loan';
     

     helps customers understand their Equated Monthly Installments (EMI).

Exam Tip

1. Diagrams Are Worth Marks:

   • Always draw ER diagrams for case studies (e.g., "Design a database for a hospital management system").
   • Sketch star schemas for data warehousing questions.

2. SQL Queries Are Critical:

   • Practice writing JOINs, subqueries, and aggregations (e.g., "Find the top 5 customers by total spending").
   • Use real-world examples (e.g., "Write a query to find all orders placed after 6 PM for Pathao’s delivery system").

3. Normalization Shortcuts:

   • For exam questions, stop at 3NF unless asked for BCNF.
   • Memorize common anomalies (e.g., update anomaly in unnormalized tables).

4. Case Study Approach:

   • If given a scenario (e.g., "Nepal’s traffic management system"), identify:
     • Entities (Vehicles, Drivers, Routes),
     • Relationships (1:M between Drivers and Vehicles),
     • SQL Queries (e.g., "Find congested routes during peak hours").

5. Ethical Questions:

   • Expect short-answer questions on:
     • GDPR vs. PDPA,
     • Risks of data silos (e.g., NTC and Ncell not sharing customer data),
     • AI bias in hiring algorithms.

Final Note: This unit is 50% theory (DBMS, ER modeling, normalization) and 50% applied (SQL, data warehousing, ethics). Focus on visuals (ERDs, star schemas) and hands-on SQL—they fetch the most marks!