Three-Schema Architecture
The three schema architecture (ANSI/SPARC architecture) divides the database into three levels to separate user applications from the physical database.
[ View 1 ] [ View 2 ] [ View 3 ] <– External Level
\ | /
+————–+————-+
|
[ Conceptual Schema ] <– Conceptual Level
|
[ Internal Schema ] <– Internal Level
|
( Physical Database )
- The Internal Level, also called the physical level, describes how data is actually stored on the storage device, including file organization, indexing techniques and storage allocation. This level is concerned only with efficiency of storage and retrieval and is generally handled by the database administrator and system designers.
- The Conceptual Level, also called the logical level, describes the overall logical structure of the entire database for the whole organization, including all entities, attributes, relationships and constraints, without going into physical storage details. This level represents what data is stored and how it is related, and it is usually defined once during database design and rarely changes afterward.
- The External Level, also called the view level, describes the part of the database that is relevant to a specific user or group of users, hiding the rest. Many external views can exist for a single conceptual schema, each tailored to different categories of users, and each view may present the data differently or restrict access to only certain attributes.
Why Do We Need Mappings Between Schema Levels?
Mappings are the processes used to connect and translate requests and results between the three schema levels of a database: the external level, conceptual level, and internal level. We need mappings because users usually interact with the database through external views, while the actual data is stored at the internal (physical) level on storage devices. Users do not need to know where or how the data is physically stored.
When a user requests some data at the view level, the DBMS translates or maps that request to the conceptual level and then further maps it to the internal level to locate and retrieve the required data from storage. Similarly, after retrieving the data, the DBMS maps the result back through the different levels and presents it to the user in the required view.
Mappings are also important for data independence. If the physical storage or conceptual structure of the database changes, the mapping rules can be updated accordingly. This allows user applications to continue working without major modifications.