Three Schema Architecture of DBMS

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.
This architecture contains three layers or levels of the database management system:

1. External level
2. Conceptual level
3. 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

Following are the 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.

Related Posts:

• DBMS View: Read, Update, Create and Drop
• DBMS Data Independence: Logical and Physical
• Hashing in DBMS: Static and Dynamic
• DBMS Joins: Inner, Outer, Natural and Self Join
• Conversion of ER Diagram into Relational Model
• Difference between DBMS and RDBMS
• DBMS Keys: Primary, Super, Candidate, Foreign
• Foreign key in DBMS
• Primary Key in DBMS
• Aggregate Functions in DBMS