Process & Data Modeling: DFDs, ER Diagrams, Normalization

TAKEAWAYS:

• Process Modeling uses Data Flow Diagrams (DFDs) to show how data moves through a system, starting with a context diagram (Level 0) and expanding to Level 1/2 diagrams with processes, data stores, and external entities.
• Data Modeling builds a logical schema (ER diagrams) before converting it to tables (normalization to 3NF) to eliminate redundancy and ensure data integrity.
• ER Diagrams map entities (e.g., Customer, Order) and their relationships (1:M, M:N) with attributes, which are later transformed into relations (tables) using foreign keys.
• Normalization (1NF → 3NF) removes anomalies by decomposing tables into smaller, dependent-only tables (e.g., splitting Order_Details from Orders).
• Real-world ties: eSewa’s transaction flows (DFD), Daraz’s inventory management (ER → relations), and Ncell’s billing system (normalized tables) all use these models.
• Exam focus: Draw context → Level 1 DFDs (e.g., Bakery Café, Student Enrollment), transform ER → relations, and explain normalization steps with examples.

1. Process Modeling: Data Flow Diagrams (DFDs)

DFDs visualize how data moves through a system, breaking it into processes, data flows, data stores, and external entities. Used in requirements analysis to clarify system boundaries and interactions.

Key Components

graph LR
    A["External Entity"] -->|"Data Flow"| B["Process"]
    B -->|"Data Flow"| C["Data Store"]
    B -->|"Data Flow"| D["External Entity"]
    C -->|"Data Flow"| B

• External Entity: Source/sink of data (e.g., Customer, Supplier).
• Process: Transformation of data (e.g., Place Order, Calculate Bill).
• Data Store: Permanent storage (e.g., Customer Database, Inventory).
• Data Flow: Movement of data between components (labeled with data name).

Types of DFDs

Rules for Drawing DFDs

1. Name processes with strong verbs: Process Order, not Order Process.
2. Number processes sequentially: P1, P2 (avoid gaps like P1, P3).
3. Data flows must have labels: Customer Details → P1: Verify Customer.
4. Data stores are passive: Data flows in/out but don’t "process" data.
5. Balance inputs/outputs: Every input to a process must have a corresponding output.

Worked Example: Bakery Café Jawlakhel (Level 0 → Level 1)

Context Diagram:

graph TD
    A["Customer"] -->|"Order"| B["Bakery Café System"]
    B -->|"Bill"| A
    C["Supplier"] -->|"Ingredients"| B
    B -->|"Payment"| C

Level 0 DFD:

graph TD
    A["Customer"] -->|"Place Order"| P1["Order Processing"]
    P1 -->|"Generate Bill"| P2["Billing"]
    P2 -->|"Bill"| A
    P3["Inventory"] -->|"Check Stock"| P1
    P1 -->|"Order Ingredients"| P4["Supplier Management"]
    P4 -->|"Update Inventory"| P3

Level 1 DFD (Decompose Order Processing):

graph TD
    A["Customer"] -->|"Order Details"| P11["Record Order"]
    P11 -->|"Order ID"| P12["Check Availability"]
    P12 -->|"Stock Status"| P13["Prepare Order"]
    P13 -->|"Order"| P2["Billing"]

Real-World Tie: eSewa’s Transaction Flow

• Context Diagram: User → eSewa System → Bank/NTC.
• Level 0:
  • P1: User Login (authentication).
  • P2: Transaction Processing (debit/credit).
  • P3: Notification (SMS/email).
• Level 1 (P2 Decomposition):
  • P21: Validate Amount.
  • P22: Deduct from User Account.
  • P23: Credit Recipient.
  • P24: Update Ledger.

2. Data Modeling: From Conceptual to Physical

Data modeling designs the structure of data before implementation. Three phases:

1. Conceptual Model: High-level entities and relationships (ER diagrams).
2. Logical Model: Refined ER diagram with attributes and constraints.
3. Physical Model: Database tables (normalized to 3NF).

Entity-Relationship (ER) Diagrams

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_DETAILS : contains
    ORDER ||--|{ PAYMENT : has
    CUSTOMER {
        int customer_id PK
        string name
        string contact
    }
    ORDER {
        int order_id PK
        date order_date
        int customer_id FK
    }
    ORDER_DETAILS {
        int detail_id PK
        int order_id FK
        string product
        int quantity
    }

• Entities: Customer, Order, Product.
• Relationships:
  • Customer places Order (1:M).
  • Order contains Order_Details (1:M).
• Attributes: Properties of entities (e.g., customer_id, quantity).

Transforming ER to Relations (Tables)

1. Entities become tables:
   • Customer(customer_id, name, contact).
2. Relationships:
   • 1:M: Add FK to the "many" side (e.g., Order(customer_id)).
   • M:N: Create a junction table (e.g., Order_Details).
3. Attributes:
   • Place on the entity they describe (e.g., quantity in Order_Details).

Example: Bank Deposit Policy

• ER Diagram:

  erDiagram
      CUSTOMER ||--o{ DEPOSIT : makes
      DEPOSIT {
          int deposit_id PK
          date deposit_date
          decimal amount
          int customer_id FK
          int duration_years
      }

• Relation:

  CREATE TABLE Deposit (
      deposit_id INT PRIMARY KEY,
      customer_id INT REFERENCES Customer(customer_id),
      amount DECIMAL(10,2),
      duration_years INT,
      interest_rate DECIMAL(5,2) DEFAULT 15.0, -- For deposits ≥50k for 5 years
      deposit_date DATE
  );
  

Real-World Tie: Daraz’s Inventory Management

• ER Diagram:
  • Product (product_id, name, price, stock).
  • Order (order_id, customer_id, order_date).
  • Order_Details (order_id, product_id, quantity) — M:N junction.
• Normalized Tables:

  CREATE TABLE Product (product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2), stock INT);
  CREATE TABLE Order (order_id INT PRIMARY KEY, customer_id INT, order_date DATE);
  CREATE TABLE Order_Details (
      order_id INT,
      product_id INT,
      quantity INT,
      PRIMARY KEY (order_id, product_id),
      FOREIGN KEY (order_id) REFERENCES Order(order_id),
      FOREIGN KEY (product_id) REFERENCES Product(product_id)
  );
  

3. Normalization: Eliminating Redundancy

Normalization organizes data to minimize redundancy and maximize integrity. Steps:

Worked Example: Ncell Billing System

Unnormalized Table:

1NF: Split plans and usage into separate rows:

Customer_Plans (customer_id, plan_type, usage_limit)

2NF: Add Plan table to remove partial dependency:

Plan (plan_id, plan_type, base_price)
Customer_Plans (customer_id, plan_id, usage_limit)

3NF: Remove transitive dependency (e.g., base_price depends on plan_id, not customer_id).

4. Comparing Process and Data Modeling

5. Common Pitfalls and Best Practices

DFD Mistakes

• Unbalanced flows: Inputs ≠ outputs in a process.
  • ❌ P1 receives Customer Details but doesn’t output anything.
  • ✅ Add Order Confirmation as output.
• Overly detailed Level 0: Should have 3–7 processes max.
• Missing data stores: Temporary data (e.g., Order Queue) should be shown.

Data Modeling Mistakes

• Redundant attributes: Storing customer_name in both Customer and Order.
• Improper relationships:
  • ❌ Direct M:N between Order and Product (use junction table).
  • ✅ Order_Details as a bridge.
• Ignoring constraints: Not defining PRIMARY KEY or FOREIGN KEY.

In the Real World

1. eSewa’s Transaction System

   • Process Modeling: DFDs show how data flows from User → eSewa Server → Bank/NTC for payments.
   • Data Modeling: ER diagram links User, Transaction, and Bank entities, normalized to avoid duplicate transaction records.

2. Daraz’s Inventory Management

   • DFD: Customer → Order Processing → Inventory Update → Supplier.
   • Normalization: Order_Details table ensures stock levels update correctly without redundancy.

3. Ncell’s Billing System

   • ER Diagram: Customer (1:M) Plan (1:M) Usage tracks data consumption.
   • 3NF Tables: Separates Customer, Plan, and Usage to avoid updating the same plan details across orders.

Exam Tip

1. DFD Questions:

   • Always start with a context diagram (1 process, external entities).
   • For Level 1, decompose the main process into 3–5 sub-processes.
   • Label data flows clearly (e.g., Customer Details → P1: Verify Customer).
   • Balance inputs/outputs: If a process receives Order, it must output Order Confirmation or Rejection.

2. ER to Relations:

   • 1:M: Add FK to the "many" side (e.g., Order(customer_id)).
   • M:N: Create a junction table with composite PK.
   • Attributes: Place on the entity they describe (e.g., quantity in Order_Details).

3. Normalization:

   • 1NF: Ensure no repeating groups (e.g., split products list into rows).
   • 2NF: Remove partial dependencies (e.g., customer_name belongs in Customer, not Order).
   • 3NF: Remove transitive dependencies (e.g., discount_rate depends on product, not order_id).

4. Short-Answer Tips:

   • Define correlation: Statistical relationship between variables (e.g., library books vs. test scores).
   • Logical data model: Platform-independent ER diagram showing entities, attributes, and relationships.
   • DFD rules: 5 bullet points (e.g., "Name processes with verbs," "Balance I/O").