Database Administration & Embedded SQL: Architecture, Embedded SQL, and DBMS Roles

TAKEAWAYS:

• Database administration includes backup/recovery, performance tuning, security management, and user administration—critical for maintaining reliable systems like Ncell’s customer databases.
• Embedded SQL integrates SQL queries into host programming languages (C, Java) to build dynamic database-driven applications, used in eSewa’s transaction processing.
• Centralized vs. distributed architectures differ in scalability, cost, and fault tolerance; distributed databases (like NEPSE’s stock trading system) handle geographically dispersed data better.
• Database recovery techniques (checkpointing, rollback) ensure data integrity after crashes, a must for banks processing high-value transactions.
• SQL injection attacks are prevented using parameterized queries, a key security practice in Kathmandu’s traffic management systems.
• Stored procedures improve performance by reducing network traffic, used in Pathao’s ride-matching algorithms.

1. Database Administration: Roles and Responsibilities

Database administration (DBA) ensures a database system operates efficiently, securely, and reliably. Key responsibilities include:

A. Database Backup and Recovery

Why it matters: Data loss from hardware failure, cyberattacks, or human error can cripple businesses. For example, if Daraz’s inventory database crashes, sales halt until recovery.

How it works:

1. Backup Types:

   • Full Backup: Copies entire database (time-consuming but complete).
   • Incremental Backup: Copies only changes since last backup (faster, used daily).
   • Differential Backup: Copies changes since the last full backup (balance between speed and completeness).
   • Log Backup: Records transactions (used for point-in-time recovery).

2. Recovery Techniques:

   • Checkpointing: Periodically saves the database state to a log (e.g., every 5 minutes).
   • Rollback: Undoes uncommitted transactions (e.g., if a user aborts a bank transfer).
   • Rollforward: Reapplies committed transactions from logs (e.g., restoring Ncell’s billing records after a crash).

   stateDiagram-v2
     [*] --> Database_Operating
     Database_Operating --> Transaction_Committed: Commit
     Transaction_Committed --> Log_Entry: Write to log
     Database_Operating --> Crash: Hardware/Software Failure
     Crash --> Recovery_Process: Triggered
     Recovery_Process --> Rollback: Uncommitted transactions
     Recovery_Process --> Rollforward: Reapply logs
     Rollforward --> [*]

Real-World Example:

• Nepal Rastra Bank (NRB) uses automated backup systems to ensure financial transaction records are never lost. During the 2015 earthquake, NRB’s distributed backups allowed quick recovery of critical banking data.

B. Performance Tuning

Slow queries or high resource usage degrade user experience. DBAs optimize using:

• Indexing: Speeds up search operations (e.g., indexing EmpID in an HR database).
• Query Optimization: Rewriting inefficient SQL (e.g., replacing SELECT * with specific columns).
• Partitioning: Splitting large tables (e.g., Daraz’s order history by month).
• Caching: Storing frequent queries (e.g., NTC’s route lookup cache).

Example:

-- Before (slow): Scans entire table
SELECT * FROM employees WHERE Salary > 50000;

-- After (fast): Uses index on Salary
CREATE INDEX idx_salary ON employees(Salary);

C. Security Management

DBAs enforce access controls to prevent unauthorized data access or modification.

• Authentication: Verifies user identity (e.g., Khalti’s OTP login).
• Authorization: Grants permissions (e.g., SELECT on accounts table for bank tellers).
• Encryption: Protects data at rest (e.g., Ncell encrypts customer call logs).

SQL Example:

-- Grant read-only access to HR staff
GRANT SELECT ON employees TO hr_staff;

Real-World Example:

• eSewa uses role-based access control (RBAC) to ensure only authorized staff can process payments. For example, a customer service agent cannot view another user’s transaction history.

D. User Administration

Manages database users, roles, and privileges.

• Roles: Groups with shared permissions (e.g., admin, auditor).
• Privileges: Granular access (e.g., INSERT into orders but not DELETE).

Example:

-- Create a role for Daraz warehouse staff
CREATE ROLE warehouse_staff;
GRANT SELECT, INSERT ON inventory TO warehouse_staff;

2. Database Architectures: Centralized vs. Distributed

Choosing the right architecture depends on scalability, cost, and fault tolerance.

A. Centralized Database Architecture

• Definition: Single database server managing all data (e.g., a small bank’s branch).
• Pros:
  • Simpler to manage.
  • Lower cost (single hardware/software setup).
• Cons:
  • Single point of failure (e.g., if the server crashes, the entire system goes down).
  • Limited scalability (slow for global operations like Google).

Example:

• A local NGO’s donor database uses a centralized MySQL server to track contributions from Nepal and abroad.

B. Distributed Database Architecture

• Definition: Data split across multiple physical locations (e.g., NEPSE’s stock exchange servers in Kathmandu and Pokhara).
• Pros:
  • High availability (if one node fails, others take over).
  • Scalability (handles global traffic like WhatsApp).
  • Localized processing (reduces latency for users).
• Cons:
  • Complex to design and maintain.
  • Higher cost (multiple servers, replication software).

Comparison Table:

Real-World Example:

• NEPSE (Nepal Stock Exchange) uses a distributed architecture to handle trading data across Kathmandu, Pokhara, and Biratnagar. If one server fails, others replicate transactions seamlessly.

C. Client-Server vs. Peer-to-Peer Architectures

Note: Most modern databases (e.g., MySQL, PostgreSQL) use client-server models, while distributed databases like Cassandra use hybrid approaches.

3. Embedded SQL: Integrating SQL with Programming Languages

Embedded SQL allows SQL queries to be written inside host languages like C, Java, or Python. Used in applications where dynamic database access is needed (e.g., Pathao’s ride-matching system).

A. How Embedded SQL Works

1. Preprocessing: Embedded SQL code is converted to standard SQL and host language calls.
2. Execution: The database engine processes SQL; the host program handles logic.

Example Workflow:

sequenceDiagram
    participant HostProgram as Java/Python App
    participant DBMS as Database Server
    HostProgram->>DBMS: EXEC SQL INSERT INTO orders VALUES (...);
    DBMS-->>HostProgram: Confirmation/Error
    HostProgram->>DBMS: EXEC SQL SELECT * FROM orders WHERE status = 'pending';
    DBMS-->>HostProgram: Result Set

B. Syntax and Keywords

Embedded SQL uses special directives (e.g., EXEC SQL):

-- Declare a cursor (for multi-row queries)
EXEC SQL DECLARE emp_cursor CURSOR FOR
    SELECT EmpID, FirstName FROM employees WHERE Salary > 50000;

-- Open the cursor
EXEC SQL OPEN emp_cursor;

-- Fetch rows
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;

Real-World Example:

• Pathao’s Driver App uses embedded SQL (likely in Java/Kotlin) to:
  1. Fetch nearby ride requests from the database.
  2. Update ride status (e.g., "accepted," "completed").
  3. Log driver earnings.

Worked Example: Problem: Write embedded SQL (in C) to calculate the total salary expense for a department in a company’s HR system. Assumptions:

• Table: employees(EmpID, FirstName, Salary, DeptID)
• Table: departments(DeptID, DeptName)

#include <sqlca.h> // SQL Communications Area (for error handling)

EXEC SQL BEGIN DECLARE SECTION;
    float total_salary;
    int dept_id = 10; // Marketing department
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT SUM(Salary) INTO :total_salary
    FROM employees WHERE DeptID = :dept_id;

printf("Total salary for Dept %d: %.2f\n", dept_id, total_salary);

Output:

Total salary for Dept 10: 150000.00

Real-World Tie-In:

• If you were the DBA for a multinational company’s Nepal branch, you’d use embedded SQL in their payroll system to generate monthly salary reports for each department (e.g., IT, HR, Marketing). For example, the DeptID = 10 query above could be part of a report sent to the Marketing Manager.

C. Advantages of Embedded SQL

• Seamless Integration: Combines database power with application logic.
• Performance: Reduces network traffic (e.g., fetching only needed data).
• Security: Uses parameterized queries to prevent SQL injection (see next section).

Disadvantage:

• Complexity: Requires knowledge of both SQL and the host language.

4. Security in Embedded SQL: Preventing SQL Injection

SQL injection attacks exploit poorly designed queries to manipulate databases. For example, a malicious user could input:

' OR '1'='1

to bypass login checks.

Prevention Techniques:

1. Parameterized Queries (Safe):

   -- Safe: Uses placeholders
   EXEC SQL INSERT INTO users VALUES (:username, :password);
   

2. Stored Procedures (Safe):

   CREATE PROCEDURE AddUser(IN uname VARCHAR(50), IN upass VARCHAR(50))
   BEGIN
       INSERT INTO users VALUES (uname, upass);
   END;
   

3. Input Validation: Reject suspicious characters (e.g., ', ;, --).

Real-World Example:

• Khalti uses parameterized queries to prevent fraud in payment processing. For example, when a user transfers money, Khalti’s backend validates the recipient ID before executing:

  EXEC SQL UPDATE accounts SET balance = balance - :amount
      WHERE user_id = :recipient_id;
  

5. Database Application Architectures

How databases interact with applications depends on the architecture:

Example:

• Ncell’s Billing System:
  • Tier 1 (Client): Mobile app/web interface.
  • Tier 2 (Application Server): Java/Spring Boot handles business logic (e.g., calculating bills).
  • Tier 3 (Database): PostgreSQL stores customer data, usage records, and payments.

6. Database Administration Tools

DBAs use tools to manage databases efficiently:

• Oracle Enterprise Manager: For Oracle databases.
• SQL Server Management Studio (SSMS): For Microsoft SQL Server.
• pgAdmin: For PostgreSQL.
• MySQL Workbench: For MySQL/MariaDB.

Example Workflow in pgAdmin:

1. Backup: Right-click database → Backup.
2. Restore: Right-click → Restore → Select backup file.
3. Monitor: View query performance in the Dashboard.

In the Real World

1. eSewa’s Payment Processing

   • Embedded SQL: Written in Java, eSewa’s backend uses embedded SQL to:
     • Validate user credentials (SELECT * FROM users WHERE email = ? AND password = ?).
     • Process transactions (UPDATE accounts SET balance = balance - ? WHERE user_id = ?).
   • Architecture: Three-tier with load balancers to handle 100,000+ daily transactions.

2. Ncell’s Customer Database

   • Distributed Architecture: Data centers in Kathmandu and Biratnagar replicate customer records for fault tolerance.
   • DBA Tasks:
     • Daily backups of call logs and billing data.
     • Performance tuning for peak hours (e.g., indexing call_date for faster reports).
     • Security: Encrypting customer call records to comply with privacy laws.

3. Daraz’s Order Management

   • Embedded SQL in Python: Daraz’s warehouse management system uses embedded SQL to:
     • Fetch pending orders (SELECT * FROM orders WHERE status = 'pending').
     • Update inventory (UPDATE products SET stock = stock - ? WHERE product_id = ?).
   • Problem Solved: Without embedded SQL, Daraz would need to manually parse CSV files for inventory updates—slow and error-prone.

Exam Tip

1. For Short Questions:

   • Define terms precisely (e.g., "Embedded SQL is SQL code embedded in a host language like C or Java, processed by a preprocessor before compilation").
   • Compare architectures in tables (e.g., centralized vs. distributed).
   • List backup types or recovery techniques in bullet points.

2. For Practical Questions:

   • Embedded SQL: Always show the EXEC SQL directive and variable binding (e.g., :var_name).
   • Architecture: Draw a simple diagram (even in text) to explain tiers or distributed nodes.
   • Worked Examples: Use real-world data (e.g., bank loans, e-commerce orders) to make answers relatable.

3. Common Pitfalls:

   • SQL Injection: Never write raw string concatenation in queries. Always use parameterized queries.
   • Architecture Choice: Justify your answer (e.g., "Distributed is better for NEPSE because it handles multiple cities").
   • Backup Types: Mix them up (e.g., "incremental" vs. "differential"). Practice distinguishing them.

4. High-Score Strategies:

   • Visuals: Sketch ER diagrams or architecture layers in your answer book.
   • Real-World Links: Tie examples to Nepalese companies (e.g., "Like Ncell, a distributed database would help Daraz scale across Nepal").
   • SQL Syntax: Write complete, executable code snippets (even if not asked).

Final Note: Database administration is about reliability, security, and performance. Master embedded SQL and architecture trade-offs to ace this unit!