Database Programming: Basics, Architecture & Access Methods

TAKEAWAYS:

• Database programming bridges SQL and programming languages to automate database tasks.
• Oracle’s architecture (user, system, and database layers) is the foundation for PL/SQL.
• Procedural access (PL/SQL) allows complex logic, while non-procedural (SQL) handles queries.
• Cursors enable dynamic interaction with database result sets.
• Triggers automate actions based on database events (e.g., insert/update/delete).
• Database programming is used in eSewa (transaction validation), Daraz (inventory updates), and NEPSE (stock market automation).

1. What is Database Programming?

Database programming refers to the use of programming languages (e.g., PL/SQL, Python, Java) to interact with databases, automate tasks, and extend database functionality beyond SQL. It combines SQL (for data manipulation) with programming logic (for business rules, validation, and workflows).

Key Definitions

• Database: An organized collection of structured data (e.g., customer records, transactions).
• Database Programming: Writing code to manipulate, control, or extend a database.
• Procedural Access: Writing step-by-step instructions (e.g., PL/SQL blocks).
• Non-Procedural Access: Using SQL commands (e.g., SELECT, INSERT) without explicit logic.

Comparison: Procedural vs. Non-Procedural Access

2. Why Database Programming?

Without programming, databases are limited to static queries. Database programming enables:

• Automation: Automate repetitive tasks (e.g., sending reminders for overdue loans).
• Complex Logic: Handle multi-step operations (e.g., calculating discounts in Daraz).
• Security: Restrict access dynamically (e.g., eSewa transaction validation).
• Performance: Optimize queries with procedural logic.

3. Oracle Database Architecture

Oracle’s architecture consists of three layers:

1. User Layer: Applications (e.g., eSewa, Daraz) interact with the database.
2. System Global Area (SGA): Memory structures (e.g., shared pool, buffer cache).
3. Database Layer: Physical storage (tables, indexes, logs).

flowchart TD
    A["User Layer
(eSewa, Daraz)"] --> B["Oracle Client
(PL/SQL, SQL)"]
    B --> C["System Global Area
(SGA: Shared Pool, Buffer Cache)"]
    C --> D["Database Layer
(Tables, Indexes, Logs)"]
    D --> E["Physical Storage
(Disk, Storage)"]

4. Procedural vs. Non-Procedural Access: Worked Example

Scenario: A bank (e.g., NMB) wants to calculate a customer’s loan interest with a 10% penalty if the loan is overdue.

Non-Procedural (SQL Only)

SELECT customer_id, loan_amount,
       (loan_amount * 0.05) AS interest,
       (loan_amount * 0.15) AS penalty_interest  -- Hardcoded penalty
FROM loans
WHERE due_date < CURRENT_DATE;

Limitation: Penalty rate is hardcoded; cannot change dynamically.

Procedural (PL/SQL)

DECLARE
    penalty_rate NUMBER := 0.10; -- Configurable
BEGIN
    FOR loan_rec IN (SELECT * FROM loans WHERE due_date < CURRENT_DATE) LOOP
        DBMS_OUTPUT.PUT_LINE('Customer: ' || loan_rec.customer_id ||
                             ', Penalty Interest: ' || (loan_rec.loan_amount * penalty_rate));
    END LOOP;
END;

Advantage: Penalty rate can be adjusted without changing SQL.

5. Cursors: Dynamic Database Interaction

A cursor is a database object that allows row-by-row processing of query results. Types:

1. Implicit Cursor: Automatically created for SELECT statements.
2. Explicit Cursor: Manually declared (e.g., for complex processing).
3. Server Cursor: Processes data on the database server.
4. Bulk Cursor: Processes multiple rows at once (e.g., batch updates).

Example: Explicit Cursor for Employee Salary Adjustments

stateDiagram-v2
    [*] --> Open_Cursor: DECLARE emp_cursor CURSOR FOR SELECT * FROM employees;
    Open_Cursor --> Fetch_Loop: FOR emp_rec IN emp_cursor LOOP
    Fetch_Loop --> Adjust_Salary: emp_rec.salary := emp_rec.salary * 1.05;
    Adjust_Salary --> Update_DB: UPDATE employees SET salary = emp_rec.salary WHERE emp_id = emp_rec.emp_id;
    Update_DB --> Check_End: IF emp_cursor%NOTFOUND THEN EXIT; END IF;
    Check_End --> Fetch_Loop
    Fetch_Loop --> [*]
    state Open_Cursor {
        direction LR
        DECLARE emp_cursor CURSOR FOR SELECT * FROM employees;
    }
    state Fetch_Loop {
        direction LR
        FOR emp_rec IN emp_cursor LOOP
    }
    state Adjust_Salary {
        direction LR
        emp_rec.salary := emp_rec.salary * 1.05;
    }
    state Update_DB {
        direction LR
        UPDATE employees SET salary = emp_rec.salary WHERE emp_id = emp_rec.emp_id;
    }

Use Case in Nepal:

• NEPSE uses cursors to process stock market trades in batches.
• Pathao dynamically adjusts driver earnings based on ride metrics.

6. Triggers: Automating Database Actions

A trigger is a PL/SQL block that executes automatically in response to database events (e.g., INSERT, UPDATE, DELETE).

Types of Triggers

Example: Trigger for Daraz Order Status

CREATE OR REPLACE TRIGGER trg_daraz_order_status
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    IF :NEW.status = 'SHIPPED' THEN
        UPDATE inventory
        SET stock = stock - 1
        WHERE product_id = :NEW.product_id;
    END IF;
END;

Real-World Impact:

• Khalti uses triggers to auto-generate transaction receipts.
• NTC triggers route updates when network congestion is detected.

7. Database Programming in Real Apps

eSewa: Transaction Validation

• Idea Used: Triggers + PL/SQL
• How: When a user initiates a payment, a trigger checks:
  • Account balance (AFTER INSERT on transactions).
  • Fraud patterns (BEFORE INSERT with validation logic).
• Worked Example:

  CREATE TRIGGER trg_esewa_fraud_check
  BEFORE INSERT ON transactions
  FOR EACH ROW
  BEGIN
      IF :NEW.amount > 100000 AND :NEW.user_id NOT IN (SELECT id FROM verified_users) THEN
          RAISE_APPLICATION_ERROR(-20001, 'High-risk transaction blocked.');
      END IF;
  END;
  

Daraz: Inventory Updates

• Idea Used: Cursors + Triggers
• How: After an order is placed (AFTER INSERT on orders), a cursor updates stock levels:

  DECLARE
      order_cursor CURSOR FOR SELECT product_id, quantity FROM orders WHERE status = 'PROCESSED';
  BEGIN
      FOR order_rec IN order_cursor LOOP
          UPDATE inventory
          SET stock = stock - order_rec.quantity
          WHERE product_id = order_rec.product_id;
      END LOOP;
  END;
  

NEPSE: Stock Market Automation

• Idea Used: Stored Procedures
• How: A stored procedure calculates daily stock indices:

  CREATE PROCEDURE calc_daily_index(p_date IN DATE)
  AS
  BEGIN
      UPDATE indices
      SET index_value = (SELECT AVG(price) FROM stocks WHERE trade_date = p_date)
      WHERE date = p_date;
  END;
  

8. Advantages and Disadvantages

9. Exam Tips

1. Define Clearly: For "database programming," emphasize automation + SQL + programming.
2. Draw Oracle Architecture: Always include 3 layers (user, SGA, database) with labels.
3. Compare Procedural vs. SQL: Use a table (as above) to show differences.
4. Cursors: Explain why they’re needed (row-by-row processing) with a state diagram.
5. Triggers: Give a real-world example (eSewa, Daraz) and specify event type (BEFORE/AFTER).
6. PL/SQL vs. SQL: Show a worked example (e.g., salary calculation) to highlight procedural logic.

Final Note: Focus on how these concepts apply to Nepalese apps (eSewa, Daraz) and global platforms (Google Workspace). Always visualize Oracle’s layers and cursor workflows in exams.