Database Management Systems: Models, DBMS, SQL, and Design

TAKEAWAYS:

• A database stores organized data persistently, while a DBMS (e.g., MySQL, Oracle) manages access, security, and concurrency.
• The relational model (tables with rows/columns) dominates modern systems, but other models (hierarchical, network, NoSQL) exist for specific needs.
• SQL (Structured Query Language) is the standard for querying relational databases, with commands like SELECT, JOIN, and GROUP BY.
• Normalization eliminates redundancy by structuring tables into 1NF, 2NF, and 3NF, improving integrity and efficiency.
• Real-world systems (e.g., Khalti’s payment records, Daraz’s inventory) use databases to handle millions of transactions daily.
• ER diagrams visually design databases by mapping entities (e.g., Customer, Order) and their relationships (e.g., "places" → "has many").

What Is a Database?

A database is an organized collection of structured data stored electronically. Unlike file-based systems (e.g., Excel sheets or text files), databases:

• Store data persistently (survives system restarts).
• Support multiple users simultaneously (e.g., 100+ bank tellers accessing accounts).
• Enforce rules (e.g., "a customer must have a unique ID").
• Optimize queries (e.g., "find all orders over $1000 in 2023").

Why Databases Over File Systems?

Database Models: How Data Is Structured

Databases organize data into models. The four key models are:

1. Hierarchical Model

• Data is stored in a tree-like structure (parent-child relationships).
• Example: Old mainframe systems (e.g., IBM’s IMS).
• Limitation: Inflexible for complex queries (e.g., "find all orders from a specific customer").

graph TD
    A["Root: Company"] --> B["Department: Sales"]
    A --> C["Department: HR"]
    B --> D["Employee: Alice"]
    B --> E["Employee: Bob"]
    D --> F["Order: #1001"]

2. Network Model

• Extends hierarchical by allowing multiple parents (e.g., a customer can place orders in multiple departments).
• Example: Used in early airline reservation systems.
• Limitation: Complex to design and maintain.

3. Relational Model (Most Common)

• Data is stored in tables (relations) with rows (tuples) and columns (attributes).
• Example: MySQL (used by eSewa for transaction logs), PostgreSQL (used by NEPSE for stock data).
• Advantages:
  • Simple to understand (like spreadsheets).
  • Powerful querying with SQL.
  • Scalable and widely supported.

4. NoSQL Model (For Big Data)

• Non-relational (e.g., documents, key-value pairs, graphs).
• Example:
  • MongoDB (used by Pathao for ride-hailing data).
  • Redis (used by Khalti for caching transactions).
• Advantages:
  • Handles unstructured data (e.g., social media posts).
  • Scales horizontally (add more servers easily).
• Disadvantages: Less query flexibility than SQL.

Database Management System (DBMS)

A DBMS is software that:

1. Creates and manages databases (e.g., MySQL, Oracle, SQLite).
2. Provides a language to query data (SQL).
3. Enforces security (user permissions, encryption).
4. Handles concurrency (multiple users accessing data safely).

How a DBMS Works (Layered Architecture)

classDiagram
    class User {
        +Sends SQL/CLI Queries
    }
    class DBMS {
        +Query Processor
        +Optimizer
        +Storage Manager
        +Transaction Manager
        +Security Manager
    }
    class Database {
        +Tables/Collections
        +Indexes
    }
    class OS {
        +File System
        +Memory Management
    }
    User --> DBMS : "Queries"
    DBMS --> Database : "Manages"
    Database --> OS : "Stores"
    DBMS -->|> Transaction Manager : "Ensures ACID"
    DBMS -->|> Security Manager : "Controls Access"

SQL: The Language of Databases

SQL (Structured Query Language) is used to:

• Query data: SELECT, WHERE, JOIN.
• Modify data: INSERT, UPDATE, DELETE.
• Define structure: CREATE TABLE, ALTER TABLE.

Key SQL Commands

Normalization: Designing Efficient Databases

Normalization reduces redundancy by organizing tables into normal forms (1NF, 2NF, 3NF).

Example: Order Management System (Before/After Normalization)

Problem: Storing customer details in every order leads to duplication and update anomalies.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ PRODUCT : contains
    CUSTOMER {
        int id PK
        string name
        string email
    }
    ORDER {
        int order_id PK
        int customer_id FK
        string date
        -- Redundant: customer_name repeated here!
    }
    PRODUCT {
        int product_id PK
        string name
        float price
    }

After Normalization (3NF):

• Customers table: Stores unique customer data.
• Orders table: Links to customer_id (no duplication).
• Products table: Stores product details separately.

Worked Example: NEPSE Stock Database NEPSE tracks stock prices for thousands of companies. A poorly designed database might store:

• Company name repeated in every trade record → wasteful.
• Normalized design:

  CREATE TABLE Companies (
      company_id INT PRIMARY KEY,
      name VARCHAR(100),
      sector VARCHAR(50)
  );
  
  CREATE TABLE Trades (
      trade_id INT PRIMARY KEY,
      company_id INT FOREIGN KEY REFERENCES Companies(company_id),
      price DECIMAL(10,2),
      volume INT,
      trade_date DATE
  );
  

Real-World Applications

1. eSewa (Nepal)

• Database Model: Relational (MySQL/PostgreSQL).
• How It Uses Databases:
  • Stores user accounts, transaction logs, and payment gateways in normalized tables.
  • SQL Example:

    SELECT user_id, amount, status
    FROM transactions
    WHERE payment_method = 'Khalti' AND status = 'pending';
    

• Challenge: Handles millions of daily transactions with low latency.

2. Khalti (Digital Payments)

• Database Model: Hybrid (SQL for transactions + NoSQL for caching).
• Key Tables:
  • Users (customer details).
  • Transactions (payment records with timestamps).
  • Merchants (business partners).
• Optimization: Uses indexes on transaction_id and user_id for fast lookups.

3. Daraz (E-Commerce)

• Database Model: Distributed NoSQL (MongoDB for product catalog + SQL for orders).
• Example Query:

  SELECT p.name, o.quantity, o.total_price
  FROM products p
  JOIN orders o ON p.product_id = o.product_id
  WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31';
  

• Challenge: Scales to millions of products and real-time inventory updates.

4. NEPSE (Stock Exchange)

• Database Model: Relational (Oracle/PostgreSQL).
• Key Tables:
  • Companies (listed firms).
  • Trades (buy/sell records).
  • Indices (e.g., NEPSE Index calculations).
• Example:

  SELECT c.name, AVG(t.price) as avg_price
  FROM companies c
  JOIN trades t ON c.company_id = t.company_id
  WHERE t.trade_date = CURRENT_DATE
  GROUP BY c.name;
  

Database Design: ER Diagrams

An Entity-Relationship (ER) Diagram visually designs databases by showing:

• Entities (e.g., Customer, Order).
• Attributes (e.g., customer_id, order_date).
• Relationships (e.g., "a customer places many orders").

Example: Bank Loan System

erDiagram
    CUSTOMER ||--o{ LOAN : applies_for
    CUSTOMER {
        int customer_id PK
        string name
        date dob
    }
    LOAN {
        int loan_id PK
        int customer_id FK
        decimal amount
        date start_date
        decimal interest_rate
    }
    BANK {
        int bank_id PK
        string name
    }
    LOAN ||--|| BANK : issued_by

Worked Example: NTC’s Customer Billing NTC stores:

• Customers (connection details).
• Bills (monthly charges).
• Payments (transaction history).

erDiagram
    CUSTOMER ||--o{ BILL : receives
    BILL ||--o{ PAYMENT : has
    CUSTOMER {
        string connection_id PK
        string name
        string address
    }
    BILL {
        int bill_id PK
        string connection_id FK
        date month
        decimal amount
    }
    PAYMENT {
        int payment_id PK
        int bill_id FK
        date payment_date
        string method
    }

Exam Tip: How to Score Full Marks

1. Define Clearly:

   • Start answers with precise definitions (e.g., "A database is an organized collection of data...").
   • Use bullet points for lists (e.g., advantages of DBMS).

2. Draw Diagrams:

   • ER diagrams (3–5 marks) for database design questions.
   • Layered models (e.g., DBMS architecture) for "explain how DBMS works" questions.

3. SQL Queries:

   • Write correct syntax (e.g., SELECT * FROM table WHERE condition).
   • For trace questions, show step-by-step execution (e.g., how a JOIN works).

4. Real-World Links:

   • Connect theory to Nepali examples (e.g., "Like eSewa’s transaction logs, a normalized database...").
   • Mention performance trade-offs (e.g., "NoSQL is faster for big data but lacks SQL’s flexibility").

5. Common Pitfalls:

   • ❌ Don’t confuse database (data storage) with DBMS (software managing it).
   • ❌ Avoid vague terms like "store data efficiently" → specify normalization or indexing.
   • ❌ For comparisons (e.g., file vs. database), use a table with clear columns.

Practice Questions (From Past Exams)

1. Define database. Explain the advantages of database over file-based system.

   • Answer: A database is a structured collection of data stored electronically, managed by a DBMS. Advantages over file systems:
     • Reduced redundancy (data stored once).
     • Data integrity (constraints like NOT NULL).
     • Concurrency control (multiple users).
     • Backup and recovery (point-in-time restore).
     • Security (user permissions).

2. Describe different types of database models with suitable examples.

   • Answer:

     Model	Structure	Example	Use Case
     Hierarchical	Tree (parent-child)	Old IBM mainframes	Legacy systems
     Network	Graph (multiple parents)	Early airline reservations	Complex relationships
     Relational	Tables (rows/columns)	MySQL (eSewa), PostgreSQL (NEPSE)	Most business applications
     NoSQL	Key-value, documents, graphs	MongoDB (Pathao), Redis (Khalti)	Big data, real-time analytics

3. Explain the fundamental components of a DBMS.

   • Answer: A DBMS consists of:
     1. Query Processor: Parses and executes SQL.
     2. Optimizer: Chooses the fastest query plan.
     3. Storage Manager: Handles data storage/retrieval.
     4. Transaction Manager: Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).
     5. Security Manager: Controls access via roles (e.g., SELECT vs. UPDATE permissions).
     • Visual: