5.5 Creating and Using Views

Views are an important tool for providing users with a simple, customized environment and for hiding data. As explained in Section 5.2, a relational view does not correspond exactly to the general external view but can be either a window into a base table or a virtual table derived from one or more underlying base tables. It usually does not exist in storage in the same way as base tables but is created by selecting specified rows and columns from the base tables and possibly performing operations on them.

A view is dynamically produced as the user works with it. To make up a view, the DBA decides which data the user needs to access, determines what base tables contain the data, and creates one or more views to display the attributes and rows the user should see. Views allow a dynamic external model to be created for the user easily. The reasons for providing views rather than allowing all users to work with base tables are as follows:

  • Views allow different users to see the data in different forms, permitting an external model that differs from the logical model.

  • The view mechanism provides a simple authorization control device, easily created and automatically enforced by the system. View users are unaware of, and cannot access, certain data items.

  • Views can free users from complicated DML operations, especially in the case where the views involve joins. The user writes a simple SELECT statement using the view name in place of a base table name in the FROM clause, and the system takes care of executing the operations on the base tables to support the view.

  • If the database is restructured on the logical level, the view can be used to keep the user’s model constant. For example, if a table is split by projection and the primary key appears in each resulting new table, the original table can always be reconstructed when needed by defining a view that is the join of the new tables.

Standard SQL supports relational views. Oracle, PostgreSQL, and SQL Server also support materialized views. Materialized views function as snapshots of data and cannot be updated directly; however, they can be refreshed periodically as needed. They are used primarily in data warehouse environments.

The following is the most common form of the command used to create a view

Syntax of the S Q L command used to create a view. Line 1. CREATE VIEW view name. Line 2. Open square bracket, open parentheses, view col name, open square bracket, comma, view col name, close square bracket, dot, dot, dot, close parentheses, close square bracket, AS. Line 3. SELECT col name, open square bracket, comma, col name, close square bracket, dot, dot, dot. Line 4. FROM base table name, open square bracket, comma, base table name, close square bracket, dot, dot, dot. Line 5. WHERE condition, semicolon.

The view name is chosen using the same rules as the table name, and it should be unique within the database. Columns chosen from the base tables may be rearranged in any desired manner in the view. Column names in the view can be different from the corresponding column names in the base tables, but they must obey the same rules of construction. If we choose to make them the same, we need not specify them twice, so we leave out the viewcolname specifications. In the AS SELECT line, also called the subquery, we list the names of the columns from the underlying base tables that we wish to include in the view. The order of these names should correspond exactly to the viewcolnames if those are specified. As in the usual SELECT . . . FROM . . . WHERE, the condition is a logical predicate that expresses restrictions on the records to be included. A more general form of CREATE VIEW uses any valid subquery in place of the SELECT we have described. For materialized views, the Oracle syntax is

A table with a single column labeled, i d name. The row entries are as follows. Row 1. 1 Smith. Row 2. 2 Jones.

  • Example 1. Choosing a Vertical and Horizontal Subset of a Table

    Operation: Assume a user needs to see IDs and names of all history majors. Create a view for this user.

    SQL Command:

    An S Q L command for choosing a Vertical and Horizontal subset of a Table. Line 1. CREATE VIEW H I S T M A J, open parentheses, last, comma, first, comma, s t u I d, close parentheses, AS. Line 2. SELECT last Name, comma, first Name, comma, s t u I d. Line 3. FROM Student. Line 4. WHERE major equals, open single quote, History, close single quote, semicolon.

    Here we renamed the columns from our base table. The user of this view need not know the actual column names.

  • Example 2. Choosing a Vertical Subset of a Table

    Operation: Create a view of all classes with their schedules and rooms.

    SQL Command:

    An S Q L command for choosing a vertical subset of a table. Line 1. CREATE VIEW Class L o c AS. Line 2. SELECT class Number, comma, schedule, comma, room. Line 3. FROM Class, semicolon.

    Notice we did not need a condition here because we wanted these parts of all Class records. This time we kept the names of the columns as they appear in the base table.

  • Example 3. A View Using Two Tables

    Operation: Assume a user needs a view containing the IDs and names of all students in class CSC101.

    The virtual table can be created by choosing records in Enroll that have a classNumber of CSC101, matching the stuId of those records with the stuId of the Student records, and taking the corresponding lastName and firstName from Student. We could express this as a join or as a subquery.

    SQL Command:

    An S Q L command for a view of 2 tables. Line 1. CREATE VIEW Class List AS. Line 2. SELECT uppercase s, dot, s t u I d, comma, last Name, comma, first Name. Line 3. FROM Enroll uppercase E, comma, Student, uppercase S. Line 4. WHERE class Number equals, open single quote, C S C 101, close single quote. Line 5. AND uppercase e dot s t u I d equals uppercase s dot s t u I d.

  • Example 4. A View of a View

    Operation: Define a view derived from a view. We can ask for a subset of the ClassLoc (virtual) table consisting of just classNumber and room.

    SQL Command:

    An S Q L command for a view of a view. Line 1. CREATE VIEW Class L o c 2 AS. Line 2. SELECT class Number, comma, room. Line 3. FROM Class L o c, semicolon.

  • Example 5. A View Using a Function

    In the SELECT statement in the AS line, we can include built-in functions and GROUP BY options.

    Operation: Create a view of Enroll that gives classNumber and the number of students enrolled in each class:

    SQL Command:

    An S Q L command for a view using a function. Line 1. CREATE VIEW Class Count, open parentheses, class Number, comma, T o t Count, close parentheses, AS. Line 2. SELECT class Number, comma, COUNT, open parentheses, asterisk, close parentheses. Line 3. FROM Enroll. Line 4. GROUP BY class Number, semicolon.

    Notice we had to supply a name for the second column of the view because there was none available from the base table.

    This could also have been done using a name for the second column.

    SQL Command:

    An S Q L command for a view using a function. Line 1. CREATE VIEW Class Count 2 AS. Line 2. SELECT class Number, comma, COUNT, open parentheses, asterisk, close parentheses, AS T o t count. Line 3. FROM Enroll. Line 4. GROUP BY class Number, semicolon.

  • Example 6. Operations on Views

    Once a view is created, the user can write SELECT statements to retrieve data through the view. The system takes care of mapping the user names to the underlying base table names and column names, and performing whatever functions are required to produce the result in the form the user expects. Users can write SQL queries that refer to joins, ordering, grouping, built-in functions, and so on, of views—just as if they were operating on base tables. Because the SELECT operation does not change the underlying base tables, there is no restriction on allowing authorized users to perform SELECT with views.

    Operation: Using the ClassLoc view, find all room values that start with the letter H.

    SQL Command:

    An S Q L command for performing operations on views. Line 1. SELECT asterisk. Line 2. FROM Class L o c. Line 3. WHERE room LIKE, open single quote, uppercase H, percentage sign, close single quote, semicolon.

    INSERT, DELETE, and UPDATE can present problems with views.

  • Example 7. A View That Is Not Updatable

    Suppose we create a view of Student records, using

    An S Q L command for a view that is not updatable. Line 1. CREATE VIEW Student V 1 AS. Line 2. SELECT last Name, comma, first Name, comma, major, comma, credits. Line 3. FROM Student, semicolon.

    and we attempt to insert a new record, using

    An S Q L command for inserting a new record. Line 1. INSERT INTO Student V 1. Line 2. VALUES, open parentheses, open single quote, Sample, close single quote, comma, open single quote, Bad, close single quote, comma, NULL, comma, 12, close parentheses, semicolon.

    This insert will be rejected. If we were permitted to insert records, any records created through this view would actually be Student records, but would not contain stuId, which is the key of the Student table. Because stuId has the NOT NULL constraint, any records without this column will be rejected.

  • Example 8. An Updatable View

    If we create the following view

    An S Q L command for an updatable view. Line 1. CREATE VIEW Student V 2 AS. Line 2. SELECT s t u I d, comma, last Name, comma, first Name, comma, credits. Line 3. FROM Student, semicolon.

    we should have no problem inserting records because we would be inserting Student records with a null major column, which is allowable.

    Operation: Insert a new StudentV2 record, providing data for all columns.

    SQL Command:

    An S Q L command for inserting a new student record. Line 1. INSERT. Line 2. INTO Student V 2. Line 3. VALUES, open parentheses, open single quote, S 1040, close single quote, comma, open single quote, Levine, close single quote, comma, open single quote, Adam, close single quote, comma, 30, close parentheses, semicolon.

    The system will actually insert the record into the Student table.

  • Example 9. Attempting to Insert a Value into a Virtual Column

    Now let us consider inserting records into the view ClassCount, as described earlier in Example 5. This view used the COUNT function on groups of records in the Enroll table. Obviously, this table was meant to be a dynamic summary of the Enroll table, rather than being a row and column subset of that table. It would not make sense for us to permit new ClassCount records to be inserted because these do not correspond to individual rows or columns of a base table. If we attempt to add a record using

    An S Q L command for inserting into Class Count. Line 1. INSERT INTO Class Count, open parentheses, class Number, comma, T o t Count, close parentheses. Line 2. VALUES, open parentheses, open single quote, INV 100 A, close single quote, comma, 100, close parentheses, semicolon.

    the record will be rejected.

    The problems we have identified for INSERT into views apply with minor changes to UPDATE and DELETE as well. As a rule, these three operations can be performed on views that consist of actual rows and columns of underlying base tables, provided the primary key is included in the view and no other constraints are violated.

  • Example 10. Modifying a View

    To modify an existing view definition, the following command, which is nearly identical to the original CREATE VIEW, is used

    The Oracle syntax for materialized view is as follows. CREATE MATERIALIZED VIEW view, underscore, name AS subquery, semicolon.

    Operation: Change StudentV2 to drop the credits column.

    SQL Command:

    An S Q L command to drop the credits column. Line 1. CREATE OR REPLACE VIEW Student V 2 AS. Line 2. Open parentheses, SELECT s t u I d, comma, first Name, comma, last Name. Line 3. FROM Student, close parentheses, semicolon.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.216.118.40