Three Schema Architecture in DBMS

Three schema architecture hides the details of DB from the user. Its other name is Three Levels of Abstraction. The database administrator should be able to change the structure of the database according to need without affecting the user’s view. This effective three schema architecture holds 3-layers as below.

  1. External level (front-end developer, user level)
  2. Conceptual level (database designer, logical level)
  3. Internal level (administrator, physical level)

 

1. External or View level

  • This is the highest level of database abstraction.
  • External or view level tells the actual view of data that is relevant to the particular user.
  • View/External level provides different views of the same DB for a specific user or a group of users. For example, the view of faculty students is different than that of faculty admin.
  • External level development with the help of front-end programming (Javascript, HTML, etc.)
  • An external view provides powerful and flexible security by hiding some information about the database from a particular user.

2. Conceptual or Logical level 

  • The logical/conceptual level tells the structure of the entire database.
  • The conceptual level acts as an intermediate layer between the physical storage(DB) and external level.
  • Database designer works on this layer, which provides the structure of the database.
  • Database any model (i.e., Relational or ER Model) can be used at this level, which provides the structure of the database.

3. Internal or Physical level 

  • This is the lowest level of database abstraction.
  • Database administrator works on this layer where it has complete control over data.
  • It describes how the physical data is actually stored (in the form of a file) in the database and provides methods to access data from the database.
  • It shows the physical representation of the database as it actually exists on the computer system.
  • It means that the changes in physical database storage devices (i.e., hard disks) and the organization of the files on database storage devices are hidden from application programs and users.

Note: Data can be stored centrally or distributed in database storage.  At the physical level, data is stored in the form of a file. But it is viewed in the form of a table.

Conclusion:

Three-level architecture provides data independence because data is independent of the user.