Views are virtual tables whose tuples or records do not physically exist in the database. Instead, the tuples of this virtual relation or virtual table are computed spontaneously when the user executes the query associated with the view.
The database stores the query associated with the view so that the user does not have to write the query again and again. In the section ahead, we will discuss more about the views in SQL.
Content: Views in SQL
Why View is Required?
Not all users are allowed to see the entire logical model. For security reasons, certain data is hidden from the users. Apart from this sometimes we need a personalized collection of tables or relations that matches the query of certain users.
Although the query matching the user’s intuition can be computed and its result can be store in the database. This stored result relation can be made available to users whenever required.
However, storing the result of a query is not favourable. As if the data inside the relations involved in the query is changed, then the stored result relation would not match the result of re-execution of the same query. So, storing the result of a query is not a good idea.
Well SQL has a solution for this, it provides the concept of ‘virtual relation’. Virtual relation is a relation that is the result of a query. It is different from what we have studied above, the virtual relation is not precomputed and stored in the database, instead, the virtual relation is recomputed by executing the query whenever the virtual relation is used.
A view in SQL can be defined as a virtual table or virtual relation that is derived from the set of actual tables in the database or from the previously defined views.
A view is referred to as a virtual table as its tuples are not physically stored in the database, on the other hand, the tuples of the base tables are actually physically stored in the database.
As the tuples of the virtual table do not exist physically in the database, we cannot perform the update operations on this table as we perform on the tuples of base tables. As a conclusion, we can refer to view as a method of specifying a relation that needs to reference frequently though they do not exist physically.
To specify a view using SQL we have the following command:
create view Ʋ as <query expression>
Here, Ʋ is the name of the view that will be generated by the query expression and <query expression> can be any legal query expression.
Example of Views in SQL
Let there be a relation instructor with fields (instr_id, name, dept_name and salary). Consider a scenario that a clerk of a college is not authorized to see the instructor’s salary amount. But what if the clerk needs information only about the instructor’s ID, name and dept_name. In this case, we can create a view and made it available to the clerk, the expression for the same would be:
create view inst_view as
select ID, name, dept_name
Now, as we have explained earlier, it would not happen that the result of the above query will be computed and stored in the database and made available to the clerk whenever required. Instead, the query specifying view relation is stored in the database.
Whenever this view relation is accessed the query expression is executed and the tuples of the view relation are computed spontaneously.
Moving ahead we will learn to create a view from multiple tables. Let us consider that we have two relations ‘course’ and ‘section’ with the fields course(course_id, title, dept_name, credits) and section(course_id, sec_id, semester, year, building, room_number, time_slot_id).
Now, if we have to identify all course sections offered by the Biology department in the summer 2009 semester with the building and room number of each section, we would specify the query expression as:
create view bio_summ_ 2009 as
select course.course id, sec id, building, room number
from course, section
where course.course id = section.course id
and course.dept name = ‘Biology’
and section.semester = ‘Summer’
and section.year = ‘2009’;
The materialized view is the view relation that is stored by the database such that if there is any change to the actual base relations specified in the view definition it will reflect in the view relations also. Thus, the process of maintaining the view relation up to date is referred to as materialized view maintenance.
Databases update the view immediately if there is a change in any relation specified in the view definition. But here also some databases are slow in maintaining the view of the database. Some databases perform view maintenance at a periodic interval. It may happen when you use the view relation it may not be up-to-date. So, applications that require up-to-date information should not use this kind of view relation.
Operations on View
The modification of views in SQL such as update, insert, delete is not permitted though exceptions are there. The SQL view can be modified if the following conditions are satisfied by the query specifying the view:
- The query expression must specify only one database relation in the from clause.
- The select clause of query expression must only specify the attribute name and no expressions, aggregation or any other distinct specification.
- The query expression defining view must not specify any group by or having clause.
- Any attribute that is not listed in the query defining view must be allowed to be set to null. Such that the corresponding attribute must not have not null constraint and the attribute must not be a part of the primary key.
If all these rules satisfy, we can implement the update, insert, and delete operations on the view. There are more complex sets of rules about when you can insert, update and delete a view.
So, this is all about the view in SQL. We have first determined why the view is required further we have seen how one can define view using SQL. We have tried to understand the concept using some examples. Later we have discussed materialized view where we have learned about the rules that must be satisfied before w can update, insert or delete in a view relation.