---

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