Three-Level Architecture of a Database System (ANSI/SPARC): External, Conceptual, and Internal Levels

The three level architecture of a database system (often associated with the ANSI/SPARC approach) organizes a database into three “schemas” at different abstraction levels: external, conceptual, and internal. This separation supports data independence (so changes at one level need not force changes at higher levels).2

What each level represents

• External level: multiple user views (different applications may see different subsets of the same database).
• Conceptual level: the logical organization of the whole database (entities, relationships, constraints).
• Internal level: the physical storage and access methods used to implement the conceptual model (files, indexes, record layout).

A helpful mental model is a layered mapping:

Key idea: systems provide transformations/mappings so that:

• external views map to the conceptual schema, and
• conceptual schema maps to the internal schema—supporting logical and physical data independence.2

Terms you’ll use throughout this section

• External schema
• Conceptual schema
• Internal schema
• Logical data independence
• Physical data independence

These terms are the backbone of why the three-level architecture exists: to reduce ripple effects of design changes.2

1) External Level (External Schemas / Views)

At the external level, the database system presents one or more external schemas to different users/applications. Each external schema describes:

• only the relevant part of the data,
• often with different formatting or naming,
• and may hide irrelevant attributes.

Why external schemas matter

Different application requirements are naturally represented as different external views:

• e.g., a billing system sees customer/payment fields;
• an analytics system sees customer demographics and aggregated activity;
• an HR system sees employee records.

This reduces complexity for each user and improves security/permissions because users interact with their own views rather than the whole database.2

External-to-conceptual mapping (conceptual mediation)

External schemas do not typically store independent “copies of the truth.” Instead, mappings translate between each external schema and the conceptual schema.2

2) Conceptual Level (Conceptual Schema)

The conceptual level defines the logical structure of the entire database—what data exists and how it is logically related—independent of physical storage.

Typically, it includes:

• Entities and relationships
• Constraints
• overall data semantics that are meaningful to the organization.2

Conceptual schema as a “global model”

Unlike external schemas (which are per application), the conceptual schema is a single coherent description of:

• all major data,
• relationships between them,
• and integrity constraints that the DBMS must enforce.2

This provides the stable “contract” used for reasoning about the database logically.

3) Internal Level (Internal Schema / Physical Level)

At the internal level, the DBMS describes how the conceptual schema is implemented physically:

• record formats,
• file organization,
• index structures,
• and access methods (e.g., how queries locate records efficiently).2

This is where the system decides things such as:

• which indexes exist,
• which file structures store records,
• how data pages/blocks are laid out.

Why internal schemas matter

Physical implementation can vary widely without changing the logical meaning of the data. The architecture ensures those implementation decisions can evolve (performance tuning, storage changes) without rewriting every application view.

Data Independence: the main payoff

A major reason for separating the three levels is data independence.

Logical Data Independence

• You can change the conceptual schema (e.g., modify logical relationships, add derived structure, adjust conceptual constraints carefully)
• without requiring changes to external schemas (and therefore external applications).2

Physical Data Independence

• You can change internal storage (e.g., add/remove indexes, change file organization, reorganize pages)
• without requiring changes to the conceptual schema (and therefore not to most application logic).2

This is achieved by preserving mappings and using schema abstractions so higher levels remain stable.