The three schema architecture describes how the data is represented or viewed by the user in the database. This architecture is also known as three-level architecture and is sometimes called ANSI/SPARC architecture.
The three schema architecture divides the database into three-level to create a separation between the physical database and the user application. In simple words, this architecture hides the details of physical storage from the user. The database administrator (DBA) should be able to change the structure of database storage without affecting the user’s view.
OBJECTIVE
Objectives of using Three schema Architecture:
Every user should be able to access the same data but able to see a customized view of the data.
The user need not to deal directly with physical database storage detail.
The DBA should be able to change the database storage structure without disturbing the user's views
The internal structure of the database should remain unaffected when changes made to the physical aspects of storage.
This architecture contains three layers or levels of the database management system:
External level
Conceptual level
Internal level
Three Schema Architecture of DBMS
1. External or View level: This is the highest level of database abstraction. External or view level describes the actual view of data that is relevant to the particular user. This level also provides different views of the same database for a specific user or a group of users. An external view provides a powerful and flexible security mechanism by hiding the parts of the database from a particular user.
2. Conceptual or Logical level: The conceptual level describes the structure of the whole database. This level acts as a middle layer between the physical storage and user view. It explains what data to be stored in the database, what relationship exists among those data, and what the datatypes are. There is only one conceptual schema per database.
Database administrator and the programmers work at this level. This level does not provide any access or storage details but concentrates on the relational model of the database. The conceptual schema also includes features that specify the checks to retain integrity and consistency.
3. Internal or Physical level: This is the lowest level of database abstraction. It describes how the data is actually stored in the database and provides methods to access data from the database. It allows viewing the physical representation of the database on the computer system. The interface between the conceptual schema and the internal schema identifies how an element in the conceptual schema is stored and how it may be accessed.
If there is any change in the internal or physical schema, it needs to be addressed to the interface between the conceptual and internal schema. But there is no need to change in the interface of a conceptual and external schema. It means that the changes in physical storage devices such as hard disks, and the files organized on storage devices, are transparent to application programs and users.
Advantages of Three-schema Architecture:
This architecture makes the database abstract. It is used to hide the details of how data is physically stored in a computer system, which makes it easier to use for a user.
This architecture allows each user to access the same database with a different customized view of data.
This architecture enables a database admin to change the storage structure of the database without affecting the user currently on the system.
Faster Migration to new graphical environments
As each tier is separate, it is possible to use different sets of developers
It is more secure as the client doesn't have direct access to the database business logic
In case of the failure of the one-tier no data loss as you are always secure by accessing the other tier
Disadvantages of Three-Schema Architecture:
Complete DB Schema is a complex structure which is difficult to understand for every one
Difficult to set up and maintain
The physical separation of the tiers can affect the performance of the Database
The Tech Platform