8. Views

View is a dynamic product of one or several relational operations, carried out for DB relations to generate a new one [4].

View is created dynamically [4, 6, 8, 12]. It is a virtual relation that doesn’t exist in reality in the DB and is a product of user’s query execution. When meeting references to the view control system finds corresponding attribution and transforms outcome query for the viewinto equivalent query to relations that are applied in the view determination, and after that modified query is executed. This process is called view resolution.

User sees it as a table with data that is composed from columns and rows. It permits to improve data protection and also provides designer means for adjusting user’s model. One of the most important reasons to use views is a desire to simplify multytable queries. After determination of views with join of several tables it can be operated by means of simple single-table queries instead of queries with execution of the same multytable join.

The order of records formation for data view is determined by operator SELECT. For its creation the following construction is used:

It is necessary to determined name of the view and operator SELECT. In addition there is a possibility to indicate names for each column of the view – [(column[, column ...])], and for updated views to prohibit input of rows that do not meet terms of formation in WHERE proposition, operator SELECT – [WITH CHECK OPTION].

List of column names must be composed from quantity of elements that is equal to the quantity of columns in the final relation that is generated by operator SELECT. If the list of column names is absent, each column of the view will contain name of corresponding column from final relation. The list of column names must always be determined if there is an ambiguity in the final relation columns names. Such situation can arise when there are computed columns in SELECT operator or when final relation is created by means of operation of join and includes attributes with similar names.

Query that is determined by operator SELECT is called key query. Depending on its structure view can be updatable one or not. In updatable views we can add and delete rows, to change values. All these operations with data can be saved in DB relations. Views that are not updatable can display information only. DBMS saves its determination in the DB.

To make view to be an updatable one, DBMS must have the possibility to display definitely any of its rows or columns on corresponding row or column of final relation. All updates carried out in DB relation can be immediately found in all views that include queries to this relation. If data is modified in updatable view, these modifications will be shown in the basic relation.

According to the ISO standard the view can be updatable only in case when:
- proposition DISTINCT is not used for its determination, that means that replicated rows are not deleted from key query product;
- each element of the SELECT proposition list of key query is a column name and not a constant, phrase or averaging function; moreover, name of each column in this list is mentioned only once;
- only one relation must be indicated in proposition FROM. that means that view must be created on the base of one relation and user must have appropriate access to it; if final relation is a view by itself it must also meet mentioned conditions; this option excludes the possibility of any views update if they are created on the base of join, union (UNION), intersection (INTERSECT) or difference of relations (EXEPT);
- proposition WHERE must not contain any nested queries that refer to relation determined in proposition FROM;
- key query must not contain propositions GROUP BY or HAVING.

Apart from this, any data row that is added through the view must not break requirements of data integrity support that are determined for outcome relation. For example, in case of adding new row of relation through the view, determinant NULL will be put into all columns. However, at the same time, all requirements regarding NOT NULL proposition that is indicated in the outcome relation description, must be respected.

The basic concept of restrictions on updatable view string can be formulated as follows. Updatable view can contain only those rows that meet WHERE condition in the key query. If the row in the view was modified in such way that doesn’t meet this condition any more, it must be deleted. Thus new rows will appear in the view every time when insert or update of data will change the situation so that new rows will meet WHERE condition. Added or deleted from the view rows are called migratory.

Phrase WITH CHECK OPTION can be indicated only after update views. It guarantees that data row that doesn’t meet condition determined in WHERE proposition, will not be added to relation, determined in the phrase FROM of SELECT operator. In general case WITH CHECK OPTION in the operator CREATE VIEW is used to avoid migration of rows within the view. If we mention parameter WITH CHECK OPTION for the view that is updated, all attempts to add new or modify existing records so that condition in WHERE of key operator SELECT is broken, will be rejected.

This function can be so useful that it is easier to work with views than with DB relations. In the case when operator INSERT or UPDATE breaks conditions indicated in WHERE of key query the operation execution is canceled. As a result, appears the possibility to implement in the DB additional limitations that are dedicated to save data integrity and correctness.

To delete the view operator DROP VIEW Name is used.

Even though all presentations are created according to the same method, in practice various methods of the view formation are applied for different aims. These are: vertical cut-off of the table, horizontal cut-off of the table, vertically-horizontal cut-off of the table, subset of rows and columns of different tables join [4, 6].

8.2.1. View as a vertical cut-off of the table

View of data is formed as a vertical cut-off of the table if operator SELECT returns values of not all columns of the table indicated in FROM and limitation for corteges that will be a part of data view is not determined. Such data view is usually used in cases when data from relation are processed by different users or groups of users. By means of vertical views they can have available virtual relations that contain only necessary attributes.

EXAMPLE 8.1.

To provide users of DB «LIBRARY» the possibility to look through the following information about readers: reader card no., family name, name, patronymic, place of work, position (table 8.1).

Table 8.1

8.2.2. View as a horizontal cut-off of the table

View of data is formed as horizontal cut-off of the table, if operator SELECT returns values from all columns, and corteges that will be included in data view are limited.

EXAMPLE 8.2.

To provide information about books, edited after 1984 for users of DB «LIBRARY» (table 8.2).

Table 8.2

8.2.3. View as vertically-horizontal cut-off of the table

View is formed as vertically-horizontal cut-off of the table, if operator SELECT returns values not from all attributes of relation and corteges that will be included in data view are limited.

EXAMPLE 8.3.

Provide users with a database «LIBRARY» information about the books published after 1984. View must contain book name, year and place of edition, code (table 8.3).

Table 8.3

8.2.4. View of data as a subset of rows and columns

Data view is formed as subset of rows and columns if operator SELECT uses inner or outer relation joins.

EXAMPLE 8.4

View must contain: reader card number, reader’s family name and name, book name, inventory number and cost, date of edition, return and actual return of the book (table 8.4).

Table 8.4

8.2.5. Limitations of view creation and application

Limitations of view creation and application are included into ISO standard. There are some of them.

If the column in view is created using aggregate function it can be indicated in propositions SELECT and ORDER BY of those queries that refer this view. However, such kind of column can not be used in WHERE, and also can not be an argument in summarizing function of each query that refer the view.

Integrated view must never be joined with DB tables or otherviews.

8.3.1. Advantages

In case when DBMS operates on personal computer that stands separately, views are applied to simplify structure of queries to DB. However in case when DBMS carries out queries from many users in the network, views play major role in the DB structure determination and providing information security. Main advantages of using views in such environment are the following [4].

8.3.1.1. Independence from data

Independence from data for user is DB structure stabilization that will remain unchanged even in case of outcome relations structure review. For example, adding or deletion of columns, links modification, division of relations, their restructuring and renaming. If attributes that are not applied in the view are added or deleted from relation, it is not necessary to change its determination. If the structure of outcome relation is recordered or divided, it is possible to create the view that permits users to work with virtual relations of previous format. In case of outcome relation division on several parts (relations), former format can be virtually updated by means of the view built on the basis of these relations join – of course if it is possible. The possibility can be provided by putting primary key of former relation to all new relations.

8.3.1.2. Data currency

Data currency is provided by immediate mapping modifications that were made in any DB relation that is indicated in key query.

8.3.1.3. Data security

Data security improvement can be realized due to the fact that each user can be provided data access only through limited set of views that include only data subset that is required for work. Such approach permits to improve significantly control over access of separate group categories and separate users to the information in DB.

8.3.1.4. Additional conveniences and possibilities of user’s data model adjustment

Additional conveniences and possibilities of user’s data model adjustment can be achieved by maximal simplification. As a result similar relations can be shown by different users correspondingly to their rights and privileges. It provides the possibility to operate only that part of data that is really requested. In such way it is possible to achieve maximal simplification of data model that will be applied by end user.

8.3.1.5. Reduction of data mapping complication

Reduction of data mapping complication is a result of structure simplification of queries that join data from several relations in one virtual relation. As a result multytable queries are reduced to simple searches that work with one data presentation.

8.3.1.6. Data integrity

Data integrity is provided by means of method when input of corteges that do not meet sampling terms of primary query. This is provided by proposition WITH CHECK OPTION, CREATE VIEW operator.

8.3.2. Disadvantages

Application of data vies permits to achieve significant advantages, but at the same time it is also necessary to pay attention to some negative aspects [4].

8.3.2.1. Limited possibility of update

Limited possibility of update is an impossibility to correct data in the view that consists of several relations.

8.3.2.2. Structural restrictions

Structural restrictions have to do with structure of data view that is determined during its designing. If the key query is presented in format SELECT * FROM …, then symbol «*» refers to all columns that exist in outcome relation for the moment of its creation. If afterwards new attributes will be added to DB relation, they will not appear in the view until it is not deleted and recreated again.

8.3.2.3. Lowering efficiency

Lowering of DBMS efficiency is caused by application of additional computational resources for permission ofdata views. Sometimes impact of this factor can be unimportant, and sometimes it can become a source of serious problems. For example data view determined by advanced multytable query can require a lot of time for processing due to necessity to carry out join of relations every time when access to data view is required.

1. What mutual features and differences have data view and table that is determined by operator CREATE TABLE?
2. How is carried out view?
3. What possibilities possesses operator CREATE VIEW to determine view?
4. What particularities of determinant operator SELECT you know?
5. When the list of column names is required in CREATE VIEW?
6. What is the difference between updatable and not updatable view?
7. What limitations are determined for updatable data views?
8. What are the particularities of application WITH CHECK OPTION proposition?
9. What differences between methods of data views formation are familiar to you?
10. What limitations are determined for creation and application of data views?
11. What are the advantages and disadvantages of data view application?