SQL & Tuple Relational Calculus

SQL & Tuple Relational Calculus

Nulls • Conditions: x op y (where op is <, >, <>, =, etc.) has value unknown (U) when either x or y is null • WHERE T.cost > T.price • Arithmetic expression: x op y (where op is +, -, *, etc.) has value NULL if x or y is null • WHERE (T. price/T.cost) > 2 • Aggregates: COUNT counts nulls like any other value; other aggregates ignore nulls SELECT COUNT (T.CrsCode), AVG (T.Grade) FROM Transcript T WHERE T.StudId = ‘1234’

Nulls (con’t) • WHERE clause uses a three-valued logic to filter rows. Portion of truth table: • Rows are discarded if WHERE condition is false or unknown • Ex: WHERE T.CrsCode = ‘CS305’ AND T.Grade > 2.5 C1 C2 C1ANDC2 C1ORC2 T U U T F U F U U U U U

Modifying Tables - Insert • Inserting a single row into a table • Attribute list can be omitted if it is the same as in CREATE TABLE (but do not omit it) • NULL and DEFAULT values can be specified INSERT INTO Transcript (StudId, CrsCode, Semester, Grade) VALUES (12345, ‘CSE305’, ‘S2000’, NULL)

Bulk Insertion CREATE TABLE DeansList ( StudId INTEGER, Credits INTEGER, CumGpa FLOAT, PRIMARY KEY StudId) • Insert the rows output by a SELECT INSERT INTO DeansList (StudId, Credits, CumGpa) SELECT T.StudId, 3 * COUNT (*), AVG(T.Grade) FROM Transcript T GROUP BY T.StudId HAVING AVG (T.Grade) > 3.5 AND 3*COUNT(*) > 30

Modifying Tables - Delete • Similar to SELECT except: • No project list in DELETE clause • No Cartesian product in FROM clause • Rows satisfying WHERE clause (general form, including subqueries, allowed) are deleted instead of output DELETE FROM Transcript WHERE Grade IS NULL AND Semester <> ‘S2000’

Modifying Data - Update UPDATE Employee SET Salary = Salary * 1.05 WHERE Department = ‘research’ • Updates rows in a single table • All rows satisfying WHERE clause (general form, including subqueries, allowed) are updated

Updating Views • Question: Since views look like tables to users, can they be updated? • Answer: Yes – a view update changes the underlying base table to produce the requested change to the view Transcript (StudId, CrsCode, Semester, Grade) CREATE VIEW CsReg (StudId, CrsCode, Semester) AS SELECT T.StudId, T. CrsCode, T.Semester FROM Transcript T WHERE T.CrsCode LIKE ‘CS%’ AND T.Semester=‘S2000’

Updating Views - Problem 1 • Question: What value should be placed in attributes of underlying table that have been projected out (e.g., Grade)? • Answer: NULL (assuming null allowed in the missing attribute) or DEFAULT INSERT INTO CsReg (StudId, CrsCode, Semester) VALUES (1111, ‘CSE305’, ‘S2000’)

Updating Views - Problem 2 INSERT INTO CsReg (StudId, CrsCode, Semester) VALUES (1111, ‘ECO105’, ‘S2000’) • Problem: New tuple not in view • Solution: • Allow insertion by default • Use ‘WITH CHECK OPTION’ clause CREATE VIEW CsReg (StudId, CrsCode, Sem) AS SELECT T.StudId, T. CrsCode, T.Semester FROM Transcript T WHERE T.CrsCode LIKE ‘CS%’ AND T.Sem=‘S2000’ WITH CHECK OPTION

Updating Views - Problem 3 • Update to the view might not uniquely specify the change to the base table(s) that results in the desired modification of the view CREATE VIEW ProfDept (PrName, DeName) AS SELECT P.Name, D.Name FROM Professor P, Department D WHERE P.DeptId = D.DeptId

Updating Views - Problem 3 (con’t) • Tuple <Smith, CS> can be deleted from ProfDept by: • Deleting row for Smith from Professor (but this is inappropriate if he is still at the University) • Deleting row for CS from Department (not what is intended) • Updating row for Smith in Professor by setting DeptId to null (seems like a good idea)

Updating Views - Restrictions • Updatable views are restricted to those in which • No Cartesian product in FROM clause • no aggregates, GROUP BY, HAVING • No expressions or DISTINCT in SELECT For example, if we allowed: CREATE VIEW AvgSalary (DeptId, Avg_Sal ) AS SELECT E.DeptId, AVG(E.Salary) FROM Employee E GROUP BY E.DeptId then how do we handle: UPDATE AvgSalary SET Avg_Sal = 1.1 * Avg_Sal

SQL and Relational Calculus • Although relational algebra is useful in the analysis of query evaluation, SQL is actually based on a different query language: relational calculus • There are two relational calculi: • Tuple relational calculus (TRC) • Domain relational calculus (DRC)

Tuple Relational Calculus • Form of query: • T is the target - a variable that ranges over tuples of values • Condition is the body of the query • Involves T (and possibly other variables) • Evaluates to true or false if a specific tuple is substituted for T {T | Condition}

Tuple Relational Calculus: Example {T | Teaching(T) AND T.Semester = ‘F2000’} • When a specific tuple has been substituted for T • Teaching(T) is true if T is in the relational instance Teaching • T.Semester = ‘F2000’ is true if the semester attribute of T has value F2000 • Equivalent to: SELECT * FROM Teaching T WHERE T.Semester = ‘F2000’

Relation Between SQL and TRC {T | Teaching(T) AND T.Semester = ‘F2000’} • Target corresponds to SELECT list: the query result contains the entire tuple • Body split between two clauses: • Teaching(T) corresponds to FROM clause • T.Semester = ‘F2000’ corresponds to WHERE clause SELECT * FROM Teaching T WHERE T.Semester = ‘F2000’

Query Result • The result of a TRC query with respect to a given database is the set of all choices of values for the variable T that make the query condition a true statement about the database

Query Condition • Atomic condition: • P(T), where P is a relation name • T.A oper S.B or T.Aoper const, where T and S are relation names, A and B are attributes and oper is a comparison operator (e.g., =, <, >, etc) • Condition: • atomic condition • If C1 and C2 are conditions then C1 AND C2 , C1 OR C2 , and NOT C1 are conditions • If C is a condition, R a relation name, T a tuple variable, then T R(C) and T R(C) are conditions

Bound and Free Variables • X is bound with an existential or universal quantifier,  X S (C(X)),  X S (C(X)), else it is free. • X is a free variable in the statement “X is in CS305” (this might be represented more formally as C(X) ) • The statement is neither true nor false in a particular state of the database until we assign a value to X • X is a bound (quantified) variable in the statement “there exists a student X such that X is in CS305” (this might be represented more formally as  X S (C(X)), where S is the set of all students) • The statement can be assigned a truth value for any particular state of the database

Bound and Free Variables in TRC Queries • Bound variables are used to make assertions about tuples in database (used in conditions). • Free variables designate the tuples to be returned by the query (used in targets) • When a value is substituted for S the condition has value true or false • S must be the only free variable in condition • {S | Student(S) AND( T Transcript • (S.Id = T.StudId ANDT.CrsCode = ‘CS305’))}

Example { E | Course(E) AND S  Student ( T  Transcript ( T.StudId = S.Id AND T. CrsCode = E.CrsCode))} • Returns the set of all course tuples corresponding to courses that have been taken by all students • How: substitute all possible values for E, then check condition – for all students, have they taken this course (from tuple E)

TRC Syntax Extension • To get a feel for the relation between TRC and SQL we introduce a syntactic extension of TRC whose only purpose is to simplify queries (the same query can be translated into TRC). Use {S.Name, T.CrsCode | Student (S) ANDTranscript (T) AND …} instead of {R | T Transcript (S Student (R.Name = S.Name AND R.CrsCode = T.CrsCode AND …} where R is a new tuple variable with attributes Name and CrsCode

Relation Between TRC and SQL • List the names of all professors who have taught MGT123 • In TRC: • In SQL: {P.Name | Professor(P) AND T Teaching (P.Id = T.ProfId AND T.CrsCode = ‘MGT123’)} SELECT P.Name FROM Professor P, Teaching T WHERE P.Id = T.ProfId AND T.CrsCode = ‘MGT123’

More on Quantification • Adjacent existential quantifiers and adjacent universal quantifiers commute: • T Transcript (T1 Teaching (…))is the same as T1 Teaching (T Transcript (…)) • Adjacent existential and univeral quantifiers do not commute: • X Y LIKES(X,Y) versus Y X LIKES(X,Y) • T Transcript (T1 Teaching (…))is not the same as T1 Teaching (T Transcript (…))

More on Quantification (con’t) • A quantifier defines the scope of the quantified variable (it is analogous to a begin/end block) •  T  R1 (U(T) AND  T  R2(V(T))) • is the same as: •  T  R1 (U(T) AND  S  R2(V(S)))

Views in TRC • Problem: List students who took a course from every professor in the Computer Science Department • Solution: • First create view. • Then use the view CSProf = {P.ProfId | Professor(P) AND P.DeptId = ‘CS’} {R.StudId | P  CSProf (T1  Teaching (Transcript(R) AND P.ProfId = T1.ProfId AND T1.CrsCode = R.Crscode AND T1.Semester = R.Semester))}

SQL Is Based on TRC • Using views, the translation between complex SQL queries and TRC is direct. R1(A,B), R2(C,D), R3(E,F) and R4(G,H) SELECT R1.A, R2.C FROM Rel1 R1, Rel2 R2 WHEREcondition1(R1, R2) AND R1.B IN (SELECT R3.E FROM Rel3 R3, Rel4 R4 WHEREcondition2(R2, R3, R4) ) Temp = {R3.E, R2.C, R2.D | Rel2(R2) AND Rel3(R3) AND R4 Rel4 (condition2(R2, R3, R4) )} {R1.A, R2.C | Rel1(R1) AND Rel2(R2) AND condition1(R1, R2) AND R Temp (R.E = R1.B AND R.C = R2.C AND R.D = R2.D) } R2 is global var, parameterizes inner query R3 & R2 are free; R4 is bounded

SQL & Tuple Relational Calculus