A3.3.5 · Database programming
Database views
A hotel's housekeeper, its receptionist and its accountant all need the same guest table, and not one of them should see the same columns. A view is how one table becomes three.
Explainer
The GUEST table holds a passport number and the last four digits of a payment card. Housekeeping needs to know which rooms are occupied and for how long. Handing them the whole table to get two useful columns is both awkward and a genuine security problem. The database's answer is to define, once, a named query that shows only what that role should see.
A view looks and behaves like an ordinary table from the outside: you write CREATE VIEW once, then query it exactly like any other table afterward. What it actually is underneath is a saved SELECT statement, dressed up to present data from one or more real tables in whatever shape a particular reader needs.
Multiple views can sit over the same base tables, each showing a different subset to a different kind of user.
A view stores the query, not the data. This is the single idea the whole statement rests on. Nothing is duplicated, so there is no second copy to keep in step with the original. When someone queries the view, the stored SELECT runs against the live base tables at that moment. Change a row in GUEST and every view over it reflects the change immediately, because there was never anything else to update.
Why a database uses them
A view can wrap a complicated multi-table query in one name, so a user can query it without needing to understand the SQL underneath.
Every reader querying the same view gets the same shape of answer back, no matter how much the tables underneath have changed since yesterday.
The schema underneath can change without breaking what the user sees, as long as the view definition is updated to match.
Write the expensive join once, inside the view, and every future user just calls the view by name instead of retyping that same query from scratch.
A large problem can be broken into smaller named pieces, with filters and calculations already applied.
Some views can be written through to the base tables, and some cannot. The conditions are set out further down this page.
Leave a column out of the view's own SELECT, and no permission trick is needed, that user simply has no way to reach it through this view at all.
Explainer
Virtual views and materialized views
The syllabus asks you to describe two kinds. The difference is exactly the one the definition above set up: whether the view stores only the query, or stores the answer as well.
Simple view
Reads from exactly one table and nothing fancier than picking which columns to show, no join, no aggregate function anywhere in it.
Example: Step 2 of the stepper below.
Complex view
Pulls from more than one table, or does something heavier than a plain field pick, a JOIN, a GROUP BY, an aggregate, anything that makes the underlying SELECT genuinely non-trivial.
Example: Step 3 of the stepper below.
Materialized view
Stores a pre-computed result set from its SELECT query, refreshed periodically rather than re-run on every query.
Example: Steps 4 and 5 of the stepper below.
Simple and complex views are both virtual: they store nothing but the query, and they re-run it every single time they are read.
A materialized (snapshot) view breaks that rule deliberately. It runs the query once, stores the result, and serves that stored result to readers until it is refreshed. It is the only kind of view that holds data of its own.
Choosing between them
This is A3's guiding question in miniature, what are the principles, structures and operations that form the basis of database systems, applied to a single decision: is it more important that the answer is current, or that the answer is fast?
Virtual view
Real example: the hotel's front desk needs to know which rooms are occupied right now. A booking made thirty seconds ago must appear.
Advantage
- Always current (the query runs against the live base tables at read time, so it cannot be out of date)
- Uses no extra storage (only the query definition is saved, not a second copy of the rows)
- Nothing to maintain (there is no refresh schedule to set, forget, or get wrong)
Disadvantage
- Slower on heavy queries (a join or aggregate over a large table is recalculated in full every single time someone reads the view)
- Repeated cost (a hundred users reading the same report means the same expensive calculation runs a hundred times)
Materialized (snapshot) view
Real example: the accountant's monthly revenue report, aggregated over every stay in the hotel's history. Nobody minds if it was calculated at 3am.
Advantage
- Much faster to read (the answer already exists, so reading it avoids re-running the query entirely)
- Cost paid once (one refresh serves every reader until the next refresh, instead of one calculation per reader)
Disadvantage
- Can be out of date (it shows the data as it was at the last refresh, so any change since then is invisible)
- Uses real storage (unlike every other view, it holds an actual copy of the result set on disk)
- Needs refreshing (someone has to decide how often, and that schedule is a trade-off between freshness and cost all over again)
Worked example
Three views on one hotel
Two base tables, three roles, three different views. Click through and watch the same data get presented three different ways.
Explainer
Read-only or updatable
Since a virtual view stores no data of its own, writing to one is really a request to write to the tables underneath it. The database allows that only when it can work out, without ambiguity, which base row each change belongs to.
An updatable view passes changes through to the underlying tables it was created from. If the conditions below are not met, the view is read-only and can be queried but not written to.
The three conditions for an updatable view
Trace the view back to one single table (or one already-updatable view) and nothing else. One source only, so a written change has exactly one place to land, never a choice between two.
Every base-table field left out of the view must allow NULL values. Inserting through the view can only fill the columns the view exposes, so any column it cannot reach has to be able to sit empty.
The view's own SELECT has to stay simple, no sub-query buried inside it. In practice that also rules out anything which collapses or combines rows before they reach the output: grouping with HAVING, de-duplicating with DISTINCT, summarising with an aggregate, or pulling rows together from more than one table. Every one of those breaks the same thing: a clean one-to-one link between an output row and a single base row.
Look at what condition 3 excludes and the pattern behind all three conditions becomes obvious. Every excluded feature destroys the one-to-one link between a row in the view and a row in a base table. The hotel's occupancy view (Step 3) joins two tables, so it is read-only. Its revenue view (Step 4) uses SUM, so it is read-only. Only the plain single-table housekeeping view (Step 2) could ever be written through.
Check your understanding
A row is updated in the base table. Which kind of view might still show the old value afterwards?
Common mistake
A3.3.5RecurringViews are widely described as "a copy of part of a table." That description is wrong for every virtual view and only accidentally right for a materialized one, and once a candidate holds it, the rest of the statement collapses: they cannot say why a view is always up to date, why most views are read-only, or what actually separates the two types the syllabus names.
What a weak answer looks like: "A view is a copy of selected columns from a table that users can look at instead of the real data." If it were a copy, it would need syncing, and the consistency and independence benefits would not exist.
What it should say: "A view is a stored SELECT query that is presented as a virtual table. It holds no data of its own, so it reads the live base tables each time it is queried. A materialized view is the exception: it stores its pre-computed result set and is refreshed periodically." The stored-query idea comes first, then the exception.
Revision questions
Past-paper style. Write an answer, then reveal the marking points and mark yourself.
[2]Describe what is meant by a database view.
Marking points
评分要点
- A view is a named stored query that behaves like a virtual table.
- It holds no data of its own; its rows come from running the underlying query on the base tables.
- 视图是一个具名的、已存储的查询,行为像一张虚拟表。
- 它本身不持有数据;它的行来自对基表运行底层查询。
[4]Distinguish between a virtual view and a materialized view.
Marking points
评分要点
- A virtual view runs its query every time it is accessed, so it always shows current data but costs processing on each read.
- A materialized view stores the result physically, so reads are fast, but the data can be stale until the next refresh.
- Virtual views suit data that changes often; materialized views suit heavy analytical reads over slow-changing data.
- 虚拟视图每次被访问都运行其查询,所以总是显示当前数据,但每次读取都有处理开销。
- 物化视图把结果物理地存下来,所以读取很快,但在下次刷新前数据可能过时。
- 虚拟视图适合经常变化的数据;物化视图适合对缓慢变化数据的繁重分析读取。
[5]A hotel gives housekeeping staff access to a view rather than to the GUEST table itself. Describe two benefits of this decision, and explain why the view is likely to be read-only.
Marking points
评分要点
- Benefit: security. The view can expose only the room and dates housekeeping needs and hide payment and passport fields.
- Benefit: simplicity. Staff query one straightforward view instead of a complex joined structure.
- Benefit: the underlying GUEST table can be restructured without changing what housekeeping sees.
- It is likely read-only because the view omits required columns of GUEST, so the database could not unambiguously insert a full valid row through it.
- 好处:安全。视图可以只暴露客房保洁所需的房间和日期,隐藏付款和护照字段。
- 好处:简单。员工查询一个直观的视图,而不是复杂的连接结构。
- 好处:底层 GUEST 表可以重构而不改变保洁人员所看到的内容。
- 它很可能是只读的,因为视图省略了 GUEST 的必填列,数据库无法通过它明确地插入一条完整有效的行。
Flashcards
Active recall for this statement's key terms.
Keep this statement's practice with you
Free account: unlock every revision question and flashcard for A3.3.5, and every other IB CS 2027 statement.