Transcript Views-basics

Views-basics
Views - basics
1
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
2
Views-basics
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 Employee
WHERE Salary > 35
• SELECT Name, Employee.Dept_id
FROM Employee, Department
WHERE Budget > 1500 AND
Employee.Dept_id = Department.Dept_id
3
Views-basics
View - a window to the database
the database - a set of base tables
4
Views-basics
View
 named expression of relational algebra
(calculus)
 relational closure
 virtual relation
 substitution process
5
Views-basics
Data definition for views in SQL
CREATE VIEW <view> [ <column names> ]
AS <relational expression>
[WITH CHECK OPTION] ;
DROP VIEW <view> <option> ;
<option> ::= RESTRICT | CASCADE
6
Views-basics
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 ;
7
Views-basics
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, Department
WHERE
Budget > 1500 AND
Employee.Dept_id = Department.Dept_id
8
Views-basics
SQL views - aggregate functions
CREATE VIEW TotSalPerDept AS
SELECT
Dept_id, SUM(Salary) AS TotSal
FROM
Employee
GROUP BY Dept_id
9
Views-basics
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
10
Views-basics
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
11
Views-basics
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
12
Views-basics
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
13
Views-basics
Activity
 Consider a view definition and a query on this
view. Explain how (do you think) the
expression is evaluated.
14
Views-basics
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, ...
15
Views-basics
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
16
Views-basics
Disadvantages
 update restriction
• will see in more detail next lecture
 structure restriction
 performance
17
Views-basics
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
18