School of Computing, Engineering and InformationSciences
University of Northumbria
ViewsLogical DataIndependence
Aim:
To learn how to create views.
To investigatethe updating of views.
To demonstrate the use of a view to conceal the replacement of abase table.
Outline of Session:
Create a number of views and check them out.
Amend, or attempt to amend, the contents of these views.
Use a view to re-create the contents of a previously-existing base table.
CREATING VIEWS
Views are also known as “virtual tables” because they look like tables, but do not have a “real” existence. Essentially they are stored definitions of queries which allow a query to be run again and again without typing in the whole SELECT statement.
The syntax of the CREATE VIEW statement is as follows:
CREATE VIEW view-name [ ( column-name [ , etc.] ) ]
AS <select expression>
WITH CHECK OPTION;
The column-nameis optional. You will need to use it if there are duplicate column names in the query result (differentiated by table name prefixes) or if you want different column names in the view to those derived from the base table(s).
Theselect expression defines the query that is used as the basis for the view. In principle, any logically permissible query can be used. For example it can be useful for views of sub-sets of data in a table (through the SQL equivalents of Project and / or Restrict operations), for views of data derived from merges of tables via Joins or Set operations, and for views of the results of calculations (through the SQL equivalents of Extend and / or GroupBy).In practice, a number of query possibilities are not available in Oracle SQL.
The WITH CHECK OPTIONphrase concerns updating of the view. If a new tuple is inserted into the view, or an existing tuple in it amended, then the new or amended tuple must satisfy the full set of integrity constraints for the view. The WITH CHECK OPTIONphrase ensures that this will happen. From an SQL point of view, the WITH CHECK OPTIONphrase is optional. However if it is omitted, new/amended tuples could be put into the view but they would not appear when the contents of the view were retrieved ! However they would appear in the underlying table(s) if the integrity constraints of the underlying table(s) were not violated. Hence the need to include the WITH CHECK OPTIONphrase to avoid inconsistency.
As an example of a view, suppose we wish to allow a user to see departmental data with the name of the manager added. The following view would provide this :-
SQL> CREATE VIEW DEPT_MGR_VIEW
2 (DEPT_NO, DEPT_NAME, MANAGER_NO, MANAGER_NAME)
3 AS
4 SELECT DEPT.DEPT_NO, DEPT_NAME, MANAGER_NO, EMP_NAME
5 FROM DEPT, EMP
6 WHERE MANAGER_NO = EMP_NO
7 WITH CHECK OPTION;
Note that the use of Join operators in the FROM phrase is not permitted in Oracle SQL. Therefore the old SQL1 style joins have been used instead.
Create the above view, andthen check that the view has been created, usingSELECT * FROM CAT;Note that this statement lists views as well as tables, but distinguishes between them.
UseDESCRIBE with the view name, in orderto see its specification.
Get the contents of the view :-
SQL> SELECT * FROM DEPT_MGR_VIEW;
Note that we have not added any new data to the DB: we have merely stored a query as a view, so that it may be re-run at any time.
The results produced by querying a view are the same as produced by querying a “real” table (or “base” table, to use the proper SQL terminology). Consequently we can make further queries on views, treating them just like base tables.
EXERCISES IN CREATING VIEWS
Exercise. Create the following views. In each case, check your results by means of the
SELECT * FROM CAT;
statement, the
DESCRIBE
statement, and by looking at the view’s contents.
1.ALLOC_EMP_VIEW, to list project numbers together with the name and ID number of the employees
working on each project.
2.EMP_NAME_VIEW, to show, for each employee, only the employee ID number and name (i.e. excluding the
“confidential” data of salary, marital status, and department number).
3 DEPT_NAME_VIEW, to show, for each department, only the department name and number, and
manager ID number (excluding the “confidential” data of budget).
4.DEPT_SUMMARY, to show the number of departments within the company, together with the smallest,
largest and average departmental budget.
Exercise.
Retrieve a list of department numbers, together with department name, manager ID number and manager name, using a join of the two views EMP_NAME_VIEW and DEPT_NAME_VIEW.
AMENDING VIEWS
A view can be used not only to display data but also to update it:
Exercise: Change the name of employee E8 to ‘McEnemy’ using the view EMP_NAME_VIEW
Has this update worked?
Where has the data been changed ?
Exercise: Change the name of an employee using the view DEPT_MGR_VIEW.
What data has actually been changed?
Can you identify the conditions that were necessary to make this change? Check your opinion by using an Oracle SQL query on its internal catalogue table USER_UPDATABLE_COLUMNS. (To make the query result easier to read, use SET LINESIZE 120 and SET PAGESIZE 60 first).
Exercise: Attempt to change the name of employee E6 using ALLOC_EMP_VIEW.
Why are you not allowed to make this update?
Suppose that changes of this kind were allowed. What would happenas a result of changing the name of
employeeE6?
Exercise: Attempt to change any of the values within DEPT_SUMMARY.
Why can this view not be updated?
Note that in theory any view should be usable for updating except where this is logically impossible — as in the last example.
COMMITall these updates to the DB, to avoid any confusion in the next task.
RECREATING BASE TABLES AS VIEWS
Suppose it was necessary for security reasons to replace the EMP table with 2 separate tables to holdthe same employee data, one to hold the confidential data (salary and marital status) and one to hold the remaining data. Such reorganisation of data is necessary from time to time in real-life situations. However itcan cause confusion for DB users who are used to working with the EMP table. In such circumstances, it can be helpful to create a view to simulate the originalEMPtable. Then users who were previously using the EMPbase table can now use anEMPview table instead, and will not even realise that the table has been changed from a base to a view table.
Carry out the following exercise to replace base table EMP with view EMP. Note that in this case it will be useful to get rid of view EMP_NAME_VIEW first, to avoid any possible confusion.
SQL> DROP VIEW EMP_NAME_VIEW ;
Exercise:Let EMP_CONF and EMP_NOT_CONF be the replacement base tables for EMP. Decide which column(s) will form the Primary Key in each of them. What column(s) will you use to join EMP_CONF and EMP_NOT_CONF together when forming the view EMP ? What form of Join operation will you use ?
Create base tables EMP_CONF and EMP_NOT_CONF and fill them with all the required data. Create each of them in one statement. Check these base tables thoroughly, including their contents.
For the purposes of this exercise,wewill not consider the integrity constraints that should be applicable to EMP_CONF and EMP_NOT_CONF (but we would have to if we were doing this ‘for real’).
Check the contents of base table EMP
We could now drop EMP, but for safety (and because we would have to handle referential integrity constraints that reference EMP) we won’t.
Create the view EMP_REPLACEMENT. Check that it is correct, as you did with the other views, and holds the same data as the base table EMP.
In reality, we should replace EMP with EMP_REPLACEMENT (and change the latter’s name to EMP), having ensured that all referential and other integrity constraints apply to EMP_CONF and/or EMP_NOT_CONF, but for convenience we won’t bother.
1