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
| AssessmentID | StudentName | ClassGroup | Score | SupportNote |
|---|---|---|---|---|
| 1 | Maya | A | 80 | Extension task |
| 2 | Leon | A | 60 | Check working |
| 3 | Aisha | A | NULL | Awaiting result |
| 4 | Noah | B | 90 | Extension 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;
| StudentName | Score |
|---|---|
| Leon | 60 |
| Maya | 80 |
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;
| ClassGroup | RecordedCount | MeanScore |
|---|---|---|
| A | 2 | 70 |
| B | 1 | 90 |
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
| Type | Reading the result | Main costs and limitations |
|---|---|---|
| Virtual | Evaluate 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. |
| Materialized | Read 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
| Feature | Virtual view | Materialized view |
|---|---|---|
| Persistent stored object | Query definition. | Query definition plus stored result. |
| Source correction | A new query uses source data visible to it. | Stored result changes according to its maintenance policy. |
| Freshness | Follows query/transaction visibility. | Can lag behind the source between updates or refreshes. |
| Repeated costly reports | Derivation may be repeated or transformed by the optimiser. | Stored summary can reduce repeated derivation. |
| Resource use | Underlying query work; ordinary view result not separately persisted. | Result storage plus refresh/maintenance resources. |
| Modification through it | May be updatable for supported definitions and permissions. | DBMS-specific; PostgreSQL materialized results are not directly DML-updatable. |
| Access control | Privileges 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.