Views 1 Views A view is a virtual

Views 1

Views • A view is a "virtual table" defined using a query • You can use a view as if it were a table, even though it doesn't contain data • The view is used as a ‘window’ to underlying tables • The view is computed every time that it is referenced • Changes can be done in both directions: View <-> Table 2

Advantages and Disadvantages • Advantages: – no memory used for views – update of table does not require updating views – gives query processor more choices for optimizing • Disadvantages: – must be recomputed every time used – if tables that view uses are dropped, view data is lost 3

Example • A View is a query that looks like a table and can be used as a table. CREATE OR REPLACE VIEW Dept 20 as SELECT Ename, Job, Sal*12 As Annual. Sal FROM Emp Where dept=20; 4

Example 2: Products table Product. Name Quantity Unit. Price Milk 100 4 Eggs 87 10 Bread 64 5 CREATE VIEW ]Products. Above. Average. Price] AS SELECT Product. Name, Unit. Price FROM Products WHERE Unit. Price>(SELECT AVG(Unit. Price) FROM Products) 5

What are views good for? (1) • Simplifying complex queries • Here is another example that allows the user to "pretend" that there is a single table in the database CREATE OR REPLACE VIEW SRB as SELECT S. sid, sname, rating, age, R. bid, day, bname, color FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid 6

What are views good for? (2) Find names of Sailors who reserved boat 103 using SRB SELECT sname FROM SRB WHERE bid=‘ 103’ 7

What are views good for? (3) • Security issues – preventing unauthorized access. • Example: hiding the rating value CREATE VIEW Sailor. Info as SELECT sname, sid, age FROM Sailors • grant SELECT on Sailor. Info to joe; 8

Changing Tables through a View 9

• Changing a view changes the underlying tables • We will specify the limitations on the changes we are allowed to perform on a view based on one table only • The guiding principle: We cannot “approach” a value which doesn’t appear in the view • (but many changes do not require “approaching”) 10

Changing a Table through a View • You can’t insert if the underlying table has non null columns not appearing in the view • You can’t insert or update if any of the view columns referenced in the command contains functions or calculations • You can’t insert, update or delete if the view contains group by or distinct 11

• You can insert a value to the view even if it will not appear in the view after insertion. • You can update a value appearing in the view so that the result will not appear in the view. • This is true unless ‘with check option’ is specified. CREATE VIEW Old. Sailors as SELECT sid, age FROM Sailors Where age>50 With check option Insert into Old. Sailors values(1132, 57); Legal! Illegal! 12

• You cannot update a value which doesn’t appear in the view (this requires “approaching”) • You cannot insert or update columns which do not appear in the view • But if you delete a row, it will delete the values of columns which are not in the view as well (does not require “approaching”) 13

Insert Only Values of Rows seen through Columns seen through the View No Yes (Yes if CHECK) Update (values after change) Update No Yes (Yes if CHECK) Yes Yes No (values before change) Delete 14

Inserting Allowed CREATE VIEW Old. Sailors as SELECT * FROM Sailors WHERE age > 50; INSERT INTO Old. Sailors(sid, sname, age, rating) VALUES(12, ‘Joe’, 51, 10); When we select from Old. Sailors next time, we will see Joe 15

Inserting Allowed CREATE VIEW Old. Sailors as SELECT * FROM Sailors WHERE age > 50; INSERT INTO Old. Sailors(sid, sname, age, rating) VALUES(12, ‘Mary’, 49, 10); When we select from Old. Sailors next time, we will not see Mary. But she will appear in Sailors! 16

Preventing Insertions that are not seen through the View CREATE VIEW Old. Sailors as SELECT * FROM Sailors WHERE age > 50 WITH CHECK OPTION; INSERT INTO Old. Sailors(sid, sname, age, rating) VALUES(12, ‘Joe’, 51, 10); OK INSERT INTO Old. Sailors(sid, sname, age, rating) VALUES(12, ‘Mary’, 49, 10); Error! 17

Inserting Not Allowed CREATE VIEW Sailors. Info as SELECT sname, rating FROM Sailors WHERE age>50; INSERT INTO Sailors. Info VALUES(‘Joe’, 10); Illegal! Why? 18

Updating Allowed CREATE VIEW Sailors. Info as SELECT sname, rating, age FROM Sailors WHERE age>50; UPDATE Sailors. Info SET rating = 6 WHERE sname = ‘Joe’; Oracle only changes the rating of Joes who are older than 50. Implemented by adding WHERE condition of view to WHERE condition of Update UPDATE Sailors SET rating = 6 WHERE sname = ‘Joe’ and age>50; 19

Updating Allowed CREATE VIEW Sailors. Info 2 as SELECT sname, rating, age FROM Sailors WHERE age>50; UPDATE Sailors. Info 2 SET age = age - 1; Will cause tuples to "disappear from the view" Can prevent this with "WITH CHECK OPTION" How is it implemented? UPDATE Sailors SET age = age - 1 WHERE age>50; 20

Updating Not Allowed if …? CREATE VIEW Sailors. Info 2 as SELECT sname, rating, age FROM Sailors WHERE age>50 WITH CHECK OPTION; UPDATE Sailors. Info 2 SET age = age - 1; 21

Updating Not Allowed CREATE VIEW Sailors. Info 3 as SELECT sname, rating + age as ra FROM Sailors WHERE age>50; UPDATE Sailors. Info 3 SET ra = 7 WHERE sname = ‘Joe’; Illegal! Why? UPDATE Sailors. Info 3 SET sname = ‘Joe’ WHERE ra = 7; legal 22

Deleting Allowed CREATE VIEW Sailors. Info 3 as SELECT sname, rating + age as ra FROM Sailors WHERE age>50; DELETE FROM Sailors. Info 3 WHERE sname = ‘Joe’ and ra = 56; Oracle only deletes Joes visible through the view. How do you think that this is implement by Oracle? DELETE FROM Sailors WHERE sname = ‘Joe’ and rating + age = 56 and age > 50; 23

Examples (1) CREATE VIEW Old. Sailors as SELECT * FROM Sailors WHERE age > 50; UPDATE Old. Sailors SET rating = 10; Update rating of sailors older than 50 24

Examples (2) CREATE VIEW Old. Sailors as SELECT * FROM Sailors WHERE age > 50; UPDATE Old. Sailors SET age = age +1 WHERE age <= 50; NOTHING! 25

Examples(3) CREATE VIEW Old. Sailors as SELECT * FROM Sailors WHERE age > 50; DELETE FROM Old. Sailors; Remove from Sailors the sailors that are older than 50 Implementation DELETE FROM Sailors WHERE age > 50 26

Inserting/Updating/Deleting Not Allowed CREATE VIEW Old. Sailors as SELECT sname FROM Sailors GROUP BY sname HAVING MIN(age) > 50; DELETE FROM Old. Sailors; INSERT INTO Old. Sailors VALUES(‘Joe’); 27

Inserting/Updating/Deleting Not Allowed CREATE VIEW Old. Sailors as SELECT distinct sname, age FROM Sailors WHERE age > 50; DELETE FROM Old. Sailors; INSERT INTO Old. Sailors VALUES(‘Joe’, 55); UPDATE Old. Sailors SET age = age +1; 28

Materialized Views • What: A materialized view is a view that actually exists as a table • Why: This can be more efficient than recomputing the view’s query each time it is accessed • How: We specify how often the materialized view is refreshed and how 29

Null Values "As we know, There are knowns. There are things we know. We also know There are known unknowns. That is to say We know there are some things We do not know. ” - Donald Rumsfeld, US Defence Secretary 30

Null Values in Expressions • The result of an arithmetic expression, over something that is null -> is null (e. g. , null*10 = null) • Three-valued logic: true, false, unknown • Nulls in logical expressions: – null AND true -> unknown – null AND false -> false – null OR true -> true – null OR false -> unknown – NOT (null) -> unknown – x { =<, =>, <, >, =} null -> unknown 31

Two-Valued Logic Table p q p AND q p OR q True True False True False Three-Valued Logic Table p q p AND q p OR q True True False True Null Unknown True False False Null False Unknown Null True Unknown True Null False Unknown Null Unknown 32

What will these return? SELECT S. sname FROM Sailors S WHERE S. age = null SELECT S. sname FROM Sailors S WHERE S. age != null 33

Null Values in Expressions (2) • Tuples only pass the WHERE/HAVING condition if the WHERE/HAVING evaluate to true (not false or unknown) • Null verification: – IS NULL (not = NULL) – IS NOT NULL (not <> NULL) 35

Examples • If x is null – x = 3 -> unknown – null = 3 -> unknown – x = x -> unknown – null = null -> unknown – x * 0 -> = null – null * 0 -> = null 36

Nulls in Aggregation Functions • count(A): counts non-null As. Returns 0 if all As are null • sum(A), avg(A), min(A), max(A) – ignore null values of A – if A only contains null value, the result is null • count(*): counts ALL rows (even rows that are all null values) 37

We want the sailors that have not reserved a boat to appear in the result as well SELECT S. sname, R. bid FROM Sailors S, Reserves R WHERE S. sid = R. sid(+) Sailors who have not reserved a boat will have null in the R. bid column 38