ANSI-SPARC Architecture

The ANSI-SPARC Architecture (American National Standards Institute, Standards Planning And Requirements Committee) is an abstract design standard for a Database Management System (DBMS), first proposed in 1975. Most modern commercial DBMS are based on this system. The ANSI-SPARC model never became a formal standard.

Three-level architecture

The objective of the three-level architecture is to separate the users’ view(s) of the database from the way that it is physically represented. This is desirable since:
*It allows independent customised user views.
** Each user should be able to access the same data, but have a different customised view of the data. These should be independent: changes to one view should not affect others.
*It hides the physical storage details from users.
**Users should not have to deal with physical database storage details. They should be allowed to work with the data itself, without concern for how it is physically stored.
*The database administrator should be able to change the database storage structures without affecting the users’ views.
**From time to time rationalisations or otherchanges to the structure of an organisation’s data will be required.
*The internal structure of the database should be unaffected by changes to the physical aspects of the storage.
**For example, a changeover to a new disk.
*The database administrator should be able to change the conceptual or global structure of the database without affecting the users.
**This should be possible while still maintaining the desired individual users’ views.

A modern perspective

Implementations of the ANSI-SPARC architecture at the external and conceptual levels were held back for decades by the lack of a SQL mechanism to create updateable views. Around 1998, database vendors offered triggers to support updateable views - finally allowing the implementation of a true three layer ANSI-SPARC architecture to support database applications. Grabczewski (2005) describes such an implementation in the United Kingdom.

A standard three level approach to database design has been agreed

- External level- Conceptual level- Internal level (includes physical data storage)

The 3 Level Architecture has the aim of enabling users to access the same data but with a personalised view of it. The distancing of the internal level from the external level means that users do not need to know how the data is physically stored in the database. This level separation also allows the Database Administrator (DBA) to change the database storage structures without affecting the users' views.

External Level (User Views)

A user's view of the database describes a part of the database that is relevant to a particular user. It excludes irrelevant data as well as data which the user is not authorised to access.

Conceptual Level

The conceptual level is a way of describing what data is stored within the whole database and how the data is inter-related. The conceptual level does not specify how the data is physically stored.

Internal Level

The internal level involves how the database is physically represented on the computer system. It describes how the data is actually stored in the database and on the computer hardware.

Database Schemas

There are three different types of schema corresponding to the three levels in the ANSI-SPARC architecture.

* The "external schemas" describe the different external views of the data and there may be many external schemas for a given database.

* The "conceptual schema" describes all the data items and relationships between them, together with integrity constraints (later). There is only one conceptual schema per database.

* At the lowest level, the "internal schema" contains definitions of the stored records, the methods of representation, the data fields, and indexes. There is only one internal schema per database.

The overall description of a database is called the "database schema".

References

* ANSI/X3/SPARC Study Group on Data Base Management Systems: (1975), "Interim Report. FDT", ACM SIGMOD bulletin. Volume 7, No. 2.

* cite book
last = Jardine
first = Donald A.
title = The ANSI/SPARC DBMS Model
publisher = North-Holland Pub. Co.
date = 1977
id = ISBN 0 7204 0719 2

* cite paper
last = Grabczewski
first = Edward
title = A Corporate Data Repository Supporting Scientific Research in the UK
publisher = Council for the Central Laboratory of the Research Councils (CCLRC)
date = 2005
id = http://epubs.cclrc.ac.uk/work-details?w=35241