Data Independence & Three-Schema Architecture

TAKEAWAYS:

• The three-schema architecture (external, conceptual, internal) decouples data from applications, enabling independent changes to schema without breaking programs.
• Logical data independence lets the conceptual schema change without altering external views or programs, while physical independence allows storage changes without touching logical or external layers.
• Data independence is critical for scalability: e.g., NEPSE’s stock database can expand storage (physical) or add new fields (logical) without disrupting trading apps.
• Mapping functions (external→conceptual, conceptual→internal) translate queries and constraints across layers, handled by the DBMS middleware.
• Real-world use: Daraz’s order system uses three-schema to separate customer views (external), inventory rules (conceptual), and warehouse layouts (internal).
• Trade-off: Three-schema adds complexity but pays off in maintainability—Google’s search index relies on it to update rankings without breaking search interfaces.

1. Why Separate Data from Programs?

Databases grow over time: new fields (e.g., adding "delivery address" to eSewa users), new users (e.g., Pathao adding bike-sharing data), or hardware upgrades (e.g., NTC switching from copper to fiber). If applications were hardcoded to the original schema, every change would require rewriting code—a nightmare for large systems.

Example: Bank Account Schema Evolution

stateDiagram-v2
    state "Initial Schema" as S1 {
        [*] --> S1: Account(ID, Balance, Branch)
    }
    state "After Adding 'LoanStatus'" as S2 {
        S1 --> S2: Add LOAN_STATUS field
    }
    state "After Moving to Cloud" as S3 {
        S2 --> S3: Change storage from SQL to NoSQL
    }

• Without three-schema: Every banking app (eSewa, Ncell bill payment) must be recompiled when LOAN_STATUS is added.
• With three-schema: The DBMS handles the change in the internal schema, while external views (e.g., "show my balance") stay unchanged.

2. The Three Layers of Three-Schema Architecture

The architecture splits data into three logical views, each with its own purpose:

Visualization:

Key Idea:

• External schemas are like menu items in a restaurant (customizable for each customer).
• Conceptual schema is the recipe (consistent ingredients across all dishes).
• Internal schema is the kitchen layout (how ingredients are stored and prepared).

3. Data Independence: The Core Benefit

Definition: Data independence is the ability to modify one schema layer without affecting others. It has two forms:

Why It Matters:

• Scalability: NEPSE can add new stock exchanges (e.g., Nepal Stock Exchange) to the conceptual schema without breaking trader apps.
• Security: If Salary data is moved to encrypted storage (internal change), HR apps (external) don’t need updates.
• Performance: Adding a GIN index to Daraz’s order table (internal) speeds up searches without changing the external API.

4. How Three-Schema Works: A Worked Example

Scenario: Kathmandu Traffic Management System (KTMS) tracks buses, taxis, and Pathao bikes. The city wants to:

1. Add a new field: VehicleType (Bus/Taxi/E-Bike).
2. Move bike data to a cloud database (Google Cloud SQL).

Step-by-Step Trace:

Before Change

• External Schema (Taxi Driver View):

  SELECT DriverID, VehicleID, Fare FROM TaxiRides WHERE DriverID = 'D001';
  

• Conceptual Schema:

erDiagram
    VEHICLE ||--o{ RIDE : "has"
    VEHICLE {
        string VehicleID PK
        string Type "Bus/Taxi"
        string LicensePlate
    }
    RIDE {
        string RideID PK
        string DriverID FK
        decimal Fare
    }
    Driver {
        string DriverID PK
        string Name
    }

• Internal Schema: All data stored in a single MySQL table Rides with a B-tree index on VehicleID.

After Adding VehicleType (Logical Change)

1. Conceptual Schema Update: Add Type to the VEHICLE entity.

   erDiagram
       VEHICLE {
           string VehicleID PK
           string Type "Bus/Taxi/E-Bike"  <-- NEW FIELD
           string LicensePlate
       }

2. External Schema Adjustment: The DBMS automatically updates the mapping function to include Type in views:

   -- Old external view (still works)
   SELECT DriverID, VehicleID, Fare FROM TaxiRides WHERE DriverID = 'D001';
   
   -- New external view (optional)
   SELECT DriverID, VehicleID, Fare, Type FROM TaxiRides WHERE DriverID = 'D001';
   

3. Physical Change (Moving Bike Data to Cloud)
   • Internal Schema Update: Split Rides into two tables: RoadVehicles (buses/taxis) and BikeRides (cloud-hosted).

• No external or conceptual changes: Drivers still query the same TaxiRides view.

Result:

• Logical Independence: The conceptual schema added Type without breaking driver apps.
• Physical Independence: Bike data moved to the cloud without changing driver queries.

5. Mapping Functions: The DBMS Glue

The DBMS middleware handles translations between schemas using mapping functions:

Visualization:

sequenceDiagram
    participant User as "Taxi Driver (External)"
    participant DBMS as "DBMS Middleware"
    participant Conceptual as "Conceptual Schema"
    participant Internal as "Internal Storage"

    User->>DBMS: "Show my rides for D001"
    DBMS->>Conceptual: "SELECT * FROM RIDE WHERE DriverID = 'D001'"
    Conceptual->>DBMS: "Optimized query: Index scan on DriverID"
    DBMS->>Internal: "Execute on MySQL/Cloud tables"
    Internal-->>DBMS: "Results"
    DBMS-->>User: "Returned data"

6. Advantages and Trade-offs

7. Real-World Applications

1. eSewa: Three-Schema in Mobile Payments

• External Schema:
  • User app shows: "Balance: ₹1,200", "Recent Transactions".
  • Merchant app shows: "Pending Payments", "Refunds".
• Conceptual Schema:
  • Unified model with User, Transaction, Merchant, and AuditLog tables.
  • Constraints: Balance >= 0, TransactionAmount > 0.
• Internal Schema:
  • Data partitioned by TransactionDate for fast queries.
  • Encrypted PIN and CVV fields.
• Why It Works: eSewa can add UPI support (conceptual change) or switch to PostgreSQL (physical change) without breaking the mobile app.

2. Daraz: Order Processing with Logical Independence

• External Schema:
  • Customer view: "My Orders", "Track Package".
  • Admin view: "Low-Stock Items", "Delivery Routes".
• Conceptual Schema:
  • Entities: Order, Product, Customer, Warehouse.
  • Rules: Stock >= 0, OrderStatus ∈ {Pending, Shipped, Delivered}.
• Physical Change Example: Daraz moved from SQL Server to Google BigQuery for analytics without changing the checkout flow.

3. NEPSE: Stock Market Data Independence

• External Schema:
  • Trader A: "Show me IPO stocks with Price > ₹100."
  • Analyst B: "Generate weekly reports on sector trends."
• Conceptual Schema:
  • Tables: Stock, Transaction, Trader, Exchange.
  • Constraints: Price > 0, Volume >= 0.
• Logical Change Example: NEPSE added CarbonFootprint to the Stock table (conceptual change) to comply with ESG regulations. Trader apps continued to work.

8. Exam Tip: How to Score Full Marks

1. Diagrams Are Mandatory:

   • Always draw the three-schema architecture with external→conceptual→internal arrows.
   • Example of a full-mark diagram:
   • Label each layer with one real-world example (e.g., "eSewa’s balance query").

2. Define with Examples:

   • Data Independence:

     "Logical independence allows the conceptual schema to change (e.g., adding LoanStatus to bank accounts) without affecting external views like eSewa’s balance check. Physical independence lets NEPSE switch from Oracle to PostgreSQL without changing trader apps."

   • Mapping Functions:

     "The DBMS translates a Pathao rider’s ‘show my last 5 rides’ into a conceptual query on the RIDE table, then optimizes it for the internal storage (e.g., index scan)."

3. Compare Logical vs. Physical Independence: Use a table like this:

   Aspect	Logical Independence	Physical Independence
   Layer Changed	Conceptual schema	Internal schema
   Example (Ncell)	Adding DataUsage to user profiles.	Switching from MySQL to MongoDB for call logs.
   Impact on Apps	No change (external views unchanged).	No change (external views unchanged).

4. Worked Example Practice:

   • For a given schema (e.g., Student, Course, Enrollment), describe:
     1. How adding Email to the conceptual schema affects external views.
     2. How moving Enrollment data to a NoSQL database (internal change) is handled.
   • Bonus: Relate to a real system (e.g., "Like how PU’s student portal can add ScholarshipStatus without breaking registration forms").

5. Avoid Common Pitfalls:

   • ❌ Don’t confuse schemas with tables: A schema is a logical structure; tables are its physical instances.
   • ❌ Don’t mix up independence types: Logical = conceptual changes; Physical = storage changes.
   • ❌ Skip constraints: Always mention how mapping functions enforce conceptual constraints in external views.

Final Note: Three-schema architecture is the backbone of scalable DBMS. For exams, focus on:

1. Drawing the three layers with real examples.
2. Explaining how changes propagate (or don’t) across layers.
3. Connecting to real systems (eSewa, NEPSE, Daraz) to show practical relevance.