Database Systems: Data, DBMS, Users, Models & 3-Schema Architecture

TAKEAWAYS:

• Understand the data hierarchy (bits → fields → records → files → databases) and how it maps to real tables like Customer in Ncell’s billing system.
• Learn the three types of database users (naive, sophisticated, and stand-alone) and their roles in apps like Daraz’s inventory vs. customer-facing interfaces.
• Master the three data models (hierarchical, network, relational) and why relational dominates today (e.g., MySQL in banks).
• Visualize the ANSI/SPARC 3-schema architecture (external → conceptual → internal) using the example of NEPSE’s stock data layers.
• Compare file-based systems vs. DBMS using a table, highlighting why DBMS wins in concurrency and integrity (e.g., Kathmandu traffic route updates).
• Know five key DBMS functions (data definition, manipulation, control, security, recovery) and how they apply to eSewa’s transaction logs.

1. What is Data? The Building Blocks of a Database

Data is the raw facts stored in a computer system, organized to be useful. It comes in layers:

graph TD
    A["Bits (0/1)"] --> B["Bytes (8 bits)"]
    B --> C["Fields/Attributes\n(e.g., SID, SName)"]
    C --> D["Records/Tuples\n(e.g., {101, 'Ramesh'})"]
    D --> E["Files/Relations\n(e.g., Student table)"]
    E --> F["Database\n(e.g., University_DB)"]

Example in Nepal:

• Ncell’s database stores records like {CustomerID: 12345, Name: 'Ram', Phone: 9800000000, Balance: 500} as a file/table in its relational DBMS.
• eSewa uses fields like TransactionID, Amount, Status to track payments.

2. Database vs. File-Based Systems: Why DBMS Wins

Real-World Tie-In:

• Kathmandu Traffic Police used to track violations in Excel files (single-user, no backups). Now, they use a DBMS to log fines, vehicles, and violations in real time—reducing errors by 90%.
• Pathao’s ride-hailing system relies on a DBMS to:
  1. Store driver locations (spatial data).
  2. Match riders to drivers (concurrency control).
  3. Process payments (transaction integrity).

3. Database Users: Who Interacts with the System?

Three types of users access a DBMS, each with different needs:

Examples in Nepal:

1. Naive User: A Khalti app user who checks their balance (SELECT balance FROM Account WHERE user_id = 123;).
2. Sophisticated User: A NEPSE analyst running queries like:

   SELECT stock_symbol, AVG(price)
   FROM Trades
   WHERE trade_date BETWEEN '2023-01-01' AND '2023-12-31'
   GROUP BY stock_symbol;
   

3. Stand-Alone User: A local grocery shop using Excel to track inventory (no DBMS).

4. Data Models: How Data is Structured

Three classic models explain how data is organized. Relational dominates today:

Visual: Relational Model

erDiagram
  STUDENT ||--o{ STUDIES : "takes"
  STUDIES }|--|| COURSE : "enrolled_in"
  STUDENT {
    string SID PK "Student ID"
    string SName "Full Name"
    string SAddress "Address"
  }
  COURSE {
    string CID PK "Course ID"
    string CName "Course Name"
    int Credit_hours "Credits"
  }
  STUDIES {
    string SID PK,FK
    string CID PK,FK
    int Semester
  }

Relational model with STUDIES junction table (many-to-many) Why Relational Wins:

• eSewa uses relational tables for:
  • Users (user_id, name, email)
  • Transactions (tx_id, user_id, amount, status)
  • Accounts (account_id, user_id, balance)
• Ncell stores:
  • Customers (cid, name, phone)
  • Calls (call_id, cid, duration, timestamp)

5. ANSI/SPARC 3-Schema Architecture: The Database "OS"

Databases are designed in three layers, like an operating system:

graph TD
    A["External Schema\n(User views)\n(e.g., Student portal sees only grades)"] --> B["Conceptual Schema\n(Logical design)\n(e.g., ER diagram of University_DB)"]
    B --> C["Internal Schema\n(Physical storage)\n(e.g., Indexes, file locations)"]
    A -->|"Mapped via"| D["External Schema\nto Conceptual Schema"]
    B -->|"Mapped via"| E["Conceptual Schema\nto Internal Schema"]
    C -->|"Implemented by"| F["DBMS\n(MySQL, Oracle)"]

Example: NEPSE’s Stock Database

1. External Schema:
   • Trader view: Only sees stock_symbol, price, volume.
   • Admin view: Sees Trades, Users, Brokerages.
2. Conceptual Schema:
   • ER diagram with entities: Trader, Stock, Trade, Brokerage.
3. Internal Schema:
   • Tables stored on SSDs with indexes on trade_id and stock_symbol.

Why This Matters:

• Daraz uses this to:
  • Show customers only product details (external).
  • Hide inventory and supplier data (conceptual).
  • Optimize storage (internal, e.g., sharding by region).

6. Database Languages: SQL and DDL/DML/DCL

Databases use three types of languages:

Worked Example: NTC’s Network Logs

-- DDL: Create a table for network alerts
CREATE TABLE NetworkAlerts (
    alert_id INT PRIMARY KEY,
    device_id VARCHAR(50),
    alert_type VARCHAR(50),
    timestamp DATETIME,
    status VARCHAR(20)
);

-- DML: Insert a new alert (e.g., fibre cut in Kathmandu)
INSERT INTO NetworkAlerts (alert_id, device_id, alert_type, timestamp, status)
VALUES (101, 'FIBER-KTM-01', 'Link Down', '2023-10-15 14:30:00', 'Open');

-- DCL: Grant a technician access to view alerts
GRANT SELECT ON NetworkAlerts TO 'tech_kathmandu';

7. DBMS Functions: What a DBMS Actually Does

A DBMS performs five core functions, visualized below:

mindmap
  root((DBMS Functions))
    Data Definition
      CREATE, ALTER, DROP
    Data Manipulation
      SELECT, INSERT, UPDATE, DELETE
    Data Control
      Concurrency, Recovery
    Security
      Authentication, Authorization
    Integrity
      Constraints, Triggers

Real-World Application: Bank Loans

1. Data Definition: Define Loan table with loan_id, customer_id, amount, interest_rate.
2. Data Manipulation: Calculate monthly payments:

   SELECT customer_id, amount, (amount * interest_rate / 100) AS monthly_payment
   FROM Loans
   WHERE status = 'Approved';
   

3. Data Control: Ensure only one user updates a loan at a time (concurrency).
4. Security: Only loan officers can UPDATE the status field.
5. Integrity: Enforce CHECK (interest_rate BETWEEN 5 AND 15).

8. Database Recovery: Handling Crashes

Databases use three methods to recover from failures:

Example: Daraz Order Processing

1. User places order → DBMS writes to transaction log.
2. System crashes → On restart, DBMS replays logs to recover unsaved orders.
3. Partial update (e.g., inventory deducted but payment failed) → Rollback cancels the order.

In the Real World

1. eSewa’s Transaction System

   • Idea Used: Relational model + 3-schema architecture
   • How: Stores Users, Transactions, and Accounts as separate tables. The external schema shows users only their transactions, while the conceptual schema links all tables via user_id. The internal schema optimizes storage (e.g., indexes on tx_id).

2. Ncell’s Billing Database

   • Idea Used: Data hierarchy + DBMS functions
   • How:
     • Bits → Bytes: Phone numbers stored as VARCHAR(15).
     • Fields → Records: Each call is a record in the Calls table.
     • DBMS Functions: Uses DML to calculate bills (SELECT SUM(duration) FROM Calls WHERE user_id = 123;), DCL to restrict access (GRANT SELECT ON Bills TO 'customer_123'), and recovery to handle power outages.

3. NEPSE’s Stock Trading Platform

   • Idea Used: 3-schema architecture + concurrency control
   • How:
     • External Schema: Traders see only stock_symbol, price, volume.
     • Conceptual Schema: Links Traders, Stocks, and Trades via trade_id.
     • Concurrency: Ensures two traders can’t buy the same share simultaneously (row-level locking).

Exam Tip

1. Diagrams Are Worth Marks:
   • Always draw the 3-schema architecture or an ER diagram for questions on design.
   • Example: For a hospital DB, show:
     • External: Doctor sees only Patients and Prescriptions.
     • Conceptual: Entities Doctor, Patient, Appointment with relationships.
     • Internal: Tables stored with indexes on patient_id.

2. SQL Queries = Easy Marks:

   • Practice INSERT, SELECT, and JOIN questions. For example:

     Given tables Account(ac_no, balance) and Customer(cid, name), write SQL to insert a new customer and link their account.

     INSERT INTO Customer VALUES (101, 'Ram', 'Kathmandu');
     INSERT INTO Account VALUES ('ACC101', 5000);
     

3. Compare File-Based vs. DBMS:

   • Exams often ask: "Why use a DBMS instead of Excel?"
   • Answer: Use a comparison table (as above) and mention concurrency, integrity, and recovery.

4. Real-World Examples:

   • Tie answers to Nepali systems (e.g., "Like eSewa’s transaction logs, a DBMS uses transaction logs for recovery").
   • Avoid generic answers like "used in banking"—specify how (e.g., "relational model for accounts").

5. Constraints and Normalization:

   • Even though this is Unit 1, exams sometimes ask:

     "Define four database constraints with examples."

     • Answer:
       1. Primary Key: SID in Student table (unique identifier).
       2. Foreign Key: SID in Enrollment table (links to Student).
       3. NOT NULL: Email in Customer table (cannot be empty).
       4. CHECK: Age > 18 for Voter table.