Storing User Views

User views can be limited or made available to users depending upon their needs. Because views are not base tables that store values, you can perform calculations to derive values for fields in a view.
Earlier in the course, we made the distinction between

1. base tables and
2. virtual tables.

A view is a virtual table composed of fields from one or more tables in the database and the tables that comprise the view are known as base tables. The relational model refers to a view as being virtual because it draws data from base tables^[1] rather than storing data on its own. The only information about a view that is stored in the database is its structure. All database managent systems (including MySQL) support views, but some RDBMS (such as Microsoft Access) refer to views as saved queries. Your specific RDBMS program will determine whether you refer to this object as a query or a view.
Views enable you to see the information in your database from many different angles, providing you with a great amount of flexibility when you work with your data. Vews can be created in various ways and they are useful when you base them on multiple related tables.

A base table is a table that stores data and is physically written to disk, while a virtual table is created on the fly and only exists in the computer's memory, more specifically, the random access memory of the computer. Unlike records in a base table, each record in a virtual table does not have to have a primary key.
The CREATE TABLE statement sets up a base table. It is convenient to think of a base table as physically existing in the internal schema, although there is no requirement that it be stored in this form. For example, it could be stored into several tables (physically split) or as records linked by pointers.

As an example of the difference between a base table and a virtual table, consider the tables in the following illustration.

The first table is a base table listing orders Stories on CD placed with its distributors. The second table is a virtual table^[2] created by a view that shows the total cost of orders placed with each distributor. Every field in the base table contains unique values, so the table conforms to the rules for tables discussed earlier in the course. The virtual table, by contrast, contains a calculated field that derives its value from more than one source and does not follow the rules for base tables.

You create user views using the Structured Query Language, called SQL. You retrieve data in a relational database by using Structured Query Language. SQL is the standard language used to create, modify, maintain, and query relational databases. To create a view, you write an SQL query which names the base table, or tables, to provide data for the virtual table to be created from that data. For example, if you found that Stories on CD employees often need information derived by joining the CD and Distributor tables, you could create a query that combined the tables on their common field (DistID), saving the users (or yourself) the trouble of creating the join every time it is needed. The specifics of SQL are beyond the scope of this course. If you want to learn more about SQL, see the two-part Introduction to SQL series . The next lesson discusses the benefits of user views.

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

[1]Base Tables: Tables that form the basis of a view.

[2]virtual table: A virtual table is a table stored in the computer's memory. Virtual tables themselves are not stored in the database. Instead, the definition of the view is stored and given a name.