Transcript CH10

Chapter 10
Views
Prof. Yin-Fu Huang
CSIE, NYUST
10.1 Introduction
 Example: Var Good_Supplier View
(S Where Status > 15) {S#, Status, City};
(See Fig. 10.1)
 The view-defining expression is not evaluated but is merely
“remembered” by the system.
a derived and virtual relvar
 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 }
 Any updates: view
 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 >
Weight(12.0);
Yin-Fu Huang
Advanced
Database
System
10.1 Introduction (Cont.)
 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?
 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.
 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
 Two Important Principles
 The Principle of Interchangeability
 The Principle of Database Relativity
Advanced Database System
Yin-Fu Huang
10.3 View Retrievals
 V = X(D)
 RO(V) = RO(X(D))
10.4 View Updates (1/12)
 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))
 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)
 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
(S6, Smith, 50, Rome)
(S7, Jones, 50, Paris)
Suppose SA and SB are two distinct base relvars.
(See Fig. 10.3)
(S6, Smith, 50, Rome)
(S7, 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
 Intersect: A Intersect B
(PA) And (PB)
 Difference: A Minus B
(PA) And Not (PB)
 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
(S6, Green, 20, London)
(S1, Green, 20, London)
(S6, Green, 20, Athens)
fail
fail
 Delete
(S1, Smith, 20, London)
 Update
(S1, Smith, 20, London)
(S6, Green, 20, London)
(S1, Smith, 20, London)
(S2, Smith, 20, London)
Advanced Database
System
(S1, Smith, 20, London)
(S1, Smith,
20, Athens)
fail
Yin-Fu Huang
fail
10.4 View Updates (6/12)
 Project: A{X}
A{X}
e.g., Var SC View
S {S#, City};
(See Fig. 10.5)
 Insert
(S6, Athens)
(S1, Athens)
fail
 Delete
(S1, London)
 Update
(S1, London)
(S1, Athens)
System
(S1, London) Advanced
(S2,Database
London)
fail
Yin-Fu Huang
10.4 View Updates (7/12)
 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
(P7, Cog, Red, 12, Paris, 5448)
(P7, Cog, Red, 12, Paris, 5449)
fail
(P1, Cog, Red, 12, Paris, 5448)
fail
 Delete
(P1, Nut, Red, 12, London, 5448)
 Update
(P1, Nut, Red, 12, London, 5448)
(P1, Nut, Red, 10, Paris, 4540)
(P1, Nut, Red, 12, London, 5448)
(P2, Nut, Red, 12, London, 5448)
fail
Advanced Database System
Yin-Fu Huang
10.4 View Updates (9/12)
 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)
 Insert
(S4, Clark, 20, London, P6, 100)
(S5, Adams, 30, Athens, P6, 100)
(S6, Green, 20, London, P6, 100)
(S4, Clark, 20, Athens, P6, 100)
fail
(S1, Smith, 20, London, P1, 400)
fail
 Delete
(S3, Blake, 30, Paris, P2, 200)
(S1, Smith, 20, London, P1, 300)
No Action or Restrict, fail
Cascade, deleting all other
SP tuples for S1
 Update
(S1, Smith, 20, London, P1, 300)
(S1, Smith, 20, London, P1, 400)
(S1, Smith, 20, London, P1, 300)
(S1, Smith, 20, Athens, P1, 400)
(S1, Smith, 20, London, P1, 300)
(S6, Smith, 20, London, P1, 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
(S1, Smith, 20, London, P7, Washer, Red, 5)
adding two tuples to the view
(S6, Green, 20, London, P7, Washer, Red, 5)
adding six tuples to the view
 Delete
(S1, Smith, 20, London, P1, Nut, Red, 12)
deleting four tuples from the view
 Other Operators
 Summarize
e.g., Summarize SP Per SP {S#} Add Sum (Qty) As Totqty;
• Update (S4, 900)
(S4, 800)
fail
• Insert (S5, 0)
fail
Advanced Database System
Yin-Fu Huang
10.5 Snapshots (A Digression)
 Like views, snapshots are derived relvars; unlike views,
however, they are real, not virtual.
e.g., Var P2SC Snapshot
((S Join SP) Where P#=P#(‘P2’)) {S#, City}
Refresh Every Day;
Advanced Database System
Yin-Fu Huang
10.6 SQL Facilities
 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;
 Drop View <view name> <behavior>;
Advanced Database System
Yin-Fu Huang
10.6 SQL Facilities (Cont.)
 SQL’s support for view updating is limited.
 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