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.
1. External Level (View Level)
Definition:
The external level represents how data is viewed and interacted with by the end users or applications. This is also known as the view level because it defines different "views" of the data.
Key Characteristics:
- User-specific Views: The external level allows users to have customized views of the data that suit their specific needs. A user may see only a subset of the database or see it in a particular format.
- Data Access: At this level, users interact with the database through views (virtual tables), which are typically derived from one or more tables in the conceptual level. These views might be filtered to show only relevant data or be simplified for specific use cases.
- Security and Access Control: This level also supports data security by restricting access to sensitive data. Different users may have access to different views or parts of the data.
Example:
Consider an organization where:
- HR Department sees the employees' personal details and salaries.
- Payroll Department sees employee names, salaries, and bonuses.
- Management sees a high-level summary of employee counts and department performance.
Each department or individual user has a view of the database tailored to their needs, and they may not have access to the entire database structure.
2. Conceptual Level (Logical Level)
Definition:
The conceptual level provides a logical view of the entire database, abstracted from physical storage details. This is where the overall schema of the database is defined, including the relationships between different data entities (such as tables, records, attributes) and constraints (e.g., primary keys, foreign keys).
Key Characteristics:
- Global View: This level provides a unified, comprehensive view of all the data in the database, regardless of how the data is physically stored.
- Data Integrity: The conceptual schema enforces data integrity constraints, such as entity integrity (ensuring that every entity is unique) and referential integrity (ensuring that relationships between tables are consistent).
- Abstraction from Storage: The conceptual level is independent of how the data is stored or accessed at the physical level, allowing changes in storage mechanisms without affecting the overall data structure.
Example:
A conceptual schema for a company’s employee database might include:
- An Employees table with columns for
EmployeeID, Name, Age, Department, and Salary.
- A Departments table with columns for
DepartmentID and DepartmentName.
- A relationship where
DepartmentID in the Employees table refers to DepartmentID in the Departments table.
At the conceptual level, all entities and relationships are defined logically without concern for how the data will be physically stored.
3. Internal Level (Physical Level)
Definition:
The internal level (also called the physical level) defines how the data is physically stored on the storage medium, such as hard drives or SSDs. It is concerned with the low-level storage structures and how data is organized for efficient access and retrieval.
Key Characteristics:
- Data Storage: This level focuses on how data is physically stored in files, blocks, or pages, and how indexes, hashing, and clustering are used to improve performance.
- Efficient Access: The internal level determines how to optimize data retrieval, storage space, and I/O operations to minimize disk access times.
- Data Organization: This level deals with file organization, indexing, and techniques such as B-trees, hashing, and compression to ensure fast access and retrieval.
Example:
In the internal level:
- Data may be stored in disk files on a server using an efficient format.
- The EmployeeID field in the Employees table might have a B-tree index to speed up searches for employees by their ID.
- The DBMS might use hashing for fast lookups of certain fields or employ record clustering to store related data together for faster retrieval.
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
-
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.
-
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.
-
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.
-
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.
-
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.
Example Use Case
Scenario: Employee Management System
Consider an organization that manages an employee database.
-
External Level:
- HR Managers may have a view of the database that only shows employees' personal details (Name, Age, Address).
- Payroll Managers may have a view that includes salary and bonus information.
- Executives may have a high-level overview of employee statistics and department performance.
-
Conceptual Level:
- The database includes a global schema that defines the structure of employees, departments, payroll data, etc.
- The schema ensures that the employee records are consistent and that foreign key relationships between tables (e.g., linking employees to departments) are maintained.
-
Internal Level:
- The database might use an index on the
EmployeeID field for faster lookups.
- Data might be stored in binary format to reduce space, and clustering might be used to store employees from the same department together for better performance.
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.