The Three-Level ANSI-SPARC Architecture

For our purposes, the fundamental point of these and later reports is the identification of three levels of abstraction, that is, three distinct levels at which data items can be described. The levels form a three-level architecture comprising an external, a conceptual, and an internal level . The way users perceive the data is called the external level. 

The way the DBMS and the operating system perceive the data is the internal level, where the data is actually stored using the data structures and file organizations . The conceptual level provides both the mapping and the desired independence between the external and internal levels. 

The objective of the three-level architecture is to separate each user’s view of the database from the way the database is physically represented. There are several reasons why this separation is desirable:

• Each user should be able to access the same data, but have a different customized view of the data. Each user should be able to change the way he or she views the data, and this change should not affect other users.
• Users should not have to deal directly with physical database storage details, such as indexing . In other words, a user’s interaction with the database should be independent of storage considerations.
• The Database Administrator (DBA) should be able to change the database storage structures without affecting the users’ views.
• The internal structure of the database should be unaffected by changes to the physical aspects of storage, such as the changeover to a new storage device. 
• The DBA should be able to change the conceptual structure of the database without affecting all users. 

External Level

The users’ view of the database. This level describes that part of the level database that is relevant to each user.

The external level consists of a number of different external views of the database. Each user has a view of the ‘real world’ represented in a form that is familiar for that user. 

The external view includes only those entities, attributes, and relationships in the ‘real world’ that the user is interested in. Other entities, attributes, or relationships that are not of interest may be represented in the database, but the user will be unaware of them. In addition, different views may have different representations of the same data. 

For example, one user may view dates in the form (day, month, year), while another may view dates as (year, month, day). Some views might include derived or calculated data: data not actually stored in the database as such, but created when needed. For example, in the DreamHome case study, we may wish to view the age of a member of staff. 

However, it is unlikely that ages would be stored, as this data would have to be updated daily. Instead, the member of staff’s date of birth would be stored and age would be calculated by the DBMS when it is referenced. Views may even include data combined or derived from several entities.

Conceptual Level

The community view of the database. This level describes what data level is stored in the database and the relationships among the data.

The middle level in the three-level architecture is the conceptual level. This level contains the logical structure of the entire database as seen by the DBA. It is a complete view of the data requirements of the organization that is independent of any storage considerations. The conceptual level represents:

• All entities, their attributes, and their relationships
• The constraints on the data
• Semantic information about the data
• Security and integrity information. 

The conceptual level supports each external view, in that any data available to a user must be contained in, or derivable from, the conceptual level. 

However, this level must not contain any storage-dependent details. For instance, the description of an entity should contain only data types of attributes (for example, integer, real, character) and their length (such as the maximum number of digits or characters), but not any storage considerations, such as the number of bytes occupied.

Internal Level

The physical representation of the database on the computer. This level level describes how the data is stored in the database.

The internal level covers the physical implementation of the database to achieve optimal runtime performance and storage space utilization. It covers the data structures and file organizations used to store data on storage devices. 

It interfaces with the operating system access methods (file management techniques for storing and retrieving data records) to place the data on the storage devices, build the indexes, retrieve the data, and so on. The internal level is concerned with such things as:

• Storage space allocation for data and indexes
• Record descriptions for storage (with stored sizes for data items)
• Record placement
• Data compression and data encryption techniques.

Below the internal level there is a physical level that may be managed by the operating system under the direction of the DBMS. However, the functions of the DBMS and the operating system at the physical level are not clear-cut and vary from system to system. 

Some DBMSs take advantage of many of the operating system access methods, while others use only the most basic ones and create their own file organizations. 

The physical level below the DBMS consists of items only the operating system knows, such as exactly how the sequencing is implemented and whether the fields of internal records are stored as contiguous bytes on the disk.