Database Administration & Embedded SQL: Roles, Tasks & Integration

TAKEAWAYS:

• Database administrators (DBAs) manage performance tuning, security, backup/recovery, and user access, ensuring 24/7 system reliability.
• Embedded SQL lets programs execute SQL queries directly (e.g., EXEC SQL SELECT...) by embedding SQL in procedural code via preprocessors like Oracle’s Pro*C.
• Backup strategies (full, incremental, differential) and recovery models (point-in-time, transaction log) are critical for disaster resilience.
• Database tuning involves optimizing queries (indexes, views), hardware (RAM, disk I/O), and configurations (buffer pools, locks).
• Embedded SQL vs. ODBC/JDBC: Embedded SQL is compiled into the host language, while ODBC/JDBC uses runtime libraries for database connectivity.
• Real-world tie: Banks like Nabil Bank use DBAs to audit transactions (Unit 8’s security focus) while embedded SQL powers loan approval systems (Unit 8’s integration focus).

1. Database Administration (DBA): Roles and Responsibilities

Database administration is the backbone of data integrity, security, and performance. A DBA’s role spans design, implementation, monitoring, and troubleshooting of database systems. Key responsibilities include:

classDiagram
    class DBA {
        +managePerformance()
        +enforceSecurity()
        +handleBackups()
        +monitorSystems()
    }
    class SystemDBA {
        +configureOS()
        +upgradeDB()
    }
    class SecurityDBA {
        +auditAccess()
        +encryptData()
    }
    class ApplicationDBA {
        +optimizeQueries()
        +designSchemas()
    }
    DBA <|-- SystemDBA
    DBA <|-- SecurityDBA
    DBA <|-- ApplicationDBA
    class NabilBankLoanSystem {
        +queryTuning
        +roleBasedAccess
        +dailyBackups
    }
    ApplicationDBA --> NabilBankLoanSystem : "Optimizes"
    SecurityDBA --> NabilBankLoanSystem : "Secures"
    SystemDBA --> NabilBankLoanSystem : "Monitors"

Core DBA Tasks

mindmap
  root((DBA Responsibilities))
    Performance
      Query Optimization
      Index Management
      Hardware Tuning
    Security
      User Authentication
      Role-Based Access Control
      Encryption
    Backup & Recovery
      Full/Incremental/Differential Backups
      Point-in-Time Recovery
    Monitoring
      Log Analysis
      Alerts for Failures
    Data Integrity
      Constraints (PK, FK, CHECK)
      Transaction Management

Types of DBAs

Worked Example: Nabil Bank’s Loan Approval System

Nabil Bank’s loan approval system relies on a DBA to:

1. Tune queries for fast retrieval of customer credit scores (indexes on customer_id and credit_rating).
2. Enforce security via role-based access (e.g., only loan officers can update loan_status).
3. Backup daily transactions to recover if a system crash occurs mid-approval.
4. Monitor performance to ensure loan requests complete within 2 hours (SLA).

2. Backup and Recovery Strategies

Data loss can cripple businesses. DBAs use backup and recovery models to mitigate risks.

Backup Types

Recovery Models

Worked Example: Daraz’s Order Processing

Daraz’s database suffers a crash during peak sales. The DBA:

1. Restores the last full backup (Sunday night).
2. Applies differential backups (Monday–Wednesday).
3. Uses transaction logs to replay orders from Wednesday 3 PM onward. Result: Orders from 3 hours before the crash are recovered without data loss.

3. Database Tuning and Performance Optimization

Slow queries and inefficient storage waste resources. DBAs optimize databases using:

Key Tuning Techniques

classDiagram
    class QueryOptimization {
        +Use EXPLAIN PLAN
        +Add Indexes (B-tree, Hash)
        +Rewrite SQL (avoid SELECT *)
    }
    class HardwareTuning {
        +Increase RAM (buffer pool)
        +Use SSDs for I/O
        +Partition large tables
    }
    class Configuration {
        +Adjust lock granularity
        +Set optimal transaction log size
    }
    QueryOptimization --> HardwareTuning : "Depends on"
    HardwareTuning --> Configuration : "Influences"

Worked Example: NTC’s Billing System

NTC’s billing system struggles with slow SELECT queries on customer_id. The DBA:

1. Adds a composite index on (customer_id, service_type).
2. Partitions the billing_records table by month.
3. Increases the buffer pool size to reduce disk I/O. Result: Query time drops from 5 seconds to 100 milliseconds.

4. Embedded SQL: Integrating SQL with Programming Languages

Embedded SQL allows SQL statements to be embedded within host languages (C, Java, COBOL) using preprocessors like Oracle’s Pro*C or Microsoft’s ODBC.

sequenceDiagram
    participant User
    participant C_Program
    participant Oracle_ProC
    participant Oracle_DB

    User->>C_Program: EXEC SQL SELECT name, age FROM customers WHERE customer_id = 101
    C_Program->>Oracle_ProC: Preprocess SQL
    Oracle_ProC->>Oracle_DB: Compiled Query
    Oracle_DB-->>Oracle_ProC: Result Set (name="Rama", age=30)
    Oracle_ProC-->>C_Program: Embedded Variables
    C_Program-->>User: printf("Customer: Rama, Age: 30")

    note right of Oracle_ProC: Pro*C converts SQL to C function calls
    note right of Oracle_DB: No runtime ODBC/JDBC overhead

How Embedded SQL Works

1. Embed SQL in code using EXEC SQL directives.
2. Preprocess the code to convert SQL into function calls.
3. Compile and link with database drivers.

Example (C with Oracle Pro*C):

EXEC SQL BEGIN DECLARE SECTION;
    char name[50];
    int age;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT name, age INTO :name, :age FROM customers WHERE customer_id = 101;
printf("Customer: %s, Age: %d\n", name, age);

Embedded SQL vs. ODBC/JDBC

Worked Example: Khalti’s Payment Processing

Khalti’s backend uses Embedded SQL (Java + JDBC) to:

1. Check user balance:

   EXEC SQL SELECT balance FROM accounts WHERE user_id = ?;
   

2. Deduct amount:

   EXEC SQL UPDATE accounts SET balance = balance - ? WHERE user_id = ?;
   

3. Log transaction:

   EXEC SQL INSERT INTO transactions VALUES (?, ?, ?);
   

Why Embedded SQL?

• Speed: Direct database access without ODBC overhead.
• Atomicity: Transactions commit only if all steps succeed (e.g., no partial deductions).

5. Database Security in Administration

DBAs enforce security via:

• Authentication: Passwords, biometrics, or Kerberos.
• Authorization: Roles (e.g., loan_officer, auditor) and privileges (SELECT, INSERT).
• Encryption: TLS for data in transit, AES for data at rest.
• Auditing: Logging all GRANT, REVOKE, and DROP operations.

Worked Example: NEPSE’s Trading System

NEPSE’s database:

1. Encrypts sensitive data (e.g., investor PINs) with AES-256.
2. Uses roles to restrict access:
   • broker can only SELECT stock prices.
   • admin can DROP TABLE (with audit logs).
3. Logs all UPDATE operations on portfolio tables.

6. Exam Tip: How This Unit is Tested

This unit is heavily practical in TU exams. Expect:

1. Short Questions (2–5 marks):
   • Define Embedded SQL vs. ODBC.
   • List 3 backup types and their uses.
   • Explain one DBA tuning technique with an example.
2. Long Questions (10–15 marks):
   • Design a backup strategy for a given scenario (e.g., "A hospital’s patient records database").
   • Write Embedded SQL code (e.g., "Embed a query to fetch top 5 customers by purchase amount in C").
   • Explain how a DBA would optimize a slow-running query (use EXPLAIN PLAN and indexes).
3. Case Studies (15–20 marks):
   • Real-world scenario: "Daraz’s database crashed during Black Friday. Describe the recovery steps a DBA would take."
   • Security: "How would you secure Nabil Bank’s loan approval system? Include roles, encryption, and auditing."

Pro Tip:

• Memorize the 3 backup types (full, incremental, differential) and their trade-offs.
• Practice Embedded SQL in C/Java—exams often ask for code snippets.
• Relate to Nepalese companies: Always tie examples to Nabil Bank, NEPSE, Daraz, or Khalti for full marks.

In the Real World

1. Nabil Bank’s Loan System

   • Embedded SQL: Java + JDBC embeds SQL to check credit scores and approve loans in real time.
   • DBA Role: DBAs optimize queries to ensure loan decisions complete within 2 hours (SLA).

2. Daraz’s Order Processing

   • Backup Strategy: Uses differential backups nightly + transaction logs for point-in-time recovery during sales peaks.
   • Performance Tuning: DBAs partition orders table by month to speed up Black Friday queries.

3. Khalti’s Payment Gateway

   • Embedded SQL: C# + ODBC embeds SQL to deduct amounts atomically (no partial transactions).
   • Security: DBAs encrypt all transaction_amount fields and audit UPDATE operations.

4. NEPSE’s Trading Platform

   • Database Tuning: DBAs use materialized views to cache daily stock prices, reducing query load.
   • Security: Role-based access ensures only brokers can trade, while auditors log all BUY/SELL actions.

Final Note: Master this unit by linking theory to Nepalese tech companies. Examiners love examples from banks, e-commerce, and fintech—use them to score full marks!

In the real world

• Nabil Bank’s Loan Approval System: Uses role-based access control (RBAC) to restrict loan officers from viewing customer salary details (enforced via DBA-defined roles).
• Daraz’s Inventory Management: Employs differential backups nightly to recover from crashes during Black Friday sales (reduces recovery time from hours to minutes).
• Khalti’s Transaction Processing: Integrates embedded SQL (Pro*C) for high-speed validation of merchant transactions, avoiding the latency of ODBC/JDBC APIs.