Three Levels of Data Abstraction in DBMS (External, Conceptual, Internal)

Data abstraction is a core principle of database systems: it separates how data is represented from how users perceive it and how it is stored. In the classic ANSI/SPARC three-level model, data is described at three abstraction levels: external level, conceptual level, and internal level. This separation supports data independence (changing storage or logical structure with minimal impact on applications), and enables different users to view the same data differently.
A convenient way to remember the split is: users see the external level, the DBMS logic/semantics are in the conceptual level, and physical details are in the internal level.

Below is a conceptual model (not a diagram of specific DBMS products) of how these levels relate.

Key idea: each level hides implementation details of the lower level while providing an interface to the layer above.

External Level (External Schemas / Views)

The external level describes how individual users or applications see the data. Each user can have a different schema (often called a view in practice) that includes only the relevant attributes and relationships.

External schema
View
User view

What it includes

• Specific tables/columns relevant to a user
• Renamed attributes, derived/calculated fields (depending on DBMS capabilities)
• Filtered rows (e.g., showing only active customers)
• User-facing semantics (what is “important” to that user)

Why it matters

• Security/privacy: users only see authorized fields
• Simplicity: each user works with a simplified representation
• Stability for applications: applications can keep working even if other users’ views or physical storage changes (within limits)

Conceptual Level (Conceptual Schema)

The conceptual level describes the overall logical structure of the database for the organization as a whole. It captures:

• Entities and their relationships (e.g., Customer–Order)
• Attributes and constraints (e.g., keys, referential integrity)
• High-level semantics independent of physical storage

Conceptual schema
Data model
Logical schema

What it includes

• Which entities exist and how they relate
• Constraints like primary keys and foreign keys
• Business rules expressed as constraints (e.g., “Order must reference a valid Customer”)

Why it matters

• It acts as a single source of truth for the database’s meaning
• It decouples user-visible views from how data is physically stored

Internal Level (Internal Schema / Physical Storage)

The internal level describes how data is actually stored on disk (or SSD), including access paths and storage structures. This level concerns:

• File organizations (e.g., heap vs sorted structures)
• Index structures
• Page/block layout
• Data representation details (e.g., compression, fixed/variable-length records)

Internal schema
Physical storage
Index structure

What it includes

• Record layout and placement
• Indexes (if any) and how they are maintained
• Storage parameters affecting performance

Why it matters

• Changes here can improve performance without rewriting application logic
• It enables query execution strategies and optimizations

How the Three Levels Work Together (Data Mappings)

The architecture relies on mappings between levels:

• External-to-conceptual mapping: defines how a user’s view corresponds to the logical model
• Conceptual-to-internal mapping: defines how logical data maps to physical storage

Mapping
Schema translation
Data independence

Data independence (the practical payoff)

• Logical data independence: changing the conceptual schema (e.g., adding an attribute carefully) ideally doesn’t require changes to external schemas or applications.
• Physical data independence: changing the internal schema (e.g., adding an index or changing file organization) ideally doesn’t require rewriting queries/views.