Part 2 of 5 · 1 min read
The three levels
External, conceptual and internal, and what each one actually describes.
Think of a company with three different documents describing the same filing cabinet. One document lists what each department is allowed to see. Another lists every folder that actually exists. A third lists exactly which drawer and shelf each folder sits on.
In plain words: the internal level describes how data is actually stored: files, pages, indexes, pointers. The conceptual level describes the overall logical structure: the tables, columns and constraints, without saying how they are stored. The external level describes one user's or application's own view of the data, which can show, hide or reshape parts of the conceptual level. Each level can change without forcing the levels above it to change too.
A worked example. The bookstore's conceptual level says customers(customer_id, full_name, address) exists. Its internal level might store that table as a B+-tree indexed by customer_id, spread across several disk pages. An external level for the billing team might expose only customer_id and address, through a view, hiding full_name entirely.
The key lines. The external-level view from the example, as real SQL:
CREATE VIEW billing_customers AS
SELECT customer_id, address FROM customers;
Internal level, conceptual level, external level
The internal level is the physical storage: files, pages, indexes. The conceptual level is the logical schema: tables, columns, constraints, independent of storage. The external level is a user- or application-specific view built on top of the conceptual level, which may expose only part of it or reshape it.
Example: billing_customers is an external-level view; the customers table it is built from is the conceptual level; whatever index or file layout stores customers on disk is the internal level.