ANSI-SPARC Architecture

The ANSI - SPARC architecture (also three- schema architecture, three- tier architecture or three-level schema architecture) describes the basic 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 has the aim of protecting, users of the database from the adverse effects of changes in the database structure.

The three levels are:

The advantages of the three-level model lie in the

• Physical independence, as separated from the internal and external of the conceptual level. Physical changes such of the storage medium or of the database product, do not affect the design or external level.
• Logical data independence, as the conceptual and external level are separated. This means that changes to the database structure (conceptual level) have no effect on the external level, so the mask layout, lists, and interfaces.

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

Example Data Warehouse

The differences between the three levels can be well illustrated by the data warehouse architecture.

In the external level, major aggregations are defined, their calculation is very time consuming.

The conceptual level defines the non-redundant base tables as dimension, fact and lookup tables.

On the internal level, the base tables are often created in denormalized form to enable performance -effective access to the stored data. In addition, often aggregation tables are set up. In order to retrieve the required aggregations quickly, all performance- intensive aggregations are calculated in the night. The results of the calculations are stored in the nighttime aggregation tables. When a user invokes an aggregation during the day, then the system can read the results within seconds from the aggregation tables. The aggregation tables inflate the volume of data on the internal level enormously. It is times greater than the volume of the base tables average of six. In addition, often a staging area will be established in which all imported from supply systems, data is first stored before they are enriched with further information and are finally inserted or added into the dimension and fact tables.