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

'Chapter5:AdvancedSQL Chapter5:AdvancedSQLAccessingSQLFromaProgrammingLanguageDynamicSQLJDBCandODBCEmbeddedSQLSQLDataTypesandSchemasFunctionsandProceduralConstructsTriggersAdvancedAggregationFeaturesOLAP JDBCandODBCAPI(application-programinterface)foraprogramtointeractwithadatabaseserverApplicationmakescallstoConnectwiththedatabaseserverSendSQLcommandstothedatabaseserverFetchtuplesofresultone-by-oneintoprogramvariablesODBC(OpenDatabaseConnectivity)workswithC,C++,C#,andVisualBasicOtherAPI’ssuchasADO.NETsitontopofODBCJDBC(JavaDatabaseConnectivity)workswithJava JDBCJDBCisaJavaAPIforcommunicatingwithdatabasesystemssupportingSQL.JDBCsupportsavarietyoffeaturesforqueryingandupdatingdata,andforretrievingqueryresults.JDBCalsosupportsmetadataretrieval,suchasqueryingaboutrelationspresentinthedatabaseandthenamesandtypesofrelationattributes.Modelforcommunicatingwiththedatabase:OpenaconnectionCreatea“statement”objectExecutequeriesusingtheStatementobjecttosendqueriesandfetchresultsExceptionmechanismtohandleerrors JDBCCodepublicstaticvoidJDBCexample(Stringdbid,Stringuserid,Stringpasswd){try{Class.forName("oracle.jdbc.driver.OracleDriver");Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@db.yale.edu:2000:univdb",userid,passwd);Statementstmt=conn.createStatement();…DoActualWork….stmt.close();conn.close();}catch(SQLExceptionsqle){System.out.println("SQLException:"+sqle);}} JDBCCode(Cont.)Updatetodatabasetry{stmt.executeUpdate("insertintoinstructorvalues(’77987’,’Kim’,’Physics’,98000)");}catch(SQLExceptionsqle){System.out.println("Couldnotinserttuple."+sqle);}ExecutequeryandfetchandprintresultsResultSetrset=stmt.executeQuery("selectdept_name,avg(salary)frominstructorgroupbydept_name");while(rset.next()){System.out.println(rset.getString("dept_name")+""+rset.getFloat(2));} JDBCCodeDetailsGettingresultfields:rs.getString(“dept_name”)andrs.getString(1)equivalentifdept_nameisthefirstargumentofselectresult.DealingwithNullvaluesinta=rs.getInt(“a”);if(rs.wasNull())Systems.out.println(“Gotnullvalue”); PreparedStatementPreparedStatementpStmt=conn.prepareStatement("insertintoinstructorvalues(?,?,?,?)");pStmt.setString(1,"88877");pStmt.setString(2,"Perry");pStmt.setString(3,"Finance");pStmt.setInt(4,125000);pStmt.executeUpdate();pStmt.setString(1,"88878");pStmt.executeUpdate();Forqueries,usepStmt.executeQuery(),whichreturnsaResultSetWARNING:alwaysusepreparedstatementswhentakinganinputfromtheuserandaddingittoaqueryNEVERcreateaquerybyconcatenatingstringswhichyougetasinputs"insertintoinstructorvalues(’"+ID+"’,’"+name+"’,"+"’+deptname+"’,"’balance+")“Whatifnameis“D’Souza”? SQLInjectionSupposequeryisconstructedusing"select*frominstructorwherename=’"+name+"’"Supposetheuser,insteadofenteringaname,enters:X’or’Y’=’Ythentheresultingstatementbecomes:"select*frominstructorwherename=’"+"X’or’Y’=’Y"+"’"whichis:select*frominstructorwherename=’X’or’Y’=’Y’UsercouldhaveevenusedX’;updateinstructorsetsalary=salary+10000;--Preparedstatementinternallyuses:"select*frominstructorwherename=’X’or’Y’=’Y’Alwaysusepreparedstatements,withuserinputsasparameters MetadataFeaturesResultSetmetadataE.g.,afterexecutingquerytogetaResultSetrs:ResultSetMetaDatarsmd=rs.getMetaData();for(inti=1;i<=rsmd.getColumnCount();i++){System.out.println(rsmd.getColumnName(i));System.out.println(rsmd.getColumnTypeName(i));}Howisthisuseful? Metadata(Cont)DatabasemetadataDatabaseMetaDatadbmd=conn.getMetaData();ResultSetrs=dbmd.getColumns(null,"univdb","department","%");//ArgumentstogetColumns:Catalog,Schema-pattern,Table-pattern,//andColumn-Pattern//Returns:Onerowforeachcolumn;rowhasanumberofattributes//suchasCOLUMN_NAME,TYPE_NAMEwhile(rs.next()){System.out.println(rs.getString("COLUMN_NAME"),rs.getString("TYPE_NAME");}Andwhereisthisuseful? TransactionControlinJDBCBydefault,eachSQLstatementistreatedasaseparatetransactionthatiscommittedautomaticallybadideafortransactionswithmultipleupdatesCanturnoffautomaticcommitonaconnectionconn.setAutoCommit(false);Transactionsmustthenbecommittedorrolledbackexplicitlyconn.commit();orconn.rollback();conn.setAutoCommit(true)turnsonautomaticcommit. OtherJDBCFeaturesCallingfunctionsandproceduresCallableStatementcStmt1=conn.prepareCall("{?=callsomefunction(?)}");CallableStatementcStmt2=conn.prepareCall("{callsomeprocedure(?,?)}");HandlinglargeobjecttypesgetBlob()andgetClob()thataresimilartothegetString()method,butreturnobjectsoftypeBlobandClob,respectivelygetdatafromtheseobjectsbygetBytes()associateanopenstreamwithJavaBloborClobobjecttoupdatelargeobjectsblob.setBlob(intparameterIndex,InputStreaminputStream). SQLJJDBCisoverlydynamic,errorscannotbecaughtbycompilerSQLJ:embeddedSQLinJava#sqliteratordeptInfoIter(Stringdeptname,intavgSal);deptInfoIteriter=null;#sqliter={selectdept_name,avg(salary)frominstructorgroupbydeptname};while(iter.next()){StringdeptName=iter.dept_name();intavgSal=iter.avgSal();System.out.println(deptName+""+avgSal);}iter.close(); ODBCOpenDataBaseConnectivity(ODBC)standardstandardforapplicationprogramtocommunicatewithadatabaseserver.applicationprograminterface(API)toopenaconnectionwithadatabase,sendqueriesandupdates,getbackresults.ApplicationssuchasGUI,spreadsheets,etc.canuseODBCWasdefinedoriginallyforBasicandC,versionsavailableformanylanguages. ODBC(Cont.)EachdatabasesystemsupportingODBCprovidesa"driver"librarythatmustbelinkedwiththeclientprogram.WhenclientprogrammakesanODBCAPIcall,thecodeinthelibrarycommunicateswiththeservertocarryouttherequestedaction,andfetchresults.ODBCprogramfirstallocatesanSQLenvironment,thenadatabaseconnectionhandle.OpensdatabaseconnectionusingSQLConnect().ParametersforSQLConnect:connectionhandle,theservertowhichtoconnecttheuseridentifier,passwordMustalsospecifytypesofarguments:SQL_NTSdenotespreviousargumentisanull-terminatedstring. ODBCCodeintODBCexample(){RETCODEerror;HENVenv;/*environment*/HDBCconn;/*databaseconnection*/SQLAllocEnv(&env);SQLAllocConnect(env,&conn);SQLConnect(conn,“db.yale.edu",SQL_NTS,"avi",SQL_NTS,"avipasswd",SQL_NTS);{….Doactualwork…}SQLDisconnect(conn);SQLFreeConnect(conn);SQLFreeEnv(env);} ODBCCode(Cont.)ProgramsendsSQLcommandstodatabasebyusingSQLExecDirectResulttuplesarefetchedusingSQLFetch()SQLBindCol()bindsClanguagevariablestoattributesofthequeryresultWhenatupleisfetched,itsattributevaluesareautomaticallystoredincorrespondingCvariables.ArgumentstoSQLBindCol()ODBCstmtvariable,attributepositioninqueryresultThetypeconversionfromSQLtoC.Theaddressofthevariable.Forvariable-lengthtypeslikecharacterarrays,ThemaximumlengthofthevariableLocationtostoreactuallengthwhenatupleisfetched.Note:AnegativevaluereturnedforthelengthfieldindicatesnullvalueGoodprogrammingrequirescheckingresultsofeveryfunctioncallforerrors;wehaveomittedmostchecksforbrevity. ODBCCode(Cont.)Mainbodyofprogramchardeptname[80];floatsalary;intlenOut1,lenOut2;HSTMTstmt;char*sqlquery="selectdept_name,sum(salary)frominstructorgroupbydept_name";SQLAllocStmt(conn,&stmt);error=SQLExecDirect(stmt,sqlquery,SQL_NTS);if(error==SQLSUCCESS){SQLBindCol(stmt,1,SQL_C_CHAR,deptname,80,&lenOut1);SQLBindCol(stmt,2,SQL_C_FLOAT,&salary,0,&lenOut2);while(SQLFetch(stmt)==SQL_SUCCESS){printf("%s%gn",deptname,salary);}}SQLFreeStmt(stmt,SQL_DROP); ODBCPreparedStatementsPreparedStatementSQLstatementprepared:compiledatthedatabaseCanhaveplaceholders:E.g.insertintoaccountvalues(?,?,?)RepeatedlyexecutedwithactualvaluesfortheplaceholdersToprepareastatementSQLPrepare(stmt,);TobindparametersSQLBindParameter(stmt,,…typeinformationandvalueomittedforsimplicity..)Toexecutethestatementretcode=SQLExecute(stmt);ToavoidSQLinjectionsecurityrisk,donotcreateSQLstringsdirectlyusinguserinput;insteadusepreparedstatementstobinduserinputs MoreODBCFeaturesMetadatafeaturesfindingalltherelationsinthedatabaseandfindingthenamesandtypesofcolumnsofaqueryresultorarelationinthedatabase.Bydefault,eachSQLstatementistreatedasaseparatetransactionthatiscommittedautomatically.CanturnoffautomaticcommitonaconnectionSQLSetConnectOption(conn,SQL_AUTOCOMMIT,0)}TransactionsmustthenbecommittedorrolledbackexplicitlybySQLTransact(conn,SQL_COMMIT)orSQLTransact(conn,SQL_ROLLBACK) ODBCConformanceLevelsConformancelevelsspecifysubsetsofthefunctionalitydefinedbythestandard.CoreLevel1requiressupportformetadataqueryingLevel2requiresabilitytosendandretrievearraysofparametervaluesandmoredetailedcataloginformation.SQLCallLevelInterface(CLI)standardsimilartoODBCinterface,butwithsomeminordifferences. ADO.NETAPIdesignedforVisualBasic.NETandC#,providingdatabaseaccessfacilitiessimilartoJDBC/ODBCPartialexampleofADO.NETcodeinC#usingSystem,System.Data,System.Data.SqlClient;SqlConnectionconn=newSqlConnection(“DataSource=,InitialCatalog=”);conn.Open();SqlCommandcmd=newSqlCommand(“select*fromstudents”,conn);SqlDataReaderrdr=cmd.ExecuteReader();while(rdr.Read()){Console.WriteLine(rdr[0],rdr[1]);/*Printsresultattributes1&2*/}rdr.Close();conn.Close();Canalsoaccessnon-relationaldatasourcessuchasOLE-DB,XMLdata,Entityframework EmbeddedSQLTheSQLstandarddefinesembeddingsofSQLinavarietyofprogramminglanguagessuchasC,Java,andCobol.AlanguagetowhichSQLqueriesareembeddedisreferredtoasahostlanguage,andtheSQLstructurespermittedinthehostlanguagecompriseembeddedSQL.ThebasicformoftheselanguagesfollowsthatoftheSystemRembeddingofSQLintoPL/I.EXECSQLstatementisusedtoidentifyembeddedSQLrequesttothepreprocessorEXECSQLEND_EXECNote:thisvariesbylanguage(forexample,theJavaembeddinguses#SQL{….};) ExampleQuerySpecifythequeryinSQLanddeclareacursorforitEXECSQLdeclareccursorforselectID,namefromstudentwheretot_cred>:credit_amountEND_EXECFromwithinahostlanguage,findtheIDandnameofstudentswhohavecompletedmorethanthenumberofcreditsstoredinvariablecredit_amount. EmbeddedSQL(Cont.)TheopenstatementcausesthequerytobeevaluatedEXECSQLopencEND_EXECThefetchstatementcausesthevaluesofonetupleinthequeryresulttobeplacedonhostlanguagevariables.EXECSQLfetchcinto:si,:snEND_EXECRepeatedcallstofetchgetsuccessivetuplesinthequeryresultAvariablecalledSQLSTATEintheSQLcommunicationarea(SQLCA)getssetto‘02000’toindicatenomoredataisavailableTheclosestatementcausesthedatabasesystemtodeletethetemporaryrelationthatholdstheresultofthequery.EXECSQLclosecEND_EXECNote:abovedetailsvarywithlanguage.Forexample,theJavaembeddingdefinesJavaiteratorstostepthroughresulttuples. UpdatesThroughCursorsCanupdatetuplesfetchedbycursorbydeclaringthatthecursorisforupdatedeclareccursorforselect*frominstructorwheredept_name=‘Music’forupdateToupdatetupleatthecurrentlocationofcursorcupdateinstructorsetsalary=salary+100wherecurrentofc ProceduralConstructsinSQL ProceduralExtensionsandStoredProceduresSQLprovidesamodulelanguagePermitsdefinitionofproceduresinSQL,withif-then-elsestatements,forandwhileloops,etc.StoredProceduresCanstoreproceduresinthedatabasethenexecutethemusingthecallstatementpermitexternalapplicationstooperateonthedatabasewithoutknowingaboutinternaldetailsObject-orientedaspectsofthesefeaturesarecoveredinChapter22(ObjectBasedDatabases) FunctionsandProceduresSQL:1999supportsfunctionsandproceduresFunctions/procedurescanbewritteninSQLitself,orinanexternalprogramminglanguage.Functionsareparticularlyusefulwithspecializeddatatypessuchasimagesandgeometricobjects.Example:functionstocheckifpolygonsoverlap,ortocompareimagesforsimilarity.Somedatabasesystemssupporttable-valuedfunctions,whichcanreturnarelationasaresult.SQL:1999alsosupportsarichsetofimperativeconstructs,includingLoops,if-then-else,assignmentManydatabaseshaveproprietaryproceduralextensionstoSQLthatdifferfromSQL:1999. SQLFunctionsDefineafunctionthat,giventhenameofadepartment,returnsthecountofthenumberofinstructorsinthatdepartment.createfunctiondept_count(dept_namevarchar(20))returnsintegerbegindeclared_countinteger;selectcount(*)intod_countfrominstructorwhereinstructor.dept_name=dept_namereturnd_count;endFindthedepartmentnameandbudgetofalldepartmentswithmorethat12instructors.selectdept_name,budgetfromdepartmentwheredept_count(dept_name)>1 TableFunctionsSQL:2003addedfunctionsthatreturnarelationasaresultExample:Returnallaccountsownedbyagivencustomercreatefunctioninstructors_of(dept_namechar(20)returnstable(IDvarchar(5),namevarchar(20),dept_namevarchar(20),salarynumeric(8,2))returntable(selectID,name,dept_name,salaryfrominstructorwhereinstructor.dept_name=instructors_of.dept_name)Usageselect*fromtable(instructors_of(‘Music’)) SQLProceduresThedept_countfunctioncouldinsteadbewrittenasprocedure:createproceduredept_count_proc(indept_namevarchar(20),outd_countinteger)beginselectcount(*)intod_countfrominstructorwhereinstructor.dept_name=dept_count_proc.dept_nameendProcedurescanbeinvokedeitherfromanSQLprocedureorfromembeddedSQL,usingthecallstatement.declared_countinteger;calldept_count_proc(‘Physics’,d_count);ProceduresandfunctionscanbeinvokedalsofromdynamicSQLSQL:1999allowsmorethanonefunction/procedureofthesamename(callednameoverloading),aslongasthenumberofargumentsdiffer,oratleastthetypesoftheargumentsdiffer ProceduralConstructsWarning:mostdatabasesystemsimplementtheirownvariantofthestandardsyntaxbelowreadyoursystemmanualtoseewhatworksonyoursystemCompoundstatement:begin…end,MaycontainmultipleSQLstatementsbetweenbeginandend.LocalvariablescanbedeclaredwithinacompoundstatementsWhileandrepeatstatements:declarenintegerdefault0;whilen<10dosetn=n+1endwhilerepeatsetn=n–1untiln=0endrepeat ProceduralConstructs(Cont.)ForloopPermitsiterationoverallresultsofaqueryExample:declarenintegerdefault0;forrasselectbudgetfromdepartmentwheredept_name=‘Music’dosetn=n-r.budgetendfor ProceduralConstructs(cont.)Conditionalstatements(if-then-else)SQL:1999alsosupportsacasestatementsimilartoCcasestatementExampleprocedure:registersstudentafterensuringclassroomcapacityisnotexceededReturns0onsuccessand-1ifcapacityisexceededSeebookfordetailsSignalingofexceptionconditions,anddeclaringhandlersforexceptionsdeclareout_of_classroom_seatsconditiondeclareexithandlerforout_of_classroom_seatsbegin…..signalout_of_classroom_seatsendThehandlerhereisexit--causesenclosingbegin..endtobeexitedOtheractionspossibleonexception ExternalLanguageFunctions/ProceduresSQL:1999permitstheuseoffunctionsandprocedureswritteninotherlanguagessuchasCorC++Declaringexternallanguageproceduresandfunctionscreateproceduredept_count_proc(indept_namevarchar(20),outcountinteger)languageCexternalname’/usr/avi/bin/dept_count_proc’createfunctiondept_count(dept_namevarchar(20))returnsintegerlanguageCexternalname‘/usr/avi/bin/dept_count’ ExternalLanguageRoutines(Cont.)Benefitsofexternallanguagefunctions/procedures:moreefficientformanyoperations,andmoreexpressivepower.DrawbacksCodetoimplementfunctionmayneedtobeloadedintodatabasesystemandexecutedinthedatabasesystem’saddressspace.riskofaccidentalcorruptionofdatabasestructuressecurityrisk,allowingusersaccesstounauthorizeddataTherearealternatives,whichgivegoodsecurityatthecostofpotentiallyworseperformance.Directexecutioninthedatabasesystem’sspaceisusedwhenefficiencyismoreimportantthansecurity. SecuritywithExternalLanguageRoutinesTodealwithsecurityproblemsUsesandboxtechniquesthatisuseasafelanguagelikeJava,whichcannotbeusedtoaccess/damageotherpartsofthedatabasecode.Or,runexternallanguagefunctions/proceduresinaseparateprocess,withnoaccesstothedatabaseprocess’memory.Parametersandresultscommunicatedviainter-processcommunicationBothhaveperformanceoverheadsManydatabasesystemssupportbothaboveapproachesaswellasdirectexecutingindatabasesystemaddressspace. Triggers TriggersAtriggerisastatementthatisexecutedautomaticallybythesystemasasideeffectofamodificationtothedatabase.Todesignatriggermechanism,wemust:Specifytheconditionsunderwhichthetriggeristobeexecuted.Specifytheactionstobetakenwhenthetriggerexecutes.TriggersintroducedtoSQLstandardinSQL:1999,butsupportedevenearlierusingnon-standardsyntaxbymostdatabases.Syntaxillustratedheremaynotworkexactlyonyourdatabasesystem;checkthesystemmanuals TriggerExampleE.g.time_slot_idisnotaprimarykeyoftimeslot,sowecannotcreateaforeignkeyconstraintfromsectiontotimeslot.Alternative:usetriggersonsectionandtimeslottoenforceintegrityconstraintscreatetriggertimeslot_check1afterinsertonsectionreferencingnewrowasnrowforeachrowwhen(nrow.time_slot_idnotin(selecttime_slot_idfromtime_slot))/*time_slot_idnotpresentintime_slot*/beginrollbackend; TriggerExampleCont.createtriggertimeslot_check2afterdeleteontimeslotreferencingoldrowasorowforeachrowwhen(orow.time_slot_idnotin(selecttime_slot_idfromtime_slot)/*lasttuplefortimeslotiddeletedfromtimeslot*/andorow.time_slot_idin(selecttime_slot_idfromsection))/*andtime_slot_idstillreferencedfromsection*/beginrollbackend; TriggeringEventsandActionsinSQLTriggeringeventcanbeinsert,deleteorupdateTriggersonupdatecanberestrictedtospecificattributesE.g.,afterupdateoftakesongradeValuesofattributesbeforeandafteranupdatecanbereferencedreferencingoldrowas:fordeletesandupdatesreferencingnewrowas:forinsertsandupdatesTriggerscanbeactivatedbeforeanevent,whichcanserveasextraconstraints.E.g.convertblankgradestonull.createtriggersetnull_triggerbeforeupdateoftakesreferencingnewrowasnrowforeachrowwhen(nrow.grade=‘‘)beginatomicsetnrow.grade=null;end; TriggertoMaintaincredits_earnedvaluecreatetriggercredits_earnedafterupdateoftakeson(grade)referencingnewrowasnrowreferencingoldrowasorowforeachrowwhennrow.grade<>’F’andnrow.gradeisnotnulland(orow.grade=’F’ororow.gradeisnull)beginatomicupdatestudentsettot_cred=tot_cred+(selectcreditsfromcoursewherecourse.course_id=nrow.course_id)wherestudent.id=nrow.id;end; StatementLevelTriggersInsteadofexecutingaseparateactionforeachaffectedrow,asingleactioncanbeexecutedforallrowsaffectedbyatransactionUseforeachstatementinsteadofforeachrowUsereferencingoldtableorreferencingnewtabletorefertotemporarytables(calledtransitiontables)containingtheaffectedrowsCanbemoreefficientwhendealingwithSQLstatementsthatupdatealargenumberofrows WhenNotToUseTriggersTriggerswereusedearlierfortaskssuchasmaintainingsummarydata(e.g.,totalsalaryofeachdepartment)Replicatingdatabasesbyrecordingchangestospecialrelations(calledchangeordeltarelations)andhavingaseparateprocessthatappliesthechangesovertoareplicaTherearebetterwaysofdoingthesenow:DatabasestodayprovidebuiltinmaterializedviewfacilitiestomaintainsummarydataDatabasesprovidebuilt-insupportforreplicationEncapsulationfacilitiescanbeusedinsteadoftriggersinmanycasesDefinemethodstoupdatefieldsCarryoutactionsaspartoftheupdatemethodsinsteadofthroughatrigger WhenNotToUseTriggersRiskofunintendedexecutionoftriggers,forexample,whenloadingdatafromabackupcopyreplicatingupdatesataremotesiteTriggerexecutioncanbedisabledbeforesuchactions.Otherriskswithtriggers:ErrorleadingtofailureofcriticaltransactionsthatsetoffthetriggerCascadingexecution RecursiveQueries RecursioninSQLSQL:1999permitsrecursiveviewdefinitionExample:findwhichcoursesareaprerequisite,whetherdirectlyorindirectly,foraspecificcoursewithrecursiverec_prereq(course_id,prereq_id)as(selectcourse_id,prereq_idfromprerequnionselectrec_prereq.course_id,prereq.prereq_id,fromrec_rereq,prereqwhererec_prereq.prereq_id=prereq.course_id)select∗fromrec_prereq;Thisexampleview,rec_prereq,iscalledthetransitiveclosureoftheprereqrelationNote:1stprintingof6thederroneouslyusedc_prereqinplaceofrec_prereqinsomeplaces ThePowerofRecursionRecursiveviewsmakeitpossibletowritequeries,suchastransitiveclosurequeries,thatcannotbewrittenwithoutrecursionoriteration.Intuition:Withoutrecursion,anon-recursivenon-iterativeprogramcanperformonlyafixednumberofjoinsofprereqwithitselfThiscangiveonlyafixednumberoflevelsofmanagersGivenafixednon-recursivequery,wecanconstructadatabasewithagreaternumberoflevelsofprerequisitesonwhichthequerywillnotworkAlternative:writeaproceduretoiterateasmanytimesasrequiredSeeprocedurefindAllPrereqsinbook ThePowerofRecursionComputingtransitiveclosureusingiteration,addingsuccessivetuplestorec_prereqThenextslideshowsaprereqrelationEachstepoftheiterativeprocessconstructsanextendedversionofrec_prereqfromitsrecursivedefinition.Thefinalresultiscalledthefixedpointoftherecursiveviewdefinition.Recursiveviewsarerequiredtobemonotonic.Thatis,ifweaddtuplestoprereqtheviewrec_prereqcontainsallofthetuplesitcontainedbefore,pluspossiblymore ExampleofFixed-PointComputation AdvancedAggregationFeatures RankingRankingisdoneinconjunctionwithanorderbyspecification.Supposewearegivenarelationstudent_grades(ID,GPA)givingthegrade-pointaverageofeachstudentFindtherankofeachstudent.selectID,rank()over(orderbyGPAdesc)ass_rankfromstudent_gradesAnextraorderbyclauseisneededtogettheminsortedorderselectID,rank()over(orderbyGPAdesc)ass_rankfromstudent_gradesorderbys_rankRankingmayleavegaps:e.g.if2studentshavethesametopGPA,bothhaverank1,andthenextrankis3dense_rankdoesnotleavegaps,sonextdenserankwouldbe2 RankingRankingcanbedoneusingbasicSQLaggregation,butresultantqueryisveryinefficientselectID,(1+(selectcount(*)fromstudent_gradesBwhereB.GPA>A.GPA))ass_rankfromstudent_gradesAorderbys_rank; Ranking(Cont.)Rankingcanbedonewithinpartitionofthedata.“Findtherankofstudentswithineachdepartment.”selectID,dept_name,rank()over(partitionbydept_nameorderbyGPAdesc)asdept_rankfromdept_gradesorderbydept_name,dept_rank;Multiplerankclausescanoccurinasingleselectclause.Rankingisdoneafterapplyinggroupbyclause/aggregationCanbeusedtofindtop-nresultsMoregeneralthanthelimitnclausesupportedbymanydatabases,sinceitallowstop-nwithineachpartition Ranking(Cont.)Otherrankingfunctions:percent_rank(withinpartition,ifpartitioningisdone)cume_dist(cumulativedistribution)fractionoftupleswithprecedingvaluesrow_number(non-deterministicinpresenceofduplicates)SQL:1999permitstheusertospecifynullsfirstornullslastselectID,rank()over(orderbyGPAdescnullslast)ass_rankfromstudent_grades Ranking(Cont.)Foragivenconstantn,therankingthefunctionntile(n)takesthetuplesineachpartitioninthespecifiedorder,anddividesthemintonbucketswithequalnumbersoftuples.E.g.,selectID,ntile(4)over(orderbyGPAdesc)asquartilefromstudent_grades; WindowingUsedtosmoothoutrandomvariations.E.g.,movingaverage:“Givensalesvaluesforeachdate,calculateforeachdatetheaverageofthesalesonthatday,thepreviousday,andthenextday”WindowspecificationinSQL:Givenrelationsales(date,value)selectdate,sum(value)over(orderbydatebetweenrows1precedingand1following)fromsales WindowingExamplesofotherwindowspecifications:betweenrowsunboundedprecedingandcurrentrowsunboundedprecedingrangebetween10precedingandcurrentrowAllrowswithvaluesbetweencurrentrowvalue–10tocurrentvaluerangeinterval10dayprecedingNotincludingcurrentrow Windowing(Cont.)CandowindowingwithinpartitionsE.g.,Givenarelationtransaction(account_number,date_time,value),wherevalueispositiveforadepositandnegativeforawithdrawal“Findtotalbalanceofeachaccountaftereachtransactionontheaccount”selectaccount_number,date_time,sum(value)over(partitionbyaccount_numberorderbydate_timerowsunboundedpreceding)asbalancefromtransactionorderbyaccount_number,date_time OLAP** DataAnalysisandOLAPOnlineAnalyticalProcessing(OLAP)Interactiveanalysisofdata,allowingdatatobesummarizedandviewedindifferentwaysinanonlinefashion(withnegligibledelay)Datathatcanbemodeledasdimensionattributesandmeasureattributesarecalledmultidimensionaldata.Measureattributesmeasuresomevaluecanbeaggregatedupone.g.,theattributenumberofthesalesrelationDimensionattributesdefinethedimensionsonwhichmeasureattributes(oraggregatesthereof)areviewede.g.,attributesitem_name,color,andsizeofthesalesrelation Examplesalesrelation........................ CrossTabulationofsalesbyitem_nameandcolorThetableaboveisanexampleofacross-tabulation(cross-tab),alsoreferredtoasapivot-table.ValuesforoneofthedimensionattributesformtherowheadersValuesforanotherdimensionattributeformthecolumnheadersOtherdimensionattributesarelistedontopValuesinindividualcellsare(aggregatesof)thevaluesofthedimensionattributesthatspecifythecell. DataCubeAdatacubeisamultidimensionalgeneralizationofacross-tabCanhavendimensions;weshow3belowCross-tabscanbeusedasviewsonadatacube HierarchiesonDimensionsHierarchyondimensionattributes:letsdimensionstobeviewedatdifferentlevelsofdetailE.g.,thedimensionDateTimecanbeusedtoaggregatebyhourofday,date,dayofweek,month,quarteroryear CrossTabulationWithHierarchyCross-tabscanbeeasilyextendedtodealwithhierarchiesCandrilldownorrolluponahierarchy RelationalRepresentationofCross-tabsCross-tabscanberepresentedasrelationsWeusethevalueallisusedtorepresentaggregates.TheSQLstandardactuallyusesnullvaluesinplaceofalldespiteconfusionwithregularnullvalues. ExtendedAggregationtoSupportOLAPThecubeoperationcomputesunionofgroupby’soneverysubsetofthespecifiedattributesExamplerelationforthissectionsales(item_name,color,clothes_size,quantity)E.g.considerthequeryselectitem_name,color,size,sum(number)fromsalesgroupbycube(item_name,color,size)Thiscomputestheunionofeightdifferentgroupingsofthesalesrelation:{(item_name,color,size),(item_name,color),(item_name,size),(color,size),(item_name),(color),(size),()}where()denotesanemptygroupbylist.Foreachgrouping,theresultcontainsthenullvalueforattributesnotpresentinthegrouping. OnlineAnalyticalProcessingOperationsRelationalrepresentationofcross-tabthatwesawearlier,butwithnullinplaceofall,canbecomputedbyselectitem_name,color,sum(number)fromsalesgroupbycube(item_name,color)Thefunctiongrouping()canbeappliedonanattributeReturns1ifthevalueisanullvaluerepresentingall,andreturns0inallothercases.selectitem_name,color,size,sum(number),grouping(item_name)asitem_name_flag,grouping(color)ascolor_flag,grouping(size)assize_flag,fromsalesgroupbycube(item_name,color,size) OnlineAnalyticalProcessingOperationsCanusethefunctiondecode()intheselectclausetoreplacesuchnullsbyavaluesuchasallE.g.,replaceitem_nameinfirstquerybydecode(grouping(item_name),1,‘all’,item_name) ExtendedAggregation(Cont.)TherollupconstructgeneratesuniononeveryprefixofspecifiedlistofattributesE.g.,selectitem_name,color,size,sum(number)fromsalesgroupbyrollup(item_name,color,size)Generatesunionoffourgroupings:{(item_name,color,size),(item_name,color),(item_name),()}Rollupcanbeusedtogenerateaggregatesatmultiplelevelsofahierarchy.E.g.,supposetableitemcategory(item_name,category)givesthecategoryofeachitem.Thenselectcategory,item_name,sum(number)fromsales,itemcategorywheresales.item_name=itemcategory.item_namegroupbyrollup(category,item_name)wouldgiveahierarchicalsummarybyitem_nameandbycategory. ExtendedAggregation(Cont.)MultiplerollupsandcubescanbeusedinasinglegroupbyclauseEachgeneratessetofgroupbylists,crossproductofsetsgivesoverallsetofgroupbylistsE.g.,selectitem_name,color,size,sum(number)fromsalesgroupbyrollup(item_name),rollup(color,size)generatesthegroupings{item_name,()}X{(color,size),(color),()}={(item_name,color,size),(item_name,color),(item_name),(color,size),(color),()} OnlineAnalyticalProcessingOperationsPivoting:changingthedimensionsusedinacross-tabiscalledSlicing:creatingacross-tabforfixedvaluesonlySometimescalleddicing,particularlywhenvaluesformultipledimensionsarefixed.Rollup:movingfromfiner-granularitydatatoacoarsergranularityDrilldown:Theoppositeoperation-thatofmovingfromcoarser-granularitydatatofiner-granularitydata OLAPImplementationTheearliestOLAPsystemsusedmultidimensionalarraysinmemorytostoredatacubes,andarereferredtoasmultidimensionalOLAP(MOLAP)systems.OLAPimplementationsusingonlyrelationaldatabasefeaturesarecalledrelationalOLAP(ROLAP)systemsHybridsystems,whichstoresomesummariesinmemoryandstorethebasedataandothersummariesinarelationaldatabase,arecalledhybridOLAP(HOLAP)systems. OLAPImplementation(Cont.)EarlyOLAPsystemsprecomputedallpossibleaggregatesinordertoprovideonlineresponseSpaceandtimerequirementsfordoingsocanbeveryhigh2ncombinationsofgroupbyItsufficestoprecomputesomeaggregates,andcomputeothersondemandfromoneoftheprecomputedaggregatesCancomputeaggregateon(item_name,color)fromanaggregateon(item_name,color,size)Forallbutafew“non-decomposable”aggregatessuchasmedianischeaperthancomputingitfromscratchSeveraloptimizationsavailableforcomputingmultipleaggregatesCancomputeaggregateon(item_name,color)fromanaggregateon(item_name,color,size)Cancomputeaggregateson(item_name,color,size),(item_name,color)and(item_name)usingasinglesortingofthebasedata EndofChapter Figure5.22 Figure5.23 Figure5.24 AnotherRecursionExampleGivenrelationmanager(employee_name,manager_name)Findallemployee-managerpairs,wheretheemployeereportstothemanagerdirectlyorindirectly(thatismanager’smanager,manager’smanager’smanager,etc.)withrecursiveempl(employee_name,manager_name)as(selectemployee_name,manager_namefrommanagerunionselectmanager.employee_name,empl.manager_namefrommanager,emplwheremanager.manager_name=empl.employe_name)select*fromemplThisexampleview,empl,isthetransitiveclosureofthemanagerrelation Mergestatement(nowinChapter24)Mergeconstructallowsbatchprocessingofupdates.Example:relationfunds_received(account_number,amount)hasbatchofdepositstobeaddedtotheproperaccountintheaccountrelationmergeintoaccountasAusing(select*fromfunds_receivedasF)on(A.account_number=F.account_number)whenmatchedthenupdatesetbalance=balance+F.amount'