Foundations Of Databases
DBMS Architecture
Chapter 2 said relational databases decouple queries from storage. This is the architecture that delivers it.
JrCodex·6 min read
Jr Codex DBMS Notes
Level: Beginner Prerequisites: Chapter 2: Data Models and the Relational Choice Time to complete: ~20 minutes
Table of Contents
- The Three-Level Schema
- Data Independence
- Inside the Engine
- The Path of a Query
- Deployment Shapes
- Summary & Next Steps
1. The Three-Level Schema
Chapter 2 said relational databases decouple queries from storage. This is the architecture that delivers it.
The Three Levels
─────────────────────────────────────────
EXTERNAL LEVEL what each user or application
(views) SEES. Many different views of
the same data — the payroll
app sees salaries, the
directory app does not.
▲
│ logical mapping
▼
CONCEPTUAL LEVEL the WHOLE database, logically:
(the schema) all tables, columns, types,
keys, constraints and
relationships. One per
database.
▲
│ physical mapping
▼
INTERNAL LEVEL how it is actually STORED:
(physical) file layout, page format, row
encoding, indexes, compression.
─────────────────────────────────────────
The Point of Separating Them
─────────────────────────────────────────
Each level can change without disturbing the one
above it, because the mapping absorbs the change.
That is not an architectural nicety. It is what
lets a database run for a decade while its
storage is rebuilt underneath it.
─────────────────────────────────────────
2. Data Independence
Two Kinds
─────────────────────────────────────────
PHYSICAL DATA INDEPENDENCE
Change the INTERNAL level; the conceptual level
is unaffected.
Add an index, switch the row format, move the
table to a different disk, partition it —
every existing query still works, unchanged.
This one is real, complete, and used daily.
LOGICAL DATA INDEPENDENCE
Change the CONCEPTUAL level; external views are
unaffected.
Add a column, split a table in two — and views
defined over it keep presenting the same shape
to applications.
This one is PARTIAL in practice. Adding a
column is safe; removing one that a view
depends on is not.
─────────────────────────────────────────
-- Physical independence, demonstrated.
SELECT name FROM customers WHERE city = 'Pune'; -- written once
CREATE INDEX idx_customers_city ON customers(city);
-- The query text does not change. The PLAN does, and it gets
-- dramatically faster (Module 5).-- Logical independence, demonstrated.
CREATE VIEW customer_summary AS
SELECT id, name, city FROM customers;
-- Later, customers gains 6 new columns and is split into two tables.
-- Redefine the VIEW; every application selecting from customer_summary
-- keeps working with no change.3. Inside the Engine
Every relational DBMS has roughly the same components. Knowing their names makes the rest of the curriculum navigable.
The Components
─────────────────────────────────────────
QUERY PROCESSOR
├─ PARSER SQL text ──► syntax tree,
│ validated against the catalog
├─ REWRITER expands views, simplifies
├─ OPTIMIZER chooses the execution PLAN
│ (Module 6)
└─ EXECUTOR runs the plan operator by
operator
STORAGE ENGINE
├─ BUFFER MANAGER caches disk pages in memory
│ (Module 5, Chapter 5)
├─ FILE MANAGER pages, heap files, layout
│ (Module 5, Chapter 1)
└─ INDEX MANAGER B+ trees, hash indexes
(Module 5, Chapters 2-3)
TRANSACTION MANAGER
├─ LOCK MANAGER concurrency control
│ (Module 7)
└─ RECOVERY MANAGER the write-ahead log
(Module 8)
CATALOG (system tables)
the database's description of ITSELF — tables,
columns, types, indexes, statistics
─────────────────────────────────────────
The Catalog Is Not a Footnote
─────────────────────────────────────────
The catalog stores metadata as ordinary TABLES,
which the database queries with its own SQL
engine.
It is also where the optimiser's STATISTICS live
— row counts, value distributions — and stale
statistics are one of the most common causes of a
suddenly-slow query (Module 6, Chapter 4).
─────────────────────────────────────────
-- The catalog is queryable. In PostgreSQL:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;4. The Path of a Query
From Text to Rows
─────────────────────────────────────────
"SELECT name FROM customers WHERE city='Pune'"
│
▼
[1] PARSE valid SQL? do these tables and
│ columns exist? (asks the CATALOG)
▼
[2] REWRITE expand views, apply rules
│
▼
[3] OPTIMIZE several correct plans exist.
│ Estimate the cost of each using
│ catalog STATISTICS; pick cheapest.
│ · scan all rows and filter?
│ · or use idx_customers_city?
▼
[4] EXECUTE run the chosen plan. Ask the
│ BUFFER MANAGER for pages; it
│ returns them from memory or reads
│ from disk.
▼
[5] RETURN rows to the client
─────────────────────────────────────────
The Step Worth Noticing
─────────────────────────────────────────
Step 3 is where a declarative language pays off.
You never said whether to use the index. The
optimiser decided, using statistics about the
data as it exists TODAY — and it will decide
differently next year when the table has grown,
without anyone editing the query.
Module 6 is entirely about this step.
─────────────────────────────────────────
5. Deployment Shapes
Three Architectures
─────────────────────────────────────────
EMBEDDED
The database is a LIBRARY inside your process.
No server, no network hop.
SQLite. Used by this curriculum through Module 6.
CLIENT-SERVER
A separate database process; clients connect
over a network.
PostgreSQL, MySQL, SQL Server.
Handles many concurrent clients, central
enforcement of rules and permissions.
DISTRIBUTED
Data spread across several machines, with
replication and/or sharding.
Scales past one machine, and pays for it in
complexity (Module 9).
─────────────────────────────────────────
Choosing
─────────────────────────────────────────
One process, one machine, local data
──► EMBEDDED. Genuinely a full database, with
none of the operations burden.
Multiple applications or users sharing data
──► CLIENT-SERVER. The default for anything
with a backend.
Data or load exceeds one machine
──► DISTRIBUTED, and read Module 9 first —
this step costs you joins, transactions,
or both.
─────────────────────────────────────────
6. Summary & Next Steps
Key Takeaways
- The three-level schema separates what users see, the logical design, and the physical storage, with mappings that absorb change at each boundary.
- Physical data independence is complete and used daily; logical data independence is real but partial — additive changes are safe, removals are not.
- The engine is a query processor, a storage engine, a transaction manager and a catalog, and each later module of this curriculum opens one of those boxes.
- The optimiser step is where declarative querying pays off: it re-decides how to run your unchanged query as the data changes underneath it.
Concept Check
- You add an index to a table. Which level of the three-level schema changed, and why does no application need editing?
- Why is logical data independence only partial in practice? Give a change that breaks it.
- Which engine component consults the catalog's statistics, and what goes wrong when those statistics are stale?
Next Chapter
→ Chapter 4: ACID — the Promise a Database Makes
Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index