Chapter 10 Views

1
Chapter 10 Views
2
Outline

• Introduction
• What are Views For?
• View Retrievals
• View Updates
• Snapshots
• SQL Facilities

3
Introduction
4
Views

• A view is created by applying an expression of
  relational algebra to a base relvar
• VAR GOOD_SUPPLER VIEW (S WHERE STATUS gt 15)
  S, STATUS, CITY
• When a view-defining expression is executed, it
  is not evaluated but is merely remembered by
  the system
• Actually by saving it in the catalog, under the
  specified name
• To the user, it is now as if there really were a
  revlar in the database
• A view is a derived or virtual relvar
• Updates to the base relvar immediately reflect to
  the view
• Updates (INSERT, DELETE, and UPDATE) to values in
  the view really only update the base relvar by
  reiterating the original algebraic expression at
  that update time

5
GOOD_SUPPLIER as a view of base relvar S
(unshaded portions)
Users can operate on GOOD_SUPPLIER just as if it
were a real or base relvar
GOOD_SUPPLIER WHERE CITY ltgt London
((S WHERE STATUS gt 15) S, STATUS, CITY) WHERE
CITY ltgt London
(S WHERE STATUS gt 15 AND CITY ltgt London)S,
STATUS, CITY)
6
Further Examples

• VAR REDPART VIEW (P WHERE COLOR COLOR(Red) )
  ALL BUT COLOR RENAME WEIGHT AS WT
• A projection of a restriction (plus attribute
  renaming) of the parts relvar
• VAR PQ VIEW SUMMARIZE SP PER P P ADD SUM
  (QTY) AS TOTQTY
• A kind of statistical summary or compression of
  the underlying data
• VAR CITY_PAIR VIEW ( (S RENAME CITY AS SCITY)
  JOIN SP JOIN (P RENANE CITY AS PCITY)) SCITY,
  PCITY
• A pair of city names (x, y) appears in the result
  iff a supplier located in x supplies a part
  stored in y
• VAR HEAVY_REPART VIEW REDPART WHERE WT gt WEIGHT
  (12.0)
• One view defined in terms of another

7
Defining and Dropping Views

• VAR ltrelvar namegt VIEW ltrelation expgt
  ltcandidate key def listgt
• The ltcandidate key def listgt is allowed to be
  empty because the system should be able to infer
  candidate keys for views
• View definition combine the external schema
  function and the external/conceptual (or
  external/external) mapping function
• DROP VAR ltrelvar namegt
• RESTRICT and CASCADE options
• SQL puts the above options on the DROP statement

8
What Are Views For?
9
What Are Views For?

• A view can make subsequent queries much simpler
  (analogous to a macro)
• Get cities that store parts that are available
  from some supplier in London ? (CITY_PAIR WHERE
  SCITYLondon) PCITY
• What if CITY_PAIR is not defined
• Views allow the same data to be seen by different
  users in different ways at the same time
• Views provide automatic security for hidden data
• Views can promote logical data independence

10
Logical Data Independence

• Logical data independence means the immunity of
  users and user programs to changes in the logical
  structure of the database (external/conceptual
  mapping)
• Growth New attributes or relvars can be added
  without affecting existing user programs
• Restructuring If a base relvar is split, the
  old structure can be emulated by a view the
  old programs can now access the view

VAR SNC BASE RELATION S S, SNAME NAME, CITY
CHAR KEY S VAR ST BASE RELATION S S,
STATUS INTEGER KEY S VAR S VIEW SNC JOIN ST
11
Two Important Principles for Views and Base
Relvars

• The Principle of Interchangeability There are no
  arbitrary and unnecessary distinctions between
  base and derived relvars (views)
• The Principle of Database Relativity From a
  users point of view, base relvars and views are
  logically equivalent

12
View Retrievals

• A relational expression is a relation-valued
  function
• It takes in relation(s) as arguments, and returns
  a relation
• A view is created using a relational expression,
  in which the relation returned is the view
• V X (D) / V be a view on D whose defining
  expression is X /
• Let RO be a retrieval operation on V (RO is
  another relation-valued function) RO(V)
  RO(X(D))
• The result of the retrieval is defined to be
  equal to the result of applying X to D (i.e.
  materializing a copy of the relation that is the
  current value of view V, and then applying RO to
  that materialized copy)

13
View Retrievals (Cont.)

• It is more efficient in practice to use the
  substitution procedure discussed previously (in
  Section 10.1)
• Forming the function C() that is the composition
  RO(X()) of the functions X and RO, in that
  order, and the applying C directly to D

14
View Updates
15
Overview

• A view is a relvar, and so is updateable by
  definition
• Given a particular update on a particular view,
  what updates need to be applied to what
  underlying base relvar(s) in order to implement
  the original view updates?
• UO(V) UO(X(D)) ? UO(X(D)) X(UO(D))
• Earlier discussions of view updates tended to
  limit them
• It is in principle not necessary to limit them,
  apart from constraint enforcement (see pp.305-307
  Toward a View-Updating Mechanism)
• View updates must adhere to the Golden Rule no
  relvar must ever be allowed to violate its own
  predicate

16
Toward A View-Updating Mechanism

• View updates must apply to appropriate underlying
  persistent relvars
• INSERT and DELETE should be inverses of each
  other
• UPDATE can be treated as a DELETE and an INSERT,
  so long as no intermediate actions or checks
  permitted
• View updates should be compatible with all
  relational operators
• More in pp.305-307

17
UNION (A UNION B)

• INSERT
• The new tuple must satisfy PA or PB or both. If
  it satisfies PA, it is inserted into A note that
  this insert might have the side effect of
  inserting the tuple into B also. If it satisfies
  PB, it is inserted into B, unless it was inserted
  B already as a side effect of inserting it into A
• The A?B order is not important (neither A nor B
  has precedence over the other)

18
UNION (Cont.)

• INSERT Examples
• VAR UV VIEW ( S WHERE STATUS gt 25 ) UNION ( S
  WHERE CITY Paris )
• (S6, Smith, 50, Rome) ? satisfy A
• (S7, Jones, 50, Paris) ? satisfy both A and B
• For Base relvars SA and SB, if UV is defined as
  SA UNION SB
• (S6, Smith, 50, Rome) ? insert into SA
• (S7, Jones, 50 Paris) ? insert into SA and SB
• The database is badly designed

19
UNION (Cont.)

• DELETE
• If the tuple to be deleted appears in A, it is
  deleted from A note that this DELETE might have
  the side effect of deleting the tuple from B
  also). If it (still) appears in B, it is deleted
  from B
• Deleting a tuple from A or B might cause a
  cascade delete or some other triggered action to
  be performed
• UPDATE
• Essentially consists of the delete rule followed
  by the insert rule except that no triggered
  actions or predicate checks are performed after
  the DELETE
• The tuple to be updated must be such that the
  updated version satisfies PA or PB or both
• A given UPDATE can cause a tuple to migrate from
  one relvar to another (ex. (S5, Adams, 30,
  Athens) ? (S5, Adams, 15, Paris)

20
INTERSECT

• The predicate is (PA) AND (PB)
• INSERT
• The new tuple must satisfy PA and PB. If it does
  not currently appear in A, it is inserted into A
  note that this insert might have the side effect
  of inserting the tuple into B also. If it (still)
  does not appear in B, it is inserted into B.
• DELETE
• The tuple to be deleted is deleted from A note
  that this DELETE might have the side effect of
  deleting the tuple from B also. If it (still)
  appears in B, it is deleted from B

21
INTERSECT (Cont.)

• UPDATE
• The tuple to be updated must be such that the
  updated version satisfies PA or PB or both
• Essentially consists of the delete rule followed
  by the insert rule except that no triggered
  actions or predicate checks are performed after
  the DELETE

22
Difference

• The predicate is (PA) AND NOT (PB)
• INSERT
• The new tuple must satisfy PA, and not PB. it is
  inserted into A
• DELETE
• The tuple to be deleted is deleted from A
• Update
• The tuple to be updated must be such that the
  updated version satisfies PA and not PB.
• The old version is deleted from A, and the
  updated version is inserted into A

23
Restrict

• V is A where p
• The predicate for V is (PA) AND p
• e.g. The predicate for S WHERE CITYLondon is
  (PS) and (CITYLondon)
• INSERT
• The new tuple must satisfy both PA and p. it is
  inserted into A
• DELETE
• The tuple to be deleted is deleted from A
• Update
• The tuple to be updated must be such that the
  updated version satisfies PA both and p.
• The old version is deleted from A, and the
  updated version is inserted into A

24
Restrict Update Example

• VAR LS VIEW S WHERE CITYLondon
• INSERT (S6, Green, 20, London) ? OK
• INSERT (S1, Green, 20, London) ? Fail
• INSERT (S6, Green, 20, Athens) ? Fail
• DELETE (S1, Smith, 20, London) ? OK
• Update (S1, Smith, 20, London) to (S6, Green, 20,
  London) ? OK
• Update (S1, Smith, 20, London) to (S1, Smith, 20,
  Athens) ?Fail

25
Project

• Let the attributes of A, with predicate PA, be
  partitioned into two disjoint groups, X and Y
• Consider X and Y as single composite attributes
• Consider the projection of A over X. Let (x) be a
  tuple of that projection, then the predicate for
  that projection is For all such x, there exists
  some y from the domain of Y values such that the
  tuple (x, y) satisfies PA
• e.g. S S, SNAME, CITY ? every tuple (s, n, c)
  appearing in that projection is such that there
  exists a status value t such that the tuple (s,
  n, t, c) satisfies the predicate for relvar S

26
Project (Cont.)

• INSERT
• Let the default value of Y be y (error if no
  default)
• The tuple (x, y) (which must satisfy PA) is
  inserted into A
• DELETE
• All tuples satisfied by X are deleted, regardless
  of the value of Y
• UPDATE from x to x
• Let a be a tuple of A with the same X value x,
  and let the value of Y in a be y
• All such tuples a are deleted from A (without
  performing any triggered actions or predicate
  checks)
• Then for each such value y, the tuple (x, y)
  which must satisfy PA is inserted into A
• Prior Y values are retained

27
Project Update Examples

• SC S, CITY
• Insert (S6, Athens) ? OK (S6, n, t, London)
• Insert (S1, Athens) ? Fail
• Delete (S1, London) ? OK
• Update (S1, London) to (S1, Athens) ? OK
• Update (S1, Smith 20, London) to (S1, Smith, 20,
  Athens)
• Update (S1, London) to (S1, London) ? Fail

28
Extend

• Let the defining expression for view V beEXTEND
  A ADD exp AS X
• The predicate PE for V is PA(a) AND e.X exp(a)
• e is a tuple of V and a is the tuple that remains
  when es X component is removed (i.e. a is the
  projection of e over all attributes of A)
• Every tuple e in the expression is such that (1)
  the tuple a that is derived from e by projecting
  away the X component satisfies PA, and (2) that X
  component has a value equal to the result of
  applying the expression exp to that tuple a

29
Extend (Cont.)

• INSERT let the tuple to be inserted be e
• e must satisfy PE, and the tuple a that is
  derived from e by projecting away the X component
  is inserted into A
• DELETE let the tuple to be deleted be e
• The tuple a that is derived from e by projecting
  away the X component is deleted from A
• UPDATE let the tuple e be updated to e
• e must satisfy PE
• Perform DELETE and then INSERT

30
Extend Update Examples

• EXTEND P ADD (weight 454) AS GMWT
• Insert (P7, Cog, Red, 12, Paris, 5448) ? OK
• Insert (P7, Cog, Red, 12, Paris, 5448) ? Fail
• Insert (P1, Cog, Red, 12, Paris, 5448) ? Fail
• Delete the tuple for P1 ? OK
• Update the tuple for P1 to (P1, Nut, Red, 10,
  Paris, 4540) ? OK

31
Join

• To update a tuple of a view based on a join, it
  may be necessary to affect other tuples to
  preserve the views predicate
• Earlier writers concluded that it was
  impermissible to update such views
• The textbook believes a better approach is to
  follow through the ancillary implications, rather
  than to avoid such updates
• Joins can be one to one, one to many, or many to
  many

32
Join (Cont.)

• Let J A JOIN B, where A, B, and J have headings
  X,Y, Y,Z AND X,Y,Z
• The predicates are PA, PB, PJ, where PJ for J is
  PA (a) AND PB (b), where a is the A portion and b
  is the B portion
• e.g. The join of S and SP over S
• Every tuple (s, n, t, c, p, q) in the join is
  such that the tuple (s, n, t, c) satisfies the
  predicate for S and the tuple (s, p, q) satisfies
  the predicate for SP

33
Join (Cont.)

• INSERT the new tuple j must satisfy PJ.
• If the A portion of j does not appear in A, it is
  inserted, and likewise for the B portion in B
• DELETE
• The A portion of the tuple to be deleted is
  deleted from A and the B
• UPDATE
• The tuple to be updated must be such that the
  updated version satisfies PJ
• The portion is deleted from A, and the B portion
  is deleted from B
• If the A (B) portion of the updated version of
  the tuple does not appear in A (B), it is
  inserted into A (B)
• Implications of the rules for one-to-one,
  one-to-many, and many-to-one

34
One to One

• More accurately called (zero-or-one) to
  (zero-or-one)
• An integrity constraint in effect that ensures
  that for each tuple of A there is at most one
  matching tuple in B and vice versa
• The set of attributes Y over which the join is
  performed must be a superkey for A and B
• Example
• S JOIN S Over S
• SR S, REST
• REST suppliers favorite restaurant
• Consider the effect of the join update rules on S
  JOIN SR
• What difference would it make if some supplier
  could appear in SR and not in S

35
One to Many

• More accurately called (zero-or-one) to
  (zero-or-more)
• An integrity constraint in effect that ensures
  that for each tuple of B there is at most one
  matching tuple in A
• The set of attributes Y over which the join is
  performed include a subset K, such that K is a
  candidate key for A and a matching foreign key
  for B
• Operations on the view will affect the underlying
  relvars appropriately
• A tuple inserted into such a view will insert a
  tuple into the underlying relvar on the one side,
  as well as a tuple into the underlying relvar on
  the many side
• DELETE will occur based on constraints on
  underlying relvars

36
One-to-Many Update Examples

• Let view SSP be defined as S JOIN SP
• Insert (S4, Clark, 20, London, P6, 100) ? OK
  Insert (S4, P6, 1200) into SP
• Insert (S5, Adams, 30, Athens, P6, 100) ? OK
  Insert (S5, P6, 100) into SP
• Insert (S6, Green, 20, London, P6, 100) ? OK
  Insert (S6, Green, 20, London) into S and (S6,
  P6, 100) into SP
• Insert (S4, Clark, 20, Athens, P6, 100), (S1,
  Smith, 20, London, P1, 400) ? Fail
• Delete (S3, Blake, 30, Paris, P2, 200) ? OK
  Delete (S3, Blake, 30, Paris) from S and (S3,
  P2, 200) from SP

37
View SSP (Sample Values)
38
One-to-Many Update Examples (Cont.)

• Let view SSP be defined as S JOIN SP (Cont.)
• Delete (S1, Smith, 20, London, P1, 300) ? OK
  Delete (S1, Smith, 20, London) from S and (S1,
  P1, 300) from SP
• Update (S1, Smith, 20, London, P1, 300) to (S1,
  Smith, 20, London, P1, 400) ? OK Update (S1, P1,
  300) in SP to (S1, P1, 400)
• Update (S1, Smith, 20, London, P1, 300) to (S1,
  Smith, 20, Athens, P1, 400) ? OK Update (S1,
  Smith, 20, London) to (S1, Smith, 20, Athens),
  and (S1, P1, 300) to (S1, P1, 400)
• Update (S1, Smith, 20, London, P1, 300) to (S6,
  Smith, 20, London, P1, 300) ? OK Update (S1,
  Smith, 20, London) to (S6, Smith, 20, London),
  and (S1, P1, 300) to (S6, P1, 400)

39
Many to Many

• More accurately called (zero-or-more) to
  (zero-or-more)
• No integrity constraint in effect to ensure that
  we are really dealing with a Case 1 or Case 2
  situation instead
• Operations on the view will affect the underlying
  relvars appropriately
• A tuple inserted into such a view will insert a
  tuple into the underlying relvars
• DELETE will occur based on constraints on
  underlying relvars
• Base relvars and views retain their predicates

40
Many-to-Many Update Examples

• S JOIN P over CITY
• Insert (S7, Bruce, 15, Oslo, P8, Wheel, White,
  25) ? OK Insert (S7, Bruce, 15, Oslo) into S and
  (P8, Wheel, White, 25, Oslo) into P
• Insert (S1, Smith, 20, London, P7, Washer, Red,
  5) ? OK Insert (P7, Washer, Red, 5, London) into
  P (thereby adding two tuples to the view the
  abovementioned tuple and (S4, Clark, 20, London,
  P7, Washer, Red, 5))
• Insert (S6, Green, 20, London, P7, Washer, Red,
  5) ? OK Insert (S6, Green, 20, London) into S
  and (P7, Washer, Red, 5, London) into P (thereby
  adding six tuples into the view)

41
Snapshots

• Snapshots are derived relvars, but they are not
  views
• Snapshots are the result of a relational
  operation that generates a read only relvar that
  is refreshed periodically
• VAR ltrelvar namegt SNAPSHOT ltrelation
  expgtltcandidate key def listgt REFRESH EVERY ltnow
  and thengt
• ltnow and thengt MONTH/WEEK/DAY/HOUR/n MINUTES
  /MONDAY/WEEKDAY/EVERY UPDATE
• VAR P2SC SNAPSHOT( (S JOIN SP) WHERE P
  P(P2) ) S, CITYREFRESH EVERY DAY
• DROP VAR ltrelvar namegt
• Can consider RESTRICT OR CASCADE options

42
Snapshots (Cont.)

• Why snapshots?
• Many applications can tolerate or require data
  as of some particular point in time reporting
  and accounting application
• Desirable to freeze large amounts of data for a
  complex query or read-only application, again
  without locking out updates
• An important case of controlled redundancy, and
  snapshot refresh is the corresponding update
  propagation process (see Chapter 1)
• Snapshots have come to be misnamed materialized
  views, an oxymoron

43
SQL Facilities

• CREATE VIEW ltview namegt AS
• lttable expgt
• WITH ltqualifiergt CHECK OPTION
• WITH CHECK OPTION is used for INSERTs and
  SELECTs will make them fail if integrity
  constraints are violated the default is that
  they will succeed regardless
• Can only be used with updateable views, as
  defined in SQL, which is narrower

44
Updateable Views in SQL

• An SQL view is certainly updatable if the
  following eight conditions are ALL satisfied
• The view-defining table expression is a simple
  select expression (that is, it does not contain
  any of the keywords JOIN, UNION, INTERSET, or
  EXCEPT)
• The SELECT clause of that select expression does
  not contain the DISTINCT keyword
• Every select item in that SELECT clause consists
  of a possibly qualified column name, representing
  a simple reference to a column of the underlying
  table (see condition 5), and no such column
  reference appears more than once
• The FROM clause of that select expression
  contains exactly one table reference

45
Updateable Views in SQL (Cont.)

• That table reference identifies either a base
  table or a view that satisfies conditions 1-8.
  Note the table identified by that table
  reference is said to be the underlying table for
  the updatable view in question (see condition 3)
• That select expression does not include a WHERE
  clause that includes a subquery that includes a
  FROM clause that includes a reference to the same
  table as is referenced in the FROM clause
  mentioned in condition 4
• That select expression does not include a GROUP
  BY clause
• That select expression does not include a HAVING
  clause