10/1/08 1 Instructor:*Amol*Deshpande********************[email protected]* Data*Models*◦ Conceptual*representa8on*of*the*data* Data*Retrieval*◦ How*to*ask*ques8ons*of*the*database*◦ How*to*answer*those*ques8ons* Data*Storage*◦ How/where*to*store*data,*how*to*access*it* Data*Integrity*◦ Manage*crashes,*concurrency*◦ Manage*seman8c*inconsistencies*10/1/08 2 SQL* Rela8onal*Algebra* Formal*Seman8cs*of*SQL* More*SQL* Movie(!tle,&year,*length,*inColor,*studioName,*producerC#)* StarsIn(movieTitle,*movieYear,*starName)* MovieStar(name,*address,*gender,*birthdate)* MovieExec(name,*address,*cert#,*netWorth)* Studio(name,*address,*presC#)*10/1/08 3 Data*Defini8on*Language*◦ Create*table,*insert*into*<>*values()*etc…*10/1/08 4 Types*of*queries/constructs*◦ Many*different*types*of*predicates*◦ Aggregates*◦ Set*opera8ons*◦ Nested*subqueries*◦ Finding*max*tuple* Key*observa8on:*A*tuple*has*the*maximum*value*if*its*value*equals*the*maximum*value*◦ Ranking* Key*observa8on:*A*tuple*is*ranked*5th*or*lower*by*“score”*iff*the*number*of*tuples*with*“score”*larger*than*it*is*less*than*5*◦ Set*comparisons* Key:**◦ Do*a*join&to*get*an*appropriate*table*◦ Use*the*constructs*for*single‐table*queries* You*will*get*used*to*doing*all*at*once* Different*types*of*joins*◦ Inner*joins/natural*joins* Only*tuples*that*have*matches**◦ Outer*joins* Le`/right*outer*join* Full*outer*join*◦ Semi‐join,*an8‐join,*theta‐join*(not*SQL*opera8ons)* Important*‐‐‐*we*will*discuss*them*later*10/1/08 5 Using*FROM*clause*with*WHERE*clause*select*8tle,*year,*me.name*as*producerName**from*movies*m,*movieexec*me*where*m.producerC#*=*me.cert#;* “inner*join”*select*8tle,*year,*me.name*as*producerName**from*movies*m*inner*join*movieexec*me***********************on*m.producerC#*=*me.cert#;* “natural*join”*◦ Finds*the*common*acributes*and*does*equality*join*◦ Useless*on*the*movies*database*(common*acributes*named*differently)*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&select&*&from&olympics&natural&join&countries& Consider*the*query:******select*8tle,*year,*producerC#,*count(starName)*******from*movies,*starsIn******where*8tle*=*starsIn.movieTitle*and*year*=*starsIn.movieYear******group*by*8tle,*year,*producerC#* You*expect:*◦ A*list*of*movies*with*the*number*of*stars*in*them* But:*◦ What*about*movies*with*no*stars*?**◦ We*would*like*to*get*“(movie,*year,*producerC#,*0)”*for*them*10/1/08 6 Le`*outer*join******select*8tle,*year,*producerC#,*count(starName)*******from*movies*le`*outer*join*starsIn****************on*8tle*=*starsIn.movieTitle*and*year*=*starsIn.movieYear*******group*by*8tle,*year,*producerC#*◦ All*tuples*from*“movies”*that*have*no*matches*in*starsIn*are*included*with*NULLs* So*if*a*tuple*(m1,*1990)*has*no*match*in*starsIn,*we*get*(m1,*1990,*NULL)*in*the*result*◦ The*count(starName)*works*correctly*then.* Note:*count(*)*would*not*work*correctly.* Extensions:*Right*outer*join,*and*Full*outer*join** Set*comparisons*in*SQL;*why*?*◦ >*some:*The*subquery*may*not*return*something*that*is*ordinal*(e.g.*it*may*return*strings)*◦ >*all:*seems*it*would*be*useful*to*simplify*some*queries*that*involve*finding*maximums*among*groups* Won’t*have*to*create*temporary*tables* E.g….*10/1/08 7 Find*the*producer*with*max*average*length*movie* We*used:***create*table*temp*as*******select*name,*avg(length)*as*aveL*******from*movieExec*me,*movies*m*******where*me.cert#*=*m.producer#********group*by*name;***select*name*******from*temp******where*aveL*=*(select*max(aveL)*from*temp);* Alterna8ve********select*name*************from*movieExec*me,*movie*m************where*me.cert#*=*m.producer#**************group*by*name*************having*avg(length)*>=*all*(select*avg(length)**************************************************from*movie*m**************************************************group*by*producer#)* Note: No need to do the join here; Grouping by producer# is identical to grouping by name Find*movie*8tles*that*appear*more*than*once*◦ Op8on*1:*Use*group*by/having*clause***select*8tle*from*movies*group*by*8tle*having*count(year)*>*1;**◦ Op8on*2:*Use*set*opera8ons*****select*8tle*from*movies*old*where*year*<*******************any*(select*year*from*movie*where*8tle*=*old.8tle)*10/1/08 8 Find*actors/actresses*3*degrees*away*from*H.*Ford*An*actor*is*one*degree*away*from*H.*Ford*if*they*starred*in*a*movie*together*First*create*a*costars*table***create*table*costars*as****select*dis8nct*s1.starName*as*star1,*s2.starname*as*star2***from*starsIn*s1,*starsIn*s2***where*s1.movieTitle*=*s2.movieTitle*and*s1.movieYear*=*s2.movieYear;*Join*mul8ple*of*these*together*appropriately***********select*c3.star2***from*costars*c1,*costars*c2,*costars*c3**where*c1.star1*=*“H.*Ford”*and*c1.star2*=*c2.star1*and*c2.star2*=*c3.star1*Different*answers*depending*on*whether*(H.*Ford,*H.Ford)*exists*in*this*table*(it*does*in*the*above*table)*10/1/08 9 SQL* Rela8onal*Algebra* Formal*Seman8cs*of*SQL* More*SQL* Procedural*language* Six*basic*operators*◦ select*◦ project*◦ union*◦ set*difference*◦ Cartesian*product*◦ rename* The*operators*take*one*or*more*rela8ons*as*inputs*and*give*a*new*rela8on*as*a*result.*10/1/08 10 Relation r A B C D α α β β α β β β 1 5 12 23 7 7 3 10 σA=B ∧ D > 5 (r) A B C D α β α β 1 23 7 10 SQL Equivalent: select * from r where A = B and D > 5 Unfortunate naming confusion Relation r A B C D α α β β α β β β 1 5 12 23 7 7 3 10 ∏A,D (r) SQL Equivalent: select distinct A, D from r A D α α β β 7 7 3 10 A D α β β 7 3 1010/1/08 11 Relation r, s SQL Equivalent: select * from r union/except/intersect select * from s; This is one case where duplicates are removed. A B α α β 1 2 1 A B α β 2 3 r s r ∪ s: A B α α β β 1 2 1 3 A B α β 1 1 r – s: Must be compatible schemas What about intersection ? Can be derived r ∩ s = r – ( r – s); Relation r, s SQL Equivalent: select distinct *
View Full Document
Unlocking...