1 / 22

Advanced Database System Overview

Learn about views, updates, restructuring, and principles in an advanced database system. Understand view retrievals, updates, and principles in database management.

ctorres
Download Presentation

Advanced Database System Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 10.1 Introduction (Cont.) • Example: Good_Supplier Where City ≠ ‘London’ (S Where Status > 15 And City ≠ ‘London’) { S#, Status, City } • Any updates: view the underlying data • Further Examples • 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); Advanced Database System

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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) insert into only SA (S7, Jones, 50, Paris) insert into SA and SB Advanced Database System

  9. 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

  10. 10.4 View Updates (5/12) • Insert (S6, Green, 20, London) (S1, Green, 20, London) fail (S6, Green, 20, Athens) fail • Delete (S1, Smith, 20, London) • Update (S1, Smith, 20, London) (S6, Green, 20, London) (S1, Smith, 20, London) (S2, Smith, 20, London) fail (S1, Smith, 20, London) (S1, Smith, 20, Athens) fail Advanced Database System

  11. 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) (S1, London) (S2, London) fail Advanced Database System

  12. 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

  13. 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

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

  15. 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 10.4 View Updates (10/12) Advanced Database System

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. The End. Advanced Database System

More Related