8.1 Virtual Views

Virtual Views

Section outline

• Introduces virtual views as SQL relations that do not physically exist but are defined by an expression, much like a query.
• Compares virtual views to persistent base tables defined with a CREATE TABLE statement.
• Explains that views can be queried as if they existed physically, and in some cases, can be modified.

#1

read: Section 8.1: Virtual Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduces virtual views as SQL relations that do not physically exist but are defined by an expression, much like a query.
- Compares virtual views to persistent base tables defined with a CREATE TABLE statement.
- Explains that views can be queried as if they existed physically, and in some cases, can be modified.

In a database system, relations can be categorized based on their physical persistence. While base tables are physically stored structures, a virtual view provides a different mechanism for defining and accessing data.

Definition: Virtual View

A virtual view is a named relation defined by a query. It does not exist as a stored set of tuples but is instead dynamically computed from the data in the database each time it is referenced in a query. The definition of a view is specified using the CREATE VIEW statement:

CREATE VIEW <view-name> AS <query>;

Let \(R_1, R_2, \dots, R_n\) be a set of base relations. A view \(V\) is defined by a query \(Q\) over these relations. The content of \(V\) at any given time is the result of the evaluation of \(Q(R_1, R_2, \dots, R_n)\).

• Base Tables vs. Virtual Views

  Relations defined with a CREATE TABLE statement are base tables. They physically exist in the database and are persistent. In contrast, virtual views, defined with CREATE VIEW, do not have their own stored data. They represent a stored query that materializes a relation when invoked.

• Query Processing

  When a query references a virtual view, the database management system does not access a stored table. Instead, the query processor modifies the query by replacing any reference to the view with the view’s underlying query definition. The expanded query is then executed against the base tables.

• Usage and Modification

  A view can be queried in the same manner as a base table. It can be named in a FROM clause, and its attributes can be referenced in SELECT and WHERE clauses. In certain, restricted cases, views can also be modified through INSERT, UPDATE, or DELETE statements, which are translated into corresponding modifications on the underlying base tables.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:35:41
• Duration: 14.131855964660645
• Cummulative cost: $1.66e-02

Declaring Views

Section outline

• Presents the simplest form of view definition syntax: CREATE VIEW <view-name> AS <view-definition>;.
• Clarifies that the view definition is a SQL query.

#2

read: Section 8.1.1: Declaring Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Presents the simplest form of view definition syntax: `CREATE VIEW <view-name> AS <view-definition>;`.
- Clarifies that the view definition is a SQL query.

The fundamental mechanism for defining a view is the CREATE VIEW statement. This statement binds a name to a query, whose result is then treated as a virtual table.

Definition: View Declaration

The simplest form of the view definition statement is:

CREATE VIEW <view-name> AS <view-definition>;

• The CREATE VIEW statement associates a <view-name> with a specific query.

  This name serves as the identifier for the virtual relation. It can be used in the FROM clause of subsequent queries as if it were a base table.

• The body of the view is a SQL query.

  The <view-definition> is a SELECT statement that specifies the columns and rows that comprise the view. The database system stores this query definition for later use.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:35:51
• Duration: 10.09822678565979
• Cummulative cost: $1.16e-02

Querying Views

Section outline

• Explains that a view can be queried exactly like a stored table by referencing its name in a FROM clause.
• Describes how a database management system (DBMS) interprets a query involving a view by replacing the view with its underlying definition, often as a subquery.

#3

read: Section 8.1.2: Querying Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Explains that a view can be queried exactly like a stored table by referencing its name in a FROM clause.
- Describes how a database management system (DBMS) interprets a query involving a view by replacing the view with its underlying definition, often as a subquery.

A virtual view, though not physically stored, can be used in a query as if it were a base table. The database system handles the query by referencing the view’s definition to access the underlying base tables.

• A view is queried by including its name in the FROM clause of a SELECT statement, just as one would for a stored table.

  For instance, to find all movies made by Paramount in 1979, one can query the ParamountMovies view directly:

  SELECT title
  FROM ParamountMovies
  WHERE year = 1979;

• The database management system (DBMS) interprets a query on a view by replacing the reference to the view with its underlying definition. This process is often called “view expansion” or “query modification.”

  A query that involves a view is not executed against a stored instance of that view. Instead, the query processor first merges the query’s logic with the view’s defining query to create a new query that operates solely on the base tables.

• The substitution can be conceptualized as replacing the view in the FROM clause with a subquery that is identical to the view’s definition.

  Consider a query that joins the ParamountMovies view with the StarsIn table to find the names of actors in Paramount films.

  SELECT DISTINCT starName
  FROM ParamountMovies, StarsIn
  WHERE title = movieTitle AND year = movieYear;

  The system interprets this query by substituting the definition of ParamountMovies as a subquery, resulting in an equivalent query on the base tables Movies and StarsIn.

  SELECT DISTINCT starName
  FROM (SELECT title, year
        FROM Movies
        WHERE studioName = 'Paramount'
       ) AS Pm, StarsIn
  WHERE Pm.title = movieTitle AND Pm.year = movieYear;

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:36:04
• Duration: 12.465755701065063
• Cummulative cost: $1.45e-02

Renaming Attributes

Section outline

• Describes the method for assigning custom names to a view’s attributes by listing them in parentheses after the view name in the CREATE VIEW statement.
• Provides an example of rewriting the view from Example 8.2 with renamed attributes.

#4

read: Section 8.1.3: Renaming Attributes (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Describes the method for assigning custom names to a view's attributes by listing them in parentheses after the view name in the `CREATE VIEW` statement.
- Provides an example of rewriting the view from Example 8.2 with renamed attributes.

• It is often desirable to assign specific names to the attributes of a view, rather than inheriting them from the underlying query.

  SQL provides a mechanism to explicitly name the columns of a view. This is accomplished by listing the desired attribute names, enclosed in parentheses, immediately after the view’s name in the CREATE VIEW statement. The number of specified names must match the number of columns produced by the view’s defining query.

Definition: View with Attribute Renaming

The syntax for creating a view with explicitly defined attribute names is:

CREATE VIEW <view-name> (<attribute1>, <attribute2>, ...) 
AS <view-definition>;

Here, <view-name> is the identifier for the new view. The list (<attribute1>, <attribute2>, ...) specifies the names for the columns of the view. The <view-definition> is the SQL query that populates the view.

• An example can be constructed by modifying the view definition from Example 8.2.

  The original MovieProd view would have attributes named title and name. We can rename these to movieTitle and prodName as follows:

  CREATE VIEW MovieProd (movieTitle, prodName) AS
    SELECT title, name
    FROM Movies, MovieExec
    WHERE producerC# = cert#;

  The resulting view is structurally the same, but its columns are now accessed using the specified names movieTitle and prodName.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:36:18
• Duration: 14.064140796661377
• Cummulative cost: $1.45e-02