Schemas & the Three-Schema (ANSI) Architecture

Three descriptions of the same data

A database can be described at three levels of abstraction, each produced in a different SDLC phase:

• Conceptual schema — a detailed, technology-independent specification of all organizational data: what data exist and how they relate, with no reference to any specific DBMS. Produced during the Analysis phase (as the detailed E-R model).
• Logical schema — the conceptual model transformed into the language of a specific data-management technology (for the relational model: tables, columns, primary keys, foreign keys). Produced during logical Design.
• Physical schema — how the logical schema is actually stored in secondary memory by a specific DBMS: file organization, indexing, storage structures. Produced during physical Design.

The separation between these levels is data independence: change the physical schema (e.g. add an index) without disturbing the logical schema, and change the logical schema without rewriting programs written against the conceptual view.

Worked example: the same fact at three levels

Conceptual:  A STUDENT has a GPA.            (technology-independent — just the fact)
Logical:     STUDENT(StudentID PK, GPA NUMERIC(3,2))   (relational tables/keys/types)
Physical:    STUDENT stored as a heap file; B-tree index on StudentID; GPA as 8-byte numeric

Moving down the list adds technology, never new facts — each level describes the same data.

The three-schema (ANSI/SPARC) architecture

Published by the ANSI/SPARC committee in 1978, the three-schema architecture defines three views of one underlying database:

• External schema — the user views: the tailored perspectives different users/managers see. (An HR clerk sees salaries; a customer sees only the product catalog — both drawn from one shared database.)
• Conceptual schema — a single, unified, technology-independent description that combines all external user views into one coherent definition of the enterprise’s data.
• Internal schema — the physical implementation; today it is split into a logical schema (the data in the target technology) and a physical schema (how it is stored by a particular DBMS).

All three describe the same data at different abstraction levels. This separation of concerns — users work in their external view, physical storage is optimized underneath — is the architectural basis for data independence.