The ANSI-SPARC Architecture, where ANSI-SPARC stands for American National Standards Institute, Standards Planning And Requirements Committee, is an abstract design standard for a Database Management System

Database management system

A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...

 (DBMS), first proposed in 1975
.

Most modern commercial DBMS are based on this system. The ANSI-SPARC model however 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 other changes 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.

Implementations of the ANSI-SPARC architecture at the external and conceptual levels were held back for decades by the lack of a SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

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.

Standard approach

A standard three level approach to database design has been agreed upon:

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

The Three 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.

Some important facts about this level are:

1. DBA works at this level.
2. Describes the structure of all users.
3. Only DBA can define this level.
4. Global view of database.
5. Independent of hardware and software.
   • 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.
• The internal schema at the lowest level 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.