IB Computer Science Topic A3.3.5: Different Database Views Notes

A View Is a Stored Query That the User Treats Like a Table

1. A view is a named SELECT statement saved in the database and queried by name.
2. Once created, it can be used anywhere a table can, so SELECT * FROM SalesSummary works without the user knowing what it joins.
3. The two kinds differ in one thing: whether the database stores only the query or also the rows it produced.

Virtual Views Store the Query, Materialized Views Store the Answer

1. Virtual view
   1. Only the query definition is saved, and no data is stored.
   2. The query runs against the base tables every time the view is read, so the result is always current.
   3. That freshness costs time, because the underlying joins are repeated on every access.
2. Materialized (snapshot) view
   1. The rows the query produced are written to disk as a physical structure.
   2. Reading it is fast, because the work was done once when the view was refreshed.
   3. The data is a snapshot of an earlier moment, so it is stale until the next refresh.

The Two Types Side by Side

Views Hide Complexity and Simplify the Queries People Write

1. A report may need four joins, a date filter and two aggregates, which is a lot to ask of a non-technical user.
2. Wrapping that in a view reduces their query to SELECT * FROM MonthlyReport;
3. The complex logic is written once and reviewed once, instead of being retyped and mistyped by every user who needs it.
4. This is abstraction applied to a database, the same idea B1.1.2 names in computational thinking.

Consistency and Independence Come From Having One Definition

1. Consistency follows because every user reads the same stored definition, so two departments cannot calculate revenue two different ways.
2. A virtual view reflects any change to the base tables at once, so nobody is reading yesterday's figures by accident.
3. Independence means applications depend on the view rather than on the tables underneath it.
   1. If a table is split in two during a redesign, the view can be rewritten to produce the same columns and no application needs changing.

Performance: The Trade Is Freshness Against Speed

1. A virtual view adds no storage but no speed either, since the underlying query runs in full on every read.
2. A materialized view precomputes the result, turning an expensive aggregation into a simple read.
3. That gain is paid for in disk space and in the cost of each refresh.
4. The busier the write traffic, the more often a refresh is needed and the smaller the advantage becomes.

Most Views Are Read-Only, and Only Simple Ones Are Updatable

1. A read-only view can be queried but not written to, so INSERT, UPDATE and DELETE against it are refused.
2. An updatable view passes writes through to the base table underneath it.
3. A view is normally updatable only when each of its rows maps to exactly one row of one base table.
4. Views built with joins, GROUP BY, DISTINCT or aggregate functions are not updatable, because one view row came from many base rows.
5. There is no way for the database to know which of those rows a change to a total was meant to alter.

Views Act as a Security Boundary Over Sensitive Columns

1. Permissions can be granted on a view while being withheld on the tables it reads.
2. A view can drop columns, so staff who need names and job titles never see the salary column at all.
3. A WHERE clause in the view restricts rows too, so a regional manager sees only their own region.
4. The restriction is enforced by the database rather than by the application, so it holds however the user connects.

Choosing a View Type in an Exam Answer

1. Name the type, then justify it from the scenario rather than from the general definition.
2. A stock-level display in a warehouse needs live data, so a virtual view is appropriate.
3. An overnight sales report over millions of rows justifies a materialized view because the same totals are read repeatedly.