Chapter 10 Views Prof YinFu Huang CSIE NYUST

Chapter 10 Views Prof. Yin-Fu Huang CSIE, NYUST

10. 1 Introduction n Example: Var Good_Supplier View (S Where Status > 15) {S#, Status, City}; (See Fig. 10. 1) n The view-defining expression is not evaluated but is merely “remembered” by the system. a derived and virtual relvar n A window into the underlying data Advanced Database System Yin-Fu Huang

10. 1 Introduction (Cont. ) Ø Example: Good_Supplier Where City ≠ ‘London’ (S Where Status > 15 And City ≠ ‘London’) { S#, Status, City } n Any updates: view n Further Examples the underlying data Ø Exam 1: Var Redpart View ((P Where Color=Color(‘Red’)){All But Color}) Rename Weight As Wt; Ø Exam 2: Var Pq View Summarize Sp Per P {P#} Add Sum (Qty) As Totqty; Ø Exam 3: Var City_Pair View ((S Rename City As Scity) Join Sp Join (P Rename City As Pcity)) {Scity, Pcity}; Ø Exam 4: Var Heavy_Redpart View Redpart Where Wt >Database Weight(12. 0); Advanced System Yin-Fu Huang

10. 1 Introduction (Cont. ) n Defining and Droping Views Var <relvar name> View <relation exp> <candidate key def list>; Drop Var <relvar name>; Advanced Database System Yin-Fu Huang

10. 2 What Are Views for? n Reasons: 1. Views provide a shorthand or “macro” capability. 2. Views allow the same data to be seen by different users in different ways at the same time. 3. Views provide automatic security for hidden data. 4. Views can provide logical data independence. n Logical Data Independence Ø The immunity of users and user programs to changes in the logical structure of the database. Ø Growth § New attributes § New base relvar Advanced Database System Yin-Fu Huang

10. 2 What Are Views for? (Cont. ) Ø Restructuring e. g. , Var Snc Base Relation {S# S#, Sname Name, City Char} Primary Key {S#}; Var St Base Relation {S# S#, Status Integer} Primary Key {S#}; Var S View Snc Join St; § Information-equivalent n Two Important Principles Ø The Principle of Interchangeability Ø The Principle of Database Relativity Advanced Database System Yin-Fu Huang

10. 3 View Retrievals n V = X(D) n RO(V) = RO(X(D)) 10. 4 View Updates (1/12) n Given a particular update on a particular view, what updates need to be applied to what underlying base relvars in order to implement the original view update? V = X(D) UO(V) = UO(X(D)) = X(UO’(D)) n The Golden Rule Revisited No update operation must ever assign to any relvar a value that causes its relvar predicate to evaluate to False. Advanced Database System Yin-Fu Huang

10. 4 View Updates (2/12) n Union: A Union B (PA) Or (PB) e. g. , Var UV View (S Where Status > 25) Union (S Where City = ‘Paris’); (See Fig. 10. 2) Advanced Database System Yin-Fu Huang

10. 4 View Updates (3/12) Ø Insert (S 6, Smith, 50, Rome) (S 7, Jones, 50, Paris) Suppose SA and SB are two distinct base relvars. (See Fig. 10. 3) (S 6, Smith, 50, Rome) (S 7, Jones, 50, Paris) insert into only SA insert into SA and SB Advanced Database System Yin-Fu Huang

10. 4 View Updates (4/12) Ø Delete Ø Update a Delete-Insert sequence n Intersect: A Intersect B (PA) And (PB) n Difference: A Minus B (PA) And Not (PB) n Restrict: A Where p (PA) And (p) e. g. , Var LS View S Where City = ‘London’; (See Fig. 10. 4) Advanced Database System Yin-Fu Huang

10. 4 View Updates (5/12) Ø Insert (S 6, Green, 20, London) (S 1, Green, 20, London) (S 6, Green, 20, Athens) fail Ø Delete (S 1, Smith, 20, London) Ø Update (S 1, Smith, 20, London) (S 6, Green, 20, London) (S 1, Smith, 20, London) (S 2, Smith, 20, London) Advanced Database System 20, Athens) (S 1, Smith, 20, London) (S 1, Smith, fail Yin-Fu Huang fail

10. 4 View Updates (6/12) n Project: A{X} e. g. , Var SC View S {S#, City}; (See Fig. 10. 5) Ø Insert (S 6, Athens) (S 1, Athens) fail Ø Delete (S 1, London) Ø Update (S 1, London) (S 1, Athens) Advanced Database System (S 1, London) (S 2, London) fail Yin-Fu Huang

10. 4 View Updates (7/12) n Extend: Extend A Add exp As X PA(a) And e. X = exp (a) e. g. , Var VPX View Extend P Add (Weight*454) As Gmwt; (See Fig. 10. 6) Advanced Database System Yin-Fu Huang

10. 4 View Updates (8/12) Ø Insert (P 7, Cog, Red, 12, Paris, 5448) (P 7, Cog, Red, 12, Paris, 5449) fail (P 1, Cog, Red, 12, Paris, 5448) fail Ø Delete (P 1, Nut, Red, 12, London, 5448) Ø Update (P 1, Nut, Red, 12, London, 5448) (P 1, Nut, Red, 10, Paris, 4540) (P 1, Nut, Red, 12, London, 5448) (P 2, Nut, Red, 12, London, 5448) fail Advanced Database System Yin-Fu Huang

10. 4 View Updates (9/12) n Join: A Join B PA(a) And PB(b) Ø Case 1 (one-to-one) e. g. , S Join SR; Ø Case 2 (one-to-many) e. g. , Var SSP View S Join SP; (See Fig. 10. 7) Advanced Database System Yin-Fu Huang

10. 4 View Updates (10/12) n Insert (S 4, Clark, 20, London, P 6, 100) (S 5, Adams, 30, Athens, P 6, 100) (S 6, Green, 20, London, P 6, 100) (S 4, Clark, 20, Athens, P 6, 100) fail (S 1, Smith, 20, London, P 1, 400) fail n Delete (S 3, Blake, 30, Paris, P 2, 200) (S 1, Smith, 20, London, P 1, 300) No Action or Restrict, fail Cascade, deleting all other SP tuples for S 1 n Update (S 1, Smith, 20, London, P 1, 300) (S 1, Smith, 20, London, P 1, 400) (S 1, Smith, 20, London, P 1, 300) (S 1, Smith, 20, Athens, P 1, 400) (S 1, Smith, 20, London, P 1, 300) (S 6, Smith, 20, London, P 1, 300) Update rule from SP to S Advanced Database System Yin-Fu Huang

10. 4 View Updates (11/12) Ø Case 3 (many-to-many) e. g. , Var S-P View S Join P; (See Fig. 10. 8) Advanced Database System Yin-Fu Huang

10. 4 View Updates (12/12) § Insert (S 1, Smith, 20, London, P 7, Washer, Red, 5) adding two tuples to the view (S 6, Green, 20, London, P 7, Washer, Red, 5) adding six tuples to the view § Delete (S 1, Smith, 20, London, P 1, Nut, Red, 12) deleting four tuples from the view n Other Operators Ø Summarize e. g. , Summarize SP Per SP {S#} Add Sum (Qty) As Totqty; • Update (S 4, 900) (S 4, 800) fail • Insert (S 5, 0) fail Advanced Database System Yin-Fu Huang

10. 5 Snapshots (A Digression) n Like views, snapshots are derived relvars; unlike views, however, they are real, not virtual. e. g. , Var P 2 SC Snapshot ((S Join SP) Where P#=P#(‘P 2’)) {S#, City} Refresh Every Day; Advanced Database System Yin-Fu Huang

10. 6 SQL Facilities n Create View <view name> As <table exp> [With [<qualifier>] Check Option]; e. g. , Create View Good_Supplier As Select S. S#, S. Status, S. City From S Where S. Status > 15 With Check Option; n Drop View <view name> <behavior>; Advanced Database System Yin-Fu Huang

10. 6 SQL Facilities (Cont. ) n SQL’s support for view updating is limited. n Loosely speaking, we can say that SQL regards the following views as updatable: Ø Views defined as a restriction and/or projection of a single base table Ø Views defined as a one-to-one or one-to-many join of two base tables (in the one-to-many case, only the ‘many’ side is updatable) Ø Views defined as a UNION ALL or INTERSECT of two distinct base tables Ø Certain combinations of Cases 1 -3 Advanced Database System Yin-Fu Huang

The End. Advanced Database System Yin-Fu Huang