The three-schema architecture, also known as the ANSI/SPARC architecture, is a framework for designing and developing database systems. It was developed in the 1970s by the American National Standards Institute (ANSI) and the Standards Planning and Requirements Committee (SPARC) to provide a standard architecture for database systems.
The three-schema architecture consists of three levels, each with its own schema:
- External Schema or View Level: This is the topmost layer of the architecture, which defines how the data is presented to the end-users. It includes the user views or user interfaces that are used to interact with the database. It describes the portion of the database that a particular user can access and the way in which the data is presented to them.
- Conceptual Schema or Logical Level: This layer provides a conceptual view of the entire database. It represents the logical structure of the entire database and is independent of any particular database management system. It defines the relationships between the different entities and their attributes.
- Internal Schema or Physical Level: This is the lowest level of the architecture, which defines how the data is physically stored in the database. It describes the physical storage structures, such as indexes, tables, and files, used by the database management system to store and retrieve data.
The three-schema architecture provides many benefits, including:
- Data Independence: The architecture provides a separation between the user views, logical schema, and physical schema. This means that changes made to one level do not affect the other levels. For example, changes to the physical schema do not affect the user views or the logical schema.
- Security: The architecture allows for the implementation of security measures at each level. Access to the data can be restricted based on user views or interfaces.
- Maintenance: The architecture makes it easy to maintain the database system. Changes made to one level do not require changes to the other levels, making it easier to upgrade or modify the system.
In conclusion, the three-schema architecture provides a standard framework for designing and developing database systems. It separates the presentation, logical, and physical aspects of the database, providing data independence, security, and easier maintenance.
Objectives of Three schema Architecture
The main objectives of the Three Schema Architecture are:
- Data Independence: The three-schema architecture provides the concept of data independence, which means that the logical and physical schema of the database are independent of each other. This means that changes made to one schema will not affect the other schemas, providing greater flexibility to modify the database structure without affecting application programs that use the database.
- Improved security: The three-schema architecture improves the security of the database by allowing database administrators to grant or restrict access to the database at different levels. For example, users can be granted access to only certain parts of the database based on their role or need-to-know basis.
- Improved data sharing: The three-schema architecture promotes data sharing by enabling multiple applications to access the same database. This eliminates the need to create separate databases for each application, which can lead to data redundancy and inconsistency.
- Enhanced data abstraction: The three-schema architecture provides a high level of data abstraction, which makes it easier for developers to work with the database. The logical schema presents the data in a meaningful way that is easy to understand and use, while the physical schema deals with the implementation details.
- Improved performance: The three-schema architecture can improve performance by allowing database administrators to optimize the physical schema without affecting the logical schema. This means that the physical schema can be tuned for maximum performance without impacting the logical schema, which provides a consistent view of the data to the application programs.
Overall, the three-schema architecture provides a flexible and scalable database design that can be adapted to changing business needs over time. It is a widely used architecture in the database industry and has been implemented in many commercial database systems.
The three-schema architecture is a framework for database design that separates the user’s view of data from the way data are physically represented in the database. The architecture consists of three levels of abstraction: the external level, the conceptual level, and the internal level.
- Internal Level: The internal level or physical level is the lowest level of the three-schema architecture. This level is concerned with how the data is actually stored in the database. It deals with the physical storage structure of the database, such as storage of data files, indexing, data compression, and data encryption. At this level, the database management system (DBMS) translates the logical representations of the data into the physical storage structures.
For example, consider a relational database that stores customer orders. At the internal level, the DBMS may use a B-tree index to efficiently retrieve data from the disk storage. The DBMS may also use compression to reduce the storage space required by the data files.
- Conceptual Level: The conceptual level or logical level is the middle level of the three-schema architecture. This level is concerned with the logical structure of the database, independent of any specific application or user. It describes what data is stored in the database and the relationships among the data. This level is sometimes called the schema level.
For example, consider the same relational database that stores customer orders. At the conceptual level, the database may have tables for customers, orders, and order items. The tables would have columns that describe the attributes of each entity, such as customer name, order date, and order item quantity. The relationships among the tables would be defined, such as the relationship between customers and orders, and between orders and order items.
- External Level: The external level or view level is the highest level of the three-schema architecture. This level is concerned with the user’s view of the data. It represents the portion of the database that a particular user or application is interested in. Users may have different views of the data, depending on their needs.
For example, an order entry clerk may have a view of the customer orders database that includes only the customer name, order date, and order status. A shipping clerk may have a view of the same database that includes the customer name, shipping address, and order items. Each view is customized for a specific user or application.
In summary, the three-schema architecture provides a framework for separating the physical storage of data from the logical structure of the database and the user’s view of the data. This architecture allows for greater flexibility, security, and maintainability of the database, and supports multiple users and applications with different views of the same data.
Mapping between Views
In the three-schema architecture, mapping between views is the process of translating data between different external schemas that are used by different applications to access the same data stored in the database.
Mapping between views involves several steps, including the identification of the source and target schemas, the definition of the mapping rules, and the actual data transformation. The mapping process can be manual or automated, depending on the complexity of the schemas and the amount of data to be transformed.
For example, suppose a company has a database containing information about its employees, including their names, salaries, job titles, and departments. The company’s HR department uses an application that displays a list of all employees and their basic information. The finance department uses a different application that displays the salaries of each employee, along with their deductions and benefits.
To support these different views of the data, the three-schema architecture provides a conceptual schema that represents the database at a high level of abstraction, as well as two external schemas that represent the views of the HR and finance applications. The mapping between the external schemas and the conceptual schema is performed through a set of mapping rules, which specify how data is transformed from one schema to another.
For example, to map the HR view to the conceptual schema, the mapping rules would specify that the employee name, job title, and department information are copied directly from the employee table in the database, while the salary information is computed by joining the employee table with the payroll table and summing the relevant fields. Similarly, to map the finance view to the conceptual schema, the mapping rules would specify that the employee name, salary, and deduction information are copied directly from the payroll table, while the benefits information is computed by joining the employee table with the benefits table and summing the relevant fields.
By providing a standard mechanism for mapping between views, the three-schema architecture enables different applications to access the same data in a consistent and reliable manner, while also allowing for the flexibility to support different views of the data.
Actually, the two types of mapping in database architecture are:
- Internal / Physical Mapping
- External / Conceptual Mapping
Let’s discuss each in more detail:
- Internal / Physical Mapping: Internal mapping refers to the mapping between the internal schema and the conceptual schema of the database. It involves how the conceptual data model is mapped to the physical storage structures such as files, indexes, and other data structures on the storage devices like hard drives, tapes, etc.
The internal schema is the lowest level of abstraction in the three-schema architecture and is concerned with how data is physically stored and organized on disk. It includes information such as the record layout, data types, indexes, and storage details.
For example, let’s say we have a conceptual schema that includes an entity called “Customer” with attributes such as “CustomerID”, “Name”, and “Address”. The internal schema would include details such as how these attributes are physically stored in a specific file or set of files on a hard drive, including the data types used and any indexing structures used to speed up access.
- External / Conceptual Mapping: External mapping refers to the mapping between the external schema and the conceptual schema of the database. It involves how the user views of the data (external schema) are mapped to the conceptual data model.
The external schema is the highest level of abstraction in the three-schema architecture and represents how the data appears to the end-users or application programs. It includes information such as the view definitions, access authorization, and constraints.
For example, let’s say we have an external schema that includes a view of the “Customer” entity with attributes such as “CustomerID” and “Name”. The external mapping would include details such as how this view is mapped to the underlying conceptual schema, including any transformations or aggregations required to derive the view from the base data.
