Remaining Topics in SQL to be covered…Null’s in SQLNeed for Care in Using Nulls...Sometimes Nulls very useful even if possible to avoid them…Interpreting SQL queries with Null (1)Interpreting SQL with Null (2)3-Valued LogicTruth TableSQL …… constraints, assertions, triggers …Some Key Laws Fail to Hold in 3-Valued LogicExampleModifying ViewsUpdatable viewsNon-updatable viewsDelete from Updatable ViewsUpdate Updatable ViewsDrop ViewsJoin Expressions in SQLJoin Expressions in SQLOther Types of Join ExpressionsRevisit to Specifying Integrity Constraints in SQLConstraints in SQLSlide 23Another Example of Tuple Based ConstraintAttribute and Tuple Based ConstraintsAssertionsExample AssertionDifferent Constraint TypesGiving Names to ConstraintsAltering ConstraintsTriggersElements of Triggers (in SQL3)Example: Row Level TriggerStatement Level TriggerBad Things Can HappenEmbedded SQLPrograms with SQLThe Impedance Mismatch ProblemInterface: SQL / Host LanguageUsing Shared VariablesSingle-Row Select StatementsCursorsMore on CursorsDynamic SQLDynamic SQL (II)Example UsageExample Usage (II)Remaining Topics in SQL to be covered…NULL values in SQLOuter joins in SQLConstraints and Triggers in SQLEmbedded SQL.Null’s in SQLSQL supports a special value -- NULL in place of a value in a tuple's componentNull’s can be used for multiple purposes --Value exists, but we do not know what it is..Information does not existExample: boris registered for pass/fail and thus has no project assigned (nulls used to represent information is inapplicable)stefan registered for letter grade but has no current project assignment (nulls used to represent unavailability of information)Cs184projectsstudent proj title dateboris null nullstefan null nullNeed for Care in Using Nulls...Using nulls for missing values may result in loss of information Cs184projectsstudent proj title dateboris oodb 11/16/95stefan oodb 11/16/95monica par dbms 11/21/95Cs184projectsstudent proj title dateboris null nullstefan null nullmonica null null• Information that boris and stefan are part of the same project team , and that monica is a team by herself is lost by using nulls!Sometimes Nulls very useful even if possible to avoid them…• Say 98% of employees have a fax number and a query accessing office number and fax number is very common.• Storing information using a different schema (employee, office num) and (employee, fax number) will cause all such queries to perform a join!• Instead using nulls is a better idea in this case.Interpreting SQL queries with Null (1)Any arithmetic operation on Null and any other value results in Null.E.g., x + 3 == Null, if x is Null Comparison of Null with any value (including other Null) results in a value UNKNOWNE.g., x > 3 results in UNKNOWN, if x is NullInterpreting SQL with Null (2)Earlier, we learnt that results of comparison was always T or F.Logical operators: AND, OR, NOT combined these truth values in a natural way to return a T or a F.However, comparison of Null to a value produces a third truth value -- UNKNOWNHow to interpret the logical operators in this case?3-Valued LogicThink of true = 1; false = 0, and unknown = 1/2.Then: AND = min. OR = max. NOT(x) = 1 - xTruth TableT = true F = false U = unknownSQL …… constraints, assertions, triggers …Some Key Laws Fail to Hold in 3-Valued LogicExample: Law of the excluded middle, i.e.,p OR NOT p = TRUE For 3-valued logic: if p = unknown, then left side = max(1/2,(1-1/2)) = 1/2  1.there is no way known to make 3-valued logic conform to all the laws we expect for 2-valued logic.ExampleBar beer priceJoe's bar Bud NULLSELECT barFROM SellsWhere price < 2.00 OR price >= 2.00 UNKNOWN UNKNOWN UNKNOWNModifying 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.viewsUpdatable viewsCREATE TABLE Emp(ename char(20), dno int, sal float default 0);CREATE VIEW toyEmp ASSELECT ename, dnoFROM empWHERE dno = 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.toyEmp (ename, dno)INSERT INTO toyEmp VALUES (‘Tom’, 111);Emp (ename, dno, sal)Non-updatable viewsCREATE TABLE Emp(ename char(20), dno int, sal float default 0);CREATE VIEW toyEmp ASSELECT ename, dnoFROM empWHERE dno = 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 generatedtoyEmp (ename, dno)INSERT INTO toyEmp VALUES (‘Tom’, 111);Emp (ename, dno, sal)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 toyEmpWHERE ename = ‘Jack’Will be translated to:DELETE FROM EmpWHERE ename = ‘Jack’ AND dno = 111;toyEmp (ename, dno)Emp (ename, dno, sal)Update Updatable ViewsWill update all tuples in the base relations that produce the updated tuples in the viewExample:CREATE VIEW toyEmp ASSELECT ename, dno, salFROM 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)Drop ViewsDROP VIEW <name>;Example: DROP VIEW toyEmp;The base tables will NOT change.Join Expressions in SQL Joins can be expressed implicitly in SQL using SELECT-FROM-WHERE clause.Alternatively, joins can also be expressed using join expressions.E.g.,relations: emp (ssn, sal, dno), dept(dno,dname)emp CROSS JOIN deptproduces a relation with 5 attributes which is a cross product of emp and dept.Join Expressions in SQLJoin expressions can also be used in FROM clauseSELECT nameFROM emp JOIN dept ON emp.dno = dept.dno AND dept.dname = ‘toy’Note the join expression R JOIN S on <condition>Other Types of Join ExpressionsR NATURAL JOIN SR NATURAL FULL OUTER JOIN SR NATURAL LEFT OUTER JOIN SR NATURAL RIGHT