Three-Schema Architecture and Data Independence
Three-Schema Architecture and Data Independence
1. Introduction
Among the important characteristics of the database approach are:
-
Use of a catalog to store database descriptions (schemas)
-
Insulation between programs and data
-
Support for multiple user views
To achieve and clearly visualize these characteristics, database systems use the Three-Schema Architecture, also called the ANSI/SPARC architecture.
2. Three-Schema Architecture
The goal of the three-schema architecture is to separate user applications from the physical storage of data. This separation allows flexibility, multiple views, and data independence.
The architecture consists of three levels:
2.1 Internal Level
-
Described by the internal schema
-
Specifies how the data is physically stored in the database
-
Uses a physical data model
-
Includes:
-
File structures
-
Record formats
-
Indexes
-
Access paths (e.g., hashing, B-trees)
-
This level deals with low-level storage details and is invisible to end users.
2.2 Conceptual Level
-
Described by the conceptual schema
-
Represents the entire logical structure of the database for the whole organization
-
Hides physical storage details but shows:
-
Entities
-
Data types
-
Relationships
-
Constraints
-
Operations allowed on data
-
Usually, a representational data model (like the relational model) is used to implement this level.
The conceptual schema is a central reference point for all users.
2.3 External (View) Level
-
Includes multiple external schemas or user views
-
Each view describes only the part of the database relevant to a particular user or user group
-
Hides the rest of the database from that user
-
Allows different users to see the data in different customized ways
Example:
A student may have a view showing courses and grades, while an administrator’s view shows personal and financial data.
3. Mappings Between the Three Levels
Since schemas exist at different levels, the DBMS must perform mappings to translate requests:
-
External ↔ Conceptual Mapping
Converts a user’s view into the global conceptual schema. -
Conceptual ↔ Internal Mapping
Converts conceptual-level operations into physical storage operations.
These mappings ensure:
-
Users do not need to know how data is stored physically
-
Data can be reformatted or reorganized without changing user queries
Although some DBMSs do not fully implement all three levels, the architecture still influences DBMS design today.
4. Data Independence
Data Independence is the ability to change the schema at one level without requiring changes at the next higher level.
There are two types:
4.1 Logical Data Independence
Definition:
The ability to change the conceptual schema without changing external schemas or application programs.
What changes are allowed?
-
Adding new entities or attributes
-
Removing existing entities or attributes
-
Changing relationships
-
Modifying constraints
Programs that use external schemas must continue to work without modification.
Example:
If the GRADE_REPORT structure is changed (e.g., new attributes added), user views that do not use these attributes should not be affected.
Logical data independence is difficult to achieve, because structural changes often impact many applications.
4.2 Physical Data Independence
Definition:
The ability to change the internal schema without altering the conceptual schema or any external schemas.
What changes are allowed?
-
Creating new indexes
-
Changing file organization
-
Using new storage devices
-
Reorganizing records on disk
The logical structure of the database remains the same, so applications are unaffected.
Example:
Building a new index for faster retrieval of SECTION records should not require rewriting user queries such as “List all sections offered in Fall 2008.”
Physical data independence is easier to achieve because internal storage details are naturally hidden from users.
5. How Data Independence is Achieved
The DBMS catalog stores metadata about:
-
Schemas at all levels
-
How they are mapped to each other
When a schema at one level changes, only the mapping information needs to be updated.
The higher-level schema and application programs remain unchanged.
This ability to isolate levels is a key strength of the database approach.
6. Summary Table
| Level | Schema | Describes | Users |
|---|---|---|---|
| External | External Schema | User views | End users |
| Conceptual | Conceptual Schema | Entire logical database | DB designers |
| Internal | Internal Schema | Physical storage | DBMS & system programmers |
| Type of Independence | What Can Change | Without Affecting |
|---|---|---|
| Physical | Internal schema | Conceptual & external schemas |
| Logical | Conceptual schema | External schemas & applications |
Comments
Post a Comment