Main menu

EN | RU | UK

To Home Page

8. Views

Helping & Consulting
Helping&Consulting
Contact me on
On top
Lecture
Purpose

The objective of the chapter is to study particularities of data view formation and classification that permit us not only to ease queries structure, transforming information into necessary format, but also to play significant role in information access restriction for different groups of users for DB sharing.

8.1. Definition, creation and classification of view

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:

CREATE VIEW Name [(column[, column ...])

    AS <operator SELECT> [WITH CHECK OPTION];

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.

8.2. Means and limitations of view formation

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).

CREATE VIEW ReadersView

AS

    SELECT ReaderCardNumber, FamilyName, Name, Patronymic, Job, Post

        FROM Readers

Table 8.1

Vertical cut-off of the table Readers according to example 8.1
FamilyName Name Patronymic ReaderCardNumber Job Post
Ivanov Petr Ivanovich 317 NMU, CM dep. Assistant
Fedorez Irina Olegovna 28 NMU, AEC Front-door security
Ilin Ivan Petrovich 1345 NMU, physics dep. Associate professor
Surenko Dmitry Pavlovich 543 NMU, geophysicist dep. Senior professor
Korshunova Natalia Yurievna 128 NMU, geo-informatics dep. Assistant
Nosenko Oleg Vladimirovitch 5672 NMU, ICC Engineer
Brusov Vladimir Mikhajlovitch 485 NMU, geodesy dep. Laboratory assistant
Kozirev Alexey Sergeevich 759 NMU, criminology dep. Professor
Levchenko Julia Pavlovna 146 NMU, political theory dep. Head of department
Svetlaya Tatyana Ivanovna 2021 NMU, translation dep. Senior professor
Sheglov Petr Yevgenievich 997 NMU, power supply dep. Assistant
Kirilenko Victor Alexandrovich 1010 NMU, electric drive dep. Deputy dean

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).

CREATE VIEW AllBookAfter1984

AS

    SELECT *

        FROM Readers

        WHERE IssueYear >= '01.01.1985'

Table 8.2

Horizontal cut-off of the table Books according to the example 8.2
Code Name Issue Year Drawing BookPublishers UDK Cipher Note
1 Automation of industrial processes on concentration plant 01.01.1985 6000 «Nadra» null 622.7-52/Т null
3 Asymptotic methods of optimal management 01.01.1987 1000 «Avtomat» null 681.513.5:/А null
5 Stochastic systems optimization methods 01.01.1987 4500 «Матстат» null 681.513.5:/DO null
6 Automotive systems of ore-dressing treatment technological process management 31.01.1987 4000 «Avtomat» null 622.7-52/P null
7 C/C++ High-level language programming 11.01.2007 5500 «Piter» null 681.3.06(075) null
8 Computer networks. Conceptions, technologies, protocols 31.01.2006 6000 «Piter» null 004.72(075) null
9 Directory of differential equations with 1-st derivatives 31.01.2003 10000 «FIZMATLIT» null 517.9 null
10 Probability theory and mathematical statistics 31.01.2004 1000 «Piter» null 519.2 null
11 C#. High-level language programming 31.01.2009 1500 «Piter» null 004.43 null
12 Probability theory and mathematical statistics 31.01.2005 2000 «Vyshcha shkola» null 519.2 null
13 Probability theory and mathematical statistics 31.01.2002 2500 «FIZMATLIT» null 519.2 null
14 Discret-batch methods of simple differential equations integration 31.01.1991 7000 «LIAN» null 517.9-37 null

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).

CREATE VIEW Bookafter1984

AS

    SELECT Name, EXTRACT(YEAR FROM IssueYear) AS IssueYear, Drawing, Cipher

        FROM Books

        WHERE IssueYear >= '01.01.1985'

Table 8.3

The product of data presentation (vertically-horizontal cut-off of the table)
Name IssueYear Drawing Cipher
Automation of industrial processes on concentration plant 1985 «Nadra» 622.7-52/T
Asymptotic methods of optimal management 1987 «Avtomat» 681.513.5:/A
Stochastic systems optimization methods 1987 «Матстат» 681.513.5:/DO
Automotive systems of ore-dressing treatment technological process management 1987 «Avtomat» 622.7-52/P
C/C++ High-level language programming 2007 «Piter» 681.3.06(075)
Computer networks. Conceptions, technologies, protocols 2006 «Piter» 004.72(075)
Directory of differential equations with 1-st derivatives 2003 «FIZMATLIT» 517.9
Probability theory and mathematical statistics 2004 «Piter» 519.2
C#. High-level language programming 2009 «Piter» 004.43
Probability theory and mathematical statistics 2005 «Vyshcha shkola» 519.2
Probability theory and mathematical statistics 2002 «FIZMATLIT» 519.2
Discret-batch methods of simple differential equations integration 1991 «LIAN» 517.9-37

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).

CREATE VIEW Debtors

    (ReaderCardNumber, ReaderFamilyName, ReaderName, BookName, BookInventoryNumber,

    (BookCost, IssueDate, ReturnDate, FactReturnDate)

AS

    SELECT R.ReaderCardNumber, R.FamilyName, R.Name, B.Name, I.InventoryNumber,

                    I.Cost, G.IssueDate, G.ReturnDate, G.FactReturnDate

        FROM R, Books B, BookInventoryNumbers I, BookGiveOutRecord G

        WHERE G.ReturnDate < G.FactReturnDate AND

                    G.ReaderCode = R.Code AND

                    G.InventoryCode = I.Code AND

                    I.BookCode = B.Code

Table 8.4

View product according to example 8.4
ReaderCardNumber ReaderFamilyName ReaderName BookName BookInventoryNumber BookCost IssueDate ReturnDate FactReturnDate
1345 Ilin Ivan Optimum automotive systems synthesis 4512890 12.99 02.09.2004 16.09.2004 11.12.2004
543 Surenko Dmitry Automotive systems of ore-dressing treatment technological process management 4632112 10.10 30.10.2004 13.11.2004 10.01.2005
485 Brusov Vladimir C/C++ High-level language programming 5478956 45.10 07.03.2009 21.03.2009 10.04.2009

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. Advantages and disadvantages of views

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.

8.4. CHECK QUIZ

  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?
Conclusion

Data view is a window that designer provides to each user to get the access to data that correspond his demands and if he is authorized to get them. Thus relational data model that is displayed for user is independent from inner DB structure. In addition data view permits to simplify the structure of SQL-queries to get reports that necessary for users.

© Yaroslav Kuvaiev, 2005—2023.

All rights reserved.

All information placed on this Web site is designed for internal use only. Its reproduction or distribution in any form is prohibited without the written permission from Author.