ANSI SPARC architecture

from Wikipedia, the free encyclopedia
Three-level schema architecture

The ANSI-SPARC architecture (also three-schema architecture , three-level architecture or three-level schema architecture ) describes the fundamental separation of different levels of description for database schemas .

The architecture was developed in 1975 by the Standards Planning and Requirements Committee (SPARC) of the American National Standards Institute (ANSI) and aims to protect the user of a database from the adverse effects of changes in the database structure.

The three levels are:

  1. The external level that provides users and applications with individual user views. Examples: forms, mask layouts, lists, interfaces.
  2. The conceptual level that describes what data is stored in the database and how they relate to each other. The design goal here is a complete and redundancy-free representation of all information to be saved. This is where the normalization of the relational database schema takes place.
  3. The internal level (also called the physical level) that represents the physical view of the database in the computer. It describes how and where the data is stored in the database. The design goal here is efficient access to the stored information. This is usually only achieved through a consciously accepted redundancy (e.g. the same data is stored in the index that is already stored in the table ).

The advantages of the three-level model are:

  • Physical data independence : The internal level is separated from the conceptual and external level. Physical changes, e.g. B. the storage medium or the database product, do not affect the conceptual or external level.
  • Logical data independence: The conceptual and external levels are separate. This means that changes to the database structure (conceptual level) have no effect on the external level, i.e. the mask layouts, lists and interfaces.

In general, therefore, one can speak of a higher robustness against changes.

Example data warehouse

The differences between the three levels can be easily explained using the data warehouse architecture.

Extensive aggregations are defined on the external level , the calculation of which is very time-consuming.

The conceptual level defines the redundancy-free basic tables as dimension, fact and lookup tables.

At the internal level, the basic tables are often created in a denormalized form in order to enable high-performance access to the stored data. In addition, aggregation tables are often set up. In order to be able to call up the required aggregations quickly, all performance-intensive aggregations are calculated during the night. The results of the nightly calculations are stored in the aggregation tables. If a user calls up an aggregation during the day, the system can read the results from the aggregation tables in seconds. The aggregation tables inflate the data volume on the internal level enormously. It is on average six times larger than the volume of the base tables. In addition, a staging area is often set up in which all data imported from supplier systems is initially cached before it is enriched with further information and finally inserted or supplemented in the dimension and fact tables.


  • Gunter Saake, Kai-Uwe Sattler, Andreas Heuer: Databases: Implementation Techniques . mitp Professional, Frechen 2011, ISBN 3-8266-9156-3 , p. 2 ff., 21 ff .
  • Ramez Elmasri, Shamkant B. Navathe: Basics of database systems. [Translator: Angelika Shafir] . Pearson Studium, Munich 2004, ISBN 3-8273-7136-8 , pp. 49 ff .
  • Theo Härder: database systems . Springer, Berlin 2001, ISBN 3-540-42133-5 , pp. 8-11 .