Three Level Architecture of Database System:
The database system provides users with an abstract view of
data, by hiding certain details of how data is stored and manipulated.
Therefore, before designing a database, the data of an organization is
considered on abstract level.
Database system architecture means design or construction of database system.
The database system architecture provides general concept and structure of
database system. The architecture of most commercial database systems is based
on the three-level architecture proposed by DBTG.
The Three Level Architecture:
An early proposal for a standardized terminology (or vocabulary) and
architecture for database system were developed and published in 1971 by DBTG
(Data Base Task Group) appointed by CODASYL. A similar architecture and
terminology were developed and published in 1975 by SPARC (Standards Planning
and Requirements Committee) of American National Institute (ANSI). As a result
of these and later reports, databases can be viewed at three levels, known as
external, conceptual and internal levels. These levels form three-level
architecture. These levels are represented by three schemas or three models
known as three-level schema. The models refer to the structure of database, not
to the data that is stored in any given time or moment. The structure of
database is called the intension of the database, or the database schema. The
data stored into the database at a given time is called the extension of
database, or a database instance. A database model is a collection of tools that
may include diagram and specialized vocabulary (or special words) for describing
the structure of database. Typically a database model describes the data, the
relationships between the data items, constraints on the data, and data meanings
The purpose of the three-level architecture is to
separate the user applications and the physical database. The reasons of this
(i) Different users need different views of the same data.
(ii) Users should not have to deal directly with the physical database storage
(iii) The DBA should be able to change the database storage structure or storage
device without affecting other user's views and applications.
Figure 2.1 shows the three-level architecture of the database system.
(i) External Level:
The external level or view is closest to the users. It is concerned with the way
the data is viewed by individual users. You can say that external level is the individual user level. .A user can either be an
application programmer or an end-user, but DBA is an important special case.
The external level consists of many different external views or external models
of the database. Each external view describes the part of the database that a
particular user group is interested in and hides the rest of the database from
that user group. In addition, different views may have different representations
of the same-data. For example, one user may view date in the form (day, month,
year), while another may view date as (year, month, day). Some users may view
virtual or calculated data, which is not actually stored in database. The
calculated data is created temporarily when needed. For example, marks of
subject "C++" of students are stored in the database and average marks of this
subject are calculated by system when the user refers to it. Similarly, if the
date-of-birth of a student is stored in the database then you can find the age
of the student.
A view may include data combined or calculated from several records. An external
record is a record as seen by a particular user, which is a part of his/her
external view. Actually, an external view is a collection of external records.
The external views are defined by means of external schemas (also called
subschemas) that are written in the data definition language (DDL). Usually, the
DBA writes an external schema to create a user view. Each user's schema gives a
complete description of each type of external record that appears in that
user's view. The external schemas are compiled by the DBMS and stored in its
data dictionary. The DBMS uses the external schema created for a specific user,
to create a user interface to access data of the database. Thus user-interface
created through external schema accepts and displays information in the format
the user expects. It also acts as barrier to hide the information of database
from users that is not permitted to him. Typically, it hides the conceptual,
internal, and physical details from the user. If any user needs some advance
access of database such as to change the existing record of database then the
external schema for that user is re-written (modified) by DBA to allow access to
(ii) Logical or Conceptual Level:
The middle level in the three-level architecture is the logical or conceptual
level. It describes the entire structure of database such as entities,
attributes, data types, relationships, constraints on the data, and user
operations. It hides the details of physical storage structures.
The conceptual level supports the external level to present the data to
end-users as they need. This level is relatively constant and the DBA designs it
after determining the present and future information needs of the organization.
However, to expand the conceptual level, the DBA adds new objects to fulfill the
requirements of the organization, without affecting the external level.
The conceptual level is defined by means of the
conceptual schema, which includes definitions of each of the various conceptual
record types. The conceptual schema is a complete description of information of
database structure such as every record type with all its fields. It also
includes security and integrity rules. The conceptual schema is written in DDL,
compiled by the DBMS and stored in its data dictionary. The DBMS uses the
conceptual schema to create the logical record interface, which defines and
creates the working environment for the conceptual level to present data to
end-users. Actually, conceptual level is a collection of logical records.
(iii) Internal or Physical Level:
The internal or physical level describes the physical storage structures of
database and file organizations used to store data on physical storage devices.
The internal level describes how the data is stored in the database. It works
with operating system and DBMS for storing and retrieving data to and from the
Although, both internal level and physical level is considered as a single
level, but there is slight difference between them. Actually, physical level in
one that is managed by the operating system under the direction of DBMS, while
the internal level is managed by DBMS.
An internal record is a single stored record. It is a storage unit that is
passed up to the internal level through stored record interface. The stored
record interface is the boundary between the physical level (for which the
operating system may be responsible) and the internal level (for which the DBMS
is responsible). This interface is provided to the DBMS by operating system. In
some cases, the DBMS itself may create this interface. The physical level below
this interface consists of data of database, which is stored and managed by
operating system. The operating system creates the physical record interface to
access the data of database stored on storage devices.
The internal level is described by means of the internal schema, which
defines how data is represented, how records are sequenced, what indexes exist
etc. The internal schema is written in DDL.