Posts tagged: Data Abstraction

Three-Schema Architecture of DBMS

The Three-Schema Architecture is a database architecture proposed by the ANSI/X3/SPARC committee that provides a clear separation between the user applications and the database system. It consists of three levels or schemas: the External Schema, the Conceptual Schema, and the Internal Schema.

1. External Schema:

The External Schema represents the user view of the data. It defines how individual users or user groups perceive the organization of the data. Each user or application can have its own external schema, customized to meet its specific requirements. This level shields users from changes in the database structure, providing a level of abstraction.

2. Conceptual Schema:

The Conceptual Schema represents the overall logical structure of the entire database as seen by the database administrator. It is an abstraction that defines the relationships between different entities and the constraints on the data. Changes to the database structure at this level impact all users but are transparent to them since their external schemas remain unchanged.

3. Internal Schema:

The Internal Schema defines how the data is stored in the physical storage devices, such as hard drives. It includes details about data structures, file organization, indexing mechanisms, and access paths. Changes at this level are transparent to both the conceptual and external schemas, ensuring that modifications to the physical storage do not affect the logical structure or user views.

Three-Schema Architecture Diagram

• User Level (External Schema): Represents various user views or applications (e.g., user A and user B), each with its own customized view of the data.
• Logical Level (Conceptual Schema): Represents the logical structure of the entire database, including relationships between entities and constraints.
• Physical Level (Internal Schema): Represents the physical storage details, such as file organization, indexing, and access paths.

Physical Data Independence

Physical data independence is one of the key advantages provided by the Three-Schema Architecture. It refers to the ability to modify the physical storage structure of the database without affecting the conceptual or external schemas. This independence ensures that changes made to improve performance or storage efficiency do not require alterations to how users perceive or interact with the data.

Example of Physical Data Independence

Let’s consider a scenario where the internal schema needs optimization for storage space. Suppose the data is initially stored in a table with a fixed-length format:

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

Later, to save storage space, the database administrator decides to compress the data and store it in a more space-efficient manner, perhaps using a different storage format or a different file organization.

-- Modified Internal Schema for Compression
CREATE TABLE employees_compressed (
    employee_id INT,
    full_name VARCHAR(100),
    hire_date DATE
);

Despite this internal change, the conceptual schema and external schemas remain unaffected. Users can still interact with the data using the same queries and applications as before, and the logical structure of the database hasn’t changed.

Logical Data Independence

Logical data independence is another critical aspect of the Three-Schema Architecture. It refers to the ability to modify the conceptual schema without affecting the external schemas or the applications built on top of them. This independence allows for changes in the logical organization of the data without disrupting user views.

Example of Logical Data Independence

Let’s consider an example where the company decides to add a new attribute, “email,” to the employees. Initially, the conceptual schema looks like this:

-- Initial Conceptual Schema
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

Now, the company wants to add an “email” attribute to the conceptual schema:

-- Modified Conceptual Schema
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    email VARCHAR(100)
);

This modification is transparent to the users operating at the external schema level. They can continue to use their applications without any changes because the external schema remains the same.

Conclusion

The Three-Schema Architecture provides a clear and organized approach to database design by separating user views (external schema), the logical structure of the database (conceptual schema), and the physical storage details (internal schema). Physical and logical data independence ensure flexibility and adaptability to changes in the storage structure or logical organization of the data without impacting users and applications. This architecture is foundational for building robust and scalable database systems.