HowtoWriteCorrectSQLandKnowItARelationalApproach.ppt

How to Write Correct SQL and Know It: A Relational Approach to SQL ;THESIS :;USING SQL RELATIONALLY :;PREREQUISITES :;FOR EXAMPLE :;What’s a relation variable? What’s prenex normal form? Can a relation have an attribute whose values are themselves relations? Is SQL relationally complete? What’s The Information Principle? How does XML fit with the relational model?;Relational terms when discussing relational theory— relation, tuple, attribute (etc.); SQL terms when discussing SQL—table, row, column (etc.) Note: The equivalences are not exact! One term I’ll use in connection with both relational theory and SQL: operator (SQL uses operator, function, procedure, routine, method, but they all mean the same thing, pretty much) Thus, e.g., =, :=, +, SELECT, DISTINCT, UNION, SUM, operators GROUP BY, etc., etc.;WHY DO YOU NEED TO KNOW RELATIONAL THEORY ??? ;Those who are enamored of practice without theory are like a pilot who goes into a ship without rudder or compass and never has any certainty where he [sic] is going. Practice should always be based on a sound knowledge of theory. —Leonardo da Vinci (1452-1519) Languages die ... mathematical ideas do not. —G. H. Hardy (1877-1947);THEORY IS PRACTICAL !;The gap between theory and practice is not as wide in theory as it is in practice —Anon.;CODD’S ORIGINAL RELATIONAL MODEL : AN OVERVIEW ;CODD’S ORIGINAL RELATIONAL ALGEBRA : AN OVERVIEW ;SP SNO PNO QTY S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 S1 P6 100 S2 P1 300 S2 P2 400 S3 P2 200 S4 P2 200 S4 P4 300 S4 P5 400;MODEL vs. IMPLEMENTATION :;MODEL vs. IMPLEMENTATION (cont.) :;MODEL vs. IMPLEMENTATION (cont.) :;E.g., JOINS ARE SLOW : MAKES NO SENSE !!!;PROPERTIES OF RELATIONS : ;· Every subset of a tuple is a tuple ... Every subset of a heading is a heading ... Every subset of a body is a body · Tuple equality: Two tuples EQUAL iff (= if and only if) ?