CS457 Syllabus & Progress

## Virtual table or view

• Virtual relation or View

• View:

• View = a virtual table that is computed when it is used

 The content of a view (virtual table) is not stored (on disk) The content of a view (virtual table) will be computed when the view (virtual table) is accessed

• Example of a virtual table (a.k.a.: view)

• Consider the following query:

 ``` select fname, lname, dname, salary from employee, department where dno=dnumber Output: +--------+---------+----------------+----------+ | fname | lname | dname | salary | +--------+---------+----------------+----------+ | John | Smith | Research | 30000.00 | | Frankl | Wong | Research | 40000.00 | | Alicia | Zelaya | Administration | 25000.00 | | Jennif | Wallace | Administration | 43000.00 | | Ramesh | Narayan | Research | 38000.00 | | Joyce | English | Research | 25000.00 | | Ahmad | Jabbar | Administration | 25000.00 | | James | Borg | Headquarters | 55000.00 | +--------+---------+----------------+----------+ ```

Notice that:

• The output of a query is a relation (= table) !!!

 This table is not stored on disk However: this table can be computed (with the above query !!!)

• Defining a view

• A view is defined using the CREATE VIEW command.

Example:

 ``` CREATE VIEW DeptEmps AS ( select fname, lname, dname, salary from employee, department where dno=dnumber ) ```

• Using a view (virtual table)

• A virtual relation can be used in queries just like any "ordinary" relation

Example:

 ``` select * from DeptEmps Output: +--------+---------+----------------+----------+ | fname | lname | dname | salary | +--------+---------+----------------+----------+ | John | Smith | Research | 30000.00 | | Frankl | Wong | Research | 40000.00 | | Alicia | Zelaya | Administration | 25000.00 | | Jennif | Wallace | Administration | 43000.00 | | Ramesh | Narayan | Research | 38000.00 | | Joyce | English | Research | 25000.00 | | Ahmad | Jabbar | Administration | 25000.00 | | James | Borg | Headquarters | 55000.00 | +--------+---------+----------------+----------+ select * from DeptEmps where dname='Research'; Output: +--------+---------+----------+----------+ | fname | lname | dname | salary | +--------+---------+----------+----------+ | John | Smith | Research | 30000.00 | | Frankl | Wong | Research | 40000.00 | | Ramesh | Narayan | Research | 38000.00 | | Joyce | English | Research | 25000.00 | +--------+---------+----------+----------+ ```

• External Schema (View) is computed on the fly.

• Consider the following view definition :

 ``` CREATE VIEW Dept_Info(dname, no_emps, total_sal) AS ( SELECT dname, count(ssn), sum(salary) FROM department, employee WHERE dnumber = dno GROUP BY dname ) ```

• Example query on this view:

 Find all department with >2 employees

Solution:

 ``` SELECT * FROM Dept_Info WHERE no_emps > 2 DNAME NO_EMPS TOTAL_SAL --------------- ---------- ---------- Administration 3 93000 Research 4 133000 ```

• If we update the salary of employees:

 ``` UPDATE employee SET salary = salary + 10000 SELECT * FROM Dept_Info WHERE no_emps > 2 will now return: DNAME NO_EMPS TOTAL_SAL --------------- ---------- ---------- Research 4 173000 Administration 3 123000 ```

Notice that the salary value changed in the view also

(So the sum is computed using the updated salary values !!!)