Database Files, DBMS, and SQL > From Commands to Bytes

When working with databases, it’s common to hear terms like App, SQL, DBMS, and Database used interchangeably. In reality, each plays a very distinct role in the data stack.

This article revisits and clarifies how these components work together, from a conceptual and practical perspective.

1. High-Level Architecture

At a high level, the interaction looks like this:

Each layer has a clear responsibility, and understanding these boundaries helps avoid confusion and design mistakes.

2. Application (App)

The Application is where users or services interact with data.

Responsibilities

• Accept input from users or other systems
• Generate SQL statements
• Send SQL to the DBMS via a database driver (JDBC, ODBC, etc.)
• Receive and process query results

Important Notes

• The application never directly accesses database files
• It communicates with the DBMS over a network or local socket
• The application does not decide how queries are executed

📌 Example:

SELECT * FROM users WHERE id = 10;

3. SQL (Structured Query Language)

SQL is a declarative language, not an execution engine.

What SQL Is

• A standardized language to interact with relational databases

• Used to:

  • Define schema (DDL)
  • Manipulate data (DML)
  • Manage permissions (DCL)
  • Control transactions (TCL)

What SQL Is Not

• ❌ It does not optimize queries
• ❌ It does not manage storage
• ❌ It does not execute operations itself

SQL simply describes what data is needed. The DBMS decides how to retrieve or modify it.

4. DBMS (Database Management System)

The DBMS is the core engine of the entire system.

Examples:

• PostgreSQL
• MySQL
• Oracle
• SQL Server

What Happens Inside the DBMS?

When the DBMS receives an SQL statement, it performs several steps:

Key Responsibilities

1. Parsing

   • Validate SQL syntax

2. Semantic Analysis

   • Check tables, columns, permissions

3. Query Optimization

   • Decide index scan vs full scan
   • Choose join strategies
   • Estimate cost using statistics

4. Execution

   • Perform CRUD operations

5. Transaction Management

   • ACID guarantees
   • Locks or MVCC
   • Crash recovery

✔️ Optimization with or without indexes happens inside the DBMS, not in the application.

5. Database (Files on Disk)

A database is ultimately a collection of physical files managed by the DBMS.

These Files Store

• Table data (rows)
• Index structures (B-tree, hash, etc.)
• Transaction logs (WAL / redo logs)
• System catalogs and metadata
• Free space maps

📌 Example (PostgreSQL-like structure):

/data
 └── base
     └── 16384
         ├── 2619   (table file)
         ├── 2620   (index file)

Critical Rule

Database files must never be accessed or modified directly by applications.

Only the DBMS understands their structure and consistency rules.

6. Logical vs Physical View

SQL operates on logical objects. The DBMS maps them to physical storage.

7. End-to-End CRUD Flow

Putting everything together:

1. Application sends SQL to DBMS
2. DBMS parses and validates SQL
3. DBMS optimizes the query (using indexes if available)
4. DBMS executes the query
5. DBMS reads/writes database files
6. DBMS returns results to the application

✅ App never touches files ✅ SQL never executes logic ✅ DBMS controls consistency, performance, and storage

8. Key Takeaway

SQL is the language, DBMS is the brain, database files are the memory.

Or simply:

SQL tells the DBMS what you want. The DBMS decides how to get it and where it lives.