Question

Describe the three-schema architecture. Why do we need mappings between schema levels? How do different schema definition languages support this architecture?

Short answer

Three-schema architecture divides a database into internal, conceptual, and external schemas allowing different levels of abstraction for storage and user interaction with data. Mappings between schema levels are necessary for user accessibility, data portability, and data independence. Schema Definition Languages, including SQL and XML, enable the support of this architecture through various commands.

Step-by-step solution

Defining the Three-Schema Architecture

The three-schema architecture is a database design methodology that divides the database into three levels to separate the physical storage of data from the user's interaction with it. The three levels are: 1. Internal/Physical schema: This is the lowest level and deals with how the data is stored physically on the system. 2. Conceptual schema: This is the middle level that defines the logical structure of the data, independent of how it's physically stored.3. External/User view schema: This is the highest level, where users interact with the database and see only the specific data that they need.

Importance of Mappings

Mappings between schema levels are necessary for few reasons:1. They allow users at the external view to interact with the database without knowing the physical or internal structure of the data.2. They make data more portable as changes in one schema level don't affect the other levels.3. They support data independence, meaning changes at one level don't affect the interactions at other levels.

Role of Schema Definition Languages

Schema Definition Languages (SDLs) like SQL, XML, etc., provide tools for defining and manipulating schemas at different levels, which enables this three-schema architecture. For example, in SQL, the `CREATE TABLE` command can be used to define a schema at the conceptual level. For the external level, `VIEW` is used to define a user-specific presentation of the data. For the internal level, commands for defining storage and access methods are available in the Data Definition Language subset of SQL.

Key concepts

Database Design Methodology

Database design methodology is a structured approach to creating a database that meets all the specified requirements effectively and efficiently. This methodology ensures that users can interact with the data in intuitive and meaningful ways without needing to understand the complexities of data storage.
One of the pivotal frameworks used in database design is the Three-Schema Architecture. It organizes the database into three distinct levels: the Internal/Physical schema, the Conceptual schema, and the External/User view schema.

• The Internal level focuses on how data is physically stored, including indexing and physical data structures.
• The Conceptual level provides a logical view, abstracting away the details of data storage.
• The External level tailors the database for individual user needs, presenting only relevant data.

By compartmentalizing the database in this fashion, we achieve data independence and simplify user access.

Conceptual Schema

The conceptual schema acts as the core of the Three-Schema Architecture, bridging the gap between the external and internal schemas. Think of it as a blueprint that defines the logical structure of the entire database. It does not concern itself with how data is stored physically—that is the job of the internal schema.
The conceptual schema allows designers to focus on what data to store and the relationships between different data entities. This level is vital as it ensures consistency across the database, providing a comprehensive map that all applications and users can refer to, without worrying about the complexities of how or where the data is stored. It maintains a unified view of data across various applications and user interfaces by keeping it relationship-oriented and disregarding physical data allocation.

Data Independence

Data independence is a crucial concept in the design and management of databases. It refers to the ability to change the schema at one level of a database system without having to change the schema at the next higher level. There are two types of data independence:

• Logical Data Independence: This is the capacity to change the conceptual schema without having to change external schemas or application programs. Adjustments like adding new fields or new relationships can be made without affecting any views tailored for user interactions.
• Physical Data Independence: This allows changes to the internal schema, such as changing from one storage device to another, without affecting the conceptual schema or any external schemas. Applications will keep running smoothly regardless of these internal shifts.

Data independence is central to the three-schema architecture because it ensures that data remains accessible and stable even amid technical modifications to the storage or logical arrangements.

Schema Definition Languages

Schema definition languages (SDLs) are essential tools for database designers. They are used to specify both the structure and constraints of the schemas at different levels of the database system. These languages enable defining, modifying, and controlling schemas efficiently, playing a pivotal role in facilitating the Three-Schema Architecture.

For example, SQL is a widely used SDL that helps define schemas at various levels:

• At the internal level, Data Definition Languages (DDL) commands such as those for creating indexes and specifying storage parameters are employed.
• At the conceptual level, the `CREATE TABLE` statements define the logical structure of data.
• For the external level, the `CREATE VIEW` statement allows the creation of user-specific data presentations.

Other languages like XML can also be used to define database schemas in more specialized contexts, underscoring SDLs' flexibility and importance in maintaining a structured and accessible database environment.