Database Systems: Models, Languages & Real-World Impact

TAKEAWAYS:

• A database organizes data persistently to minimize redundancy and maximize consistency, unlike file systems that store data in isolated files.
• The three-schema architecture (external, conceptual, internal) decouples user views from physical storage, enabling flexibility.
• DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language) are the three pillars of database languages, each serving distinct roles in schema design, data operations, and access control.
• The relational model (tables, rows, columns, keys) dominates modern databases due to its simplicity, query power (SQL), and support for integrity constraints.
• Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability), critical for financial systems like Khalti or Ncell billing.
• Real-world systems (e.g., eSewa’s user authentication, Daraz’s order processing) rely on these concepts to handle concurrent access, recover from failures, and enforce security.

1. What is a Database? Why Not Just Use Files?

A database is an organized collection of structured data stored electronically, designed to:

• Eliminate redundancy (e.g., storing customer details once in a bank’s database instead of in every transaction file).
• Enforce integrity (e.g., ensuring a bank account balance never goes negative).
• Support concurrent access (e.g., multiple users checking/updating eSewa balances simultaneously).
• Enable complex queries (e.g., "Find all Daraz orders over $50 placed in Kathmandu last month").

Comparison: File Systems vs. Databases

2. Database Models: How Data is Structured

Databases can be classified based on their data model, which defines how data is organized and accessed. The three primary models are:

A. Hierarchical Model

• Structure: Tree-like (parent-child relationships).
• Example: Early IBM IMS databases.
• Use Case: Legacy systems (e.g., some government record-keeping).
• Limitation: Inflexible for complex queries (e.g., "Find all orders for a customer who bought product X").

graph TD
    A["Root: Company"] --> B["Department: HR"]
    A --> C["Department: IT"]
    B --> D["Employee: Ram"]
    B --> E["Employee: Sita"]
    C --> F["Employee: Hari"]

B. Network Model

• Structure: Graph-like (many-to-many relationships via pointers).
• Example: CODASYL databases (used in old banking systems).
• Use Case: Complex relationships (e.g., a student enrolling in multiple courses, each with multiple instructors).
• Limitation: Complex to design and maintain.

C. Relational Model (Most Common Today)

• Structure: Tables (relations) with rows (tuples) and columns (attributes).
• Key Features:
  • Primary Key (PK): Uniquely identifies a row (e.g., employee_id).
  • Foreign Key (FK): Links tables (e.g., department_id in Employees references Departments).
  • Constraints: Ensures data integrity (e.g., NOT NULL, UNIQUE).
• Example: MySQL, PostgreSQL, SQLite (used in eSewa, Khalti, and most modern apps).

3. Three-Schema Architecture: Decoupling Users from Storage

The ANSI/SPARC architecture separates concerns into three layers:

classDiagram
    class ExternalSchema {
        +User-specific views
        +e.g., "Employee Salary Report"
    }
    class ConceptualSchema {
        +Logical design
        +e.g., tables, relationships
    }
    class InternalSchema {
        +Physical storage
        +e.g., indexes, file organization
    }
    ExternalSchema --> ConceptualSchema : "Mapped to (External/Conceptual)"
    ConceptualSchema --> InternalSchema : "Mapped to (Conceptual/Internal)"
    note for ExternalSchema "User views (e.g., HR, Finance)"
    note for ConceptualSchema "Logical schema (e.g., ER diagram)"
    note for InternalSchema "Physical schema (e.g., B-tree indexes)"

Why This Matters

• External Schema: Users see only what they need (e.g., a bank teller sees CustomerAccounts but not SystemLogs).
• Conceptual Schema: The "blueprint" of the database (e.g., Employees table with emp_id, name, salary).
• Internal Schema: How data is physically stored (e.g., B-trees for indexes, disk blocks).

Real-World Example: In eSewa, the external schema for a user shows only their transaction history, while the conceptual schema includes tables like Users, Transactions, and Merchants. The internal schema might use hashing for fast user lookups.

4. Database Languages: DDL, DML, DCL

Databases use three types of languages to manage data:

A. Data Definition Language (DDL)

• Purpose: Define the schema (structure) of the database.
• Commands: CREATE, ALTER, DROP, TRUNCATE.
• Example:

  CREATE TABLE Employees (
      emp_id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      salary DECIMAL(10, 2),
      dept_id INT,
      FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
  );
  

B. Data Manipulation Language (DML)

• Purpose: Retrieve, insert, update, or delete data.
• Commands: SELECT, INSERT, UPDATE, DELETE.
• Example:

  -- Insert a new employee
  INSERT INTO Employees (emp_id, name, salary, dept_id)
  VALUES (101, 'Ram', 50000, 10);
  
  -- Update salary
  UPDATE Employees SET salary = 55000 WHERE emp_id = 101;
  

C. Data Control Language (DCL)

• Purpose: Manage access and permissions.
• Commands: GRANT, REVOKE, DENY.
• Example:

  -- Grant select permission on Employees to 'HR'
  GRANT SELECT ON Employees TO HR_ROLE;
  

5. Schema vs. Instance

• Schema: The structure of the database (e.g., table definitions, constraints).
  • Example: The Employees table schema includes columns emp_id, name, etc.
• Instance: The actual data stored in the database at a point in time.
  • Example: A row INSERT INTO Employees VALUES (101, 'Ram', 50000, 10) is an instance.

Real-World Example: In Ncell’s billing system:

• Schema: Tables like Customers, Plans, Payments.
• Instance: A specific customer’s data (e.g., customer_id = 12345, plan = "Unlimited Data").

6. Database Management System (DBMS): The Engine Behind Databases

A DBMS is software that:

1. Stores data (e.g., MySQL, Oracle).
2. Processes queries (e.g., SQL commands).
3. Enforces security (e.g., user roles).
4. Recovers from failures (e.g., transaction logs).

Layers of a DBMS

7. ACID Properties: Why Transactions Matter

Transactions ensure reliable data operations in databases. The ACID properties are:

Real-World Example: When you book a ticket on NEPSE (Nepal Stock Exchange):

1. Atomicity: Deduct money from your account or cancel the order if funds are insufficient.
2. Isolation: Another user can’t buy the same share while your transaction is processing.
3. Durability: Your purchase is recorded permanently in the exchange’s database.

8. Data Dictionary: The Database’s Blueprint

A data dictionary (or metadata repository) stores:

• Table structures (columns, data types).
• Constraints (primary keys, foreign keys).
• Default values and indexes.
• User permissions.

Example Entry:

Why It Matters:

• Helps DBAs (Database Administrators) understand the schema.
• Used by tools like MySQL Workbench or pgAdmin to generate diagrams.

9. In the Real World

Example 1: eSewa (Digital Payments)

• Concept Used: Relational Model + Transactions
• How It Works:
  • Tables: Users, Transactions, Merchants.
  • Transaction: When you pay a merchant, eSewa:
    1. Deducts money from your User balance (atomicity).
    2. Adds the amount to the merchant’s MerchantWallet (consistency).
    3. Logs the transaction in Transactions (durability).
  • Concurrency: Multiple users can pay simultaneously without conflicts (isolation).

Example 2: Daraz (E-Commerce)

• Concept Used: Schema Design + Indexing
• How It Works:
  • Schema: Tables like Products, Orders, Customers, Inventory.
  • Indexing: The Products table is indexed by product_id and category for fast searches.
  • Real-Time Updates: When you place an order, Daraz:
    1. Checks Inventory (is stock available?).
    2. Updates Orders and CustomerOrders.
    3. Sends a confirmation email (triggered by a database event).

Example 3: Ncell (Telecom Billing)

• Concept Used: Normalization + Stored Procedures
• How It Works:
  • Normalization: The database avoids redundancy by separating Customers, Plans, and UsageRecords.
  • Stored Procedures: A procedure generate_bill(customer_id):
    1. Fetches the customer’s plan from Plans.
    2. Sums usage from UsageRecords.
    3. Calculates the bill and updates Billing.
  • Recovery: If the system crashes, Ncell’s DBMS uses transaction logs to replay uncommitted changes.

10. Exam Tip

What Examiners Look For

1. Definitions:
   • Clearly distinguish between schema (structure) and instance (data).
   • Define DDL, DML, and DCL with one example each.
2. Diagrams:
   • Draw a three-schema architecture or an E-R diagram for a simple system (e.g., Library with Books, Members, Loans).
   • Label primary keys and foreign keys explicitly.
3. Real-World Links:
   • Relate concepts to Nepali systems (e.g., "eSewa uses transactions for secure payments").
   • Avoid vague answers like "used in banking"; specify how (e.g., "ACID ensures atomic transfers").
4. Short Notes:
   • For questions like "Stored Procedures," explain:
     • What it is (precompiled SQL code stored in the database).
     • Why it’s used (reusability, security, performance).
     • Example: CREATE PROCEDURE UpdateSalary(IN emp_id INT, IN new_salary DECIMAL).

Common Pitfalls

• Mixing schema and instance: Don’t say "the database has 100 employees" (instance) when asked about schema.
• Ignoring constraints: In E-R diagrams, always mark weak entities and identifying relationships.
• Overcomplicating: For short-answer questions, stick to one clear example (e.g., "DDL is CREATE TABLE because it defines the table structure").

Sample Exam Answer (Schema vs. Instance)

Question: Differentiate between database schema and instance. Briefly describe DDL, DML, and DCL.

Model Answer: The database schema defines the structure of the database, including tables, columns, constraints, and relationships. For example, the schema for a Students table specifies columns like student_id (PK), name, and department. In contrast, the database instance refers to the actual data stored in the database at a given time, such as the row INSERT INTO Students VALUES (101, 'Ram', 'CS').

• DDL (Data Definition Language): Used to define the schema. Example:

  CREATE TABLE Students (student_id INT PRIMARY KEY, name VARCHAR(50));
  

• DML (Data Manipulation Language): Used to interact with data. Examples:

  INSERT INTO Students VALUES (101, 'Ram'); -- Adds data
  SELECT * FROM Students WHERE department = 'CS'; -- Retrieves data
  

• DCL (Data Control Language): Manages access permissions. Example:

  GRANT SELECT ON Students TO Faculty; -- Allows Faculty to view Students
  

Visual for Exam: