Database Views and the Concept of a Subschema

Learning objective

By the end of this section, you will be able to distinguish among a module, relational model, schema, and subschema, and identify the correct answer to the question:

The way a particular application views the data from the database that the application uses is a
(i) module
(ii) relational model
(iii) schema
(iv) subschema

The correct answer is (iv) subschema.

A subschema describes the portion of a database that a particular application program can see and use. In modern database terminology, this idea closely corresponds to an external schema or database view. A subschema may expose only selected data items, records, relationships, or operations while hiding the rest of the database.

Footnotes

1. Schemas and Subschemas - Broadcom documentation defining a subschema as the database view seen by an application program. ↩

Why “subschema” is correct

A database may contain many tables and relationships, but an individual application usually needs only a limited portion of that information. For example:

• A payroll application may access employee identifiers, salaries, tax information, and deductions.
• A library application may access member records, books, loans, and return dates.
• A student portal may access course registrations, grades, and schedules.

Each application can be given a specialized view rather than access to the complete database. That specialized application-level description is called a subschema.

The subschema provides:

1. Data abstraction — the application does not need to understand the entire database.
2. Security — sensitive or irrelevant data can be hidden.
3. Simplicity — the application works with only the fields and relationships it requires.
4. Logical independence — changes to unrelated parts of the database need not affect the application.

Footnotes

1. Three Level Database Architecture - Educational reference explaining external schemas and application-specific views. ↩

2. ANSI-SPARC Architecture - Overview of external views, data hiding, and data independence in the three-level architecture. ↩

The three-schema architecture

The three-schema architecture separates a database system into three levels:

The external level may contain several different views, while the conceptual level normally describes the complete logical database. The internal level describes how the data is physically stored.

A subschema belongs to the external level because it describes the database as seen by a particular application.

Footnotes

1. The Three-Level ANSI-SPARC Architecture - Explanation of external, conceptual, and internal database levels. ↩

Example: university database

Suppose a university database contains the following relations:

• STUDENT(StudentID, Name, Program, DateOfBirth)
• COURSE(CourseID, Title, Credits)
• ENROLLMENT(StudentID, CourseID, Semester, Grade)
• PAYMENT(StudentID, Amount, PaymentDate)

A student portal does not need direct access to payment records. Its subschema might expose:

1CREATE VIEW StudentPortal AS
2SELECT
3    s.StudentID,
4    s.Name,
5    s.Program,
6    e.CourseID,
7    e.Semester,
8    e.Grade
9FROM STUDENT AS s
10JOIN ENROLLMENT AS e
11  ON s.StudentID = e.StudentID;

This view gives the student portal the information it needs while excluding PAYMENT.Amount and other sensitive data. In this example, StudentPortal is an application-specific external view and illustrates the practical meaning of a subschema.

Footnotes

1. CREATE VIEW - PostgreSQL documentation describing views as query-defined representations of data. ↩

Distinguishing the four answer choices

The word schema is sometimes used broadly for any database description. However, in traditional database architecture and examination terminology, the application-specific view is more precisely called a subschema.

Footnotes

1. Schemas and Subschemas - Broadcom documentation defining a subschema as the database view seen by an application program. ↩

Schema versus subschema

A schema can be understood at different levels:

• The conceptual schema describes the complete logical database.
• The internal schema describes physical storage structures.
• An external schema describes a particular user or application view.
• In traditional terminology, an external schema for an application is often called a subschema.

The relationship can be represented as:

$$\text{Complete database schema} \supseteq \text{Application subschema}$$

The subschema is therefore not usually a separate database. It is a controlled and simplified description of a selected portion of the larger database.

Footnotes

1. CSC 261/461 Database Systems Lecture - Lecture material describing conceptual, external, and internal schemas. ↩

Benefits of subschemas

Security

A subschema can hide confidential attributes such as salaries, passwords, medical information, or payment details. It supports the principle of least privilege by exposing only what an application needs.

Simplicity

Applications can use a smaller and more stable data representation. Developers do not need to understand every table, relationship, or constraint in the database.

Data independence

The DBMS can change physical storage structures without requiring changes to an application's external view. This separation is a central goal of the three-schema architecture.

Application specialization

Different applications can use different views of the same underlying data. A reporting system, customer portal, and administrative system may all access the same database through distinct subschemas.

Footnotes

1. ANSI-SPARC Architecture - Overview of external views, data hiding, and data independence in the three-level architecture. ↩

2. The Three-Level ANSI-SPARC Architecture - Explanation of external, conceptual, and internal database levels. ↩

Final answer

The way a particular application views the data from the database that it uses is called a:

$$\boxed{\text{(iv) subschema}}$$

A subschema is an application-specific external view of the database. It contains the data relevant to that application and hides the remaining database details.