Three Schema Architecture of DBMS

The three-schema architecture of DBMS hides the details of the database from the user. Its other name is Three Levels of Abstraction. The database administrator should be able to change the structure of the database according to need without affecting the user’s view. This effective three-schema architecture has three layers, which are given below

  1. External level (front-end developer, user level)
  2. Conceptual level (database designer, logical level)
  3. Internal level (administrator, physical level)

1. External or View level

This is the highest level of database abstraction.  The View / External level of database abstraction refers to how data is presented to specific users or groups of users. Instead of showing the entire database, it provides tailored views that display only the information relevant to the user. 

Note: In a database system, you can have one logical view but many External views

Example:

Consider a university portal that is designed with different user views to serve various groups such as students, faculty, and administrators.

  • For students: When a student logs into the portal, they are shown only their personal data, such as grades, assignments, course schedules, and academic history. They do not have access to any faculty or administrative data, ensuring their view is restricted to only what is relevant to them.

Three Schema Architecture of DBMS - Student View Example

  • For faculty: Faculty members, on the other hand, have a different view. They can access their own course schedules, the list of students in their courses, and the students’ grades and assignments. They can also update grades and assignments, but do not have access to the student records of other departments or the overall university financial data.

Three Schema Architecture of DBMS - Faculty View Example

  • For administrators: An administrator would have access to a comprehensive view of the entire university database, including student records, faculty schedules, university finances, and internal communications. They can manage users (students and faculty) and oversee the entire university’s operations, which is different from the limited access provided to students or faculty.

Three Schema Architecture of DBMS - Admin View Example

This level is typically developed using front-end technologies like JavaScript, HTML, and other web programming languages. These technologies help create a user interface (UI) that interacts with the database, ensuring that the right data is displayed to the user.

Advantages of External / View level

  • Hides Sensitive Data: The external level helps hide sensitive or unnecessary data from users.
  • Access Control: Only the data needed for each user’s role is visible to them.
  • Protects Privacy: Private information, like financial details, is kept hidden from those who don’t need to see it.
  • Prevents Changes: Users can’t change or delete data they’re not allowed to access.
  • Reduces Mistakes: By limiting what users can see and do, there’s less chance of errors happening.

  • Stronger Security: The system is more secure because users only have access to what they need.

2. Conceptual or Logical level 

The Conceptual or Logical level is the middle layer of database abstraction. It provides the schema or structure of the database, showing what data is stored and how it is related. This level focuses on the logical organization of data without dealing with how the data is physically stored or how individual users access it.

  • It describes the structure of the data, such as tables, relationships, constraints, and other logical components, without concerning itself with the physical storage details or user-specific views. This level is typically used for designing the database structure, ensuring that the data is logically organized and related in a way that supports the database’s overall function.

This level is typically designed using Entity-Relationship (ER) diagrams or other similar tools to represent the structure of the database logically.

Basic Example 01:

A logical view of the Student entity with its attributes in the ER Model representation is given below

Three Schema Architecture of DBMS - ER Model - Student Entity

A logical view of the Student entity with its attributes in RDBMS representation is given below

Three Schema Architecture of DBMS - RDBMS, Student Entity Schema

Advance Example 02:

Imagine a university database. At the conceptual level, the database designer defines the structure of the data, such as:

  • Entities like students, courses, and faculty, and how they are related.
  • The relationship between students and courses (e.g., students enroll in courses, faculty teaches courses).
  • Constraints like students must have a unique ID, courses must have specific credit hours, etc.

However, the database designer does not need to worry about how the data will be stored in files or how the user will view or interact with the data. The conceptual model focuses only on representing the logical relationships between the data entities.

Advantages of Conceptual or Logical Level:

  • Abstract Representation: It provides an abstract, high-level view of the data, focusing on what data is stored and how it is logically connected.

  • Database Design: Helps in designing the overall structure of the database without worrying about storage or user views.

  • Simplifies Changes: Changes in how data is stored or accessed (physical level) don’t affect the conceptual design. For example, you can change the underlying storage format without altering the logical relationships between data.

  • Consistency: Ensures consistency by defining relationships and rules, such as a student can only be enrolled in a course if they meet certain criteria.

  • Centralized Management: It allows for better management of the database schema, ensuring all components are well-organized and logically connected.

  • Security through Design: By defining relationships and constraints, it helps to indirectly prevent unwanted data manipulation, ensuring that data integrity is maintained.

In summary, the conceptual level provides a structured, unified view of the data without focusing on physical storage or user-specific views, helping to maintain a logical organization and consistency in the database design.

3. Internal or Physical level 

The Internal or Physical level is the lowest level of database abstraction. It defines how the data is actually stored in the database, focusing on the physical storage structures and the efficiency of data retrieval and storage. This level deals with the organization of data on disk, such as the use of indexes, file systems, and access paths that optimize the performance of the database.

Basic Example 01:

Student entity with its simple data in RDBMS is given below

Three Schema Architecture of DBMS - RDBMS, Student Entity Data

Advance Example 02:

Consider a university database where the physical storage involves how student data is saved on the server:

  • Data Files: The records of students, courses, and faculty are stored in files on the server, using specific formats for fast data retrieval.

  • Indexes: The system may use indexes to speed up searches, like creating an index on student IDs to quickly access student records.

  • Data Organization: The data may be organized in specific blocks or pages on the disk to minimize access time and maximize storage efficiency.

This level is invisible to users; they don’t see how data is stored or organized. It’s only concerned with the physical storage and retrieval of the data, ensuring that the system operates efficiently.

Advantages of Internal / Physical Level:

  • Efficient Data Storage: Ensures that the data is stored in an optimized manner for quick access and minimum storage requirements.
  • Improved Performance: Data is organized and indexed in a way that speeds up query responses and reduces search times.
  • Data Integrity: Ensures that data is stored correctly and securely, without corruption or loss.
  • Transparency to Users: Users don’t need to know how data is stored, as they only interact with the higher levels of abstraction (external or conceptual).
  • Optimized for Access: Organizes the data in ways that improve retrieval speed and reduce disk space usage, making it more efficient to work with large datasets.

Functions of Three Schema Architecture

Here are the major functions of the three-schema architecture in DBMS, which work together to make databases more efficient, flexible, and secure by isolating users from physical details

I. Data Abstraction:

This function hides the complexities of data storage from the user. The three-schema architecture divides the database system into three levels (external, conceptual, and internal), with data abstraction ensuring that users only interact with the part of the data that is relevant to them.

  • For example, users do not need to know how data is physically stored or organized; they only see what is needed for their tasks, providing a simpler and more focused interface.

II. Data Independence:

This principle ensures that changes made at one level do not impact other levels. It means that the structure or organization of data can be modified (such as how it is stored or indexed) without disrupting the application or user experience.

There are two types of data independence:

  • Logical Data Independence: Changes to the conceptual schema (e.g., altering the structure or relationships between data) do not affect the external schema or application programs.

  • Physical Data Independence: Changes to the internal schema (e.g., altering how data is stored on disk) do not affect the conceptual schema or higher levels.

III. Security & Access Control:

This function ensures secure access to the database by controlling what data each user can view or modify based on their role or privileges.

  • Through the external level (user views), access is restricted so that users only interact with the data they are authorized to see. For example, a student may only access their own academic records, while administrators may have access to the entire student database.

  • It helps protect sensitive information and ensures that users can only perform actions allowed by their role, maintaining data security and integrity.

Advantages of Three Schema Architecture

Three Schema Architecture provides several key advantages, ,major are listed below

I. Improved Data Independence (Logical & Physical):

The three-schema architecture provides strong data independence, meaning changes made at one level (e.g., physical storage or logical structure) do not affect other levels.

  • Logical data independence ensures that changes to the logical schema (how data is organized or related) don’t impact user views or applications.

  • Physical data independence means changes to how data is physically stored (e.g., changing storage formats or indexing) do not affect the logical or external schemas. This flexibility allows for easier updates and modifications without disrupting the system.

II. Better Database Design & Maintenance:

By separating the database into three distinct layers (external, conceptual, and internal), the architecture helps streamline database design and maintenance.

  • It simplifies the process of updating, scaling, or restructuring the database without disturbing the user interface or underlying storage.

  • This separation allows for better management of the database’s logical structure and easier updates or optimizations to the physical storage layer, enhancing overall system efficiency.

III. Enhanced Security with Controlled Access:

The three-schema architecture enhances data security by defining role-based access control at the external level.

  • Users are granted access only to the data relevant to their role, ensuring sensitive or confidential data is not exposed. For example, students can only see their own grades, while administrators have access to the entire database.

  • This separation between user views and the actual data provides better control over who can view, modify, or delete data, reducing the risk of unauthorized access.

IV. Simplified User Interaction through Customized Views:

The external schema provides customized views for different users, simplifying their interaction with the database.

  • Each user or group of users sees only the data that is relevant to them, tailored to their specific needs. For example, faculty members may see course schedules and student grades, while administrators see everything in the database.

  • This personalized access not only improves usability but also enhances user experience by presenting data in a way that is easy to understand and interact with.

V. Better Scalability & Flexibility:

The three-schema architecture makes it easier to scale and extend the database system.

  • Changes to the structure, storage, or user views can be handled independently, making it possible to adjust to growing data needs or changing user requirements without significant disruptions.

  • This flexibility allows businesses and organizations to adapt to new technologies, data models, or user needs without completely redesigning the system, supporting long-term growth and innovation.