180 likes | 187 Views
Views - basics. 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.
E N D
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
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
View - a window to the database the database - a set of base tables
View • named expression of relational algebra (calculus) • relational closure • virtual relation • substitution process
Data definition for views in SQL CREATE VIEW <view> [ <column names> ] AS <relationalexpression> [WITH CHECK OPTION] ; DROP VIEW <view> <option> ; <option> ::= RESTRICT | CASCADE
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 ;
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
SQL views - aggregate functions CREATE VIEW TotSalPerDept AS SELECT Dept_id, SUM(Salary) AS TotSal FROM EmployeeGROUP BY Dept_id
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
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
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
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
Activity • Consider a view definition and a query on this view. Explain how (do you think) the expression is evaluated.
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, ...
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
Disadvantages • update restriction • will see in more detail next lecture • structure restriction • performance
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