Data Models, Schemas, Instances: Foundations of Database Design

1. Introduction: What is a Data Model?

A data model is a structured representation of data, its relationships, and constraints. It defines how data is organized, stored, and accessed in a database. Think of it as the "blueprint" for a database system.

Key Types of Data Models

mindmap
  root((Data Models))
    Hierarchical
      Tree-like structure
      Example: Old mainframe systems
    Network
      Many-to-many relationships
      Example: Early IBM databases
    Relational
      Tables (relations), rows (tuples), columns (attributes)
      Example: Modern SQL databases
    Object-Oriented
      Objects with methods + attributes
      Example: ObjectDB, MongoDB (NoSQL)
    XML/JSON
      Semi-structured, nested data
      Example: APIs (e.g., Daraz product listings)

Why Relational Models Dominate

• Simplicity: Tables are intuitive for most users.
• Flexibility: Supports complex queries (e.g., "Find students enrolled in >3 courses").
• Scalability: Used by banks (NMB, Standard Chartered), e-commerce (Daraz), and telecom (NTC).

2. Schemas and Instances: The Static vs. Dynamic View

Definitions

• Schema: The structure of the database (e.g., table names, columns, data types). Example: Student(SID INT, SName VARCHAR(50), City VARCHAR(30)).
• Instance: A snapshot of data at a point in time (e.g., all students enrolled in 2024).
• Metadata: Data about data (e.g., "SID is a primary key").

Example: University Database

erDiagram
    Student ||--o{ Enrollment : takes
    Enrollment ||--o{ Course : enrolls_in
    Student {
        int SID PK
        string SName
        string City
    }
    Course {
        int CID PK
        string CName
        int Credits
    }
    Enrollment {
        int SID FK
        int CID FK
        char Grade
    }

Instance (2024 data):

3. Functional Dependencies (FD) and Quality Measures

Functional Dependency (FD)

• An attribute determines another (e.g., SID → SName).
• Notation: X → Y means X functionally determines Y.

Worked Example: Enrollment Table

FDs:

• SID → Grade (each student has one grade per course).
• CID → CName (course ID uniquely identifies the course name).

Four Informal Quality Measures

4. Relational Data Model vs. Flat Files

5. Three-Schema Architecture: Isolation Layers

A DBMS separates concerns into three layers to ensure data independence.

graph TD
    A["External Schema"] -->|"Mapping 1: Logical Data Independence"| B["Conceptual Schema"]
    B -->|"Mapping 2: Physical Data Independence"| C["Internal Schema"]
    A -->|"User View"| D["What users see (e.g., 'Find students in Pokhara')"]
    B -->|"Global View"| E["Unified data model (e.g., Student, Course tables)"]
    C -->|"Physical Storage"| F["How data is stored (e.g., B-tree indexes)"]

Key Definitions

• Logical Data Independence: Change the conceptual schema without affecting external views. Example: Adding a Faculty column to Student doesn’t break user queries.
• Physical Data Independence: Change storage (e.g., from HDD to SSD) without changing conceptual schema.

6. Real-World Applications

In the Real World

1. eSewa/Khalti (Payment Gateways)

   • Idea: Relational schema for transactions (e.g., Transaction(ID, UserID, Amount, Status)).
   • How: Ensures atomicity (no partial payments) and avoids redundancy (e.g., storing user details in one table).

2. Daraz (E-commerce)

   • Idea: Functional dependencies in Order(OrderID → CustomerID, ProductID).
   • How: Prevents anomalies like updating a customer’s address in multiple orders.

3. NTC/Ncell (Telecom)

   • Idea: Three-schema architecture separates:
     • External: "Show my bill."
     • Conceptual: Customer(Phone, Plan), Bill(Phone, Date, Amount).
     • Internal: Physical storage on RAID arrays.

7. Worked Example: Bank Loan Database

Schema:

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

Instance (2024 Q1):

Functional Dependencies:

• LoanID → (CustomerID, Amount, InterestRate) (unique loan).
• CustomerID → Name (from Customer table).

Quality Check:

• No Redundancy: InterestRate isn’t duplicated per customer.
• No Update Anomaly: Changing InterestRate for all loans of a customer requires a single update.

8. Exam Tip

• Focus Areas:

  1. Define terms: Schema vs. instance (always give an example).
  2. Draw diagrams: ER diagrams, three-schema layers, or FD examples.
  3. Compare models: Relational vs. flat files (highlight SQL vs. manual parsing).
  4. Apply quality measures: Identify anomalies in given schemas.
  5. Real-world links: Tie FD/three-schema to eSewa, Daraz, or NTC.

• Common Pitfalls:

  • Confusing logical vs. physical independence.
  • Forgetting to include primary keys in FD examples.
  • Overlooking atomicity in quality measures (e.g., splitting Address into Street, City).

• Sample Question Breakdown:

  • 20 marks: Define schema/instance + draw ER diagram (4 marks each).
  • 15 marks: Explain three-schema architecture with a diagram (8) + FD example (7).
  • 10 marks: Compare relational vs. flat file (5) + quality measures (5).

9. Summary Table: Key Concepts