Views & Integrity Constraints: Security, Abstraction & Data Rules

1. SQL Views: Virtual Tables for Simplified Queries

A view is a virtual table defined by a saved SQL query. It does not store data but dynamically retrieves results when queried. Views are used to:

• Hide complexity (e.g., join logic).
• Restrict access (e.g., HR sees only employee salaries).
• Enforce security (e.g., customers see only their orders).

Syntax

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example: Create a view for high-value customers in eSewa.

CREATE VIEW HighValueCustomers AS
SELECT customer_id, name, total_spent
FROM transactions
WHERE total_spent > 10000;

Query the view:

SELECT * FROM HighValueCustomers;

Types of Views

Advantages

• Security: Restrict sensitive data (e.g., bank account balances).
• Performance: Reduce redundant queries (e.g., pre-filtered reports).
• Abstraction: Hide schema changes (e.g., if orders table is renamed, views still work).

Disadvantages

• No Indexing: Views cannot be indexed (slower for large datasets).
• Limited DML: Cannot modify underlying data directly (unless updatable view).
• Dependency: Changes in base tables break views.

2. Data Integrity Constraints: Rules to Keep Data Clean

Constraints ensure data accuracy, consistency, and validity. SQL supports:

• Column-level: Applied to individual columns.
• Table-level: Applied to entire tables (e.g., PRIMARY KEY).

Key Constraints

Referential Actions (ON DELETE/UPDATE)

When a referenced row is deleted/updated, specify action:

FOREIGN KEY (order_id)
REFERENCES orders(id)
ON DELETE CASCADE  -- Deletes dependent rows
ON UPDATE SET NULL -- Sets FK to NULL on update

Example: NEPSE’s stock trades.

FOREIGN KEY (symbol)
REFERENCES stocks(symbol)
ON DELETE RESTRICT;  -- Prevents deleting a stock if trades exist

3. Worked Example: Daraz’s Order Processing

Scenario: Daraz wants to track orders with constraints:

• Each order has a unique order_id.
• Only valid customer_id (from customers table) can place orders.
• Order total must be ≥ 0.

Step 1: Define Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10,2) CHECK (total >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Step 2: Insert Data

INSERT INTO customers VALUES (1, 'Ramesh', 'ramesh@example.com');
INSERT INTO orders VALUES (101, 1, '2023-10-01', 500.00);

Step 3: Create a View for High-Value Orders

CREATE VIEW HighValueOrders AS
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total > 1000;

Query the view:

SELECT * FROM HighValueOrders;

4. Comparison: Views vs. Tables

5. Real-World Applications

In the Real World

1. eSewa’s Transaction Logs

   • Idea: Views filter sensitive data (e.g., only show merchant names, not full customer details).
   • How: A SecureTransactions view hides PII (Personally Identifiable Information) from support staff.

2. NEPSE’s Stock Trade Validation

   • Idea: CHECK constraints ensure trades are valid (e.g., quantity > 0).
   • How: Prevents negative stock trades or trades for non-existent symbols.

3. Pathao’s Driver-Ride History

   • Idea: A DriverPerformance view aggregates ride metrics (e.g., avg. rating, earnings).
   • How: Simplifies analytics for Pathao’s operations team.

6. Exam Tip

• Views:

  • Know the CREATE VIEW syntax and when to use simple/composite views.
  • Practice writing queries on views (e.g., SELECT * FROM view_name).
  • Common Exam Question: Given a table, write a view to filter/summarize data.

• Constraints:

  • Memorize PRIMARY KEY, FOREIGN KEY, CHECK, and ON DELETE/UPDATE actions.
  • Common Exam Question: Design a table with constraints (e.g., "Create a bank_accounts table with balance >= 0").

• Worked Example:

  • Always show table definitions + constraints + view creation in answers.
  • Use real-world examples (e.g., Daraz orders, Ncell customers) to explain constraints.

Visuals

1. SQL View vs. Table

2. Referential Actions

sequenceDiagram
    participant User
    participant Orders
    participant Customers

    User->>Orders: DELETE order_id=101
    Orders->>Customers: ON DELETE CASCADE (if defined)
    alt CASCADE
        Customers->>Customers: DELETE customer_id=1
    else RESTRICT
        Customers-->>Orders: Error: Cannot delete (referenced)
    end

3. Daraz’s Order Table Schema

erDiagram
    customers ||--o{ orders : "places (1:N)"
    customers {
        int customer_id PK, FK
        string name
        string email UNIQUE
    }
    orders {
        int order_id PK
        int customer_id FK
        date order_date
        decimal total CHECK(total >= 0)
    }
    orders ||--|{ order_items : "contains"
    order_items {
        int item_id PK
        int order_id FK
        string product
        int quantity
    }