In the previous tutorial, we have discussed different types of databases in DBMS. If you want to read that article then click here.
Our next topic is 3 tier architecture in DBMS. In this guide, you will learn about three level architecture of DBMS with the help of proper diagrams and suitable examples.
Let’s Get Started, Happy learning!
What is a 3 Tier Architecture in DBMS?
- 3 tier architecture in DBMS is also called three schema architecture in DBMS, three level architecture of DBMS or ANSI/SPARC architecture.
- The levels or you can say schemas are used to define the database, and it comprises three levels, external, conceptual and internal level and called as levels of data abstraction in DBMS.
- Three level architecture of DBMS explains the structure of the specific database at each level and is useful for describing general database concepts.
DBMS Architecture Diagram
The following diagram shows the three level architecture of the DBMS (database management system). Mapping is used to make a connection between all three levels of the database. As you can see in the below DBMS architecture diagram, there are two types of mapping is used which connects the three levels to transfer the database request from one level to another level.
External/conceptual mapping shows a relation between the external and conceptual level and it transfers the database request from external to the conceptual schema.
Conceptual/ internal mapping shows the relationship between two different levels, i.e. conceptual and internal levels. It is used to pass the database request from conceptual to the internal schema.
Levels of Abstraction in DBMS
As we have discussed above that there are three levels of abstraction in DBMS. We name these levels as external level, conceptual level and internal level. Let’s understand these levels of abstraction in DBMS one by one:
Internal level
This is the lowest level of the three level architecture of DBMS as you can see in the diagram. The internal level is the physical representation of the database on the computer, and that’s why it is also called a physical level or schema. The internal level describes how the data is stored in the database and It is also known as the storage level. At the internal level, data is stored in external hard drives as bits.
Conceptual level
The middle level of three level architecture of DBMS is the conceptual level and works as an intermediate between internal and external level. The conceptual level is the community view of the database. This level describes what data is stored in the database and the relationships among the data. Following are the major features of a conceptual level:
- It is the DBA view
- It provides security and integrity information.
- The conceptual level is also called a logical level or schema.
- Database administrators and programmers mainly used this level.
External level
This is the highest level of the three schema architecture in DBMS. The external level is the user’s view of the database and that’s why it is also called a view level or schema. The end-users directly communicate with the external level to access the data from the database. This level only shows the relevant content to the user as a view and hides the other data from the user. So there can be a different view for different users as per their requirements.
As you can see in the above DBMS architecture diagram, there are three different views of database i.e. view 1, view 2 and view 3. Each view may contain different information because the view is created based on the demand of a specific user. Whatever data a user want to access from the entire database is showed under their view and all the other data is hidden from that particular user.
Let’s take an example of a student database to understand the concept of three schema architecture in DBMS. At the physical level, space is allocated for each entry of the student database to store the records of students.
Now, at a conceptual level, the design of the student database is described and a relationship is created to connect the different tables of the student database.
The external level is directly used by users who want to access the student database. Let suppose there are two users A and B. A just want to see the records 1-20 from the student database and B want to access the records 25 – 50 from the student database.
So here two different views are used to represent the data from the same student database i.e view A and View B. View A is created for person A and it will show 1-20 records. View B is created for person B and it will show 25-50 as per the demand of person B.
So, the external or view level creates a view of the database based on the requirements of each user and it only shows the information that is requested from the entire database.
Have you enjoyed this tutorial on 3 tier architecture in DBMS? If you have any questions feel free to ask in the comment section.