DBMS Basics: Systems, Architecture, and Data Management

TAKEAWAYS:

• A database is an organized collection of structured data, while a DBMS (Database Management System) is software that interacts with the database to store, retrieve, and manage data.
• The three-schema architecture (external, conceptual, internal) ensures logical and physical data independence, allowing changes in storage or schema without affecting applications.
• DBMS provides advantages like data integrity, security, concurrency control, and reduced redundancy, but also has disadvantages such as complexity and cost.
• Data models (hierarchical, network, relational) define how data is organized and related, with the relational model being the most widely used today.
• DDL (Data Definition Language) defines the database schema, while DML (Data Manipulation Language) is used for querying and modifying data.
• Real-world applications like eSewa (transaction processing), Khalti (data security), and NEPSE (stock market data management) rely on DBMS principles.

1. What is a Database?

A database is an organized collection of structured data stored electronically. It allows efficient storage, retrieval, and management of data. Databases are used in almost every industry, from banking to healthcare, to manage large volumes of information.

Types of Databases

1. Relational Databases (RDBMS) – Data stored in tables (e.g., MySQL, PostgreSQL).
2. NoSQL Databases – Flexible schema for unstructured data (e.g., MongoDB, Cassandra).
3. Object-Oriented Databases – Store data as objects (e.g., db4o).
4. Graph Databases – Optimized for relationships (e.g., Neo4j).

2. What is a Database Management System (DBMS)?

A DBMS is software that interacts with the database to perform operations like:

• Data storage and retrieval
• Data security and integrity
• Concurrency control (handling multiple users)
• Backup and recovery

Examples of DBMS

• MySQL (Open-source RDBMS)
• Oracle Database (Enterprise-grade)
• Microsoft SQL Server (Used in Windows environments)
• MongoDB (NoSQL database)

3. Three-Schema Architecture

The three-schema architecture ensures data independence by separating concerns into three layers:

stateDiagram-v2
    [*] --> ExternalSchema: "User Views"
    ExternalSchema --> ConceptualSchema: "Mapped via External Schema"
    ConceptualSchema --> InternalSchema: "Mapped via Conceptual Schema"
    InternalSchema --> [*]: "Physical Storage"

Key Components

Why is this important?

• Logical Data Independence: Changes in the conceptual schema do not affect external schemas.
• Physical Data Independence: Changes in storage (internal schema) do not affect conceptual or external schemas.

Visual representation of the three-schema model (Image: Fred the Oyster iThe source code of this SVG is valid. This , Public domain, via Wikimedia Commons)

4. Advantages and Disadvantages of DBMS

Advantages

✅ Data Integrity – Ensures accuracy and consistency. ✅ Reduced Redundancy – Eliminates duplicate data. ✅ Concurrency Control – Handles multiple users efficiently. ✅ Security – Access control and encryption. ✅ Backup and Recovery – Protects against data loss.

Disadvantages

❌ Complexity – Requires expertise to design and manage. ❌ Cost – Licensing and maintenance expenses. ❌ Performance Overhead – Some operations may be slower than file systems.

5. Data Models

A data model defines how data is organized and related. The three main types are:

6. DDL vs. DML

Example of DDL:

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

Example of DML:

INSERT INTO Students (student_id, name, email)
VALUES (1, 'John Doe', 'john@example.com');

7. Database System Architecture

A typical DBMS architecture consists of:

graph TD
    A["User"] --> B["Application Program"]
    B --> C["Query Processor"]
    C --> D["Storage Manager"]
    D --> E["Database Files"]
    E --> F["OS"]

Key Components

1. User Interface – Allows users to interact (e.g., SQL queries).
2. Query Processor – Parses and executes queries.
3. Storage Manager – Handles data storage and retrieval.
4. Database Files – Physical storage of data.
5. Operating System – Manages hardware resources.

In the Real World

1. eSewa (Nepal)

   • Uses a relational database to store user transactions, bill payments, and financial records.
   • Three-schema architecture ensures that changes in backend storage (e.g., switching from MySQL to PostgreSQL) do not break the user interface.

2. Khalti (Digital Payment System)

   • Relies on DBMS for security and concurrency control when multiple users make transactions simultaneously.
   • Stored procedures are used to validate transactions before processing.

3. NEPSE (Stock Market Database)

   • Uses a high-performance DBMS to manage real-time stock data, ensuring fast retrieval and updates.
   • Data integrity constraints prevent invalid trades (e.g., negative shares).

Worked Example: Bank Loan Interest Calculation Suppose a bank uses a DBMS to store loan records. The three-schema architecture ensures:

• External Schema: A loan officer sees only customer details and interest rates.
• Conceptual Schema: The database stores loan amounts, interest rates, and repayment schedules.
• Internal Schema: Data is stored in optimized tables for fast queries.

If the bank changes its storage engine (e.g., from MySQL to Oracle), the external and conceptual schemas remain unchanged, ensuring logical data independence.

Exam Tip

• Define clearly: Always define database and DBMS with examples.
• Three-schema architecture: Draw and explain the three layers (external, conceptual, internal).
• DDL vs. DML: Give SQL examples for both.
• Advantages/Disadvantages: List at least 3 of each with brief explanations.
• Real-world applications: Relate concepts to eSewa, Khalti, or NEPSE in exam answers.

Physical hardware where DBMS software runs (Image: Aaron Hall, CC BY-SA 2.0, via Wikimedia Commons)