Revision for Final Exam

1. CS157A Lecture 25 Revision for Final Exam Prof. Sin-Min Lee Department of Computer Science

2. Relational Query Languages • Languages for describing queries on a relational database • Structured Query Language (SQL) • Predominant application-level query language • Declarative • Relational Algebra • Intermediate language used within DBMS • Procedural

3. What is an Algebra? • A language based on operators and a domain of values • Operators map values taken from the domain into other domain values • Hence, an expression involving operators and arguments produces a value in the domain • When the domain is a set of all relations (and the operators are as described later), we get the relational algebra • We refer to the expression as a query and the value produced as the queryresult

4. Relational Algebra • Domain: set of relations • Basic operators: select, project, union, setdifference, Cartesianproduct • Derived operators: set intersection, division, join • Procedural: Relational expression specifies query by describing an algorithm (the sequence in which operators are applied) for determining the result of an expression

5. The Role of Relational Algebra in a DBMS

6. Produce table containing subset of rows of argument table satisfying condition condition (relation) Example: Person Hobby=‘stamps’(Person) Select Operator Id Name Address Hobby Id Name Address Hobby 1123 John 123 Main stamps 1123 John 123 Main coins 5556 Mary 7 Lake Dr hiking 9876 Bart 5 Pine St stamps 1123 John 123 Main stamps 9876 Bart 5 Pine St stamps

7. Selection Condition • Operators: <, , , >, =,  • Simple selection condition: • <attribute> operator <constant> • <attribute> operator <attribute> • <condition> AND <condition> • <condition> OR <condition> • NOT <condition>

8. Selection Condition - Examples •  Id>3000OR Hobby=‘hiking’ (Person) •  Id>3000 AND Id <3999(Person) •  NOT(Hobby=‘hiking’)(Person) •  Hobby‘hiking’(Person)

9. Project Operator • Produces table containing subset of columns of the table taken as argument attribute list(relation) • Example: Person Name,Hobby(Person) IdName AddressHobbyNameHobby John stamps John coins Mary hiking Bart stamps 1123 John 123 Main stamps 1123 John 123 Main coins 5556 Mary 7 Lake Dr hiking 9876 Bart 5 Pine St stamps

10. Project Operator • Example: • Person Name,Address(Person) Id Name Address Hobby Name Address John 123 Main Mary 7 Lake Dr Bart 5 Pine St 1123 John 123 Main stamps 1123 John 123 Main coins 5556 Mary 7 Lake Dr hiking 9876 Bart 5 Pine St stamps Result is a table (no duplicates); can have fewer tuples than the original

11. Expressions Id, Name(Hobby=’stamps’OR Hobby=’coins’(Person) ) Id Name Address Hobby Id Name 1123 John 9876 Bart 1123 John 123 Main stamps 1123 John 123 Main coins 5556 Mary 7 Lake Dr hiking 9876 Bart 5 Pine St stamps Result Person

12. Set Operators • Relation is a set of tuples, so set operations should apply: , ,  (set difference) • Result of combining two relations with a set operator is a relation; hence all its elements must be tuples having the same structure • Hence, scope of set operations limited to union compatible relations

13. Union Compatible Relations • Two relations are union compatible if • Both have same number of columns • Names of attributes are the same in both • Attributes with the same name in both relations have the same domain • Union compatible relations can be combined using union, intersection, and setdifference

14. Example Tables: Person(SSN, Name, Address, Hobby) Professor(Id, Name, Office, Phone) are not union compatible. But Name(Person)and Name(Professor) are union compatible so Name(Person) -Name(Professor) makes sense.

15. Cartesian Product • If Rand Sare two relations, RS is the set of all concatenated tuples <x,y>, where x is a tuple in R and y is a tuple in S (but see naming problem next) • RS is expensive to compute: • Factor of two in the size of each row • Quadratic in the number of rows A B C D A B C D x1 x2 y1 y2 x1 x2 y1 y2 x3 x4 y3 y4 x1 x2 y3 y4 x3 x4 y1 y2 RS x3 x4 y3 y4 RS

16. Renaming in Cartesian Product Result of expression evaluation is a relation. Attributes of relation must have distinct names. So what do we do if they don’t? E.g., suppose R(A,B) and S(A,C) and we wish to compute RS . One solution is to rename the attributes of the answer: RS( R.A, R.B, S.A, S.C) Although only A needs to be renamed, it is“cleaner” to rename them all.

17. Renaming Operator • Previous solution is used whenever possible but it won’t work when R is the same as S. • Renaming operator resolves this. It allows to assign any desired names, say A1, A2,… An , to the attributes of the n column relation produced by expression expr with the syntax expr [A1, A2, … An]

18. Example Transcript (StudId, CrsCode, Semester, Grade) Teaching (ProfId, CrsCode, Semester) StudId, CrsCode(Transcript)[StudId, CrsCode1]  ProfId, CrsCode(Teaching) [ProfId, CrsCode2] This is a relation with 4 attributes: StudId, CrsCode1, ProfId, CrsCode2

19. Derived Operation: Join A (general or theta) join of R and S is the expression Rjoin-conditionS where join-condition is a conjunction of terms: Ai oper Bi in which Ai is an attribute of R;Bi is an attribute of S; and oper is one of =, <, >,  , . The meaning is:  join-condition´(R  S) where join-condition and join-condition´ are the same, except for possible renamings of attributes caused by the Cartesian product.

20. Theta Join – Example Employee(Name,Id,MngrId,Salary) Manager(Name,Id,Salary) Output the names of all employees that earn more than their managers. Employee.Name(EmployeeMngrId=Id AND Salary>SalaryManager) The join yields a table with attributes: Employee.Name, Employee.Id, Employee.Salary, Employee.MngrId Manager.Name, Manager.Id, Manager.Salary

21. Relational Algebra • Relational algebra operations operate on relations and produce relations (“closure”) f: Relation -> Relation f: Relation x Relation -> Relation • Six basic operations: • Projection A (R) • Selection  (R) • Union R1[ R2 • Difference R1– R2 • Product R1£ R2 • (Rename) A->B (R)

22. Equijoin Join - Example Equijoin: Join condition is a conjunction of equalities. Name,CrsCode(Student Id=StudId Grade=‘A’ (Transcript)) Student Transcript Id Name Addr Status 111 John ….. ….. 222 Mary ….. ….. 333 Bill ….. ….. 444 Joe ….. ….. StudId CrsCode Sem Grade 111 CSE305 S00 B 222 CSE306 S99 A 333 CSE304 F99 A The equijoin is used very frequently since it combines related data in different relations. Mary CSE306 Bill CSE304

23. Natural Join • Special case of equijoin + a special projection • join condition equates all and only those attributes with the same name (condition doesn’t have to be explicitly stated) • duplicate columns eliminated (projected out) from the result Transcript (StudId, CrsCode, Sem, Grade) Teaching (ProfId, CrsCode, Sem) Teaching = Transcript StudId, Transcript.CrsCode, Transcript.Sem, Grade, ProfId (Transcript CrsCode=CrsCodeANDSem=Sem Teaching ) [StudId, CrsCode, Sem, Grade, ProfId]

24. Natural Join (cont’d) • More generally: R S = attr-list(join-cond(R × S) ) where attr-list = attributes (R) attributes (S) (duplicates are eliminated) and join-cond has the form: A1 = A1AND … ANDAn = An where {A1 … An} = attributes(R) attributes(S)

25. Natural Join Example • List all Ids of students who took at least two different courses: StudId( CrsCode  CrsCode2 ( Transcript Transcript[StudId, CrsCode2, Sem2, Grade2] )) We don’t want to join on CrsCode, Sem, and Grade attributes, hence renaming!

26. Example Data Instance STUDENT COURSE Takes PROFESSOR Teaches

27. Natural Join and Intersection Natural join: special case of join where  is implicit – attributes with same name must be equal: STUDENT ⋈ Takes ´ STUDENT ⋈STUDENT.sid = Takes.sid Takes Intersection: as with set operations, derivable from difference A  B B-A A-B A B

28. Division • A somewhat messy operation that can be expressed in terms of the operations we have already defined • Used to express queries such as “The fid's of faculty who have taught all subjects” • Paraphrased: “The fid’s of professors for which there does not exist a subject that they haven’t taught”

29. Division Using Our Existing Operators • All possible teaching assignments: Allpairs: • NotTaught, all (fid,subj) pairs for which professor fidhas not taught subj: • Answer is all faculty not in NotTaught: fid,subj (PROFESSOR £subj(COURSE)) Allpairs - fid,subj(Teaches ⋈ COURSE) fid(PROFESSOR) - fid(NotTaught) ´ fid(PROFESSOR) - fid( fid,subj (PROFESSOR £subj(COURSE)) - fid,subj(Teaches ⋈ COURSE))

30. Division: R1¸ R2 • Requirement: schema(R1) ¾ schema(R2) • Result schema: schema(R1) – schema(R2) • “Professors who have taught all courses”: • What about “Courses that have been taught by all faculty”? fid (fid,subj(Teaches ⋈ COURSE) ¸subj(COURSE))

31. Division • Goal: Produce the tuples in one relation, r, that match all tuples in another relation, s • r (A1, …An, B1, …Bm) • s (B1 …Bm) • r/s, with attributes A1, …An, is the set of all tuples <a> such that for every tuple <b> ins,<a,b> is in r • Can be expressed in terms of projection, set difference, and cross-product

32. Division (cont’d)

33. Division - Example • List the Ids of students who have passed all courses that were taught in spring 2000 • Numerator: • StudId and CrsCode for every course passed by every student: StudId, CrsCode (Grade ‘F’ (Transcript) ) • Denominator: • CrsCode of all courses taught in spring 2000 CrsCode(Semester=‘S2000’ (Teaching) ) • Result is numerator/denominator

34. Relational Calculus • Important features: • Declarative formal query languages for relational model • Based on the branch mathematical logic known as predicate calculus • Two types of RC: • 1) tuple relational calculus • 2) domain relational calculus • A single statement can be used to perform a query

35. Tuple Relational Calculus • based on specifying a number of tuple variables • a tuple variable refers to any tuple

36. Generic Form • {t | COND (t)} • where • t is a tuple variable and • COND(t) is Boolean expression involving t

37. Simple example 1 • To find all employees whose salary is greater than $50,000 • {t| EMPLOYEE(t) and t.Salary>5000} • where • EMPLOYEE(t) specifies the range of tuple variable t • The above operation selects all the attributes

38. Simple example 2 • To find only the names of employees whose salary is greater than $50,000 • {t.FNAME, t.NAME| EMPLOYEE(t) and t.Salary>5000} • The above is equivalent to • SELECT T.FNAME, T.LNAME • FROM EMPLOYEE T • WHERE T.SALARY > 5000

39. Elements of a tuple calculus • In general, we need to specify the following in a tuple calculus expression: • Range Relation (I.e, R(t)) = FROM • Selected combination= WHERE • Requested attributes= SELECT

40. More Example:Q0 • Retrieve the birthrate and address of the employee(s) whose name is ‘John B. Smith’ • {t.BDATE, t.ADDRESS| EMPLOYEE(t) AND t.FNAME=‘John’ AND t.MINIT=‘B” AND t.LNAME=‘Smith}

41. Formal Specification of tuple Relational Calculus • A general format: • {t1.A1, t2.A2,…,tn.An |COND ( t1 ,t2 ,…, tn, tn+1, tn+2,…,tn+m)} • where • t1,…,tn+m are tuple var • Ai : attributeR(ti) • COND (formula) • Where COND corresponds to statement about the world, which can be True or False

42. Elements of formula • A formula is made of Predicate Calculus atoms: • an atom of the from R(ti) • ti.A op tj.B op{=, <,>,..} • F1 And F2 where F1 and F2 are formulas • F1 OR F2 • Not (F1) • F’=(t) (F) or F’= (t) (F) •  Y friends (Y, John) • X likes(X, ICE_CREAM)

43. Example Queries Using the Existential Quantifier • Retrieve the name and address of all employees who work for the ‘ Research ’ department • {t.FNAME, t.LNAME, t.ADDRESS| EMPLOYEE(t) AND ( d) (DEPARTMENT (d) AND d.DNAME=‘Research’ AND d.DNUMBER=t.DNO)}

44. More Example • For every project located in ‘Stafford’, retrieve the project number, the controlling department number, and the last name, birthrate, and address of the manger of that department.

45. Cont. • {p.PNUMBER,p.DNUM,m.LNAME,m.BDATE, m.ADDRESS|PROJECT(p) and EMPLOYEE(M) and P.PLOCATION=‘Stafford’ and ( d) (DEPARTMENT(D) AND P.DNUM=d.DNUMBER and d.MGRSSN=m.SSN))}

46. Safe Expressions • A safe expression R.C: • An expression that is guaranteed to generate a finite number of rows (tuples) • Example: • {t | not EMPLOYESS(t))} results values not being in its domain (I.e., EMPLOYEE)

47. Domain Relational Calculus (DRC) • Another type of formal predicate calculus-based language • QBE is based on DRC • The language shares a lot of similarities with the tuple calculus

48. DRC • The only difference is the type of variables: • variables range over singles values from domains of attributes • An expression of DRC is: • {x1, x2,…,xn|COND(x1,x2,…,xn, xn+2,…,xn+m)} • where x1,x2,…,xn+m are domain var range over attributers • COND is a condition (or formula)

49. Examples • Retrieve the birthdates and address of the employee whose name is ‘John B. Smith’ • {uv| (q)(r)(s) (EMPLOYEE(qrstuvwxyz) and q=‘John’ and r=‘B’ and s=‘Smith’

50. Alternative notation • Ssign the constants ‘John’, ‘B’, and ‘Smith’ directly • {uv|EMPLOYEE (‘John’, ’B’, ’Smith’ ,t ,u ,v ,x ,y ,z)}