Database Design & Data Modeling: ER Diagrams, DFDs, and SDLC

TAKEAWAYS:

• ER diagrams model real-world entities (e.g., students, orders) and their relationships (e.g., "places" for a course) using rectangles, ovals, and diamonds.
• DFDs break down systems into processes (circles), data flows (arrows), and stores (open rectangles) to visualize how data moves (e.g., a Pathao ride’s payment flow).
• Normalization eliminates redundancy in tables (e.g., splitting a "Student" table with repeated course data into separate Student and Enrollment tables).
• SDLC phases (planning → analysis → design → implementation → maintenance) guide building AI-powered systems like Ncell’s customer service chatbot.
• Interfaces and dialogues must follow usability rules (e.g., Khalti’s OTP screen limits input to 6 digits and shows a timer).
• Logical vs. physical design: Logical design (e.g., ER diagrams) is platform-independent; physical design (e.g., SQL tables) depends on DBMS like MySQL.

Core Concepts: What Is Data Modeling?

Data modeling is the process of creating a visual representation of data structures (entities, attributes, relationships) and processes in a system. It bridges the gap between real-world requirements and database implementation. For AI applications (e.g., recommendation systems in Daraz), it ensures efficient data storage and retrieval.

1. Types of Data Models

Step 1: Conceptual Data Modeling with ER Diagrams

ER diagrams use three symbols to model data:

• Rectangle: Entity (e.g., Customer, Order).
• Oval: Attribute (e.g., customer_id, order_date).
• Diamond: Relationship (e.g., "places" between Customer and Order).

Worked Example: Daraz Order System

Scenario: Model a simplified Daraz system where customers place orders for products.

1. Identify Entities:
   • Customer (attributes: customer_id, name, email)
   • Product (attributes: product_id, name, price)
   • Order (attributes: order_id, order_date, status)
2. Define Relationships:
   • Customer places Order (1:N: one customer can place many orders).
   • Order contains Product (M:N: one order can have multiple products, and one product can be in multiple orders).
3. Add Cardinality:
   • Use crow’s foot notation:

     Customer (1) —— places (N) —— Order (1) —— contains (M:N) —— Product (N)
     

erDiagram
    Customer ||--o{ Order : places
    Order ||--o{ Product : contains
    Customer {
        string customer_id PK
        string name
        string email
    }
    Product {
        string product_id PK
        string name
        float price
    }
    Order {
        string order_id PK
        date order_date
        string status
    }

Key Rules for ER Diagrams:

• Primary Key (PK): Unique identifier (e.g., customer_id).
• Foreign Key (FK): Links to another table’s PK (e.g., order_id in a Payment table).
• Weak Entities: Depend on another entity (e.g., Order_Detail depends on Order).

Step 2: Logical Database Design

Convert ER diagrams into tables with constraints. For the Daraz example:

1. Normalize Tables:
   • Order_Detail (resolves M:N between Order and Product):

     CREATE TABLE Order_Detail (
         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)
     );
     

2. Apply Normal Forms:
   • 1NF: No repeating groups (e.g., Product list in Order → split into Order_Detail).
   • 2NF: Remove partial dependencies (e.g., price in Order_Detail → move to Product).
   • 3NF: Remove transitive dependencies (e.g., customer_city derived from customer_id → store separately).

Why Normalization Matters:

• Reduces redundancy: A product’s price isn’t duplicated in every order.
• Improves integrity: Foreign keys enforce relationships (e.g., no orphaned orders).

Step 3: Data Flow Diagrams (DFDs)

DFDs visualize how data moves in a system. Used in AI-driven workflows like:

• Pathao’s ride booking: Passenger → Driver → Payment → Ride Confirmation.
• Ncell’s billing: Customer → Usage Data → Bill Generation → Payment.

Worked Example: eSewa Transaction System (Level 0 DFD)

Context Diagram:

[Customer] → [eSewa System] → [Bank/NTC]

Level 1 DFD:

1. Processes:
   • P1: Authenticate User (checks OTP).
   • P2: Select Service (e.g., bill payment).
   • P3: Process Payment (deducts amount).
   • P4: Confirm Transaction.
2. Data Stores:
   • DS1: User Accounts.
   • DS2: Transaction History.
3. Data Flows:
   • User Credentials → P1 → Authentication Result.
   • Selected Service → P2 → Service Details.

flowchart LR
    A["Customer"] -->|"User Credentials"| B("P1: Authenticate")
    B -->|"Authentication Result"| C("P2: Select Service")
    C -->|"Service Details"| D("P3: Process Payment")
    D -->|"Payment Request"| E["Bank/NTC"]
    E -->|"Confirmation"| D
    D -->|"Transaction Data"| F["DS1: User Accounts"]
    D -->|"Transaction Data"| G["DS2: Transaction History"]
    G -->|"History"| A

Rules for Drawing DFDs:

• Level 0: Single process (context diagram).
• Level 1: Break into sub-processes (max 6–7 per diagram).
• Data Stores: Represent persistent data (e.g., databases, files).
• External Entities: Sources/sinks of data (e.g., Customer, Bank).

Step 4: Systems Development Life Cycle (SDLC)

SDLC is a structured approach to designing AI-integrated systems (e.g., NEPSE’s stock analysis tool). Phases:

Modern Approaches:

• Agile: Iterative development (e.g., Daraz’s continuous feature updates).
• Prototyping: Build a mockup first (e.g., Khalti’s OTP screen prototype).
• CASE Tools: Tools like Lucidchart or ERwin automate DFD/ER diagram creation.

Step 5: Designing Interfaces and Dialogues

Interfaces must be intuitive and efficient. Key principles:

1. Consistency: Buttons in the same place (e.g., "Pay Now" in Khalti).
2. Feedback: Confirmation messages (e.g., "Transaction successful!" in eSewa).
3. Minimal Input: Reduce steps (e.g., Pathao’s one-tap ride request).
4. Error Handling: Clear messages (e.g., "Invalid OTP. Retry").

Worked Example: Ncell’s Top-Up Dialogue

1. Screen 1: Enter phone number (input field + "Next" button).
2. Screen 2: Select top-up amount (radio buttons: ₹100, ₹500, ₹1000).
3. Screen 3: Confirm via OTP (6-digit input + timer).
4. Screen 4: Success message + option to retry.

flowchart TD
    A["Enter Phone"] --> B["Select Amount"]
    B --> C["Enter OTP"]
    C -->|"Valid"| D["Success"]
    C -->|"Invalid"| C
    D --> E["Home Screen"]

Common Dialogue Patterns:

• Wizards: Step-by-step (e.g., Daraz checkout).
• Menus: Hierarchical (e.g., eSewa’s service selection).
• Forms: Data entry (e.g., NTC’s complaint form).

In the Real World

1. eSewa’s Transaction System:

   • ER Diagram: Models User, Transaction, and Service_Provider entities with relationships like "initiates" and "processes".
   • DFD: Shows data flow from Customer → eSewa Server → Bank → Confirmation.
   • Normalization: Separates User and Transaction tables to avoid redundancy (e.g., user details aren’t repeated per transaction).

2. Pathao’s Ride Booking:

   • DFD Level 1:
     • Customer → Request Ride (P1) → Match Driver (P2) → Process Payment (P3) → Confirm Ride (P4).
   • Data Store: Driver Availability (updated in real-time via AI).
   • Interface: One-tap ride request minimizes user effort.

3. NEPSE’s Stock Alerts:

   • ER Diagram: Links User, Stock, and Alert with a many-to-many relationship (users can subscribe to multiple stocks).
   • SDLC: Agile approach for rapid updates (e.g., adding new stock symbols).

Worked Example: NTC’s Customer Complaint System

Scenario: Design a database for NTC’s complaint tracking.

1. ER Diagram:
   • Entities: Customer, Complaint, Technician, Service_Request.
   • Relationships:
     • Customer submits Complaint (1:N).
     • Complaint assigned to Technician (1:1).
2. Normalized Tables:

   CREATE TABLE Customer (
       customer_id INT PRIMARY KEY,
       name VARCHAR(100),
       contact VARCHAR(15)
   );
   
   CREATE TABLE Complaint (
       complaint_id INT PRIMARY KEY,
       customer_id INT,
       issue_date DATE,
       status VARCHAR(20),
       FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
   );
   
   CREATE TABLE Technician (
       technician_id INT PRIMARY KEY,
       name VARCHAR(100),
       specialization VARCHAR(50)
   );
   
   CREATE TABLE Service_Request (
       request_id INT PRIMARY KEY,
       complaint_id INT,
       technician_id INT,
       resolution_date DATE,
       FOREIGN KEY (complaint_id) REFERENCES Complaint(complaint_id),
       FOREIGN KEY (technician_id) REFERENCES Technician(technician_id)
   );
   

3. DFD (Level 1):
   • P1: Customer submits complaint → Complaint Database.
   • P2: Dispatcher assigns technician → Service_Request.
   • P3: Technician updates status → Complaint Database.

Exam Tip

1. ER Diagrams:

   • Always show primary keys (underlined) and foreign keys.
   • Use crow’s foot notation for cardinality (e.g., 1:N between Order and Order_Detail).
   • Common Mistakes:
     • Forgetting to mark weak entities (e.g., Order_Detail depends on Order).
     • Incorrect cardinality (e.g., drawing 1:1 instead of M:N).

2. DFDs:

   • Level 0: Only one process (the entire system).
   • Level 1: Break into 3–6 sub-processes (e.g., eSewa: Authenticate, Select Service, Process Payment).
   • Label arrows clearly (e.g., "User Credentials" not just "Data").

3. SDLC:

   • Compare waterfall (sequential) vs. Agile (iterative).
   • For case studies (e.g., Daraz), mention AI integration (e.g., recommendation systems use data from User and Product tables).

4. Normalization:

   • 2NF: No partial dependencies (e.g., product_price in Order_Detail → move to Product).
   • 3NF: No transitive dependencies (e.g., customer_city derived from customer_id → store separately).

5. Interfaces:

   • Describe dialogue flow (e.g., "Screen 1: Enter OTP → Screen 2: Confirm").
   • Highlight usability rules (e.g., "Limits input to 6 digits for OTP").

Pro Tip: For DFD questions, assume real-world entities (e.g., "Customer" instead of vague terms like "User"). Use Nepali examples (e.g., eSewa, Daraz) to make answers relatable. Always draw diagrams—examiners reward visual clarity!