DBMS Architecture & Installation: Layers, Components & Setup

TAKEAWAYS:

• Three-schema architecture separates external, conceptual, and internal schemas to ensure data independence and modularity.
• DBMS layers (physical, logical, view) map to storage, query processing, and user interfaces, each with distinct functions.
• Installation methods differ by OS (Windows/Linux), licensing (open-source vs. commercial), and deployment (local vs. cloud).
• Configuration files (e.g., my.cnf for MySQL, postgresql.conf) control performance, security, and resource limits.
• Real-world use: Banks like NMB use three-schema architecture to isolate customer-facing views from core ledgers, while Daraz’s logical layer optimizes product search queries.
• Exam focus: Compare architectures (e.g., Oracle vs. PostgreSQL), trace data flow through layers, and explain installation steps for a given DBMS.

1. DBMS Architecture: The Three-Schema Model

The three-schema architecture is the backbone of modern DBMS design. It decouples:

• External schemas (user-specific views),
• Conceptual schema (global logical structure),
• Internal schema (physical storage details).

classDiagram
    class ExternalSchema {
        +Views tailored to users/apps
        +Example: Customer portal (NMB Bank)
    }
    class ConceptualSchema {
        +Entity-Relationship model
        +Example: Unified ledger (all branches)
    }
    class InternalSchema {
        +File organization (heap, B-tree)
        +Example: Disk blocks in NTC’s billing DB
    }
    ExternalSchema -->|Maps to| ConceptualSchema : 1:N
    ConceptualSchema -->|Maps to| InternalSchema : 1:1
    note for ConceptualSchema "Hides physical details\nfrom users/apps"

Why it matters:

• Data independence: Change storage (e.g., switch from HDD to SSD) without altering user queries.
• Security: External schemas restrict access (e.g., a Daraz seller sees only their inventory).
• Modularity: Update one schema without breaking others (e.g., add a new field to NEPSE’s stock table).

Worked Example: NMB Bank’s Loan System

1. External Schema: Loan officer sees customer_id, loan_amount, interest_rate (no raw transaction logs).
2. Conceptual Schema: Links customers, loans, and payments tables with foreign keys.
3. Internal Schema: loans table stored as a B-tree index on customer_id for fast lookups.
   • Visual: See how a B-tree speeds up searches in the next section.

2. DBMS Layers: From Storage to User Interface

A DBMS is organized into three functional layers, each handling a specific role:

Key Processes in the Logical Layer:

1. Query Parsing: Converts SQL into a tree (e.g., SELECT * FROM orders WHERE date > '2023-01-01' → parse tree).
2. Optimization: Chooses the fastest execution plan (e.g., index scan vs. full table scan).
3. Execution: Fetches data from storage (physical layer).

Worked Example: Pathao’s Ride Request

1. View Layer: User taps "Request Ride" in the app (SQL: INSERT INTO rides(user_id, status) VALUES (123, 'pending')).
2. Logical Layer: Optimizer picks an index on user_id to avoid scanning all rides.
3. Physical Layer: Data written to a tablespace on AWS RDS.

3. DBMS Components: The Moving Parts

A DBMS consists of core components that work together:

mindmap
  root((DBMS Components))
    StorageManager
      Data files
      Buffer manager
      Authorization
    QueryProcessor
      DDL/DML compiler
      Optimizer
      Executor
    TransactionManager
      Locking
      Recovery
      Concurrency control
    DBATools
      Backup utilities
      Monitoring tools

Critical Components Explained:

• Storage Manager:

  • Handles tablespaces (logical storage units) and data files (physical files on disk).
  • Uses buffer pools (in-memory cache) to speed up reads/writes.
  • Example: PostgreSQL’s pg_tblspc directory stores tablespaces.

• Query Processor:

  • DDL/DML Compiler: Converts SQL into internal commands (e.g., CREATE TABLE → metadata update).
  • Optimizer: Decides whether to use an index or a hash join (e.g., for Daraz’s product searches).
  • Visual: See how a query executes in the next section.

• Transaction Manager:

  • Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).
  • Uses locks (e.g., SELECT ... FOR UPDATE in Ncell’s billing system) to prevent race conditions.

(Shows how the buffer pool caches data blocks in memory.)

4. Installation Methods: From Local to Cloud

Installing a DBMS depends on:

1. Operating System: Windows (GUI installer) vs. Linux (CLI or package managers).
2. Licensing: Open-source (PostgreSQL, MySQL) vs. commercial (Oracle, SQL Server).
3. Deployment: On-premise, cloud (AWS RDS, Azure SQL), or hybrid.

Installation Steps for PostgreSQL (Linux)

# 1. Update package list
sudo apt update

# 2. Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# 3. Check service status
sudo systemctl status postgresql

# 4. Connect to default user (postgres)
sudo -u postgres psql

Post-Installation Configuration: Edit /etc/postgresql/<version>/main/postgresql.conf to:

• Set shared_buffers (e.g., 4GB for high-traffic sites like Daraz).
• Enable remote connections (listen_addresses = '*').

Comparison Table: Installation Methods

5. Configuration Files: Tuning Performance

DBMS performance hinges on configuration files. Key files:

• MySQL: /etc/mysql/my.cnf or /etc/my.cnf
• PostgreSQL: /etc/postgresql/<version>/main/postgresql.conf
• Oracle: $ORACLE_HOME/dbs/init<SID>.ora

Critical Parameters to Configure:

Worked Example: Optimizing Khalti’s Payment DB

• Problem: Slow SELECT * FROM transactions WHERE user_id = 123 during peak hours.
• Solution: Increase shared_buffers to 8GB and add an index on user_id:

  CREATE INDEX idx_user_id ON transactions(user_id);
  

• Result: Query time drops from 500ms → 10ms.

6. Real-World Applications

1. NMB Bank’s Loan Processing System

• Architecture: Three-schema model separates:
  • External: Loan officer’s dashboard (only sees approved loans).
  • Conceptual: Links customers, loans, and payments tables.
  • Internal: loans table stored as a B-tree index on customer_id.
• Installation: Oracle Database 19c on-premise (high security, low latency).
• Configuration: db_block_size = 8KB (optimized for Nepali character sets).

2. Daraz’s Product Catalog

• Logical Layer: Uses hash partitioning on category_id to distribute products across servers.
• Query Optimization: Optimizer chooses index scan over full table scan for searches like "laptops under Rs. 50,000".
• Cloud Deployment: PostgreSQL on AWS RDS with read replicas for global users.

3. NTC’s Billing System

• Physical Layer: Data stored in tablespaces on high-speed SSDs.
• Transaction Management: Uses row-level locking to prevent double-billing.
• Backup: Automated daily snapshots to S3 (for disaster recovery).

(Shows how customer_id is stored for fast lookups in NMB’s loan system.)

Exam Tip

1. Diagrams Are Your Friend:

   • Draw the three-schema architecture and label each schema’s role.
   • Sketch the DBMS layers with arrows showing data flow (physical → logical → view).
   • Example Question: "Explain how a query like SELECT name FROM customers WHERE city = 'Kathmandu' moves through the layers."

2. Compare DBMS Installations:

   • Be ready to contrast GUI vs. CLI installation (steps, tools used).
   • Example: "How would you install MySQL on Windows vs. Ubuntu?"

3. Configuration Shortcuts:

   • Memorize 3 key parameters (shared_buffers, max_connections, work_mem) and their impact.
   • Example: "Why might a bank increase shared_buffers during Diwali?"

4. Real-World Scenarios:

   • Link concepts to Nepalese companies:
     • Three-schema: "How does NMB isolate loan officers from raw transaction data?"
     • Query Optimization: "How does Daraz’s optimizer choose between index and full scans?"
   • Example Question: "Describe the internal schema for NEPSE’s stock trading database."

5. Common Pitfalls:

   • Don’t confuse schemas and tablespaces:
     • Schema = logical grouping (e.g., hr, finance).
     • Tablespace = physical storage unit.
   • ACID is not optional: Always mention transaction management in recovery questions.

Final Visual Summary:

flowchart TD
    A["User Query<br/>(e.g., eSewa bill check)"] --> B["View Layer<br/>(SQL Client)"]
    B --> C["Logical Layer<br/>(Query Processor)"]
    C --> D["Optimizer<br/>(Picks index or scan)"]
    C --> E["Executor<br/>(Fetches data)"]
    E --> F["Physical Layer<br/>(Tablespaces/Disks)"]
    F --> G["Storage Manager<br/>(Buffer Pool)"]
    G -->|"Data"| F