Views

1
Views
2
Outline

• Introduction
• What are Views For?
• View Retrievals intuitive
• 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 saved in the catalog
• To the user, it is now as if there really were a
  revlar in the database
• 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
Defining and Dropping Views in SQL

• Example view
• VAR GOOD_SUPPLER VIEW (S WHERE STATUS gt 15)
  S, STATUS, CITY
• Defining the example view in SQL
• CREATE VIEW GOOD_SUPPLIER ASSELECT SNO, STATUS,
  CITY FROM SWHERE STATUS gt 15
• Dropping the example view
• DROP VIEW GOOD_SUPPLIER

6
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)
7
Further Examples

• VAR REDPART VIEW (P WHERE COLOR Red ) ALL
  BUT COLOR
• A projection of a restriction 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_REDPART VIEW REDPART WHERE WT gt 12.0
• One view defined in terms of another

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
View Updates
12
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)

13
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

14
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)

15
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

16
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

17
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

18
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

19
S, P, and SP
20
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

21
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

22
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

23
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

24
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

25
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

26
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

27
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

28
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

29
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

30
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

31
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

32
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

33
View SSP (Sample Values)
34
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)

35
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

36
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)

37
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

38
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

39
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

40
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

41
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