1 / 11

Context

Context. “Updating Union, Intersection and Difference views” - introduces a systematic approach to updating a view formed specifically by using Union Intersection Difference operators. Updating a Table.

lilly
Download Presentation

Context

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. Context “Updating Union, Intersection and Difference views” - introduces a systematic approach to updating a view formed specifically by using • Union • Intersection • Difference operators

  2. Updating a Table The predicate of a table represents the criterion for update acceptability for that table. Ex. Let the meaning (or the predicate) of base table EMP be the following: • e.EMP# in EMP# domain AND • e.ENAME in NAME domain AND • e.DEPT# in DEPT# domain AND • e.SALARY in US CURRENCY domain AND • IF e.DEPT# = 'D1' THEN e.SALARY < 44K • EMP# is the primary key Try : 1. Insert a tuple (‘E5’, ‘Lopez’, ‘D1’, 20K)  Insert OK. 2. Update the above tuple and make salary = 50K  Update FAILED.

  3. Updating a View • A view is nothing but a derived table • A view can updated only if the predicate of the view is satisfied

  4. Updating a Union View CREATE VIEW UV AS (SELECT * FROM EMP WHERE DEPT# = ‘D1’) ----- A UNION (SELECT * FROM EMP WHERE SALARY > 30K) ---- B Table : 1 EMP Table : 2 VIEW A Table : 3 VIEW B Table : 4 VIEW UV(A UNION B)

  5. Updating a Union View…Contd. I. Insert :The new row is inserted if it satisfies either PA or PB or both. Caution : The new row must not already appear in either A or B, because otherwise we would be trying to insert a row that already exists. Ex. 1. Insert (E5,Smith,D1,20K) . This row satisfies PA though not PB. It is therefore inserted into A i.e effectively, inserted into the EMP base table. 2. Insert (E6,Jones,D1,40K). This row satisfies PA and PB. It is therefore logically inserted into both, effectively only once into the EMP base table

  6. Updating Union View … Contd. II. Delete :If the row to be deleted appears in A, it is deleted from A. If it (still) appears in B, it is deleted from B. Ex. 1. Delete (E1,JOHN, D1, 20K) -> Present in A , deleted from A 2. Delete (E3,JAMES,D1,40K) -> Present in A , deleted from A.

  7. Updating Union View … Contd. III. Update :The row to be updated must be such that the updated version satisfies PA or PB or both. Note : Update is accomplished by deleting the old record and then inserting a new record Ex. Update(E1,JOHN,D1,20K) to (E1,JOHN,D2,40K) • Delete from A, if it appears in A, without performing triggered actions.(such as cascade delete and so on) • Delete from B, if it (still) appears in B • If the updated version of the row satisfies PA, it is inserted into A. • Finally, if the updated version satisfies PB, it is inserted into B, unless it was inserted into B already as a side-effect of inserting it into A.

  8. Updating Intersection Views CREATE VIEW IV AS (SELECT * FROM EMP WHERE DEPT# = ‘D1’) ----- A INTERSECT (SELECT * FROM EMP WHERE SALARY > 30K) ---- B I. Insert:The new row must satisfy both PA and PB. If it does not currently appear in A, it is inserted into A. If it (still) does not appear in B, it is inserted into B. II. Delete:The row to be deleted is deleted from A. If it (still) appears in B, it is deleted from B. III. Update:The row to be updated must be such that the updated version satisfies both PA and PB.

  9. Updating Difference Views CREATE VIEW DV AS (SELECT * FROM EMP WHERE DEPT# = ‘D1’) ----- A MINUS (SELECT * FROM EMP WHERE SALARY > 30K) ---- B I. Insert: The new row must satisfy PA and not PB. It is inserted into A II. Delete: The row to be deleted is deleted from A. III. Update: The row to be updated must be such that the updated version satisfies PA and not PB. The row is deleted from A and the updated version of the row is then inserted into A

  10. Comments • A systematic approach to view update problem • It adheres to the semantics of the data and is thus database-independent.

  11. Reference Date, C. J. and McGoveran, D.O. : “Updating Union, Intersection and Difference Views”, Database Programming & Design 7, No. 6 (June 1994)

More Related