What is a Three Level Architecture in DBMS?

3 - Level Architecture of DBMS:

This approach was proposed by an ANSI/SPARC (American National Standard Institute / Standards Planning and Requirements Committee).

Fig: Three Level Architecture

The overall architecture is divided into the following three levels viz.

1. External level

2. Conceptual level and;

3. Internal or physical level

The objective of the three-level architecture is to separate the user's view.

• It allows independent customized user views: Each user should be able to access the same data, but have a different customized 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.
• 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 the storage: For example, a changeover to a new disk.

 

1. External Level:

It is the highest level of data abstraction. The way users recognize the data is called an External level. Each external level is defined using an External schema. External schema consists of definitions of each of the various types of external record found at that level. It determines how data looks like regardless of how they are stored in the storage media. It is also called as user's view of the database that 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.

2. Conceptual Level:

It is the next lower level of abstraction. The conceptual level offers both the mapping and independence between the external and internal levels. This schema describes the database structure and hides information about the physical storage structures. The main focus is on describing data types, entities, relationships, etc. This logical level sits between the user level and physical storage view. But, there is only single conceptual view of a single database. It also holds security and integrity information. The Database Administrator (DBA) works at this level and only DBA can define this level. It provides a global view of database and it is independent of hardware and software.

 

3. Internal or Physical Level:

It is the lowest level of the data abstraction. The way the DBMS and the operating system distinguish the data is the internal level, where the data is stored using the data structures and file. The Physical level is defined by the means of Internal schema. It is concerned with the physical storage of the information means what data are stored and how and provides complete internal structure of storage, access paths, specification of keys, indexes, pointers etc.

 

Mapping Between the Three Levels:

Mapping corresponds to one record of one level to record of another level. There are two mappings in three levels architecture:

1. Mapping between External and Conceptual level.
2. Mapping between Internal and Conceptual level.
3. Internal to physical database.