The three-schema architecture, also known as the ANSI/SPARC architecture, is a framework for database systems that aims to separate the user applications from the physical database.
• This architecture is designed to support data independence, which allows changes to the schema at one level to not affect the schema at another level.
The three-schema architecture consists of three levels:
1.) External Level (User View):
This level defines how individual users or user groups view the data. It includes multiple external schemas, or user views, which represent different ways of looking at the data for different users.
- Purpose: Provides customized views of the database to different users, ensuring that each user sees only the data relevant to them.
- Example: A sales manager might see customer contact information and order history, while an accountant might see customer billing information.
2.) Conceptual Level (Logical View):
This level defines the logical structure of the entire database as a community of users sees it. It includes the logical schema, which describes all the entities, attributes, relationships, constraints, and security and integrity information.
- Purpose: Provides a unified and comprehensive view of the entire database, abstracting the details of the physical storage.
- Example: The conceptual schema might define tables for customers, orders, products, and employees and their relationships.
3.) Internal Level (Physical View):
This level describes the physical storage of the database. It includes the physical schema, which specifies how data is stored in the storage devices, such as data structures, file organizations, and indexing mechanisms.
- Purpose: Manages the physical storage of data and aims to optimize performance and storage efficiency.
- Example: The internal schema might specify that the customer table is stored as a B-tree index file, and the orders table is stored in a heap file.
Data Independence:
Data independence refers to the ability to change the schema at one level of the database system without affecting the schema at the next higher level.
Types of data independence:
1.) Logical Data Independence:
Logical data independence is the ability to change the logical (conceptual) schema without altering the external schemas or application programs.
- Importance: Allows for modifications to the logical structure of the database, such as adding or removing tables or fields, without impacting the user views or the applications.
- Example: Adding a new attribute to a table or splitting a table into two without changing the user views.
2.) Physical Data Independence:
Physical data independence is the ability to change the physical schema without altering the logical schema.
- Importance: Allows for modifications to the physical storage of data, such as changing indexing strategies or storage devices, without impacting the logical structure of the database or user views.
- Example: Changing the storage format of a table from a heap file to a clustered index file without affecting the logical structure of the database.
Summary
Three-Schema Architecture:
- External Level: Customized user views of the data.
- Conceptual Level: Logical structure of the entire database.
- Internal Level: Physical storage of the data.
Data Independence:
- Logical Data Independence: Ability to change the conceptual schema without affecting the external schemas.
- Physical Data Independence: Ability to change the internal schema without affecting the conceptual schema.
The three-schema architecture and data independence together ensure that a database system can adapt to changes without causing disruption to users or applications, thereby providing a more flexible and maintainable system.
