Data Models & Schemas: Types, Structures & Design Principles

TAKEAWAYS:

• Data models are blueprints for organizing data (e.g., relational tables vs. hierarchical trees), each with trade-offs in flexibility and performance.
• Schema is the logical structure (e.g., Customer(CID, Name)), while instance is the actual data stored at a moment (e.g., CID=1, Name="Ramesh").
• Abstraction layers (physical/logical/view) isolate users from storage details, enabling data independence.
• Hierarchical/network models use parent-child links (e.g., eSewa’s transaction hierarchy), while relational models use tables (e.g., Ncell’s customer orders).
• Schema design must balance normalization (reducing redundancy) and denormalization (improving query speed).
• Worked examples tie theory to real systems: e.g., how Daraz’s product catalog uses a relational schema to link Products, Orders, and Customers.

---

1. What Is a Data Model?

A data model is a conceptual framework for organizing data to meet business needs. It defines:

• Data structures (how data is stored, e.g., tables, trees).
• Operations (how data is accessed/updated, e.g., SQL queries).
• Constraints (rules like "a customer must have a unique ID").

Types of Data Models

---

2. Schema vs. Instance: The Core Abstraction

Schema: The Blueprint

• Definition: A logical structure that defines:
  • Tables/fields (e.g., Customer(CID INT, Name VARCHAR(50))).
  • Relationships (e.g., Customer → Order).
  • Constraints (e.g., CID PRIMARY KEY).
• Example: The schema for a bank’s loan system might include:

  CREATE TABLE Loan (
      LoanID INT PRIMARY KEY,
      CustomerID INT REFERENCES Customer(CID),
      Amount DECIMAL(10,2),
      InterestRate DECIMAL(5,2),
      StartDate DATE
  );
  

  Visualization:

  erDiagram
      Customer ||--o{ Loan : "applies_for"
      Customer {
          int CID PK
          string Name
          string Address
      }
      Loan {
          int LoanID PK
          int CustomerID FK
          decimal Amount
          decimal InterestRate
          date StartDate
      }

Instance: The Actual Data

• Definition: A snapshot of data at a specific time (e.g., all loans approved on 2023-10-01).
• Example: An instance of the Loan table might look like:

  LoanID | CustomerID | Amount  | InterestRate | StartDate
  -------|------------|---------|--------------|-----------
  1001   | 501        | 500000  | 8.5           | 2023-10-01
  1002   | 502        | 200000  | 7.2           | 2023-10-02
  

Key Insight:

• Schema = What data exists (e.g., "a Loan table with InterestRate").
• Instance = Current data (e.g., "Loan 1001 has 8.5% interest").

---

3. Abstraction Layers in Databases

Databases use three layers to separate users from physical storage:

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   View Layer    │    │  Logical Layer  │    │ Physical Layer  │
│ (User’s view)   │    │ (Schema)        │    │ (Storage)       │
└─────────┬───────┘    └─────────┬───────┘    └─────────┬───────┘
          │                     │                     │
          ▼                     ▼                     ▼
┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│ SQL Queries     │    │ Tables, Keys,  │    │ Files, Indexes,│
│ (e.g., SELECT   │    │ Constraints     │    │ Disk Blocks    │
│ Name FROM       │    │ (e.g., PRIMARY  │    │ (e.g., B-tree  │
│ Customer)       │    │ KEY)           │    │ indexes)       │
└─────────────────┘    └─────────────────┘    └─────────────────┘

Why It Matters:

• Data Independence: Changes to the physical layer (e.g., switching from HDD to SSD) don’t break applications.
• Example: When NTC upgrades its network database from SQL Server to PostgreSQL, the Customer schema remains the same for their billing app.

---

4. Designing Schemas: Worked Example

Scenario: Design a schema for Khalti’s payment system to track:

• Users (UserID, Name, Email).
• Transactions (TransactionID, UserID, Amount, Status).
• Merchants (MerchantID, Name, BankAccount).

Step 1: Identify Entities and Attributes

Step 2: Define Relationships

• One User → Many Transactions (1:N).
• One Transaction → One Merchant (1:1, via MerchantID in a Payments table if needed).

Step 3: Schema in SQL

CREATE TABLE User (
    UserID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(15)
);

CREATE TABLE Merchant (
    MerchantID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    BankAccount VARCHAR(50) UNIQUE
);

CREATE TABLE Transaction (
    TransactionID INT PRIMARY KEY,
    UserID INT REFERENCES User(UserID),
    MerchantID INT REFERENCES Merchant(MerchantID),
    Amount DECIMAL(10,2) NOT NULL,
    Status VARCHAR(20) CHECK (Status IN ('Pending', 'Completed', 'Failed')),
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

Visualization:

Real-World Tie-In:

• When you transfer Rs. 2,000 from Khalti to a Daraz merchant, the Transaction table records:
  • UserID (your Khalti ID),
  • MerchantID (Daraz’s merchant ID),
  • Amount = 2000,
  • Status = "Completed".

---

5. Comparing Models: When to Use Which?

---

6. Schema Design Principles

A. Normalization (Reducing Redundancy)

Goal: Eliminate anomalies (update, insert, delete) by organizing data into tables. Example: Poorly designed Order table:

-- Bad: Repeating customer details in every order
Order (OrderID, CustomerName, CustomerAddress, Product, Quantity, Price)

Problem: If a customer moves, you must update every row where they appear!

Fixed (3NF):

Customer (CustomerID, Name, Address)
Order (OrderID, CustomerID, Product, Quantity, Price)

Visualization:

B. Denormalization (Trade-offs for Speed)

When to denormalize:

• Read-heavy systems (e.g., Daraz’s product catalog).
• Example: Combine Customer and Order to avoid joins:

  Order (OrderID, CustomerID, CustomerName, CustomerAddress, Product, Quantity)
  

Trade-off: Faster reads but harder to maintain.

---

7. In the Real World

Example 1: eSewa’s Transaction Hierarchy

• Model Used: Hierarchical
• How: eSewa’s database stores transactions in a tree:

  User → [Transaction 1, Transaction 2] → [Payment 1, Payment 2]
  

• Why: Each user has many transactions, and each transaction has one payment. This mirrors real-world billing flows.

Example 2: Ncell’s Billing System

• Model Used: Relational + Network
• How:
  • Relational: Tables for Customer, Plan, Usage.
  • Network: A customer can have multiple plans (e.g., postpaid + data), and each plan has many usage records.
• Query Example:

  -- Find all customers with >1000 minutes used in October
  SELECT C.Name, SUM(U.Minutes) AS TotalMinutes
  FROM Customer C
  JOIN Usage U ON C.CustomerID = U.CustomerID
  WHERE U.Date BETWEEN '2023-10-01' AND '2023-10-31'
  GROUP BY C.Name HAVING SUM(U.Minutes) > 1000;
  

Example 3: Daraz’s Order Processing

• Model Used: Relational
• Schema Snippet:

  Product (ProductID, Name, Price, Stock)
  Order (OrderID, CustomerID, OrderDate, Status)
  OrderItem (OrderID, ProductID, Quantity, UnitPrice)
  

• Real Scenario: When you place an order for a laptop:
  1. Order table gets a new row with Status = "Processing".
  2. OrderItem links to the Product table to deduct stock.
  3. Joins ensure you see:

     SELECT P.Name, OI.Quantity, OI.UnitPrice
     FROM Order O
     JOIN OrderItem OI ON O.OrderID = OI.OrderID
     JOIN Product P ON OI.ProductID = P.ProductID
     WHERE O.OrderID = 12345;
     

---

8. Exam Tip

What Examiners Look For

1. Definitions:

   • Data Model: "A set of concepts to describe data structures and operations."
   • Schema: "The logical design of a database (tables, fields, relationships)."
   • Instance: "The current data stored in the database at a point in time."

2. Diagrams:

   • ER Diagrams: Always draw them for schema design questions. Label:
     • Entities (rectangles),
     • Attributes (ovals),
     • Relationships (diamonds with cardinality, e.g., 1:N).
   • Layered Models: Draw the 3-layer abstraction (view → logical → physical).

3. SQL Queries:

   • For schema creation, use CREATE TABLE with PRIMARY KEY, FOREIGN KEY, and CHECK constraints.
   • Example:

     CREATE TABLE Customer (
         CID INT PRIMARY KEY,
         Name VARCHAR(50) NOT NULL,
         Age INT CHECK (Age >= 18)
     );
     

4. Comparisons:

   • Compare models in a table (like above) with pros/cons and real-world examples.
   • Example answer for "Advantages of relational model":

     "The relational model uses tables and SQL, which are standardized and support ACID transactions (critical for banks like Nabil). Joins allow flexible queries, and normalization reduces redundancy (e.g., storing customer details once in a Customer table)."

5. Worked Examples:

   • Always tie theory to Nepalese apps. For instance:

     "How would you design a schema for NEPSE’s stock trading system?" Answer: Use a relational model with tables for Trader, Share, and Transaction, where Trader has a 1:N relationship with Transaction (each trader can buy/sell many shares).

6. Common Pitfalls:

   • Confusing schema vs. instance: Remember schema = structure, instance = data.
   • Over-normalizing: Don’t split tables unnecessarily (e.g., separating City and Address if the app rarely queries cities alone).
   • Ignoring constraints: Always include PRIMARY KEY, FOREIGN KEY, and CHECK in schema answers.

Sample Exam Question & Answer

Question:

"Consider a university database with the following requirements:

• Students enroll in courses.
• Each course has an instructor.
• Instructors can teach multiple courses. Design the schema using an ER diagram and SQL."

Answer:

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(50)
);

CREATE TABLE Instructor (
    InstructorID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(50)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    InstructorID INT REFERENCES Instructor(InstructorID)
);

CREATE TABLE Enrollment (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT REFERENCES Student(StudentID),
    CourseID INT REFERENCES Course(CourseID),
    Grade CHAR(2) CHECK (Grade IN ('A', 'B', 'C', 'D', 'F'))
);

Key Points for Examiners:

• Used 1:N (Student → Enrollment) and 1:1 (Course → Instructor) relationships.
• Included constraints (FOREIGN KEY, CHECK for grades).
• Normalized to avoid redundancy (e.g., instructor details stored once in Instructor).

---

9. Practice Questions

1. Design a schema for a hospital management system with:

   • Patient (PID, Name, Age, Disease),
   • Doctor (DocID, Name, Specialization),
   • Appointment (AppID, PID, DocID, Date, Status). Draw the ER diagram and write SQL.

2. Compare hierarchical and relational models using:

   • Structure,
   • Query flexibility,
   • Example from eSewa or Ncell.

3. Explain why the following schema is poorly normalized:

   Order (OrderID, CustomerName, CustomerAddress, Product, Quantity, Price)
   

   Redesign it in 3NF.

4. Write SQL to: a. Create the Customer and Loan tables from the earlier example. b. Insert 2 customers and 1 loan. c. Query all loans with interest > 8%.

---

10. Further Reading

• Books:
  • Database System Concepts (Silberschatz) – Covers models and schemas in depth.
  • SQL for Data Analysis (O’Reilly) – Practical schema design examples.
• Tools:
  • MySQL Workbench: Design schemas visually.
  • dbdiagram.io: Generate ER diagrams from SQL.
• Nepali Context:
  • Study how Nepal Rastra Bank’s core banking system uses relational models for loans/accounts.
  • Analyze NEPSE’s stock trading platform (object-relational hybrid).