Three Level Architecture of Database Systems (DBMS)

The architecture is a framework for describing database concepts and specifying the structure of database system. The three level architecture was suggested by ANSI/SPARC. Here database is divided into three levels external level, conceptual level and internal level as shown in  Figure  1.7.

1. Levels or Views

The three levels or views are discussed below:

  • Internal Level : Internal level describes the actual physical storage of data or the way in which the data is actually stored in This level is not relational because data is stored according to various coding schemes instead of tabular form (in tables). This is the low level representation of entire database. The internal view is described by means of an internal schema.

The internal level is concerned with the following aspects:

    • Storage space allocation
    • Access paths
    • Data compression and encryption techniques
    • Record placement

The internal level provides coverage to the data structures and file organizations used to store data  on  storage  devices.

  • Conceptual Level : The conceptual level is also known as logical level which describes the overall logical structure of whole database for a community of This level is relational because data visible at this level will be relational tables and operators will be relational operators. This level represents entire contents of the database in an abstract form in comparison with physical level. Here conceptual schema is defined which hides the actual physical storage and  concentrate  on  relational  model  of  database.
  • External Level : The external level is concerned with individual This level describes the actual view of data seen by individual users. The external schema is defined by the DBA for every user. The remaining part of database is hidden from that user. This means user can only access data of its own interest. In other words, user can access only that part of database for which he is authorized by DBA. This level is also relational or very close to it.

2.  Different Mappings in Three Level Architecture of DBMS

The process of transforming requests and results between the three levels are called mappings. The database management system is responsible for this mapping between internal, external and conceptual schemas.

There are two types of mappings:

  1. Conceptual/Internal
  2. The External/Conceptual
  1. The Conceptual/Internal Mapping : This mapping defines the correspondence or operations between the conceptual view and the physical view. It specifies how the data is retrieved from physical storage and shown at conceptual level and vice-versa. It specifies how conceptual records and fields are represented at the internal It also allows any differences in entity names, attribute names and their orders, data types etc., to be resolved.
  2. The External/Conceptual Mapping : This mapping defines the correspondence between the conceptual view and the physical It specifies how the data is retrieved from conceptual level and shown at external level because at external level some part of database is hidden from a  particular user  and even  names of  data fields  are changed  etc.

There could be one mapping between conceptual and internal level and several mappings between external and conceptual level. The physical data independence is achieved through conceptual/internal mapping while the logical data independence is achieved through external/ conceptual mapping. The information about the mapping requests among various schema levels are included in the system catalog of DBMS. When schema is changed at some level, the schema at the next higher level remains unchanged, only the mapping between the two levels is changed.

3.  Advantages of Three-level Architecture

The motive behind the three-level architecture is to isolate each user’s view of the database from the way the database is physically stored or represented. The advantages of the three- level architecture  are  as  follows  :

  1. Each user is able to access the same data but have a different customized view of the data as  per  the requirement.
  2. The changes to physical storage organization does not affect the internal structure of the e.g., moving the database to a new storage device.
  3. To use the database, the user is no need to concern about the physical data storage details.
  4. The conceptual structure of the database can be changed by the DBA without affecting any user.
  5. The database storage structure can be changed by the DBA without affecting the user’s view.

4. Data Independence

It is defined as the characteristics of a database system to change the schema at one level without having to change the schema at the next higher level. It can also be defined as the immunity of the application programs to change in the physical representation and access techniques of the database. The above definition says that the application programs do not depend on any particular physical representation or access technique of the database. The DBMS achieved the data independence by the use of three-level architecture. The data independence is of TWO types:

  1. Physical Data Independence : It indicates that the physical storage structures or devices used for storing the data could be changed without changing the conceptual view or any of the external Only the mapping between the conceptual and internal level is changed. Thus, in physical data independence, the conceptual schema insulates the users from changes in  the  physical  storage  of  the  data.
  2. Logical Data Independence : It indicates that the conceptual schema can be changed without changing the existing external schemas. Only the mapping between the external and conceptual level is changed and absorbed all the changes of the conceptual schema. DBMS that supports logical data independence, changes to the conceptual schema is possible without making any change in the existing external schemas or rewriting the application programs. Logical data independence also insulates application programs from operations like combining of two records into one or splitting an existing record into more than one records.

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Database Management System, 2nd Edition-University Science Press (2017)

Leave a Reply

Your email address will not be published. Required fields are marked *