Storing User Views

A user view is a customized way of looking at data. Instead of exposing every base table to every user, you create views that show just the columns and rows required for a particular task, department, or application.
Because a view is not a base table and does not store its own data, it can:

• Hide sensitive columns while still allowing queries.
• Join multiple tables into a single, convenient result set.
• Include calculated columns (for example, totals or derived values).

Earlier in the course, we distinguished between:

1. Base tables – physical tables that persist data on disk.
2. Virtual tables – logical tables defined by queries (views).

A view is a virtual table built from one or more base tables in the database. The base tables contain the stored data; the view contributes the definition of how that data should be presented.
In the relational model, a view is called virtual because:

• It does not maintain its own set of rows on disk.
• It returns rows dynamically each time the view is queried.
• Only the view definition (the SQL query) is stored in the system catalog.

Most modern relational database systems (for example Oracle Database, SQL Server, PostgreSQL, MySQL, and others) support views. Some tools and products, such as Microsoft Access, expose views as "saved queries," but the underlying concept is the same: you store the query definition and then treat the result as a reusable, named table.

Views enable you to see the information in your database from many different angles. They are especially useful when they:

• Combine multiple related tables into a single, logical result.
• Simplify complex joins and filters that users need frequently.
• Provide a stable interface for applications even when the underlying schema evolves.

A base table is a physical table that stores data and is written to disk. Every row in a well-designed base table follows the rules of normalization and is typically identified by a primary key.

A virtual table, by contrast, is created “on the fly” by a query and exists only in memory while the query runs. When you query a view, the database engine:

1. Expands the view definition into the underlying SQL.
2. Retrieves the appropriate rows and columns from base tables.
3. Returns the final result set as if it were a standalone table.

The CREATE TABLE statement defines a base table and allocates storage. A view uses a CREATE VIEW statement, which stores only the metadata—the view name, column list, and SELECT statement—in the data dictionary. The engine uses that metadata to reconstruct the virtual table for each query.

Consider the example illustrated below.

The first table is a base table listing individual orders that “Stories on CD” has placed with its distributors. Each row represents a single order. The base table follows the normal rules for relational tables: each row is uniquely identified (typically by a primary key), and columns store atomic values.

The second table is a virtual table^[2] created by a view. It groups the orders by distributor and calculates a total order amount for each distributor. That total is a derived (calculated) value based on multiple rows from the base table. As a result, the virtual table does not have to follow all the rules that apply to base tables, and it does not store its own data. Instead:

• The definition of the view is stored in the system catalog.
• The summarized rows are computed each time you query the view.

Modern relational databases store views as metadata in the data dictionary or system catalog. When you create a view, you:

1. Assign a name to the view.
2. Provide a SELECT statement that defines the view’s columns and rows.
3. Optionally specify column aliases or security options.

Only this definition is stored; the data remains in the underlying base tables.

A typical view definition might look like the following:

CREATE VIEW DistributorOrderTotals AS
SELECT   d.DistID,
         d.DistName,
         SUM(o.OrderTotal) AS TotalOrderAmount
FROM     Orders o
JOIN     Distributors d
   ON    o.DistID = d.DistID
GROUP BY d.DistID, d.DistName;

In this example:

• Orders and Distributors are base tables.
• DistributorOrderTotals is a view that presents summarized data.
• The database engine expands the view into its underlying query at runtime.

For users and applications, the view behaves like a table, but all changes to the underlying data are automatically reflected when the view is queried.

The specifics of SQL syntax (including updatable vs. non-updatable views, security, and performance) are covered in more detail in SQL-focused modules. If you want a deeper introduction to SQL, see the two-part Introduction to SQL series. The next lesson discusses the benefits of user views in more detail.

Before moving on to the next lesson, click the Exercise link below to reinforce your understanding of how user views are stored and used.
Storing User Views – Exercise

[1] Base tables: Tables that physically store rows of data on disk and form the foundation for views.

[2] Virtual table (view): A named query whose definition is stored in the database catalog. The view itself does not store data; the rows are recomputed from base tables whenever the view is queried.