Views

Tables and Views

• A table defined by the CREATE TABLE statement is often call a base table

– A base table contains rows that are actually stored on disk, normally in the form of physical records with contiguous fields of different types

• An important property of the relational model is that the result of a query is also a table.

• If we save this resultant table, queries should be able to be run on it.

• A view table is a table that results from a query, but has its own name and can be treated in most ways as if it were a base table

Views

• A view is a logical window on selected data from the base tables of a database.

• It can be named in the FROM clause of SELECT statements

• It appears to the user as a table, just like a base table

• You can sometimes update, insert into and delete from views

– Views that involve a JOIN of two or more tables can usually not be updated

• You cannot explicitly define integrity constraints on views

Storage for views

• In general, a view is not allocated any storage space

• It is a virtual table that does not physically exist; it does not actually contain data

• When the view is created, no data is retrieved or stored.

– The definition of the view is placed in the system catalogs as a distinct object of the db.

– It is retrieved later whenever a query or update statement is issued with the view name in the FROM clause

The syntax to create a view

• CREATE VIEW view_name AS

SELECT attributes
FROM tables
WHERE conditions

• Example

create view worksOn1 as
select fname, lname, pname, hours
from employee, project, works_on
where ssn=essn AND pno=pnumber;

Querying the view

• Views are queried just like base tables.

• List the names of all employees who work on the project ‘ProjectX’

• SELECT fname, LNAME
FROM worksOn1
WHERE pname = ‘ProjectX”

Naming the attributes in a view

• If no attribute names for views are specified, the table attribute names are used.

• You can rename the attributes in the views

• CREATE VIEW dept_info(deptName,
NumEmployees, totalSalary AS
SELECT dname, count(*), sum(salary)
FROM department, employee
WHERE dnumber = dno
GROUP BY dname;

Dropping views

• When the view is not needed anymore, it can be dropped.

• DROP VIEW worksOn1;

• When you drop the view, all you are really dropping is the specification of the stored query in the data dictionary. No data is effected.

• If another view refers to the view you want to drop, the drop will fail.

• Dropping tables will also fail if a view is defined on the table.

Implementing views for queries

• There are two main approaches

– Query modification

• This involves mapping the query on the view to a query on the underlying base tables

• It is inefficient if multiple queries are made to the view in a short period of time

• It is also inefficient if the underlying query is complex and time-consuming to execute.

– View materialization

• The view involves physically creating a temporary view table when the view is first queried, and keeping the table for a while

• Usually DBMSs have a certain amount of memory allocated for these stored views…as that space is used up, the last view to be queried is kicked out

Updating views

• Updating views is not always possible, and/or may not make sense. The problem:

– Given a particular update on a particular view, what updates need to be applied to the underlying base tables to implement the original view update

• Example: UPDATE dept_info
SET total_salary = 100000
WHERE dname = ‘Research’;

• How should this update be carried out?

Ambiguous updates

• How should this update be interpreted?

• UPDATE worksON1
SET pname = ‘ProductY’
WHERE lname=‘Smith’ AND fname=‘John’
AND pname = ‘ProductX’;

• It could mean to change only the tuple with the name John Smith,

• But the desired effect could also be accomplished by changing all the ProductX tuples to ProductY.

• Updating views is a semantic issue, not a syntactic one

When can a view be updated

• It is feasible to update view when only one possible update on the base relations can accomplish the desired update effect on the view.

• Some researchers have developed methods to try to figure out the most likely desired outcome when the update on the view can be mapped to more than one update on the base tables.

Updating views (in summary)

• A view with a single unlying base table is updatable if the view attributes contain the PK of the base relation

– The view is mapped directly to the base table

• Views defined on multiple base tables using joins are usually not updatable.

• Views defined using grouping and aggregate functions are not updatable.

Keeping the view up to date

• A view must always be up to date

– If the data in the underlying tables is modified, the view must show these changes

• This is one reason the view table is not created at view definition, but when the view is queried

• What about when the view is materialized, and the view table stored?

– An efficient strategy must be developed by the DBMS to keep the view up to date

• It is the responsibility of the DBMS and not the user to make sure that the view is up to date.

The value of views

• They hid complexity (joins, conditions, selects, etc.)

• They simplify commands for the user; can save writing complex queries

• They present data in a different perspective from that of the base tables

– They provide automatic security for hidden data

– They allow the same data to be seen by different usersin different ways at the same time.

• They can achieve improvements in availability and performance

– Large tables can be partitioned

Views serve two different purposes

• The user who defines a view is obviously aware of the underlying base tables

– To this user, the view is like a macro or shorthand

• To a user who is merely informed that a view V exists and is available for use, is typically not aware of how the view was defined, and should look and behave exactly like a base table.