Understanding SQL Views: Definition, Usage, and SQL Restrictions

1. Views - basics

2. Introduction • a view is a perspective of the database • different users may need to see the database differently; this is achieved through the view mechanism • a view is part of the external level • remember the three level architecture: internal, conceptual and external

3. Introduction • view = a named relational expression • suppose • Employee ( Emp_Id, Name, Address, Salary, Dept_id ) • Department (Dept_id, Manager, Budget, Office ) • then views can be defined as • SELECT Name, Salary, Dept_id FROM EmployeeWHERE Salary > 35 • SELECT Name, Employee.Dept_id FROM Employee, DepartmentWHERE Budget > 1500 AND Employee.Dept_id = Department.Dept_id

4. View - a window to the database the database - a set of base tables

5. View • named expression of relational algebra (calculus) • relational closure • virtual relation • substitution process

6. Data definition for views in SQL CREATE VIEW <view> [ <column names> ] AS <relationalexpression> [WITH CHECK OPTION] ; DROP VIEW <view> <option> ; <option> ::= RESTRICT | CASCADE

7. SQL views - vertical and horizontal --”vertical” view CREATE VIEW Emp AS SELECT Emp_Id, Name, Address, Dept_id FROM Employee ; --supposing that the salary is confidential --horizontal view CREATE VIEW GoodEmp AS SELECT Emp_id, Name, Address, Salary, Dept_id FROM Employee WHERE Salary > 45 ;

8. SQL views - join --an employee is safe if s/he works for a “rich” department CREATE VIEW SafeEmployees AS SELECT Name, Employee.Dept_id FROM Employee, DepartmentWHERE Budget > 1500 AND Employee.Dept_id = Department.Dept_id

9. SQL views - aggregate functions CREATE VIEW TotSalPerDept AS SELECT Dept_id, SUM(Salary) AS TotSal FROM EmployeeGROUP BY Dept_id

10. Using views • views are used as if they were base relations • from the point of view of the user, a view is the same as a base relation • however, certain restrictions exist • e.g. see next slide

11. SQL restrictions on views • in a view, a column that is based on an aggregate function cannot be subject to an aggregate function or to a WHERE clause (e.g. TotSal before) • a grouped view may never be joined with a base table or another view

12. WITH CHECK OPTION • only for updateable views • migrating rows • a row of a view, after being updated, may not satisfy the condition of the view anymore, therefore it will migrate out of the view • WITH CHECK OPTION avoids such situations; the update is not permitted if the row will no longer satisfy the condition of the defining query

13. View resolution • view = virtual relation • view = expression that is evaluated every time it is used • evaluating an expression on a view = view resolution • view resolution  substitution

14. Activity • Consider a view definition and a query on this view. Explain how (do you think) the expression is evaluated.

15. Advantages • logical data independence • users and user programs are immune to changes in the logical structure of the DB • to what extent logical data independence can be guaranteed? • restructuring tables : add/delete columns, change key definition, change integrity constraints, rename columns/tables, split tables, ...

16. Advantages • automatic/improved security • reduced complexity • through macro facility • customisation • the same data can be seen differently by users • through macro facility • data integrity • WITH CHECK OPTION

17. Disadvantages • update restriction • will see in more detail next lecture • structure restriction • performance

18. Activity • Find out, through experiments, to what extent logical data independence is provided in Postgres • some changes on base relations or on the views used in other views definitions can be made without requiring the redefinition of the views • some other changes require view redefinition, but the structure (and name) of the view can stay the same