Remaining Topics in SQL to be covered….ppt

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 tuples 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) Need for Care in Using Nulls... Using nulls for missing values may result in loss of information Sometimes Nulls very useful even if possible to avoid them… 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 Null Interpreting 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 - x Truth Table SQL …… 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. Example Modifying Views Updatable views Non-updatable views Delete from Updatable Views Update Updatable Views Drop Views Join Expressions in SQL Joins can be expressed implicitly in SQL using SE