ICS 184: Introduction to Data Management

1. ICS 184: Introduction to Data Management Lecture Note 12: NULL Values and Views

2. NULL Values • NULL is a special value for representing data we don’t have (but: NULL is not a constant) • Three different interpretations • unknown: there is a value that makes sense here, I just don’t know it • e.g., unknown birth date • inapplicable: no value makes sense here • e.g., NULL value in column spouse for unmarried movie star • withheld: we are not entitled to know the value that belongs here • e.g., unlisted phone number Notes 11

3. Using NULL values Two important rules: • Operations (*,+,-,/) involving NULL and any other value  NULL. Examples: • NULL + 3  NULL • NULL – NULL = NULL • 0 * NULL = NULL • Comparisons (=, >, <) involving NULL and any other value UNKNOWN • NULL > 5? • NULL = NULL? • Third truth value (true, false, unknown) Notes 11

4. Truth Table One way to remember the rules: • TRUE = 1, FALSE = 0, UNKNOWN = 0.5 • x AND y = min(x,y) • x OR y = max(x,y) • NOT x = 1 - x x y x AND y x OR y NOT x TRUE TRUE TRUE TRUE FALSE TRUE UNKNOWN UNKNOWN TRUE FALSE TRUE FALSE FALSE TRUE FALSE UNKNOWN TRUE UNKNOWN TRUE UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN FALSE False UNKNOWN UNKNOWN FALSE TRUE FALSE TRUE TRUE FALSE UNKNOWN FALSE UNKNOWN TRUE FALSE FALSE FALSE FALSE TRUE Notes 11

5. NULL is not a constant • To check if a value x is a NULL • Cannot use: “NULL = x”! • Use “x IS NULL” • produces TRUE if x has value NULL • FALSE otherwise Notes 11

6. Example SELECT * FROM emp WHERE sal IS NULL; emp Notes 11

7. Using NULL in SQL queries Evaluating SELECT-FROM-WHERE queries: • For each tuple, one of the truth values is produced. • however, only tuples for which condition evaluates to TRUE become part of answer Notes 11

8. Example • Query 1: SELECT * FROM emp WHERE sal < 55; • Query 2: SELECT * FROM emp WHERE sal <= 50 OR sal >= 50; emp Notes 11

9. Next topic: Views • Views are “virtual” relations, not physically stored. • Goals: • Simplify complex queries. • Define conceptually different views of DB to different users. • Syntax: CREATE VIEW <name> AS <query>; views Notes 11

10. Examples Dept(dno, dname, mgr) Emp (ename, dno, sal) • “Employees in the Toys department.” CREATE VIEW toyEmp AS SELECT ename, sal, emp.dno, mgr FROM Emp, Dept WHERE emp.dno = dept.dno AND dname = ‘toys’; Notes 11

11. Examples Dept(dno, dname, mgr) Emp (ename, dno, sal) • Example 2: some attributes can be dropped (e.g., dno) CREATE VIEW toyEmp AS SELECT ename, sal, mgr FROM Emp, Dept WHERE emp.dno = dept.dno AND dname = ‘toys’; Notes 11

12. Queries on Views • Views can be used as relations to form a query • Example 1: SELECT name FROM toyEmp WHERE salary >= 40000; • How does the system answer a query on views? • “Expand” it (using view definitions) to a query on base relations SELECT name FROM emp, dept WHERE salary >= 40000 AND emp.dno = dept.dno AND dept.dname = ’toys’; Notes 11

13. Queries on Views SELECT avg(sal) FROM toyEmp; SELECT avg(sal) FROM emp, dept WHERE salary >= 40000 AND emp.dno = dept.dno AND dept.dname = ’toys’; Notes 11

14. Modifying Views • How can we modify a view that is “virtual”? • Many views cannot be modified • Some views can be “modified,” called “updatable views” • Their definitions must satisfy certain requirements. • A modification is translated to a modification to its base tables. views Notes 11

15. Updatable views CREATE TABLE Emp(ename char(20), dno int, sal float default 0); CREATE VIEW toyEmp AS SELECT ename, dno FROM emp WHERE dno = 111; toyEmp (ename, dno) Emp (ename, dno, sal) INSERT INTO toyEmp VALUES (‘Tom’, 111); • Insert a tuple to a view: • Insert a corresponding tuple to its base table(s) • Missing values will use NULL or default value • Inserted tuples in base table(s) must generate the new view tuple. Notes 11

16. Non-updatable views CREATE TABLE Emp(ename char(20), dno int, sal float default 0); CREATE VIEW toyEmp AS SELECT ename, dno FROM emp WHERE dno = 111; toyEmp (ename, dno) Emp (ename, dno, sal) INSERT INTO toyEmp VALUES (‘Tom’, 111); • Insert a tuple to a view: • Not allowed: what do we insert into Emp?  view not updatable! • The system is not “smart” enough to know the value of “dno” is 111. • If we fill “dno” with “NULL,” then this view tuple cannot be generated Notes 11

17. Delete from Updatable Views • When deleting a tuple from a view, should delete all tuples from base table(s) that can produce this view tuple. • Example: DELETE FROM toyEmp WHERE ename = ‘Jack’ Will be translated to: DELETE FROM Emp WHERE ename = ‘Jack’ AND dno = 111; toyEmp (ename, dno) Emp (ename, dno, sal) Notes 11

18. Update Updatable Views • Will update all tuples in the base relations that produce the updated tuples in the view • Example: CREATE VIEW toyEmp AS SELECT ename, dno, sal FROM Emp WHERE dno = 111; UPDATE toyEmp SET sal = sal * 0.9 WHERE ename = ‘Jack’ Will be translated to: UPDATE Emp SET sal = sal * 0.9 WHERE ename = ‘Jack’ AND dno = 111; toyEmp (ename, dno) Emp (ename, dno, sal) Notes 11

19. Drop Views • DROP VIEW <name>; • Example: DROP VIEW toyEmp; • The base tables will NOT change. Notes 11

20. Views: Data Independence • We may use views to make programs compatible with changes • Example: Old schema: Emp(emp, dno, sal), Dept(dno, dname, mgr) • All applications use the old schema. • Suppose for some reasons (e.g., save space, or better renaming), we change the schema to: E(emp, deptno, sal), D(deptno, dname, mgr) • All old applications will not work with new schema! • Solution: using views create view Emp(ename, dno, sal) AS create view Dept(dno, dname, mgr) AS select ename, deptno, sal from E; select deptno, dname, mgr from D; • Then old queries still run. Dept(dno, dname, mgr) Emp (ename, dno, sal) Notes 11

21. Views: Data Independence Queries Dept(dno, dname, mgr) Emp (ename, dno, sal) Old schema Queries New schema create view Dept(dno,dname, mgr) AS select deptno,dname, mgr from D; create view Emp(ename, dno, sal) AS select ename, deptno, sal from E; D (deptno, dname, mgr) E (ename, deptno, sal) Notes 11