Three Level Schema Architecture

Three-Level Schema Architecture

The Three-Level Schema Architecture is a key concept in database management systems (DBMS) that provides a framework for separating the user view of the data from the physical storage of the data. This architecture was proposed by the ANSI/SPARC DBMS study group in 1975 as a way to provide data independence and to separate the logical view of data from the physical view.

This model divides the database system into three distinct levels: Internal Level, Conceptual Level, and External Level. Each level addresses different concerns regarding how data is stored, organized, and accessed.

---

a. Internal Level (Physical Level)

• Definition: The internal level, also known as the physical level, describes how the data is stored on the physical storage medium (e.g., hard drives, SSDs). It focuses on the physical storage of data, the indexing methods, and the access paths used for efficient data retrieval.
• Responsibilities:
  • Specifies the data structures (such as tables, indexes, clusters) used to store data efficiently.
  • Defines how the data is organized and stored, including methods for file storage, indexing, and record organization.
  • Optimizes how data is retrieved using access paths, such as B-trees, hashing, and bitmaps.

Example: The internal level might define how employee data is stored in memory, the indexing of employee IDs, and the storage of data on specific disk locations.

b. Conceptual Level (Logical Level)

• Definition: The conceptual level provides an abstraction of the data. It represents the logical structure of the entire database without dealing with how the data is stored or accessed physically. This level describes the data model used (e.g., relational model) and the relationships between different data entities.
• Responsibilities:
  • Provides a high-level description of the entire database, including the tables, views, relationships, constraints, and entities.
  • Ensures data integrity and consistency by defining constraints (e.g., primary keys, foreign keys) and business rules.
  • This level is independent of the underlying physical storage mechanisms and of the ways data will be accessed.

Example: At the conceptual level, the database might contain a description of the Employee table with columns like EmployeeID, Name, and Department, and the relationship between employees and departments.

c. External Level (View Level)

• Definition: The external level, also known as the view level, describes the way data is presented to the user or application. It represents user views of the data, which are tailored to the needs of individual users or applications.
• Responsibilities:
  • Defines various user views or schemas that allow different users to interact with the data in ways that suit their needs.
  • This level can combine data from multiple tables and present it in a simplified or customized form (e.g., through views, stored procedures, or queries).
  • Supports data security by ensuring that users can only access the data they are authorized to see.

Example: A user who manages HR data may have a view that only shows employee names, IDs, and departments, while a manager in the payroll department may have a view that includes employee salaries, bonuses, and tax details.

---

Diagram of the Three-Level Schema Architecture

+-----------------------------------------------------+
|                External Level (User Views)          |
|     (Individual user or application-specific view) |
+-----------------------------------------------------+
                         ↑
                         |
+-----------------------------------------------------+
|                Conceptual Level (Logical Schema)    |
|     (Global view of the entire database structure)  |
+-----------------------------------------------------+
                         ↑
                         |
+-----------------------------------------------------+
|                Internal Level (Physical Schema)     |
|     (How data is stored physically in the database) |
+-----------------------------------------------------+

---

Advantages of the Three-Level Schema Architecture

1. Data Independence:

   • Logical Data Independence: Changes at the internal level (physical storage) can be made without affecting the conceptual level (logical schema). This allows for easier database maintenance, such as modifying how data is stored or indexed without affecting user views.
   • Physical Data Independence: Changes at the conceptual level (logical schema) can be made without affecting the external level (user views). This ensures that the user interface and application programs do not need to be modified when changes to the schema occur.

2. Security:

   • Different users or groups of users can have access control over different parts of the database at the external level, restricting what data they can view or modify.

3. Data Abstraction:

   • The system provides abstraction by separating concerns about how data is stored from how it is used. Users and applications can focus on what data they need, without worrying about how or where it is stored.

4. Simplified Database Management:

   • Administrators can manage storage and performance optimization at the internal level, modify the logical schema for better organization at the conceptual level, and cater to different user needs at the external level.

5. Improved Flexibility and Scalability:

   • The separation of concerns allows databases to evolve more easily. For example, new views can be created at the external level without modifying the underlying schema, and new storage strategies can be adopted without affecting user views.

---

Conclusion

The three-level schema architecture helps provide a structured and organized approach to managing databases. It offers data independence, which is crucial for the long-term scalability, flexibility, and maintenance of a database system. By separating concerns at different levels—external, conceptual, and internal—the system can efficiently cater to both user needs and performance requirements while maintaining integrity and security.