1 / 6

Managing Updatable Views in Database Systems: A Case Study on Room Reservations

This document examines the View-Update Problem within database systems, focusing on the creation, updating, and optimization of views related to room reservations. Using a practical example involving a "PresSuite" view for rooms with two beds and specific cost and arrival date criteria, we illustrate how to perform updates safely. The discussion includes potential ambiguities in update translations and explores how to manage view restrictions to ensure successful updates. This foundational concept is critical for efficient database management.

kiri
Télécharger la présentation

Managing Updatable Views in Database Systems: A Case Study on Room Reservations

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. View Processing & Update

  2. View Processing create view PresSuite as select * from Room where NrBeds = 2 select * from PresSuite p natural join Reservation s where Cost > 85 and ArrivalDate = “10 May” = select * from (select * from Room where NrBeds = 2) p natural join Reservation s where Cost > 85 and ArrivalDate = “10 May”

  3. View Processing & Optimization Create view p: NrBeds = 2 r Query with view: Cost > 85  ArrivalDate = “10 May” (p |×| s) Substitute view: Cost > 85  ArrivalDate = “10 May” ((NrBeds = 2 r) |×| s) Optimize: NrBeds = 2  Cost > 85 r |×| ArrivalDate = “10 May” s

  4. The View-Update Problem • Q is the query that defines view V based on database D. • U is the update specification. • V is the updated view. • T is the translator for U, i.e., the actual update applied to D. • D is the updated database. T D D Q Q U V V Problem: there may be more than one translator T for a given update specification U.

  5. View-Update Problem – Example Base Relations: r = Guest Room s = Room RmView G1 R1 R1 Sea R2 City View: create view WindowView as select Guest, RmView from r natural join s Guest RmView G1 Sea View Update: update WindowView set RmView = ‘City’ where Guest = ‘G1’ Two Translations (ambiguous): update r set Room = “R2” where Guest = “G1” update s set View = “City” where Room = “R1”

  6. Updatable Views When appropriately restricted, view updates work. Often overly restricted: typically to just  and  on a single table. create view PresSuite as select * from Room where NrBeds = 2 Then: update PresSuite set Name = “Clinton” where Name = “Nixon” delete from PresSuite where Name = “Kennedy” insert into PresSuite values (6, “Bush”, 2, 100)

More Related