Security: there are many aspects, one of which is to limit access on a need-to-know basis. A view is a virtual table that is materialized on demand. For example, a “stocker” need only be able to see Book.Isbn and Book.CopiesInSock. CREATE VIEW Stocker (Isbn, Copies) AS SELECT B.Isbn, B.CopiesInStock FROM Book Stocker (Isbn, Copies) …… CopiesInStock Book Isbn ……. 123 25
We can use a view in a query just as we can use a base table. SELECT S.Copies FROM Stocker S WHERE S.Isbn = I If I = 123, then this query would return (25) How is the query evaluated? Do we always/generally have to materialize the view? No. Minimally, substitute (behind the scenes) the view definition in the query (and optimize/evaluate) that modified query. SELECT S.Copies FROM (SELECT B.Isbn, B.CopiesInStock FROM Book B) AS S WHERE S.Isbn = I I is a parameter. Note the Query nested in the FROM clause
An update (including Insert and Delete) on a view invokes an update on the base table. For example, if I=123 and N=100, then UPDATE Stocker S SET S.Copies = S.Copies + N WHERE S.Isbn = I would result in …… CopiesInStock Book Isbn ……. 123 125 Since Isbn is key of Book, only one row would be updated, but in general an update to a view can result in 0 or more rows being updated in a base table (top of next page).
DELETE FROM Stocker S WHERE S.Copies < 5 will delete all rows from Book in which the CopiesInStock < 5 (undoubtedly, we would NOT want a Stocker to be able to do this, which motivates discussion of privilege granting later). INSERT INTO STOCKER (Isbn, Copies) VALUES (567, 200) will insert a row into Book, where Isbn = 567, CopiesInStock = 200 and ALL other attributes are NULL. However, if such an insert will violate a constraint of Book (e.g., PublisherName is NOT NULL), the the insert is rejected. A View can be defined over multiple base tables, as in the following:
Define a view that is required by a person (or process) responsible to “shipping” books that are ready to be shipped. That is, the books have been paid for [PaymentClearanceDate is not null], the books have not been shipped yet [ShipDate is null], and there are enough copies in stock of the book to satisfy the order