Levels of view

External Level (user view) :

In the relational model, the external schema presents data as a set of relations. An external schema specifies a view of the data in terms of the conceptual level. It serves the specific needs of different users. It implements a level of security by showing data only to the authorized users.

Examples:

  • An employee should not see the managers salaries.
  • A manager should not see employee details of other departments.

Information that can be derived from stored data might be viewed as if it were stored.

  • Age of a person is derived from data of birth when needed.

Applications are written in terms of an external schema. The external view is computed when accessed.  It is not stored. Different external schemas can be provided to different categories of users. Translation from external level to conceptual level is done automatically by DBMS at run time.

The conceptual schema can be changed without changing anything in the application. Only the mapping from external to conceptual must be changed. This is called as conceptual data independence.

Conceptual Level (logical view) :

This level is also called as Logical level. It hides details of the physical level and gives a logical view of whole database. e.g. the conceptual schema presents data as a set of tables in relational model.

The DBMS maps data access between the conceptual to physical level. Physical schema can be changed without changing application. Only the mapping from conceptual to physical needs to be changed. This property is called physical data independence.

Physical Level (internal organisation) :

The physical schema describes how data is internally stored: files, indexes, etc. on the hard disk system and describes the record layout in the files(B+ tree, csv, list, etc). The internal details of physical level is completely insulated from the applications that access the database.

In the old days, applications worked at this level. The issues that applications faced are – the application needed to micromanage the storage operations, making it longer to develop the application. Applications became complex and any changes to data structures directly affected the applications. Applications had to be redesigned.