5s

CHAPTER5AdvancedSQLPracticeExercises5.1Describethecircumstancesinwhichyouwouldchoosetouseembed-dedSQLratherthanSQLaloneoronlyageneral-purposeprogramminglanguage.Answer:WritingqueriesinSQListypicallymucheasierthancodingthesamequeriesinageneral-purposeprogramminglanguage.HowevernotallkindsofqueriescanbewritteninSQL.Alsonondeclarativeactionssuchasprintingareport,interactingwithauser,orsendingtheresultsofaquerytoagraphicaluserinterfacecannotbedonefromwithinSQL.Undercircumstancesinwhichwewantthebestofbothworlds,wecanchooseembeddedSQLordynamicSQL,ratherthanusingSQLaloneorusingonlyageneral-purposeprogramminglanguage.EmbeddedSQLhastheadvantageofprogramsbeinglesscomplicatedsinceitavoidstheclutteroftheODBCorJDBCfunctioncalls,butrequiresaspecializedpreprocessor.5.2WriteaJavafunctionusingJDBCmetadatafeaturesthattakesaResult-Setasaninputparameter,andprintsouttheresultintabularform,withappropriatenamesascolumnheadings.Answer:publicclassResultSetTableimplementsTabelModel{ResultSetresult;ResultSetMetaDatametadata;intnumcols;ResultSetTable(ResultSetresult)throwsSQLException{this.result=result;metadata=result.getMetaData();numcols=metadata.getColumnCount();for(inti=1;i=numcols;i++){System.out.print(metadata.getColumnName(i)+‘‘‘‘);12Chapter5AdvancedSQL}System.out.println();while(result.next()){for(inti=1;i=numcols;i++){System.out.print(result.getString(metadata.getColumnName(i)+‘‘‘‘));}System.out.println();}}}5.3WriteaJavafunctionusingJDBCmetadatafeaturesthatprintsalistofallrelationsinthedatabase,displayingforeachrelationthenamesandtypesofitsattributes.Answer:DatabaseMetaDatadbmd=conn.getMetaData();ResultSetrs=dbmd.getTables();while(rs.next()){System.out.println(rs.getString(‘‘TABLENAME’’);ResultSetrs1=dbmd.getColumns(null,‘‘schema-name’’,rs.getString(‘‘TABLENAME’’),‘‘%’’);while(rs1.next()){System.out.println(rs1.getString(‘‘COLUMNNAME’’),rs.getString(‘‘TYPENAME’’);}}5.4Showhowtoenforcetheconstraint“aninstructorcannotteachintwodifferentclassroomsinasemesterinthesametimeslot.”usingatrigger(rememberthattheconstraintcanbeviolatedbychangestotheteachesrelationaswellastothesectionrelation).Answer:FILL5.5Writetriggerstoenforcethereferentialintegrityconstraintfromsectiontotimeslot,onupdatestosection,andtimeslot.NotethattheoneswewroteinFigure5.8donotcovertheupdateoperation.Answer:FILL5.6Tomaintainthetotcredattributeofthestudentrelation,carryoutthefollowing:a.Modifythetriggeronupdatesoftakes,tohandleallupdatesthatcanaffectthevalueoftotcred.b.Writeatriggertohandleinsertstothetakesrelation.Exercises3c.Underwhatassumptionsisitreasonablenottocreatetriggersonthecourserelation?Answer:FILL5.7ConsiderthebankdatabaseofFigure5.25.Letusdefineaviewbranchcustasfollows:createviewbranchcustasselectbranchname,customernamefromdepositor,accountwheredepositor.accountnumber=account.accountnumberSupposethattheviewismaterialized;thatis,theviewiscomputedandstored.Writetriggerstomaintaintheview,thatis,tokeepitup-to-dateoninsertionstoanddeletionsfromdepositororaccount.Donotbotheraboutupdates.Answer:Forinsertingintothematerializedviewbranchcustwemustsetadatabasetriggeronaninsertintodepositorandaccount.Weassumethatthedatabasesystemusesimmediatebindingforruleexecution.Fur-ther,assumethatthecurrentversionofarelationisdenotedbytherelationnameitself,whilethesetofnewlyinsertedtuplesisdenotedbyqualifyingtherelationnamewiththeprefix–inserted.Theactiverulesforthisinsertionaregivenbelow–definetriggerinsertintobranchcustviadepositorafterinsertondepositorreferencingnewtableasinsertedforeachstatementinsertintobranchcustselectbranchname,customernamefrominserted,accountwhereinserted.accountnumber=account.accountnumberdefinetriggerinsertintobranchcustviaaccountafterinsertonaccountreferencingnewtableasinsertedforeachstatementinsertintobranchcustselectbranchname,customernamefromdepositor,insertedwheredepositor.accountnumber=inserted.accountnumberNotethatiftheexecutionbindingwasdeferred(insteadofimmediate),thentheresultofthejoinofthesetofnewtuplesofaccountwiththesetofnewtuplesofdepositorwouldhavebeeninsertedbybothactiverules,leadingtoduplicationofthecorrespondingtuplesinbranchcust.Thedeletionofatuplefrombranchcustissimilartoinsertion,exceptthatadeletionfromeitherdepositororaccountwillcausethenaturaljoinoftheserelationstohavealessernumberoftuples.Wedenotethenewly4Chapter5AdvancedSQLdeletedsetoftuplesbyqualifyingtherelationnamewiththekeyworddeleted.definetriggerdeletefrombranchcustviadepositorafterdeleteondepositorreferencingoldtableasdeletedforeachstatementdeletefrombranchcustselectbranchname,customernamefromdeleted,accountwheredeleted.accountnumber=account.accountnumberdefinetriggerdeletefrombranchcustviaaccountafterdeleteonaccountreferencingoldtableasdeletedforeachstatementdeletefrombranchcustselectbranchname,customernamefromdepositor,deletedwheredepositor.accountnumber=deleted.accountnumber5.8ConsiderthebankdatabaseofFigure5.25.WriteanSQLtriggertocarryoutthefollowingaction:Ondeleteofanaccount,foreachowneroftheaccount,checkiftheownerhasanyremainingaccounts,andifshedoesnot,deleteherf