- Three-level architecture, or ANSI/SPARC architecture, is another name for the three-level architecture.
- A specific database system’s structure is described using this approach.
- The physical database and user applications are kept apart using the three-level design.
- There are three levels in the three-level architecture. The database is divided into three sections by it.
Three Schema Architecture’s Objectives
The major purpose of three level architecture is to enable different users to access the same data with a personalized perspective while storing the underlying data just once. It does this by dividing the database’s physical structure from the user’s perspective. The following justifies the desire for this separation:
- Different views of the same data are required for different users.
- Over time, the method by which a certain user needs to view the data may vary.
- Users of the database shouldn’t be concerned about the internal operations and physical implementation of the system, including hashing, internal structure optimization, data compression and encryption methods, etc.
- The same data should be accessible to all users based on their needs.
- The conceptual structure of the database should be modifiable by the DBA without compromising the user’s
- Modifications to the physical components of the storage should not impact the internal structure of the database.
Internal Level
The internal level has a plan called an “internal schema” that explains how the database is physically stored. This internal schema is also called the “physical schema.” It uses a physical data model to show how the data will be saved in storage blocks. The physical level gives detailed information about how complicated data is stored in a more basic way.
Here’s an example to explain Internal Level:
Imagine a library where books are stored on shelves. The internal schema is like a detailed plan that tells you exactly how and where each book is placed on the shelves (physical storage). It includes information like which shelf, what order, and how much space each book takes. In the same way, the physical schema in a database explains how data is stored on the hard disk, like the arrangement of files in storage blocks. This level deals with all the technical details of storing and organizing the data.
Conceptual Level
The conceptual schema explains how the database is designed at a higher level. This level is also called the logical level. It shows the structure of the entire database. The conceptual schema tells what data will be stored in the database and how the different pieces of data are connected to each other. At this level, the technical details of how the data is stored are not shown. Programmers and database administrators usually work at this level.
Here’s an example to explain Conceptual Level:
Imagine a library catalog that shows you which books are available and how they are related, like which books are by the same author or belong to the same genre. The conceptual schema is like the catalog that describes what books (data) are in the library (database) and how they are connected. It doesn’t show how the books are physically placed on the shelves but focuses on what information is stored and how it’s organized logically. Librarians (programmers) use this catalog to manage and find the books (data).
External Level
At the external level, a database has several smaller plans, called subschemas, which show different views of the database. An external schema, also known as a view schema, describes what part of the database a specific user group can see and use, while hiding the rest of the database from them. This schema helps show how end users interact with the database.
Here’s an example to explain External Level:
Imagine a school database that contains information about students, teachers, and grades. The principal, teachers, and students all use the database, but each group sees different parts of it. For example, students can only see their own grades, while teachers can see grades for all students in their classes. The external schema (or view schema) is like a customized view that each group sees. It controls what information each user group can access, hiding the rest of the database from them.
Mapping between Views
The three levels of DBMS architecture are connected and do not work separately. There needs to be a link between the three levels to show how they relate to each other. The DBMS manages this connection between the three types of schema, and this connection is called “mapping.”
There are basically two types of mapping in the database architecture:
- Conceptual/ Internal Mapping
- External / Conceptual Mapping
Conceptual/ Internal Mapping
The Conceptual/Internal Mapping connects the conceptual level and the internal level. Its job is to show how the records and fields from the conceptual level match with the files and data structures at the internal level.
External/ Conceptual Mapping
The External/Conceptual Mapping connects the external level and the conceptual level. Its job is to show how a specific external view matches with the overall conceptual view of the database.