What is 3 Schema Architecture and Why is it Important?

Introduction

If you are working with databases, you may have heard of the term 3 schema architecture. But what does it mean and why is it important? In this blog post, I will explain the concept of 3 schema architecture, its benefits and challenges, and some examples of how it is used in practice.

What is 3 Schema Architecture?

The 3 schema architecture is a framework for designing and managing database systems. It was first proposed by the ANSI/X3/SPARC committee in 1975 as a way to separate the user applications and the physical database, and to promote data independence and integration.

The 3 schema architecture consists of three levels or schemas:

• External schema: This is the user view of the data. It defines how different users or applications see and access the data according to their needs and preferences. For example, a salesperson may only see the customer name, address, and order details, while an accountant may see the invoice number, amount, and payment status. The external schema can have multiple views for different users or applications.

• Conceptual schema: This is the logical view of the data. It defines what data are stored in the database and how they are related to each other. It also defines the constraints and rules that apply to the data. For example, the conceptual schema may define that a customer has a unique ID, a name, an address, and zero or more orders and that an order has a date, a quantity, and a reference to a customer. The conceptual schema is independent of any specific user view or physical implementation.

• Internal schema: This is the physical view of the data. It defines how the data are stored and organized on the computer hardware. It also defines the access methods and indexes that are used to optimize the performance of the database. For example, the internal schema may define that the customer data are stored in a B-tree file, while the order data are stored in a hash file, and that there is an index on the customer ID field.

The following diagram illustrates the 3 schema architecture:

Why is it Important?

The 3 schema architecture has several benefits for database design and management:

• Data independence: Data independence means that changes in one level of the schema do not affect other levels. For example, if the internal schema changes due to hardware upgrades or performance tuning, the external and conceptual schemas do not need to change. Similarly, if the external schema changes due to user requirements or application development, the conceptual and internal schemas do not need to change. This reduces the maintenance cost and complexity of the database system.

• Data integration: Data integration means that different user views of the data are consistent and coherent with each other. For example, if a salesperson updates a customer’s address in one view, an accountant should see the same updated address in another view. This ensures that there is no data inconsistency or redundancy in the database system.

• Data abstraction: Data abstraction means that users or applications do not need to know or care about the details of how the data are stored or accessed. For example, a user can query the database using a high-level language such as SQL without knowing how the data are physically organized or indexed. This simplifies the interaction between users or applications and the database system.

What are some Challenges?

The 3 schema architecture also has some challenges for database design and management:

• Complexity: The 3 schema architecture introduces more complexity to the database system compared to a simpler approach where there is only one level of schema. For example, there needs to be mapping mechanisms between different levels of schemas to transform requests and responses. There also needs to be coordination mechanisms between different views of data to ensure consistency and integrity.

• Overhead: The 3 schema architecture introduces more overhead to the database system compared to a simpler approach where there is only one level of schema. For example, there may be more processing time and memory usage required to perform mapping and coordination operations between different levels of schemas.

• Trade-offs: The 3 schema architecture involves trade-offs between different goals such as data independence, data integration, data abstraction, performance, security, usability, and scalability. For example, achieving higher data independence may compromise performance or security, while achieving higher data integration may compromise usability or scalability. There is no one-size-fits-all solution for the 3 schema architecture, and different database systems may adopt different strategies and techniques to balance these trade-offs.

What are some Examples?

The 3 schema architecture is widely used in various database systems and applications. Here are some examples:

• Relational database systems: Relational database systems are based on the relational model of data, which defines data as tables of rows and columns. The external schema of a relational database system consists of views, which are subsets or combinations of tables that are defined by queries. The conceptual schema consists of relations, which are tables that define the logical structure and constraints of the data. The internal schema consists of files, records, and fields, which define the physical storage and organization of the data. Relational database systems use SQL as a standard language for defining and manipulating data at different levels of schemas.

• Object-oriented database systems: Object-oriented database systems are based on the object-oriented model of data, which defines data as objects that have attributes and methods. The external schema of an object-oriented database system consists of classes, which are templates for creating objects that have specific attributes and methods. The conceptual schema consists of objects, which are instances of classes that define the logical state and behavior of the data. The internal schema consists of pages, segments, and clusters, which define the physical storage and organization of the data. Object-oriented database systems use OQL or other object-oriented languages for defining and manipulating data at different levels of schemas.

• Data warehouse systems: Data warehouse systems are specialized database systems that store and analyze large amounts of historical and aggregated data from multiple sources. The external schema of a data warehouse system consists of cubes, which are multidimensional structures that allow users to slice and dice the data along different dimensions and measures. The conceptual schema consists of facts and dimensions, which define the logical structure and relationships of the data. The internal schema consists of star or snowflake schemas, which define the physical storage and organization of the data. Data warehouse systems use OLAP or other analytical languages for defining and manipulating data at different levels of schemas.

Conclusion

The 3 schema architecture is a framework for designing and managing database systems that separate the user view, the logical view, and the physical view of the data. It has several benefits such as data independence, data integration, and data abstraction, but also some challenges such as complexity, overhead, and trade-offs. It is widely used in various database systems and applications such as relational, object-oriented, and data warehouse systems.

I hope you enjoyed this blog post on 3 schema architecture. If you have any questions or comments, please feel free to leave them below. Thank you for reading! 😊