1. Views

2. What is a view? • A named expression of the relational algebra • Ex) VAR GOOD_SUPPLIER VIEW (S WHERE STATUS > 15) {S#, STATUS, CITY}; • The view defining expression is stored in the catalog • Users can access the view like a relation • Ex) GOOD_SUPPLIER WHERE CITY  ‘London’ GOOD_SUPPLIER

3. What is a view?(cont.) • The previous 2 statements • VAR GOOD_SUPPLIER VIEW (S WHERE STATUS > 15) {S#, STATUS, CITY}; • GOOD_SUPPLIER WHERE CITY  ‘London’ • are equivalent to the following expression • ((S WHERE STATUS>15){S#, STATUS, CITY}) WHERE CITY  ‘London’

4. Defining views • Format • VAR <relvar name> VIEW <relational expression> <candidate key definition list> • If system can infer candidate keys, the <candidate key definition list> can be omitted • Ex) for GOOD_SUPPLIER, {S#} is the candidate key

5. Dropping views • Format • DROP VAR <relvar name> • If any view refers to a base relation, the base relation cannot be dropped • If any view refers to other views, the referred views cannot be dropped

6. Reasons for supporting views • Automatic security for hidden data • Ex) supplier names in the case of view GOOD_SUPPLIER ※VAR GOOD_SUPPLIER VIEW (S WHERE STATUS > 15) {S#, STATUS, CITY}; • A shorthand or macro capability • Ex) if we don’t have the view GOOD_SUPPLIER, we should write • ((S WHERE STATUS>15){S#, STATUS, CITY}) WHERE CITY  ‘London’ • Instead of GOOD_SUPPLIER WHERE CITY  ‘London’

7. Reasons for supporting views(cont.) • To allow the same data to be seen by different users in different ways at the same time • Focusing on related portion of the database only • Logical data independence

8. Logical data independence • The immunity of users and user programs to changes in the logical structure of the database • Independence from the conceptual or community logical level • Growth and restructuring

9. Logical data independence from growth • The growth of database does not affect existing views • The expansion of an existing base relvar to include a new attribute • The inclusion of a new base relvar

10. Logical data independence from restructuring • The restructuring of database does not affect existing views • Although the overall information content remains the same, the logical placement of information changes • Ex) S{S#, SNAME, STATUS, CITY}  SNC{S#, SNAME, CITY} and ST{S#, STATUS} • S can be derived by joining the two relations • VAR S VIEW SNC JOIN ST;

11. Two important principles of views • The principle of interchangeability • No arbitrary and unnecessary distinctions between base and derived relvars • Ex) we can define • S as a base relvar, and • SNC and ST as projection views of S • Or we can define • SNC and ST as base relvars, and • S as a join view of SNC and ST • We can update S in both cases with no difference

12. Two important principles of views(cont.) • The principle of database relativity • From the user’s point of view, all relvars are base relvars. • The choice of which database is the ‘real’ base relvar is arbitrary as long as the choices contain equivalent information

13. View retrievals • Let D: a database, X: an expression, V: a view defined by X on D • That is, V = X(D) • Let R: a retrieval operation on V • So, R(V) = R(X(D)) • A retrieval operation on a view is converted into an equivalent operation on the underlying base relvar(s) --- substitution • A view is a materialization so that it is easier to users than the substitution

14. Snapshots • Derived relvars • Exist as materialized copy of data • Read only relvar • Periodically refreshed • Ex) VAR P2SC SNAPSHOT ((S JOIN SP) WHERE P# = P#(‘P2’)) {s#, CITY} REFRESH EVERY DAY;

15. Snapshots(cont.) • Applications of snapshots • Accounting • To freeze the data at the end of an accounting period • Reporting • To freeze large amounts of data for a complex query or read-only applications without locking out updates