Data Models & ER Modeling: Concepts, Types, ER-to-Relational Mapping

TAKEAWAYS:

• Data models define how data is structured, stored, and accessed (e.g., hierarchical trees vs. relational tables).
• The ER model uses entities, attributes, and relationships to design databases before implementation.
• Logical vs. physical independence lets database schemas change without breaking applications (e.g., NEPSE’s stock data updates).
• Mapping ER to relational requires resolving 1:1, 1:N, and M:N relationships into normalized tables (e.g., Daraz’s order-customer link).
• Constraints (e.g., NOT NULL, UNIQUE) enforce data integrity in SQL (e.g., Khalti’s transaction IDs must be unique).
• Three-schema architecture (external, conceptual, internal) isolates users from storage changes (e.g., NTC’s network upgrades).

1. What is a Data Model?

A data model is a blueprint that defines:

• How data is organized (e.g., records, trees, graphs).
• How data relationships are represented (e.g., parent-child in hierarchical models).
• How operations (queries, updates) are performed.

Types of Data Models

Data models are classified into three categories based on their structure and complexity:

Comparison Table: Hierarchical vs. Network vs. Relational Models

2. Entity-Relationship (ER) Model

The ER model is a conceptual tool to design databases before implementing them in SQL. It consists of:

• Entities: Real-world objects (e.g., Customer, Product).
• Attributes: Properties of entities (e.g., Customer.Name, Product.Price).
• Relationships: How entities interact (e.g., Customer PLACES Order).

ER Diagram Symbols

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ PRODUCT : contains
    CUSTOMER {
        int id PK
        string name
        string email
    }
    ORDER {
        int order_id PK
        date order_date
        float total_amount
    }
    PRODUCT {
        int product_id PK
        string name
        float price
    }

Key Symbols:

• Rectangle: Entity (e.g., Customer).
• Oval: Attribute (e.g., Name).
• Diamond: Relationship (e.g., PLACES).
• Crow’s Foot (||--o{): Cardinality (1:N, M:N).

Types of Relationships

3. Mapping ER Model to Relational Model

To convert an ER diagram into tables (relations), follow these rules:

Step 1: Convert Entities to Tables

• Each entity becomes a table.
• Attributes become columns.
• Primary Key (PK): Underlined attribute (e.g., Customer.ID).

Example: Convert the ER diagram above to tables:

CREATE TABLE Customer (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price FLOAT
);

Step 2: Handle Relationships

Worked Example: Daraz Order System ER Diagram:

• Customer (1:N) Order (M:N) Product. Relational Tables:

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price FLOAT
);

-- Junction table for M:N (Order → Product)
CREATE TABLE OrderItem (
    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)
);

Step 3: Resolve Weak Entities

• Weak entities (e.g., OrderItem depends on Order) get a partial PK (composite key).
• Example: OrderItem needs both order_id and product_id to uniquely identify a row.

4. Three-Level Database Architecture

To achieve data independence, databases use a three-schema architecture:

Why It Matters:

• Logical Independence: Change the conceptual schema (e.g., add ShippingAddress table) without breaking external views.
• Physical Independence: Change storage (e.g., switch from HDD to SSD) without altering logical design.

Real-World Example: NTC’s Network Database

• External Schema: Different teams see only their data (e.g., BillingView, CustomerSupportView).
• Conceptual Schema: Unified Customer, Service, Payment tables.
• Internal Schema: Data stored in optimized NoSQL (for fast lookups) and SQL (for transactions).

5. Data Constraints in SQL

Constraints enforce rules on data to maintain integrity. Common types:

Worked Example: Khalti Transaction System

CREATE TABLE Transaction (
    transaction_id VARCHAR(50) PRIMARY KEY,
    user_id INT NOT NULL,
    amount FLOAT CHECK (amount > 0),
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    UNIQUE (transaction_id)  -- No duplicate transactions
);

6. In the Real World

1. eSewa’s Payment System

   • Data Model: Relational (SQL) for transactions, with 1:N (User → Transaction) and M:N (Transaction → ServiceProvider).
   • ER → Relational: Junction table TransactionProvider links payments to services (e.g., electricity, SIM).
   • Constraints: Transaction.amount > 0, UNIQUE(transaction_id).

2. Pathao’s Ride-Matching

   • Data Model: Hybrid (relational for users, object-oriented for dynamic ride assignments).
   • ER Concept: Driver (M:N) Ride (M:N) Passenger → mapped to junction tables for real-time matching.
   • Constraint: Ride.status IN ('pending', 'accepted', 'cancelled').

3. NEPSE’s Stock Trading

   • Data Model: Relational with temporal tables (e.g., StockPrice with date as part of PK).
   • ER Example: Trader (1:N) Order (M:N) Stock → normalized into 3 tables + junction.
   • Constraint: Order.quantity >= 1, CHECK (Order.price >= Stock.current_price).

7. Exam Tip

How to Score Full Marks:

1. For ER-to-Relational Mapping:

   • Always draw the ER diagram first (even if not asked).
   • Show all tables and foreign keys explicitly.
   • For M:N, create a junction table and label its composite PK.
   • Example: If asked to map Student → Course, write:

     CREATE TABLE StudentCourse (
         student_id INT,
         course_id INT,
         PRIMARY KEY (student_id, course_id),
         FOREIGN KEY (student_id) REFERENCES Student(id),
         FOREIGN KEY (course_id) REFERENCES Course(id)
     );
     

2. For Data Independence:

   • Define logical independence as: "Changing conceptual schema (e.g., adding a table) doesn’t affect external views."
   • Define physical independence as: "Changing storage (e.g., indexing) doesn’t affect logical design."
   • Three-schema diagram: Always include it if the question asks about architecture.

3. For Constraints:

   • List 5 constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK) with one example each.
   • Use real-world analogies:
     • PRIMARY KEY = Aadhaar number (unique to a person).
     • FOREIGN KEY = Linking a bank account to a customer.

4. Common Pitfalls:

   • ❌ Forgetting to include foreign keys in relational mapping.
   • ❌ Not handling M:N relationships correctly (missing junction table).
   • ❌ Confusing logical vs. physical independence (mix up schema levels).

Past Exam Question Analysis:

• Question: "How ER model can be mapped to the relational data model?" Marks Distribution:
  • 1 mark: Correctly identify entities → tables.
  • 2 marks: Handle 1:N relationships with FK.
  • 2 marks: Handle M:N with junction table.
  • 1 mark: Include primary/foreign keys.

8. Summary Checklist

Before the exam, verify you can: ✅ Draw an ER diagram with entities, attributes, and relationships. ✅ Convert an ER diagram to SQL tables (including junction tables for M:N). ✅ Explain three-schema architecture with a diagram. ✅ List 5 SQL constraints and give examples. ✅ Differentiate logical vs. physical data independence. ✅ Apply concepts to real-world systems (e.g., eSewa, Pathao).