The ANSI/SPARC Three-Level Architecture
The three-level model is the reference vocabulary of database architecture. This lesson presents it as the standard defines it, and then examines honestly where real systems depart from it.
Beginner
Learning objectives
- Name the three schemas of the ANSI/SPARC model and state what each one describes
- Explain the two mappings and which independence each one provides
- Identify which design decisions belong to which schema
- Explain the main criticisms of the model and how real DBMSs depart from it
- Use the model's vocabulary correctly when discussing a database architecture
Prerequisites
- Database Management Systems: Definition and Functions
- Logical and Physical Data Independence
Introduction
In 1975 a study group of the American National Standards Institute, working under its Standards Planning and Requirements Committee (hence ANSI/X3/SPARC), published an interim report proposing a standard architecture for database management systems. The proposal had one purpose: to make databases portable and interoperable by separating what users see from how data are stored.
Fifty years later the standard has never been implemented in full, and no DBMS conforms to it exactly. It is nonetheless the vocabulary every textbook, every DBMS manual and every job interview uses. That is worth understanding precisely: the model is a reference architecture, not a description of any product.
The three schemas
The standard calls them schemas (not "levels"), and defines exactly three.
1. The external schema (or external view)
External schema
An external schema describes the part of the database that a particular user group needs, in the form that group needs it. There may be many external schemas; each is derived from the conceptual schema.
Key points:
- It is per user group, not per user. All desk operators share one external schema; all department directors share another.
- It may restructure: rename attributes, omit some, combine several tables into one, compute derived values, restrict rows.
- It does not describe storage, and it does not need to describe the whole database.
- It is the level at which security is expressed: what a group may see is exactly what its external schema contains.
In SQL, an external schema is realised by views (and, in some products, by column-level privileges).
2. The conceptual schema
Conceptual schema
The conceptual schema describes the whole database: all entities, all attributes, all relationships, all constraints, all integrity rules — with no reference to how anything is stored and no reference to who may see what.
Key points:
- There is exactly one conceptual schema per database. This is the heart of the model: a single, complete, implementation-independent description.
- It contains semantic information: not just "there is a table with a column called
socio", but "a loan must refer to an existing member, and a member may hold at most five open loans". - It is where design decisions live: which things are entities, which are attributes, which are relationships, what the keys are.
- It is the level that a conceptual E-R diagram describes — which is why the E-R model and ANSI/SPARC are studied together.
3. The internal schema
Internal schema
The internal schema describes how the data are stored: file organisation, record placement, index structures, compression, encryption, page size, buffer strategy.
Key points:
- There is exactly one internal schema per database (per physical copy).
- It is where performance decisions live: which index exists, whether a table is clustered, whether it is partitioned.
- It contains no semantics: nothing in it says what the data mean.
The two mappings
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ external 1 │ │ external 2 │ │ external 3 │ ← per user group
└──────┬──────┘ └──────┬──────┘ └──────┬──────┘
│ │ │
└──── external/conceptual mapping ─┘
│
┌────────▼────────┐
│ conceptual │ ← exactly one
└────────┬────────┘
│
┌────────▼────────┐
│ internal │ ← exactly one
└────────┬────────┘
│
stored database
| Mapping | Connects | Provides | Who maintains it |
|---|---|---|---|
| External/conceptual | Each external schema to the conceptual schema | Logical data independence | Views (declared in DDL) |
| Conceptual/internal | Conceptual schema to internal schema | Physical data independence | Storage engine + index definitions |
The mappings are the whole point. Without them, three descriptions would be three unrelated documents. With them, a change in one description can be absorbed by adjusting the mapping — which is exactly what data independence means.
Which decision belongs to which schema
This is the practical value of the model. Given a decision, the model tells you who owns it and what a change costs.
| Decision | Schema | Who decides | Cost of changing it |
|---|---|---|---|
| "A loan must refer to an existing member" | Conceptual | Designer | Migration + possible data cleanup |
| "Members are identified by a 6-character code" | Conceptual | Designer | Migration, affects every reference |
| "The desk operator cannot see the fiscal code" | External | Designer + security owner | Redefine a view and a grant |
| "Directors see monthly aggregates, not individual loans" | External | Designer | Redefine a view |
"Loans are indexed on dataInizio" |
Internal | Designer / DBA | CREATE INDEX — minutes |
"The prestito table is partitioned by year" |
Internal | DBA | Rebuild the table — hours, but no application change |
| "The engine is InnoDB" | Internal | DBA | Storage change, no application change |
Note the asymmetry that the model makes visible: decisions at the conceptual level are expensive to change; decisions at the internal level are cheap. This is not an accident of implementation — it follows from the fact that the conceptual schema is the shared contract, and the internal schema is a private implementation detail of the DBMS.
Where real systems depart from the model
The standard has been criticised since the 1980s, and the criticisms are instructive rather than destructive.
1. The conceptual schema rarely exists as an artefact
The model assumes a single, complete, formally maintained conceptual schema. In practice most systems have a set of tables generated by a framework, documented (if at all) by an E-R diagram that is out of date. The conceptual schema exists implicitly in the tables, but nobody maintains it as a description.
The consequence is real: without an explicit conceptual schema, there is no place to record the constraints that the tables cannot express, and no way to detect that two parts of the system model the same concept differently.
This is precisely the gap ERMap's editor exists to fill: the E-R diagram is the conceptual schema, and keeping it as a first-class artefact is a design discipline.
2. Most DBMSs implement two levels, not three
In MySQL, PostgreSQL and SQL Server:
- The internal schema is real but largely opaque: you influence it through indexes, engine choice and partitioning, but you do not write it.
- The conceptual schema is realised by tables, keys and constraints — a logical schema in the model's terms, because it already reflects the relational model.
- The external schema is realised by views.
So in practice you get: views → tables → storage. Three layers, but the middle layer is a relational schema, not a pure conceptual one. The pure conceptual level (entities, relationships, generalisations) lives outside the DBMS — in the E-R diagram, in the documentation, in the designer's head.
3. The mapping is not a separate artefact
The model imagines explicit mapping declarations. Real DBMSs derive the mappings from view definitions and index definitions; there is nothing extra to maintain. This is an improvement on the standard, not a violation of it.
4. Some systems have no external level
An application that queries base tables directly, with no views, has collapsed the external level into the conceptual one. This is legal and extremely common, and it forfeits logical data independence — the model explains exactly why.
5. NoSQL systems collapse the levels deliberately
Document and key-value stores typically have no conceptual/internal separation at all: the "schema" is the storage layout. This is a conscious trade: performance and flexibility in exchange for the guarantees the model provides. The next lesson examines that trade.
Reading a real system through the model
A library on MySQL. Which artefact implements which schema?
| Model element | Real artefact |
|---|---|
| External schema for desk operators | VIEW v_prestito_banco + GRANT SELECT ON v_prestito_banco TO 'desk' |
| External schema for directors | VIEW v_statistiche_mensili |
| External schema for members | VIEW v_miei_prestiti (filtered by session user) |
| Conceptual schema | CREATE TABLE socio/prestito/... with PRIMARY KEY, FOREIGN KEY, CHECK; documented by the E-R diagram |
| Internal schema | ENGINE=InnoDB, CREATE INDEX idx_prestito_dataInizio, partitioning by year |
| External/conceptual mapping | The SELECT inside each CREATE VIEW |
| Conceptual/internal mapping | The optimiser's translation of table access into index/page access |
Notice that the conceptual schema is split between the database (tables and constraints) and the documentation (the E-R diagram with entities, relationships, generalisations). Neither half is sufficient alone: the tables cannot express "a member may hold at most five open loans" as a declarative constraint in a portable way, and the diagram cannot be executed.
Assign the decision to the schema
For each decision, indicate which ANSI/SPARC schema owns it, who should decide, and what a later change would cost.
- A member's subscription type is Base, Plus or Premium.
- The desk application displays the member's name and current loans on one screen.
prestitohas 4 million rows and queries by date are slow.- The fiscal code must never be visible to desk operators.
- A loan's due date is 30 days after the start date.
- The database uses
utf8mb4character encoding. - The monthly report shows one row per department with the number of loans.
- A book can have several authors, in a defined order.
- Old loans are archived to a separate table after two years.
- Two members may not share the same e-mail address.
Show the commented solution
1. Conceptual. The set of allowed values is a domain constraint on an attribute — a statement about the real world. Designer decides. Changing it (adding Gold) is a small migration if the column is an ENUM, and a data migration if it is a lookup table — which is itself a conceptual design decision (is the subscription type an attribute or an entity?).
2. External. It describes what a user group sees and in what arrangement. Designer decides, possibly with UX input. Changing it costs a view redefinition and a front-end release.
3. Internal. It is a storage/performance question. Designer or DBA decides, guided by EXPLAIN. Changing it costs a CREATE INDEX — minutes, with no application change. Note that the symptom ("queries are slow") is not itself a schema decision; the fix is internal.
4. External (with a grant). Designer plus the security owner. Changing it costs a view redefinition and a privilege change. Note that the rule ("fiscal code is sensitive") is a conceptual/security requirement, but the mechanism that enforces it is external.
5. Conceptual. It is a business rule about the data. Designer decides. Note that expressing it is not trivial: it could be a CHECK if it can reference only the current row (MySQL cannot reference other columns in a CHECK before 8.0.16, and cannot reference other rows at all), a trigger, or an application rule. The conceptual rule is the same; only the enforcement mechanism changes — and that mechanism is an internal/logical decision.
6. Internal. Character encoding is a storage decision. DBA decides. Changing it on an existing database is expensive (a full rebuild), which is why it is usually chosen correctly once at the beginning.
7. External. An aggregated view for a specific user group. Designer decides. Changing it costs a view redefinition.
8. Conceptual. "Several authors in a defined order" is a relationship with an attribute (the position). It is one of the most important conceptual decisions in the whole model, and getting it wrong (e.g. storing autore1, autore2) is a classic error. Module 3 covers it in depth.
9. Internal — but with conceptual consequences. Archiving is a physical/storage strategy. However, if archiving changes what the applications see (old loans disappear from reports), it is no longer purely internal: the external schemas must be defined so that the change is invisible, or the change of meaning must be accepted explicitly. This is a good example of a decision that looks internal and is not entirely.
10. Conceptual. A uniqueness constraint on an attribute. Designer decides. Changing it (allowing shared mailboxes) is a migration: drop the constraint. Adding it to a database that already contains duplicates requires reconciling the data first — a data-quality task, not a schema task.
What this exercise teaches. The model's value is not classification for its own sake. It is knowing who owns the decision and what a change costs. Conceptual decisions are expensive; internal decisions are cheap; external decisions are the buffer that makes the other two survivable.
Common mistakes
- "The conceptual schema is the E-R diagram." The E-R diagram is a notation for the conceptual schema. In practice it is often the only artefact that records the conceptual schema — which is why keeping it current matters.
- "The three levels are three copies of the data." Only the internal schema holds data. The others are descriptions in the catalogue.
- "ANSI/SPARC is obsolete." The vocabulary is universal. Every DBMS manual describes views as external schemas and indexes as internal schema, whatever it calls them.
- "My application has no external schema, so the model does not apply." It has one: it is just collapsed into the conceptual schema, and logical independence is forfeited. The model diagnoses the situation precisely.
- "NoSQL systems do not follow the model, so the model is wrong." NoSQL systems make a deliberate trade against the model's guarantees. Understanding the model is what lets you evaluate the trade.
Summary
- The ANSI/SPARC model defines three schemas: external (per user group), conceptual (exactly one, complete, implementation-independent) and internal (exactly one, storage).
- Two mappings connect them: external/conceptual (provides logical independence, realised by views) and conceptual/internal (provides physical independence, realised by the storage engine).
- The model's practical value is assigning ownership: conceptual decisions are expensive to change, internal ones are cheap, external ones are the buffer.
- Real systems implement the model partially: the internal schema is opaque, the conceptual schema is split between tables and documentation, and many applications have no external level at all.
- The most common departure is the missing conceptual schema as an explicit artefact — the gap that a maintained E-R diagram fills.
Verification questions
- Why is there exactly one conceptual schema but many external schemas?
- Which mapping provides logical data independence, and which artefact implements it in SQL?
- A colleague says "we do not need views, our ORM handles the mapping". Which level of the model has been removed, and what has been lost?
- Explain why an index is an internal-schema object and a foreign key is a conceptual-schema object.
- Why is a change to the conceptual schema more expensive than a change to the internal schema?
Show the answers
-
Because the conceptual schema describes the whole database once, independently of who uses it — it is the single shared contract. External schemas are per user group because different groups need different subsets, arrangements and restrictions of the same database.
-
The external/conceptual mapping, implemented by views (
CREATE VIEW, and theGRANTs that restrict access to them). -
The external schema has been removed: the ORM maps objects directly to base tables. What is lost is logical data independence — every schema change that alters a table's shape becomes an application change, because there is no stable view layer between them. It is a legitimate choice for a small system, and it is a real cost that should be a conscious one.
-
An index says nothing about meaning: it says "access these rows quickly by this column", which is a storage decision with no semantic content. A foreign key says "this value must exist in another table", which is a statement of fact about the world — a rule that must hold regardless of how the data are stored. The model classifies them by what kind of statement they make.
-
Because the conceptual schema is the shared contract: applications, views, reports, integrations and other schemas all depend on it, so a change propagates. The internal schema is a private implementation detail of the DBMS: nothing outside depends on it, so it can be changed in isolation. This asymmetry is not a technical accident; it is the reason the three-level architecture exists.
Where to go next
- Logical and Physical Data Independence: the two kinds of independence the mappings provide, and the practical discipline that realises them.
- Relational and Non-Relational Databases: what happens to these levels when the model changes.
- The Phases of Database Design: the process that produces each schema.
- Database Management Systems: the components that maintain the mappings at run time.