Three Schema Architecture

Databases are characterized by a three-schema architecture because there are three different ways to look at them. Each schema is important to different groups in an organization. The graphic below illustrates this architecture and the groups most involved with each schema.

The "Three-Schema Architecture" is a framework used in database management systems (DBMS) to separate the user applications from the physical database. This separation ensures data independence, improves flexibility, and simplifies database design and maintenance. The architecture consists of three levels:
1. External Level (User Views)

• Purpose: Represents the way users or applications view the data.
  • Description:
    • This level describes the part of the database that is relevant to a particular user or group of users.
    • Different users may have different views of the same data, depending on their needs.
    • For example, a student view might include only course grades, while an instructor view might include grades and attendance records.
  • Key Features:
    • Focuses on user-specific data requirements.
    • Hides irrelevant or sensitive data from users.
    • Provides a customized interface for different user groups.

2. Conceptual Level (Logical Schema)

• Purpose: Represents the overall logical structure of the entire database.
  • Description:
    • This level describes what data is stored in the database and the relationships between the data.
    • It provides a global view of the database, independent of any specific user or application.
    • For example, it defines entities like students, courses, and instructors, along with their attributes and relationships.
  • Key Features:
    • Acts as a bridge between the external and internal levels.
    • Ensures data consistency and integrity across the database.
    • Hides the physical storage details from users and applications.

3. Internal Level (Physical Schema)

• Purpose: Describes how the data is physically stored in the database.
  • Description:
    • This level deals with the storage structures, file organization, indexing, and access methods used to store and retrieve data.
    • It defines how the data is represented on disk, including details like data types, compression, and encryption.
    • For example, it specifies whether a table is stored as a heap file or a B-tree index.
  • Key Features:
    • Focuses on performance optimization and storage efficiency.
    • Hides the physical details from the conceptual and external levels.
    • Managed by database administrators (DBAs) and system developers.

Diagram of the Three-Schema Architecture

+-------------------+
| External Level    |  (User Views)
| - User-specific   |
|   views of data   |
+-------------------+
          ^
          | (Mapping)
+-------------------+
| Conceptual Level  |  (Logical Schema)
| - Global view of  |
|   the database    |
+-------------------+
          ^
          | (Mapping)
+-------------------+
| Internal Level    |  (Physical Schema)
| - Physical storage|
|   details         |
+-------------------+

Key Benefits of the Three-Schema Architecture

1. Data Independence:
   • Logical Data Independence: Changes to the conceptual schema (e.g., adding a new table) do not affect the external views.
   • Physical Data Independence: Changes to the internal schema (e.g., changing file structures) do not affect the conceptual or external levels.
2. Modularity:
   • Each level can be modified independently, making the system more flexible and easier to maintain.
3. Security:
   • Users only see the data they are authorized to access, as defined by the external views.
4. Simplified Design:
   • Separating concerns (user views, logical structure, and physical storage) makes the database design process more manageable.

Example: Consider a university database:

1. External Level:
   • A student sees their grades and course schedule.
   • An instructor sees the list of students enrolled in their courses and their grades.
2. Conceptual Level:
   • Defines entities like Students, Courses, and Enrollments, along with their attributes and relationships.
3. Internal Level:
   • Specifies how the Students table is stored on disk, including file organization and indexing.

Conclusion The Three-Schema Architecture is a foundational concept in database design that promotes data independence, security, and flexibility. By separating the user views, logical schema, and physical schema, it ensures that changes at one level do not disrupt the others, making the database system more robust and easier to manage.

• User Views: The notion of user views^[1] was mentioned earlier. SQL is used to create (DDL) data definition language, which enables the creation of tables. The data manipulation language^[2] is used to invoke user views. User views specify which users are permitted access to the specific data in a database. For example, an employee database might contain employee names, addresses, and phone numbers. An employee database is likely to contain employee salaries and other sensitive data as well. A user view can be created that enables a secretary, for example, to gain access to the names and addresses of employees. This is useful when it is time to send out company Christmas card but is not useful for salary information.
• Logical Schema: A database's logical schema is its overall logical plan. This schema is developed with ERD diagrams that define the content of database tables and describe how the tables are linked together for data access. Database designers are responsible for creating the logical schema. Application developers and database administrators may find the logical schema useful for performing certain tasks. The logical design^[3] will be discussed later, is directly related to the logical schema.
  In fact, the most significant end-product of logical design is the logical schema.
• Physical Schema and Design: The physical schema of a database refers to how data is stored on the computer on which it resides. Generally (but not always), physical storage is handled by the RDBMS. Neither users nor database designers get involved in the physical schema. Storage manipulation is a task for the systems programmer, who interact with the RDBMS to perform basic CRUD operations. The physical design will be discussed later is different from the physical schema. Physical design^[4] involves tuning a database to optimize performance, which does involve the databased designer.

Before moving on to the next lesson, click the link below to read more about database three-schema architecture.
Three Schema Architecture
The next lesson introduces a special class of tools often used in database design.

[1]user views:Saved queries created with SQL and specifies which users are permitted access to specific data in a database.

[2]DML:DML (Data Manipulation Language) is a set of SQL commands used to manage and modify data within existing database tables, such as adding, removing, or changing records.

[3]logical design: The second stage in the DBLC: creating a logical schema, followed by data normalization.

[4]physical design: The third stage in the DBLC: tweaking data design elements to optimize database performance.