Views

1
Views
2
What is a view?

• A named expression of the relational algebra
• Ex)
• VAR GOOD_SUPPLIER VIEW
• (S WHERE STATUS gt 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 gt 15) S, STATUS, CITY
• GOOD_SUPPLIER WHERE CITY ? London
• are equivalent to the following expression
• ((S WHERE STATUSgt15)S, STATUS, CITY) WHERE
  CITY ? London

4
Defining views

• Format
• VAR ltrelvar namegt VIEW ltrelational expressiongt
  ltcandidate key definition listgt
• If system can infer candidate keys, the
  ltcandidate key definition listgt can be omitted
• Ex) for GOOD_SUPPLIER, S is the candidate key

5
Dropping views

• Format
• DROP VAR ltrelvar namegt
• 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 gt 15) S, STATUS, CITY
• A shorthand or macro capability
• Ex) if we dont have the view GOOD_SUPPLIER, we
  should write
• ((S WHERE STATUSgt15)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) SS, SNAME, STATUS, CITY ? SNCS, SNAME,
  CITY and STS, 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 users 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