DB Admin: Roles, DBMS Types, Data Models & Challenges

1. Role of a Database Administrator (DBA)

A Database Administrator (DBA) is responsible for managing, securing, and optimizing databases to ensure data integrity, availability, and performance. Their key responsibilities include:

• Database Design and Implementation: Creating and maintaining database structures.
• Security Management: Controlling access, enforcing permissions, and protecting data from unauthorized access.
• Backup and Recovery: Ensuring data is backed up and can be restored in case of failures.
• Performance Tuning: Optimizing queries and database configurations for efficiency.
• User Management: Creating and managing user accounts and roles.
• Compliance and Auditing: Ensuring databases meet regulatory requirements and tracking access for auditing.

Why is a DBA Important?

Without a DBA, databases can become:

• Unorganized (poor schema design).
• Vulnerable (security breaches).
• Slow (inefficient queries).
• Unreliable (data loss due to lack of backups).

---

2. Types of Database Management Systems (DBMS)

DBMS software manages databases by providing tools for storage, retrieval, and manipulation of data. There are four main types:

Comparison: Relational vs. NoSQL

Worked Example: Relational DBMS in eSewa eSewa, Nepal’s popular digital payment platform, uses a relational DBMS (likely PostgreSQL or MySQL) to:

• Store user transactions in tables (users, transactions, wallets).
• Enforce relationships (e.g., a transaction must reference a valid user via a foreign key).
• Run complex queries like:

  SELECT u.name, SUM(t.amount)
  FROM transactions t
  JOIN users u ON t.user_id = u.id
  WHERE t.date BETWEEN '2023-01-01' AND '2023-12-31'
  GROUP BY u.id;
  

  This query retrieves the total transaction amount for each user in 2023.

---

3. Data Models in Databases

A data model defines how data is structured, stored, and manipulated. The three primary models are:

A. Hierarchical Model

• Structure: Tree-like (parent-child relationships).
• Example:

• Use Case: Legacy systems (e.g., IBM IMS).
• Disadvantage: Difficult to update (rigid structure).

B. Network Model

• Structure: Graph-like (multiple parent-child relationships).
• Example:

• Use Case: Old financial systems.
• Disadvantage: Complex to manage.

C. Relational Model (Most Common)

• Structure: Tables with rows (tuples) and columns (attributes).
• Example:

  erDiagram
    USER ||--o{ TRANSACTION : places
    USER {
      int id PK
      string name
      string email
    }
    TRANSACTION {
      int id PK
      int user_id FK
      decimal amount
      datetime date
    }

• Advantages:
  • Flexible (joins allow complex queries).
  • ACID compliance (ensures reliable transactions).
• Disadvantages:
  • Can be slow for unstructured data.
  • Requires careful normalization.

D. Object-Oriented Model

• Structure: Data stored as objects (like in OOP).
• Example:

  classDiagram
    class User {
      +int id
      +string name
      +List~Transaction~ transactions
    }
    class Transaction {
      +int id
      +decimal amount
      +User user
    }
    User "1" --> "0..*" Transaction

• Use Case: Multimedia applications (e.g., storing images with metadata).

E. NoSQL Models

1. Document Model (e.g., MongoDB):
   • Stores data in JSON-like documents.
   • Example:

     {
       "_id": 1,
       "name": "Ramesh",
       "transactions": [
         { "amount": 500, "date": "2023-01-15" },
         { "amount": 200, "date": "2023-02-20" }
       ]
     }
     

2. Key-Value Model (e.g., Redis):
   • Simple key → value pairs.
   • Example: user_123 → {"name": "Sita", "balance": 5000}.
3. Column-Family Model (e.g., Cassandra):
   • Stores data in columns rather than rows.
   • Example: Storing sensor data for IoT devices.

---

4. Challenges in Database Administration

DBAs face several challenges in managing databases effectively:

---

In the Real World

1. eSewa (Digital Payments)

   • Idea Used: Relational DBMS (PostgreSQL/MySQL) for transaction records.
   • How: Stores user transactions in normalized tables (users, transactions) to ensure data integrity and fast retrieval. Foreign keys link transactions to users, preventing orphaned records.
   • Example Query:

     -- Find all transactions for a user in the last 30 days
     SELECT * FROM transactions
     WHERE user_id = 123 AND date >= CURRENT_DATE - INTERVAL '30 days';
     

2. Khalti (Mobile Wallet)

   • Idea Used: ACID Transactions for financial integrity.
   • How: When you transfer money from Khalti to a bank, the system:
     1. Deducts from your balance (atomic).
     2. Adds to the recipient’s balance (consistent).
     3. Logs the transaction (durable). If any step fails, the entire transaction rolls back.

3. NTC (Telecom Billing System)

   • Idea Used: Hierarchical Data Model for call records.
   • How: Call logs are stored in a tree structure where:
     • Root = Customer
     • Child = Call
     • Grandchild = Call Details (duration, timestamp).
   • Challenge: Updating records is slow, but it works well for read-heavy billing systems.

4. Daraz (E-Commerce)

   • Idea Used: NoSQL (MongoDB) for Product Catalogs.
   • How: Product data (e.g., specifications, reviews) is stored as flexible JSON documents because:
     • Products vary (e.g., a phone has different attributes than a book).
     • Scaling horizontally is easier than with relational databases.

---

5. Database Administration Workflow

A typical DBA workflow involves the following steps:

Worked Example: Ncell’s Billing System

Ncell uses a hybrid approach:

• Relational DBMS for structured data (customer records, call logs).
• NoSQL for real-time analytics (e.g., predicting usage patterns).
• DBA Tasks:
  1. Design: Normalize tables for call records (e.g., customers, calls, plans).
  2. Security: Encrypt customer data (GDPR compliance).
  3. Backup: Daily snapshots of call logs.
  4. Performance: Index customer_id in the calls table for fast lookups.

---

Exam Tip

This unit is conceptual but practical. Expect questions on:

1. Definitions: What is a DBA? What are the types of DBMS?
2. Comparisons: Relational vs. NoSQL (table format).
3. Applications: How does eSewa/Khalti use databases? (Describe the data model and one query.)
4. Challenges: Explain data security or scalability issues with solutions.
5. Diagrams: Draw a simple ER diagram or hierarchical model.
6. Short Answers: "List 3 responsibilities of a DBA" or "Name 2 NoSQL databases."

Common Mistakes to Avoid:

• Confusing NoSQL (flexible schema) with relational (fixed schema).
• Forgetting ACID properties in relational databases.
• Not linking real-world examples (e.g., eSewa uses SQL, Khalti uses transactions).

Scoring Full Marks:

• Use bullet points for lists (e.g., DBA responsibilities).
• Draw ER diagrams or mermaid tables for comparisons.
• Relate every concept to a real-world Nepalese app (eSewa, Khalti, NTC).
• For calculations (e.g., backup strategies), show step-by-step reasoning.

---