From the Calculus to the Structured Query Language

0
0
1605 days ago, 498 views
PowerPoint PPT Presentation

Presentation Transcript

Slide 1

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

Slide 2

Administrivia Recall: no class next Monday 9/24 – exceptional TA available time rather SQL examination proceeds with 9/26 Preparation for Homework 2 (passed out one week from now) To test your SQL inquiries, we have Oracle set up on eniac.seas.upenn.edu Go to: www.seas.upenn.edu/~zives/cis550/prophet faq.html Click on " make Oracle account " connect Enter your login data so you'll get an Oracle account

Slide 3

Recall Last Time Which understudies have taken more than one course from a similar teacher? {<name> | sid,cid,fid,cid2 . ( <sid,name> ϵ STUDENTS ^ <sid,_,cid> ϵ Takes ^ <fid,cid> ϵ Teaches ^ <sid,_,cid2> ϵ Takes ^ <fid,cid2> ϵ Teaches ^ cid  cid2 )} OR {<name> | sid,cid,fid . ( <sid,name> ϵ STUDENTS ^ <sid,_,cid> ϵ Takes ^ <fid,cid> ϵ Teaches ^ cid2 ( <sid,_,cid2> ϵ Takes ^ <fid,cid2> ϵ Teaches ^ cid  cid2 ))}

Slide 4

Algebra versus Math We've guaranteed that the analytics (when safe) and the variable based math are proportionate Thus (center) SQL => analytics  polynomial math bodes well Let's look all the more carefully at this… STUDENT COURSE Takes Calculus SELECT * FROM STUDENT, Takes, COURSE WHERE STUDENT.sid = Takes.sID AND Takes.cID = cid

Slide 5

Translating from RA to DRC Core of social variable based math:  ,  ,  , x, - We have to work our way through the structure of a RA expression, deciphering every conceivable shape. Give TR[e] a chance to be the interpretation of RA expression e into DRC. Connection names : For the RA expression R , the DRC expression is {<x 1 ,x 2 , … , x n >| <x 1 ,x 2 , … , x n >  R }

Slide 6

Selection: TR[   R] Suppose we have   (e'), where e' is another RA expression that deciphers as: TR[e']= {<x 1 ,x 2 , … , x n >| p } Then the interpretation of  c (e') is {<x 1 ,x 2 , … , x n >| p   " } where  " is acquired from  by supplanting every characteristic with the comparing variable Example : TR[  #1=#2  #4>2.5 R] (if R has arity 4) is {<x 1 ,x 2 , x 3 , x 4 >| < x 1 ,x 2 , x 3 , x 4 >  R  x 1 =x 2  x 4 >2.5}

Slide 7

Projection: TR[  i 1 ,… ,i m (e)] If TR[e]= {<x 1 ,x 2 , … , x n >| p} then TR[  i 1 ,i 2 ,… ,i m (e)]= {<x i 1 ,x i 2 , … , x i m >|  x j 1 ,x j 2 , … , x j k . p}, where x j 1 ,x j 2 , … , x j k are factors in x 1 ,x 2 , … , x n that are not in x i 1 ,x i 2 , … , x i m Example : With R as some time recently,  #1,#3 (R)={<x 1 ,x 3 >|  x 2 ,x 4 . <x 1 ,x 2, x 3 ,x 4 >  R}

Slide 8

Union: TR[R 1  R 2 ] R 1 and R 2 must have a similar arity For e 1  e 2 , where e 1 , e 2 are polynomial math expressions TR[e 1 ]={<x 1 ,… ,x n >|p} and TR[e 2 ]={<y 1 ,… y n >|q} Relabel the factors in the second: TR[e 2 ]={< x 1 ,… ,x n >|q'} This may include relabeling bound factors in q to maintain a strategic distance from conflicts TR[e 1  e 2 ]={<x 1 ,… ,x n >|p  q'}. Illustration : TR[ R 1  R 2 ] = {< x 1 ,x 2, x 3 ,x 4 >| <x 1 ,x 2, x 3 ,x 4 >  R 1  <x 1 ,x 2, x 3 ,x 4 >  R 2

Slide 9

Other Binary Operators Difference : similar conditions hold with respect to union If TR[e 1 ]={<x 1 ,… ,x n >|p} and TR[e 2 ]={< x 1 ,… ,x n >|q} Then TR[e 1 - e 2 ]= {<x 1 ,… ,x n >|p  q} Product : If TR[e 1 ]={<x 1 ,… ,x n >|p} and TR[e 2 ]={< y 1 ,… ,y m >|q} Then TR[e 1  e 2 ]= {<x 1 ,… ,x n , y 1 ,… ,y m >| p  q} Example : TR[R  S]= {<x 1 ,… ,x n , y 1 ,… ,y m >| <x 1 ,… ,x n >  R  <y 1 ,… ,y m >  S }

Slide 10

What about the Tuple Relational Calculus? We've been taking a gander at the Domain Relational Calculus The Tuple Relational Calculus is about the same, however factors are at the level of a tuple, not a quality {Q | 9 S  COURSES, 9 T 2 Takes (S.cid = T.cid Æ Q.cid = S.cid Æ Q.exp-review = T.exp-grade)}

Slide 11

Tuple Relational Calculus (in More Detail) Queries of frame: {T | p} Predicate: boolean expression over T x attribs Expressions: T x  R T X .an operation T Y .b T X .an operation const operation T X .a T.a = T x .a where operation is  ,  ,  ,  ,  ,  T x ,… are tuple factors, T x .a, … are characteristics Complex expressions: e 1  e 2 , e 1  e 2 ,  e , and e 1 e 2 Universal and existential quantifiers predicate

Slide 12

Domain Relational Calculus to 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)))}

Slide 13

Mini-Quiz on the Relational Calculus How would you compose: TRC: Which staff instruct each course?

Slide 14

Limitations of the Relational Algebra/Calculus Can't do: Aggregate operations (total, number) Recursive inquiries (self-assertive # of joins) Complex (non-forbidden) structures Most of these are expressible in SQL, OQL, XQuery – utilizing other unique administrators Sometimes we even need the force of a Turing-finish programming dialect

Slide 15

Summary Can make an interpretation of social variable based math into social analytics DRC and TRC are somewhat extraordinary punctuations however proportional Given syntactic confinements that assurance security of DRC question, can make an interpretation of back to social polynomial math These are the standards behind beginning improvement of social databases SQL is near math; inquiry plan is near polynomial math Great case of hypothesis prompting to hone!

Slide 16

Basic SQL: A Friendly Face Over the Tuple Relational Calculus SELECT [DISTINCT] {T 1 . attrib , … , T 2 . attrib } FROM { connection } T 1 , { connection } T 2 , … WHERE { predicates } Let's do a few illustrations, which will influence your insight into the social math… Faculty ids Course IDs for courses with understudies expecting a "C" Courses taken by Jill select-list from-rundown capability

Slide 17

Our Example Data Instance STUDENT COURSE Takes PROFESSOR Teaches

Slide 18

Some Nice Features SELECT * All STUDENTs As a "range variable" (tuple variable): discretionary As a trait rename administrator Example: Which understudies (names) have taken more than one course from a similar teacher?

Slide 19

Expressions in SQL Can do calculation over scalars (int, genuine or string) in the select-list or the capability Show all understudy IDs decremented by 1 Strings: Fixed ( CHAR(x) ) or variable length ( VARCHAR(x) ) Use single quotes: 'A string' Special examination administrator: LIKE Not equivalent: <> Typecasting: CAST(S.sid AS VARCHAR(255))

Slide 20

Set Operations Set operations default to set semantics, not pack semantics: (SELECT … FROM … WHERE … ) {op} (SELECT … FROM … WHERE … ) Where operation is one of: UNION INTERSECT , MINUS/EXCEPT (numerous DBs don't bolster these last ones!) Bag semantics: ALL

Slide 21

Exercise Find all understudies who have taken DB yet not AI Hint: use EXCEPT

Slide 22

Nested Queries in SQL Simplest: IN/NOT IN Example: Students who have taken subjects that have (anytime) been educated by Martin

Slide 23

Correlated Subqueries Most basic: EXISTS/NOT EXISTS Find all understudies who have taken DB yet not AI

Slide 24

Universal and Existential Quantification Generally utilized with subqueries: {op} ANY, {op} ALL Find the understudies with the best expected evaluations

Slide 25

Table Expressions Can substitute a subquery for any connection in the FROM condition: SELECT S.sid FROM (SELECT sid FROM STUDENT WHERE sid = 5) S WHERE S.sid = 4 Notice that we can really rearrange this question! What is this comparable to?

Slide 26

Aggregation GROUP BY SELECT { amass attribs } , { total administrator }( attrib ) FROM { connection } T 1 , { connection } T 2 , … WHERE { predicates } GROUP BY { gather list } Aggregate administrators AVG , COUNT , SUM , MAX , MIN DISTINCT watchword for AVG , COUNT , SUM

Slide 27

Some Examples Number of understudies in every course offering Number of various evaluations expected for every course offering Number of (unmistakable) understudies taking AI courses

Slide 28

What If You Want to Only Show Some Groups? The HAVING proviso gives you a chance to do a determination in light of a total (there must be 1 esteem for every gathering): SELECT C.subj, COUNT(S.sid) FROM STUDENT S, Takes T, COURSE C WHERE S.sid = T.sid AND T.cid = C.cid GROUP BY subj HAVING COUNT(S.sid) > 5 Exercise: For every subject educated by no less than two teachers, list the base expected review

Slide 29

Aggregation and Table Expressions Sometimes need to register comes about over the aftereffects of a past collection: 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

Slide 30

Something to Ponder Tables are incredible, yet… Not everybody is uniform – I may have a wireless however not a fax We may just be feeling the loss of certain data We might be uncertain about qualities How would we handle these things?

SPONSORS