IB COMPUTER SCIENCE | A3.3.5 Database Views (HL ONLY)

Lesson objective

Describe virtual and materialized views, their freshness and performance, query simplification, data independence, security and read-only or updatable behaviour.

Learn

A3.3.5 | DATABASE VIEWS (HL ONLY)

01 | SAME DATABASE, DIFFERENT USEFUL PRESENTATIONS

A school stores assessment results and internal support notes in one database. A results page needs names and scores, a tutor needs only Class A, and a head of department needs a class summary. Giving everyone the same full table would be awkward and could reveal information they should not receive.

A database view is a named query-based presentation of data that can be queried much like a table. It can select columns, filter rows, join tables or calculate summaries. It is a database object, not simply a screen in an application.

A virtual view stores its query definition and obtains results from the underlying data when queried. A materialized view also stores the query result, so reading that stored result can avoid repeating expensive work.

For describe, give the main characteristics and how each type behaves. Connect the definition to freshness, query simplification, storage, access and update behaviour.

02 | THE BASE TABLE IS THE SOURCE

AssessmentIDStudentNameClassGroupScoreSupportNote
1MayaA80Extension task
2LeonA60Check working
3AishaANULLAwaiting result
4NoahB90Extension task
CREATE TABLE Assessment (
    AssessmentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(60) NOT NULL,
    ClassGroup VARCHAR(10) NOT NULL,
    Score DECIMAL(5,2) CHECK (Score >= 0 AND Score <= 100),
    SupportNote VARCHAR(120)
);

A base table stores the underlying records. The four fictional rows include a missing Score for Aisha and internal notes that are not needed in a general results report.

A view can expose only the required columns and rows while the base table remains unchanged. Excluding a column from one view does not delete the stored column or hide it from every other route to the database.

The key idea is a controlled presentation derived from shared data. Permissions and the DBMS’s view security rules determine who can actually use that presentation and whether other access routes remain available.

03 | A VIRTUAL VIEW STORES A DEFINITION

CREATE VIEW PublishedResults AS
SELECT AssessmentID, StudentName, ClassGroup, Score
FROM Assessment
WHERE Score IS NOT NULL;

CREATE VIEW names the view PublishedResults. AS introduces the SELECT query defining its rows and columns. It exposes four columns and filters out records whose Score is NULL. SupportNote is not selected.

This ordinary view does not store its own persistent copy of the result rows. Conceptually, referencing it asks the DBMS to evaluate its definition against the base data visible to that query. The optimiser may combine it with the surrounding query.

The saved object is reusable. Other queries can refer to PublishedResults without writing its column selection and missing-score filter every time. This reduces repeated query text, not necessarily the underlying calculation work.

04 | QUERY THE VIEW AS A SOURCE

SELECT StudentName, Score
FROM PublishedResults
WHERE ClassGroup = 'A'
ORDER BY StudentName, AssessmentID;
StudentNameScore
Leon60
Maya80

FROM names the view instead of Assessment. The view already restricts which records and columns are available through it; the outer query adds a Class A filter and a requested order.

Aisha is excluded by the view’s Score IS NOT NULL condition. SupportNote cannot be selected through PublishedResults because it is not one of the view’s columns.

This is query simplification: the user sees a smaller, more relevant interface. Always specify ORDER BY in the query when an output order is required rather than relying on a view behaving like a sorted spreadsheet.

05 | FRESH RESULTS DO NOT MEAN A LIVE SCREEN

UPDATE Assessment
SET Score = 100
WHERE AssessmentID = 1;

After Maya’s correction is committed and visible to a new query, querying PublishedResults returns her Score as 100. There is no separately stored virtual-view result that must be manually refreshed.

However, a result already downloaded to a browser does not update itself just because the base table changes. The application must make another request or use an appropriate update mechanism. Distinguish the database view from a displayed page.

“Current” also follows transaction visibility rules. A long-running transaction may see an earlier consistent snapshot, and uncommitted changes in another session are not simply guaranteed to appear. A virtual view uses the data visible to the query, not every change occurring anywhere instantly.

06 | HIDE COMPLEXITY BEHIND A NAME

CREATE VIEW ClassSummary AS
SELECT ClassGroup, COUNT(Score) AS RecordedCount,
       AVG(Score) AS MeanScore
FROM Assessment
GROUP BY ClassGroup;
SELECT ClassGroup, RecordedCount, MeanScore
FROM ClassSummary
ORDER BY ClassGroup;
ClassGroupRecordedCountMeanScore
A270
B190

This output starts from the original data, before Maya’s correction. Class A’s two known scores average 70; Aisha’s NULL is not counted as a known score. The stored view definition hides the grouping and aggregate expressions from the reporting query.

A more complex view could also hide joins across Student, Assessment and Course. Consumers can use one named interface without repeatedly reconstructing those relationships.

The complexity still exists in the definition and the DBMS’s work. If its joins or grouping are wrong, every consumer can receive the same wrong answer. Centralising logic makes it easier to maintain consistently, but does not prove it correct.

07 | CONSISTENCY THROUGH A SHARED DEFINITION

Suppose “recorded count” is defined once as COUNT(Score) in ClassSummary. Different reports using that view share the same rule, rather than one report counting rows and another counting only known scores without noticing the difference.

Virtual views derive results from shared base records, so users do not separately edit a duplicate results table to keep it synchronised. Correcting the source can affect later queries through all relevant virtual views.

This supports a consistent representation, but views do not replace primary keys, foreign keys, validation or transaction rules. They also cannot make conflicting business definitions agree automatically. State the intended population and meaning of each output column.

08 | LOGICAL DATA INDEPENDENCE HAS LIMITS

A view can provide a stable interface while an administrator changes some underlying design. For example, a report may continue using a view column called StudentName even if the underlying name-storage arrangement changes and the view is redefined to produce the same output.

This is a form of logical data independence: consumers can be shielded from some changes to the logical schema. It is useful when many applications rely on the same interface.

It is not unlimited protection. Removing a required base column can break a dependent view, and changing the meaning, type or availability of a published view column can break consumers. The administrator must preserve and test the view’s contract.

Prefer explicit output columns for a controlled interface. Do not assume SELECT * is a permanent promise that every future schema change will be handled safely or exposed identically by every DBMS.

09 | A MATERIALIZED VIEW STORES THE RESULT

CREATE MATERIALIZED VIEW ClassSummarySnapshot AS
SELECT ClassGroup, COUNT(Score) AS RecordedCount,
       AVG(Score) AS MeanScore
FROM Assessment
GROUP BY ClassGroup;

This example uses PostgreSQL-style syntax. In addition to the defining query, a materialized view stores the resulting rows. ClassSummarySnapshot initially stores A, 2, 70 and B, 1, 90.

Reading this stored summary need not calculate the full grouping from Assessment on every request. This can be useful when expensive reports are read frequently and some delay in reflecting changes is acceptable.

The cost is additional result storage and work to create and maintain it. If the base records change while the stored result is not maintained, that result becomes stale relative to the newer source data. Syntax and maintenance mechanisms vary by DBMS.

10 | SNAPSHOT TIME AND REFRESH TIME

REFRESH MATERIALIZED VIEW ClassSummarySnapshot;

In the PostgreSQL-style example, REFRESH reruns the defining query and replaces the stored result. If Maya has changed from 80 to 100, the new Class A mean becomes (100 + 60) ÷ 2 = 80.

Until refresh, the stored Class A mean can remain 70 even though a new virtual ClassSummary query gives 80. The materialized result can correctly represent the earlier snapshot while being out of date for a current-results question.

Other DBMSs may offer scheduled, incremental, on-demand or on-commit maintenance under specific restrictions. A materialized view is not inherently always stale or automatically fresh: its refresh policy determines the lag and the extra work.

A snapshot view is not a substitute for backups or a complete history. Refresh can replace old results rather than preserve each historical version.

11 | WATCH THE TWO VIEW TYPES RESPOND

Change a fictional source record, then compare newly evaluated virtual results with the stored materialized summary. Refresh the snapshot to make it reflect the current model. Notice the version labels.

Every display update below represents a new read after the model’s change; it does not claim that a real open web page updates itself. This activity uses JavaScript and fixed data, not a live DBMS, permissions system or transaction engine.

BASE TABLE

VIRTUAL PUBLISHED RESULTS

New query; internal notes excluded; only recorded scores.

VIRTUAL CLASS SUMMARY

New query over the visible source data.

MATERIALIZED CLASS SUMMARY

12 | PERFORMANCE IS A TRADE-OFF

TypeReading the resultMain costs and limitations
VirtualEvaluate the view definition against visible base data; the optimiser may transform the query.Complex joins or grouping can still be expensive; no separate persistent result storage for the ordinary view.
MaterializedRead stored result rows, potentially with supported indexes.Storage plus refresh/maintenance work; freshness depends on policy.

A virtual view can make SQL shorter without inherently making execution faster. Its performance depends on the underlying query, indexes, data and execution plan.

A materialized view can avoid repeated expensive derivation, but it is not automatically the fastest answer to every query. A small simple lookup may not benefit enough to justify its maintenance.

When updates are frequent, synchronous maintenance can add write cost; asynchronous refresh can leave a lag. Choose the balance according to the report’s freshness requirement and measured workload.

13 | VIEWS CAN SUPPORT CONTROLLED ACCESS

PublishedResults excludes SupportNote, so granting a role access to that view can offer the required result columns without exposing the note through that object. A filtered view can similarly restrict the rows offered through it.

The security comes from correctly configured privileges and view semantics. Merely creating a view does not remove existing rights to query Assessment directly, and a hidden field in a website is not a database access policy.

A real deployment must check view-owner versus view-invoker permissions, underlying access, allowed functions and other routes to the same data. Some DBMSs offer additional security options for filtered views.

Views do not encrypt data, identify the logged-in user automatically or prevent every inference from a permitted summary. Both virtual and materialized results need appropriate access control. These are design features to configure and test, not guarantees supplied by the word “view”.

14 | READ-ONLY AND UPDATABLE VIEWS

A read-only view can be queried but does not provide a permitted direct route for INSERT, UPDATE or DELETE through that view. Its base tables can still change through authorised routes.

An updatable view allows supported changes through the view to map to underlying base records. It is not a separate editable copy: a successful UPDATE through it changes the base data.

Simple single-table views are often candidates for automatic updatability in DBMSs that support it. Grouped or aggregated views are commonly not automatically updatable because a summary value may not identify a unique underlying change. Permissions and DBMS rules still apply.

A virtual view is therefore not necessarily read-only. Conversely, being able to query a view like a table does not imply every table modification is supported. Some systems provide special rules or triggers for additional cases.

15 | AN UPDATE THROUGH A SIMPLE VIEW

CREATE VIEW ClassAResults AS
SELECT AssessmentID, StudentName, ClassGroup, Score
FROM Assessment
WHERE ClassGroup = 'A'
WITH CHECK OPTION;
UPDATE ClassAResults
SET Score = 75
WHERE AssessmentID = 2;

This is a PostgreSQL-style example of a simple automatically updatable view, subject to the DBMS’s rules and permissions. The UPDATE targets Leon’s base Assessment record and changes its Score to 75. There is no independent ClassAResults copy to synchronise.

The filter defines which records appear through the view. WITH CHECK OPTION requires applicable INSERT/UPDATE operations through it to satisfy the filter, so changing ClassGroup from A to B through this view is rejected under the demonstrated semantics.

Without a suitable check option, an otherwise supported update might make a row stop appearing through the view. CHECK OPTION is about the view condition; it does not replace all base-table constraints or grant user permissions.

These examples illustrate behaviour, not universal syntax. For instance, some DBMSs require additional mechanisms to modify views, and PostgreSQL does not allow direct INSERT/UPDATE/DELETE on its materialized views.

16 | WHY A SUMMARY IS USUALLY READ-ONLY

ClassSummary shows A’s mean as 70 in the original example. What should UPDATE ClassSummary SET MeanScore = 80 change? Maya’s score, Leon’s score, both, or a newly recorded score for Aisha? The summary alone does not specify one valid underlying operation.

This ambiguity is why aggregate views are generally not automatically updatable. Change the intended base records using a precise operation instead, then evaluate the virtual summary again or refresh the materialized result as appropriate.

Do not confuse read-only with frozen. A read-only virtual summary can show different results after the base data changes. Read-only describes the allowed modification route, not whether the visible values can ever change.

17 | DESCRIBE THE DIFFERENCES SIDE BY SIDE

FeatureVirtual viewMaterialized view
Persistent stored objectQuery definition.Query definition plus stored result.
Source correctionA new query uses source data visible to it.Stored result changes according to its maintenance policy.
FreshnessFollows query/transaction visibility.Can lag behind the source between updates or refreshes.
Repeated costly reportsDerivation may be repeated or transformed by the optimiser.Stored summary can reduce repeated derivation.
Resource useUnderlying query work; ordinary view result not separately persisted.Result storage plus refresh/maintenance resources.
Modification through itMay be updatable for supported definitions and permissions.DBMS-specific; PostgreSQL materialized results are not directly DML-updatable.
Access controlPrivileges and view semantics must be configured.Stored results also need privileges and suitable protection.

18 | CHOOSE A VIEW FOR THE PURPOSE

A live equipment-availability lookup needs decisions based on current authorised data. A virtual view over the relevant records can simplify access without introducing a separately refreshed result, although concurrency and transaction design still matter.

A management dashboard summarising millions of completed sales may tolerate a nightly snapshot. A materialized summary can reduce repeated query work if its refresh completes within the required window and readers understand its timestamp.

A tutor-facing report can use a restricted view interface for relevant rows and columns with suitable access controls. A complex joined interface can also shield consumers from some schema details.

The description should name the mechanism and consequence: “The materialized view stores calculated rows, therefore readers can reuse them; however, the report may omit changes made since its last refresh.”

19 | CHECK THE CHARACTERISTIC

Choose the best description for each requirement. Your first checked response counts; restart for another attempt.

20 | WRITE A CLEAR DESCRIPTION

Start with what the view stores, then explain how results are obtained. Add its main use, freshness behaviour, modification limits and relevant access controls.

For example: “A virtual view stores a query definition and derives results from the base data visible to each query. It can hide a complex join and offer a simpler interface without storing a separate result. A materialized view also stores the result, which can speed repeated reports but needs storage and maintenance; its data can be older than the source between refreshes.”

Check for overstatements: virtual does not mean an automatically updating browser, materialized does not mean a backup, read-only does not mean permanently unchanged, and hiding a column through one view does not remove every other permission to read it.

References: virtual views and updatability and materialized views.

MATCH THE VIEW CONCEPT TO ITS ROLE

Terminology

Terminology

View

A named query-based presentation of data that can be queried as a database source.

Base table

A table storing the underlying records used by a view.

Virtual view

An ordinary view storing its definition and deriving results from visible base data when queried.

View definition

The saved query describing the view’s output.

CREATE VIEW

Defines an ordinary virtual view.

Materialized view

A view that stores the result of its defining query as well as its definition.

Snapshot view

A stored query result representing source data at a particular maintenance point.

Refresh

Recomputing or maintaining a stored materialized result under the DBMS’s rules.

Stale result

A stored result older than relevant newer source data.

Refresh policy

The conditions or schedule controlling materialized-result maintenance.

On-demand refresh

Refresh initiated when requested.

Scheduled refresh

Refresh performed at planned intervals.

Incremental maintenance

Updating affected result portions where the DBMS and query support it.

Query simplification

Allowing consumers to refer to a view instead of repeating its underlying logic.

Data complexity

Details such as joins, filtering and grouping hidden behind a simpler interface.

Shared definition

One maintained rule used by multiple consumers for consistent representation.

Logical data independence

Shielding consumers from some logical-schema changes through a stable interface.

View contract

The expected names, types and meanings of the columns exposed to consumers.

Read-only view

A view that does not permit direct data modification through that interface.

Updatable view

A view supporting defined modifications that map to underlying base records.

WITH CHECK OPTION

A supported rule requiring applicable changes through a filtered view to satisfy its condition.

Aggregate view

A view exposing grouped or calculated summaries, generally not automatically updatable.

Privilege

A permission to perform a database operation on an object.

View-owner semantics

A permission model using relevant rights of the view’s owner, subject to DBMS rules.

View-invoker semantics

A permission model checking relevant rights of the user querying the view.

Row filtering

Restricting which records appear through a view.

Column selection

Exposing only specified fields through a view.

Transaction visibility

The rules determining which committed or local data changes a query can see.

Maintenance overhead

Resources spent keeping stored derived results usable and sufficiently fresh.

Query optimiser

DBMS component choosing execution strategies while preserving query meaning.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which describe a virtual view?
2. Which describe a materialized view?
3. Which follow Maya’s visible source correction before snapshot refresh?
4. Which describe simplification and independence?
5. Which describe security accurately?
6. Which describe view modification?
7. Which describe read-only views?
8. Which describe WITH CHECK OPTION in the demonstrated simple view?
9. Which are materialized-view trade-offs?
10. Which descriptions avoid common mistakes?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Describe a virtual database view.

2. Describe PublishedResults and its output from the original data.

3. Describe what happens after Maya’s correction is visible to a new query.

4. Describe a materialized view and its freshness.

5. Describe the purpose and cost of refresh.

6. Describe how views hide complexity.

7. Describe logical data independence using a view.

8. Describe how a view can support security and one limitation.

9. Describe the difference between read-only and updatable views.

10. Describe why a class-average view is generally not automatically updatable.

11. Describe WITH CHECK OPTION in the demonstrated Class A view.

12. Describe suitable view types for a current lookup and a nightly dashboard.

DESCRIBE IT | RESULTS AND MANAGEMENT REPORTS

Describe a suitable view interface for a current results lookup and a stored nightly class summary. Explain what each stores, how source corrections become visible, the performance and storage implications, and the permissions needed to limit access. Describe why a summary may be read-only while a supported simple view can map changes to base records.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.3.5 Database Views (HL ONLY) is coming soon. Complete the matching task and written questions in your exercise book.