数据库系统概念全套配套课件PPT ch6.ppt 88页

'Chapter6:FormalRelationalQueryLanguages Chapter6:FormalRelationalQueryLanguagesRelationalAlgebraTupleRelationalCalculusDomainRelationalCalculus RelationalAlgebraProcedurallanguageSixbasicoperatorsselect:project:union:setdifference:–Cartesianproduct:xrename:Theoperatorstakeoneortworelationsasinputsandproduceanewrelationasaresult. SelectOperation–ExampleRelationrA=B^D>5(r) SelectOperationNotation:p(r)piscalledtheselectionpredicateDefinedas:p(r)={t|trandp(t)}Wherepisaformulainpropositionalcalculusconsistingoftermsconnectedby:(and),(or),(not)Eachtermisoneof:oporwhereopisoneof:=,,>,.<.Exampleofselection:dept_name=“Physics”(instructor) ProjectOperation–ExampleRelationr:A,C(r) ProjectOperationNotation:whereA1,A2areattributenamesandrisarelationname.TheresultisdefinedastherelationofkcolumnsobtainedbyerasingthecolumnsthatarenotlistedDuplicaterowsremovedfromresult,sincerelationsaresetsExample:Toeliminatethedept_nameattributeofinstructorID,name,salary(instructor) UnionOperation–ExampleRelationsr,s:rs: UnionOperationNotation:rsDefinedas:rs={t|trorts}Forrstobevalid.1.r,smusthavethesamearity(samenumberofattributes)2.Theattributedomainsmustbecompatible(example:2ndcolumnofrdealswiththesametypeofvaluesasdoesthe2ndcolumnofs)Example:tofindallcoursestaughtintheFall2009semester,orintheSpring2010semester,orinbothcourse_id(semester=“Fall”Λyear=2009(section))course_id(semester=“Spring”Λyear=2010(section)) SetdifferenceoftworelationsRelationsr,s:r–s: SetDifferenceOperationNotationr–sDefinedas:r–s={t|trandts}Setdifferencesmustbetakenbetweencompatiblerelations.randsmusthavethesamearityattributedomainsofrandsmustbecompatibleExample:tofindallcoursestaughtintheFall2009semester,butnotintheSpring2010semestercourse_id(semester=“Fall”Λyear=2009(section))−course_id(semester=“Spring”Λyear=2010(section)) Cartesian-ProductOperation–ExampleRelationsr,s:rxs: Cartesian-ProductOperationNotationrxsDefinedas:rxs={tq|trandqs}Assumethatattributesofr(R)ands(S)aredisjoint.(Thatis,RS=).Ifattributesofr(R)ands(S)arenotdisjoint,thenrenamingmustbeused. CompositionofOperationsCanbuildexpressionsusingmultipleoperationsExample:A=C(rxs)rxsA=C(rxs) RenameOperationAllowsustoname,andthereforetoreferto,theresultsofrelational-algebraexpressions.Allowsustorefertoarelationbymorethanonename.Example:x(E)returnstheexpressionEunderthenameXIfarelational-algebraexpressionEhasarityn,thenreturnstheresultofexpressionEunderthenameX,andwiththeattributesrenamedtoA1,A2,….,An. ExampleQueryFindthelargestsalaryintheuniversityStep1:findinstructorsalariesthatarelessthansomeotherinstructorsalary(i.e.notmaximum)usingacopyofinstructorunderanewnamedinstructor.salary(instructor.salary=5Three-valuedlogicusingthetruthvalueunknown:OR:(unknownortrue)=true,(unknownorfalse)=unknown(unknownorunknown)=unknownAND:(trueandunknown)=unknown,(falseandunknown)=false,(unknownandunknown)=unknownNOT:(notunknown)=unknownInSQL“Pisunknown”evaluatestotrueifpredicatePevaluatestounknownResultofselectpredicateistreatedasfalseifitevaluatestounknown DivisionOperatorGivenrelationsr(R)ands(S),suchthatSR,rsisthelargestrelationt(R-S)suchthattxsrE.g.letr(ID,course_id)=ID,course_id(takes)ands(course_id)=course_id(dept_name=“Biology”(course)thenrsgivesusstudentswhohavetakenallcoursesintheBiologydepartmentCanwritersastemp1R-S(r)temp2R-S((temp1xs)–R-S,S(r))result=temp1–temp2Theresulttotherightoftheisassignedtotherelationvariableontheleftofthe.Mayusevariableinsubsequentexpressions. ExtendedRelational-Algebra-OperationsGeneralizedProjectionAggregateFunctions GeneralizedProjectionExtendstheprojectionoperationbyallowingarithmeticfunctionstobeusedintheprojectionlist.Eisanyrelational-algebraexpressionEachofF1,F2,…,FnarearearithmeticexpressionsinvolvingconstantsandattributesintheschemaofE.Givenrelationinstructor(ID,name,dept_name,salary)wheresalaryisannualsalary,getthesameinformationbutwithmonthlysalaryID,name,dept_name,salary/12(instructor) AggregateFunctionsandOperationsAggregationfunctiontakesacollectionofvaluesandreturnsasinglevalueasaresult.avg:averagevaluemin:minimumvaluemax:maximumvaluesum:sumofvaluescount:numberofvaluesAggregateoperationinrelationalalgebraEisanyrelational-algebraexpressionG1,G2…,Gnisalistofattributesonwhichtogroup(canbeempty)EachFiisanaggregatefunctionEachAiisanattributenameNote:Somebooks/articlesuseinsteadof(CalligraphicG) AggregateOperation–ExampleRelationr:ABC77310sum(c)(r)sum(c)27 AggregateOperation–ExampleFindtheaveragesalaryineachdepartmentdept_nameavg(salary)(instructor)avg_salary AggregateFunctions(Cont.)ResultofaggregationdoesnothaveanameCanuserenameoperationtogiveitanameForconvenience,wepermitrenamingaspartofaggregateoperationdept_nameavg(salary)asavg_sal(instructor) ModificationoftheDatabaseThecontentofthedatabasemaybemodifiedusingthefollowingoperations:DeletionInsertionUpdatingAlltheseoperationscanbeexpressedusingtheassignmentoperator MultisetRelationalAlgebraPurerelationalalgebraremovesallduplicatese.g.afterprojectionMultisetrelationalalgebraretainsduplicates,tomatchSQLsemanticsSQLduplicateretentionwasinitiallyforefficiency,butisnowafeatureMultisetrelationalalgebradefinedasfollowsselection:hasasmanyduplicatesofatupleasintheinput,ifthetuplesatisfiestheselectionprojection:onetupleperinputtuple,evenifitisaduplicatecrossproduct:Iftherearemcopiesoft1inr,andncopiesoft2ins,therearemxncopiesoft1.t2inrxsOtheroperatorssimilarlydefinedE.g.union:m+ncopies,intersection:min(m,n)copiesdifference:min(0,m–n)copies SQLandRelationalAlgebraselectA1,A2,..Anfromr1,r2,…,rmwherePisequivalenttothefollowingexpressioninmultisetrelationalalgebraA1,..,An(P(r1xr2x..xrm))selectA1,A2,sum(A3)fromr1,r2,…,rmwherePgroupbyA1,A2isequivalenttothefollowingexpressioninmultisetrelationalalgebraA1,A2sum(A3)(P(r1xr2x..xrm))) SQLandRelationalAlgebraMoregenerally,thenon-aggregatedattributesintheselectclausemaybeasubsetofthegroupbyattributes,inwhichcasetheequivalenceisasfollows:selectA1,sum(A3)fromr1,r2,…,rmwherePgroupbyA1,A2isequivalenttothefollowingexpressioninmultisetrelationalalgebraA1,sumA3(A1,A2sum(A3)assumA3(P(r1xr2x..xrm))) TupleRelationalCalculus TupleRelationalCalculusAnonproceduralquerylanguage,whereeachqueryisoftheform{t|P(t)}ItisthesetofalltuplestsuchthatpredicatePistrueforttisatuplevariable,t[A]denotesthevalueoftupletonattributeAtrdenotesthattupletisinrelationrPisaformulasimilartothatofthepredicatecalculus PredicateCalculusFormula1.Setofattributesandconstants2.Setofcomparisonoperators:(e.g.,,,,,,)3.Setofconnectives:and(),or(v)‚not()4.Implication():xy,ifxiftrue,thenyistruexyxvy5.Setofquantifiers:tr(Q(t))”thereexists”atupleintinrelationrsuchthatpredicateQ(t)istruetr(Q(t))Qistrue“forall”tuplestinrelationr ExampleQueriesFindtheID,name,dept_name,salaryforinstructorswhosesalaryisgreaterthan$80,000Asinthepreviousquery,butoutputonlytheIDattributevalue{t|sinstructor(t[ID]=s[ID]s[salary]80000)}Noticethatarelationonschema(ID)isimplicitlydefinedbythequery{t|tinstructort[salary]80000} ExampleQueriesFindthenamesofallinstructorswhosedepartmentisintheWatsonbuilding{t|ssection(t[course_id]=s[course_id]s[semester]=“Fall”s[year]=2009vusection(t[course_id]=u[course_id]u[semester]=“Spring”u[year]=2010)}FindthesetofallcoursestaughtintheFall2009semester,orintheSpring2010semester,orboth{t|sinstructor(t[name]=s[name]udepartment(u[dept_name]=s[dept_name]“u[building]=“Watson”))} ExampleQueries{t|ssection(t[course_id]=s[course_id]s[semester]=“Fall”s[year]=2009usection(t[course_id]=u[course_id]u[semester]=“Spring”u[year]=2010)}FindthesetofallcoursestaughtintheFall2009semester,andintheSpring2010semester{t|ssection(t[course_id]=s[course_id]s[semester]=“Fall”s[year]=2009usection(t[course_id]=u[course_id]u[semester]=“Spring”u[year]=2010)}FindthesetofallcoursestaughtintheFall2009semester,butnotintheSpring2010semester SafetyofExpressionsItispossibletowritetuplecalculusexpressionsthatgenerateinfiniterelations.Forexample,{t|tr}resultsinaninfiniterelationifthedomainofanyattributeofrelationrisinfiniteToguardagainsttheproblem,werestrictthesetofallowableexpressionstosafeexpressions.Anexpression{t|P(t)}inthetuplerelationalcalculusissafeifeverycomponentoftappearsinoneoftherelations,tuples,orconstantsthatappearinPNOTE:thisismorethanjustasyntaxcondition.E.g.{t|t[A]=5true}isnotsafe---itdefinesaninfinitesetwithattributevaluesthatdonotappearinanyrelationortuplesorconstantsinP. UniversalQuantificationFindallstudentswhohavetakenallcoursesofferedintheBiologydepartment{t|rstudent(t[ID]=r[ID])(ucourse(u[dept_name]=“Biology”stakes(t[ID]=s[ID]s[course_id]=u[course_id]))}Notethatwithouttheexistentialquantificationonstudent,theabovequerywouldbeunsafeiftheBiologydepartmenthasnotofferedanycourses. DomainRelationalCalculus DomainRelationalCalculusAnonproceduralquerylanguageequivalentinpowertothetuplerelationalcalculusEachqueryisanexpressionoftheform:{x1,x2,…,xn|P(x1,x2,…,xn)}x1,x2,…,xnrepresentdomainvariablesPrepresentsaformulasimilartothatofthepredicatecalculus ExampleQueriesFindtheID,name,dept_name,salaryforinstructorswhosesalaryisgreaterthan$80,000{|instructors80000}Asinthepreviousquery,butoutputonlytheIDattributevalue{|instructors80000}FindthenamesofallinstructorswhosedepartmentisintheWatsonbuilding{|i,d,s(instructorb,a(departmentb=“Watson”))} ExampleQueries{|a,s,y,b,r,t(sections=“Fall”y=2009)va,s,y,b,r,t(section]s=“Spring”y=2010)}FindthesetofallcoursestaughtintheFall2009semester,orintheSpring2010semester,orbothThiscasecanalsobewrittenas{|a,s,y,b,r,t(section((s=“Fall”y=2009)v(s=“Spring”y=2010))}FindthesetofallcoursestaughtintheFall2009semester,andintheSpring2010semester{|a,s,y,b,r,t(sections=“Fall”y=2009)a,s,y,b,r,t(section]s=“Spring”y=2010)} SafetyofExpressionsTheexpression:{x1,x2,…,xn|P(x1,x2,…,xn)}issafeifallofthefollowinghold:Allvaluesthatappearintuplesoftheexpressionarevaluesfromdom(P)(thatis,thevaluesappeareitherinPorinatupleofarelationmentionedinP).Forevery“thereexists”subformulaoftheformx(P1(x)),thesubformulaistrueifandonlyifthereisavalueofxindom(P1)suchthatP1(x)istrue.Forevery“forall”subformulaoftheformx(P1(x)),thesubformulaistrueifandonlyifP1(x)istrueforallvaluesxfromdom(P1). UniversalQuantificationFindallstudentswhohavetakenallcoursesofferedintheBiologydepartment{|n,d,tc(student(ci,ti,dn,cr(coursedn=“Biology”si,se,y,g(takes))}Notethatwithouttheexistentialquantificationonstudent,theabovequerywouldbeunsafeiftheBiologydepartmenthasnotofferedanycourses.*Abovequeryfixesbuginpage246,lastquery EndofChapter6 Figure6.01 Figure6.02 Figure6.03 Figure6.04 Figure6.05 Figure6.06 Figure6.07 Figure6.08 Figure6.09 Figure6.10 Figure6.11 Figure6.12 Figure6.13 Figure6.14 Figure6.15 Figure6.16 Figure6.17 Figure6.18 Figure6.19 Figure6.20 Figure6.21 DeletionAdeleterequestisexpressedsimilarlytoaquery,exceptinsteadofdisplayingtuplestotheuser,theselectedtuplesareremovedfromthedatabase.Candeleteonlywholetuples;cannotdeletevaluesononlyparticularattributesAdeletionisexpressedinrelationalalgebraby:rr–EwhererisarelationandEisarelationalalgebraquery. DeletionExamplesDeleteallaccountrecordsinthePerryridgebranch.DeleteallaccountsatbrancheslocatedinNeedham.r1branch_city=“Needham”(accountbranch)r2account_number,branch_name,balance(r1)r3customer_name,account_number(r2depositor)accountaccount–r2depositordepositor–r3Deleteallloanrecordswithamountintherangeof0to50loanloan–amount0andamount50(loan)accountaccount–branch_name=“Perryridge”(account) InsertionToinsertdataintoarelation,weeither:specifyatupletobeinsertedwriteaquerywhoseresultisasetoftuplestobeinsertedinrelationalalgebra,aninsertionisexpressedby:rrEwhererisarelationandEisarelationalalgebraexpression.TheinsertionofasingletupleisexpressedbylettingEbeaconstantrelationcontainingonetuple. InsertionExamplesInsertinformationinthedatabasespecifyingthatSmithhas$1200inaccountA-973atthePerryridgebranch.ProvideasagiftforallloancustomersinthePerryridgebranch,a$200savingsaccount.Lettheloannumberserveastheaccountnumberforthenewsavingsaccount.accountaccount{(“A-973”,“Perryridge”,1200)}depositordepositor{(“Smith”,“A-973”)}r1(branch_name=“Perryridge”(borrowerloan))accountaccountloan_number,branch_name,200(r1)depositordepositorcustomer_name,loan_number(r1) UpdatingAmechanismtochangeavalueinatuplewithoutchargingallvaluesinthetupleUsethegeneralizedprojectionoperatortodothistaskEachFiiseithertheIthattributeofr,iftheIthattributeisnotupdated,or,iftheattributeistobeupdatedFiisanexpression,involvingonlyconstantsandtheattributesofr,whichgivesthenewvaluefortheattribute UpdateExamplesMakeinterestpaymentsbyincreasingallbalancesby5percent.Payallaccountswithbalancesover$10,0006percentinterestandpayallothers5percentaccountaccount_number,branch_name,balance*1.06(BAL10000(account))account_number,branch_name,balance*1.05(BAL10000(account))accountaccount_number,branch_name,balance*1.05(account) ExampleQueriesFindthenamesofallcustomerswhohavealoanandanaccountatbank.customer_name(borrower)customer_name(depositor)Findthenameofallcustomerswhohavealoanatthebankandtheloanamountcustomer_name,loan_number,amount(borrowerloan) Query1customer_name(branch_name=“Downtown”(depositoraccount))customer_name(branch_name=“Uptown”(depositoraccount))Query2customer_name,branch_name(depositoraccount)temp(branch_name)({(“Downtown”),(“Uptown”)})NotethatQuery2usesaconstantrelation.ExampleQueriesFindallcustomerswhohaveanaccountfromatleastthe“Downtown”andtheUptown”branches. FindallcustomerswhohaveanaccountatallbrancheslocatedinBrooklyncity.BankExampleQueriescustomer_name,branch_name(depositoraccount)branch_name(branch_city=“Brooklyn”(branch))'