1 / 27

From the Calculus to the Structured Query Language

From the Calculus to the Structured Query Language. Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems September 22, 2005. Some slide content courtesy of Susan Davidson & Raghu Ramakrishnan. Administrivia. Homework 1 due Tuesday

jaden
Download Presentation

From the Calculus to the Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. From the Calculus to the Structured Query Language Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems September 22, 2005 Some slide content courtesy of Susan Davidson & Raghu Ramakrishnan

  2. Administrivia • Homework 1 due Tuesday • Homework 2 will also be handed out • Will involve writing SQL • Oracle set up on eniac.seas.upenn.edu (also eniac-l.seas.upenn.edu) • Go to:www.seas.upenn.edu/~zives/cis550/oracle-faq.htmlClick on “create Oracle account” linkEnter your login info so you’ll get an Oracle account

  3. Tuple Relational Calculus (in More Detail) Queries of form: {T | p} Predicate: boolean expression over Tx attribs • Expressions: TxR TX.a op TY.b TX.a op constconst op TX.a T.a = Tx.a where op is , , , , ,  Tx,… are tuple variables, Tx.a, … are attributes • Complex expressions: e1e2, e1e2, e, and e1e2 • Universal and existential quantifiers predicate

  4. Domain Relational Calculusto Tuple Relational Calculus • {<subj> | 9 cid, sem, cid, sid (<cid, subj, sem> 2 COURSE Æ <sid, “C”, cid> 2 Takes} • {<cid> | 9 s1, s2 (<cid, s1, s2> 2 COURSE Æ9 cid2, s3, s4 (<cid2, s3, s4> 2 COURSE Æ (cid > cid2)))}

  5. Mini-Quiz on the Relational Calculus How do you write: • DRC: Which students have taken more than one course from the same professor? • TRC: Which faculty teach every course?

  6. Algebra vs. Calculus • We’ve claimed thatthe calculus (when safe)and the algebra areequivalent • Thus (core) SQL => calculus  algebramakes sense • Let’s look moreclosely at this… STUDENT COURSE Takes Calculus SELECT * FROM STUDENT, Takes, COURSE WHERE STUDENT.sid = Takes.sID AND Takes.cID = cid

  7. Translating from RA to DRC • Core of relational algebra: , , , x, - • We need to work our way through the structure of an RA expression, translating each possible form. • Let TR[e] be the translation of RA expression e into DRC. • Relation names: For the RA expression R, the DRC expression is {<x1,x2, …, xn>| <x1,x2, …, xn> R}

  8. Selection: TR[R] • Suppose we have (e’), where e’ is another RA expression that translates as: TR[e’]= {<x1,x2, …, xn>| p} • Then the translation ofc(e’) is {<x1,x2, …, xn>| p’}where’ is obtained fromby replacing each attribute with the corresponding variable • Example: TR[#1=#2 #4>2.5R] (if R has arity 4) is {<x1,x2, x3, x4>| < x1,x2, x3, x4>  R  x1=x2  x4>2.5}

  9. Projection: TR[i1,…,im(e)] • If TR[e]= {<x1,x2, …, xn>| p} thenTR[i1,i2,…,im(e)]= {<x i1,x i2, …, x im>|  xj1,xj2, …, xjk.p}, where xj1,xj2, …, xjk are variablesin x1,x2, …, xn that are not inx i1,x i2, …, x im • Example: With R as before,#1,#3 (R)={<x1,x3>| x2,x4. <x1,x2, x3,x4> R}

  10. Union: TR[R1  R2] • R1 and R2must have the same arity • For e1  e2, where e1, e2 are algebra expressions TR[e1]={<x1,…,xn>|p} and TR[e2]={<y1,…yn>|q} • Relabel the variables in the second: TR[e2]={< x1,…,xn>|q’} • This may involve relabeling bound variables in q to avoid clashes TR[e1e2]={<x1,…,xn>|pq’}. • Example: TR[R1 R2] = {< x1,x2, x3,x4>| <x1,x2, x3,x4>R1  <x1,x2, x3,x4>R2

  11. Other Binary Operators • Difference: The same conditions hold as for union If TR[e1]={<x1,…,xn>|p} and TR[e2]={< x1,…,xn>|q} Then TR[e1-e2]= {<x1,…,xn>|pq} • Product: If TR[e1]={<x1,…,xn>|p} and TR[e2]={< y1,…,ym>|q} Then TR[e1 e2]= {<x1,…,xn, y1,…,ym >| pq} • Example:TR[RS]= {<x1,…,xn, y1,…,ym >| <x1,…,xn> R  <y1,…,ym > S }

  12. Relational Algebra vs. Calculus • Can translate relational algebra into relational calculus • Given syntactic restrictions that guarantee safety of calculus query, can translate back to relational algebra • These are the principles behind initial development of relational databases • SQL is close to calculus; query plan is close to algebra • But SQL can do other things (recursion, aggregation that RA/RC can’t) • Great example of theory leading to practice!

  13. Basic SQL: A Friendly FaceOver the Tuple Relational Calculus SELECT [DISTINCT]{T1.attrib, …, T2.attrib}FROM {relation} T1, {relation} T2, …WHERE {predicates} Let’s do some examples, which will leverage your knowledge of the relational calculus… • Faculty ids • Course IDs for courses with students expecting a “C” • Courses taken by Jill select-list from-list qualification

  14. Our Example Data Instance STUDENT COURSE Takes PROFESSOR Teaches

  15. Some Nice Features • SELECT * • All STUDENTs • AS • As a “range variable” (tuple variable): optional • As an attribute rename operator • Example: • Which students (names) have taken more than one course from the same professor?

  16. Expressions in SQL • Can do computation over scalars (int, real or string) in the select-list or the qualification • Show all student IDs decremented by 1 • Strings: • Fixed (CHAR(x)) or variable length (VARCHAR(x)) • Use single quotes: ’A string’ • Special comparison operator: LIKE • Not equal: <> • Typecasting: • CAST(S.sid AS VARCHAR(255))

  17. Set Operations • Set operations default to set semantics, not bag semantics: (SELECT … FROM … WHERE …){op}(SELECT … FROM … WHERE …) • Where op is one of: • UNION • INTERSECT, MINUS/EXCEPT(many DBs don’t support these last ones!) • Bag semantics: ALL

  18. Exercise • Find all students who have taken DB but not AI • Hint: use EXCEPT

  19. Nested Queries in SQL • Simplest: IN/NOTIN • Example: Students who have taken subjects that have (at any point) been taught by Martin

  20. Correlated Subqueries • Most common: EXISTS/NOT EXISTS • Find all students who have taken DB but not AI

  21. Universal and Existential Quantification • Generally used with subqueries: • {op}ANY,{op}ALL • Find the students with the best expected grades

  22. Table Expressions • Can substitute a subquery for any relation in the FROM clause: SELECT S.sidFROM (SELECT sid FROM STUDENT WHERE sid = 5) SWHERE S.sid = 4 Notice that we can actually simplify this query! What is this equivalent to?

  23. Aggregation • GROUP BY SELECT{group-attribs}, {aggregate-operator}(attrib)FROM{relation} T1, {relation} T2, …WHERE {predicates}GROUP BY {group-list} • Aggregate operators • AVG, COUNT, SUM, MAX, MIN • DISTINCT keyword for AVG, COUNT, SUM

  24. Some Examples • Number of students in each course offering • Number of different grades expected for each course offering • Number of (distinct) students taking AI courses

  25. What If You Want to Only ShowSome Groups? • The HAVING clause lets you do a selection based on an aggregate (there must be 1 value per group): SELECT C.subj, COUNT(S.sid)FROM STUDENT S, Takes T, COURSE CWHERE S.sid = T.sid AND T.cid = C.cidGROUP BY subjHAVING COUNT(S.sid) > 5 • Exercise: For each subject taught by at least two professors, list the minimum expected grade

  26. Aggregation and Table Expressions • Sometimes need to compute results over the results of a previous aggregation:SELECT subj, AVG(size)FROM ( SELECT C.cid AS id, C.subj AS subj, COUNT(S.sid) AS size FROM STUDENT S, Takes T, COURSE C WHERE S.sid = T.sid AND T.cid = C.cid GROUP BY cid, subj)GROUP BY subj

  27. Something to Ponder • Tables are great, but… • Not everyone is uniform – I may have a cell phone but not a fax • We may simply be missing certain information • We may be unsure about values • How do we handle these things?

More Related