3-level architecture

The term architecture refers to a high-level view of the structure of a system. It can be used at different levels of abstraction - for example, we can talk about a client-server architecture when thinking about the structure of an application. A client-server application typically includes a database which in some cases may account for the entire server component; however, at a lower level, we can also use the term when thinking about the structure of the database itself.

The accepted standard architecture for most relational databases is the three-level ANSI-SPARC model shown in the diagram below. ANSI-SPARC stands for the American National Standards Institute Standard Planning and Requirement Committee.

External level

A user is anyone who needs to access the data including end users (business users), application programmers and database administrators. One group of users may require only a subset of the information; another group may need information to be presented in a particular way. For example, a university accommodation service might need access to limited information about students to check their eligibility and to lists of available rooms and flats. Academic tutors require different information about students and are concerned about modules and programmes rather than accommodation. All information would be stored in a comprehensive university database, but different users require different views.

Conceptual level

Different user needs must be supported by an overall database design which constitutes a model of the real world, and which must also be efficient and internally consistent. The job of the database designer is to abstract this model from information collected from users, and to ensure that it is correctly structured. This is the level where we will concentrate out efforts in this module.

Internal level

With very few exceptions, all data in computer systems is stored in files on disks, and the way in which data is spread across files and disks can have a significant impact on the efficiency of a database. The internal view is concerned with these low-level storage issues and describes what files exist and how they are managed. This level is of more concern to the database administrator (DBA) than to the end-user or application programmer.

Mappings

When a user (of any kind) interacts with the database, they will do so using their own view of the data. Any view-specific terms they use need to be mapped onto the shared conceptual structure. References to objects at the conceptual level must be resolved into file access requests at the internal level in order of the user's initial request to succeed. The task of mapping from one level to another is carried out transparently by the database management system (DBMS). Changes at one level - such as a file reorganisation by the DBA - entails a change to the mapping from the modified level to the next; however, the structure of the next level does not change - this is the basis of data independence.