《數(shù)據(jù)庫系統(tǒng)》教學課件

《數(shù)據(jù)庫系統(tǒng)》教學課件,數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫,系統(tǒng),教學,課件 Overview of Database SystemscourtesyofJoeHellersteinandetcforsomeslides.What is a Database?nData(http:/ is a Database Management System?nADatabaseManagementSystem(DBMS)is:qAsoftwaresystemdesignedtostore,manage,andfacilitatequerytodatabases.nPopularDBMSqOracleqIBMDB2qMicrosoftSQLServernDatabaseSystem=Databases+DBMSTypical Applications Supported by Database SystemsnOnlineTransactionProcessing(OLTP)qRecordingsalesdatainsupermarketsqBookingflightticketsqElectronicbankingnOnlineanalyticalprocessing(OLAP)andDataWarehousingqBusinessreportingforsalesdataqCustomerRelationshipManagement(CRM)Is the WWW a DBMS?nTheWeb=SurfaceWeb+DeepWebnSurfaceWeb:simplytheHTMLpagesqAccessedby“search”:nPosekeywordsinsearchbox.nDeepWeb:contenthiddenbehindHTMLformsAccessedby“query”nFillinqueryforms.“Search”as“Simple Search”“Query”as“Advanced Search”“Search”vs.“Query”nSearchisstructure-free.qThekeywords“databasesystems”canappearinanyplaceinaHTMLpages.nQueryisstructure-aware.qSay,werestructthatthekeywords“databasesystems”canonlyappearinthe“TITLE”field.qi.e.,weassumethereisanunderlyingSTRUCTURE(ofabook).What is a“STUCTURE”?nReferringtotheCprogramminglanguagestructBOOKcharTITLE256;charAUTHOR256;floatPRICE;intYEAR;nInthiscourse,westudydatabasemanagementsystemsthatfocusonprocessingstructureddata.Files vs.DBMSnWecanstoredatainOSfiles.qE.g.,GooglehasitsowndistributedfilesystemcalledGoogleFileSystem(GFS).nWhataretheadvantagesofDBMS?qGooddatamodelingnDataIndependencenDataIntegrityandSecurityqSimpleandefficientad-hocqueriesnReducedapplicationdevelopmenttimeqConcurrencycontrolqCrashrecoveryA Historical Perspective(1)nIntegratedDataStore(IDS),byCharlesBachman,early1960s.qNetworkdatamodelqTuringAwardSpeech,“TheProgrammerasNavigator”,1973.nInformationManagementSystem(IMS),byIBM,late1960s.qHierarchicaldatamodelA Historical Perspective(2)nRelationalDataModel,byEdgarCodd,1970.qCodd,E.F.(1970).ARelationalModelofDataforLargeSharedDataBanks.Communications of the ACM13(6):377387.qTuringAward,1981.nSystemR,byIBM,startedin1974qStructuredQueryLanguage(SQL)nINGRES,byBerkeley,startedin1974qPOSTGRES,Mariposa,C-StoreA Historical Perspective(3)nDatabaseTransactionProcessing,mainlybyJimGray.qJGray,AReuter.Transactionprocessing:conceptsandtechniques.1993.qTuringAward,1998.nObject-RelationalDBMS,1990s.qStonebraker,MichaelwithMoore,Dorothy.Object-Relational DBMSs:The Next Great Wave.1996.qPostgres(UCBerkeley),PostgreSQL.qIBMsDB2,Oracledatabase,andMicrosoftSQLServerFrom OLTP to OLAP and Data WarehousingnOLAP(On-LineAnalyticalProcessing,Codd,1993)qFlexibleReportingforBusinessIntelligencenCharacteristicsofOLAPapplications:qTransactionsthatinvolvelargenumbersofrecordsqFrequentAd-hocqueriesandInfrequentupdatesqAfewdecisionmakingusersqFastresponsetimesnDatawarehousesaredesignedtofacilitatereportingandanalysis.qRead-MostlyDBMS:C-Store,MonetDBData Mining(DM)nDM is the exploration and analysis of large quantities ofdatainordertodiscovervalid,novel,potentiallyuseful,andultimatelyunderstandablepatternsindata.nAssociationRulesq60%ofallcustomerswhopurchasediapersalsobuybeer.nClassification:EmailspamnClusteringqClusterSinaWeibousersbysimilarinterestsnWebPageRanking:GooglesPageRankBig Data(1)nByOxfordDictionaries:datasetsthataretoolargeandcomplextomanipulateorinterrogatewithstandardmethodsortools.nThisdatacomesfromeverywhere:qsensorsusedtogatherclimateinformation,qpoststosocialmediasites,qdigitalpicturesandvideos,qstockrecords,qcellphoneGPSsignals.Big Data(2)nByIBM,http:/ Data:Data ModelsnAdata model isacollectionofconceptsfordescribingdata.nAschema isadescriptionofaparticularcollectionofdata,usingagivendatamodel.nTherelational data model isthemostwidelyusedmodeltoday.qMainconcept:relation,basicallyatablewithrowsandcolumns.qEveryrelationhasaschema,whichdescribesthecolumns,orfields(theirnames,types,constraints,etc.).Schema in Relation Data Modelsidnameloginagegpa53666Jonesjonescs183.453688Smithsmithee183.253650Smithsmithmath193.8Students(sid:string,name:string,login:string,age:integer,gpa:real)Table1.AnInstanceoftheStudentsRelationDefinitionoftheStudentsSchemaArelationschemaisaTEMPLATEofthecorrespondingrelation.Levels of Abstraction in a DBMSnManyviewsdescribehowusersseethedata.qPersonalizedaccessofdata.nConceptualschemadefineslogicalstructureqi.e.,whatrelationstostore.nPhysicalschemaspecifiesphysicalstructure.qHowthe“l(fā)ogical”relationsarephysicallystoredonexternalstoragesuchasdisk.courtesyofJoeHellersteinExample:University DatabasenConceptualschema:qStudents(sid:string,name:string,login:string,age:integer,gpa:real)qCourses(cid:string,cname:string,credits:integer)qEnrolled(sid:string,cid:string,grade:string)nPhysicalschema:qRelationsstoredasunorderedfiles.qIndexonfirstcolumnofStudents.nExternalSchema(View):qCourse_info(cid:string,enrollment:integer)Data IndependencenApplicationsinsulatedfromhowdataisstructuredandstored.nLogicaldataindependence:Protectionfromchangesinlogicalstructureofdata.nPhysicaldataindependence:Protectionfromchangesinphysicalstructureofdata.*OneofthemostimportantbenefitsofusingaDBMS!Queries in a Relational DBMSnSpecifiedinaNon-ProceduralwayqUsersonlyspecifywhatdatatheyneed;qADBMStakescaretoevaluatequeriesasefficientlyaspossible.naNon-ProceduralQueryLanguage:qSQL:StructuredQueryLanguageqBasicformofaSQLquery:SELECT target-listFROM relation-listWHERE qualificationA Simple SQL ExamplenAtanairport,agateagentclicksonaformtorequestthepassengerlistforaflight.SELECT nameFROMPassengerWhereflight=510275Passenger(pid:string,name:string,flight:integer)Concurrent execution of user programsnWhy?qUtilizeCPUwhilewaitingfordiskI/On(databaseprogramsmakeheavyuseofdisk)qAvoidshortprogramswaitingbehindlongonesne.g.ATMwithdrawalwhilebankmanagersumsbalanceacrossallaccountsConcurrent executionnInterleavingactionsofdifferentuserprogramscanleadtoinconsistency:Example:Billtransfers$100fromsavingstocheckingSavings=100;Checking+=100Meanwhile,Billswiferequestsaccountinfo.Badinterleaving:nSavings=100nPrintbalancesnChecking+=100qPrintoutismissing$100!Concurrency ControlnDBMSensuressuchproblemsdontarise.nUserscanpretendtheyareusingasingle-usersystem.Key concept:TransactionnAnTransactionisanatomicsequenceofdatabaseactions(reads/writes)nEachtransaction,executedcompletely,mustleavetheDBinaconsistentstateifDBisconsistentwhenthetransactionbegins.Example of Transaction Incomplete Transaction and System CrashesnIncompletetransactionqCanceledbythetransactionorDBMSqAbortedunexpectedlybysystemcrashnIdea:Keepalog(history)ofallactionscarriedoutbytheDBMSwhileexecutingasetoftransactions:qBeforeachangeismadetothedatabase,thecorrespondinglogentryisforcedtoasafelocation.(WALprotocol;OSsupportforthisisofteninadequate.)qAfteracrash,theeffectsofpartiallyexecutedtransactionsareundoneusingthelog.STRUCTURE OF A DBMSDatabases make these folks happy.nEndusersandDBMSvendorsnDBapplicationprogrammersqE.g.,smartwebmastersnDatabaseadministrator(DBA)qDesignslogical/physicalschemasqHandlessecurityandauthorizationqDataavailability,crashrecoveryqDatabasetuningasneedsevolveqMustunderstandhowaDBMSworks!SummarynDBMSusedtomaintain,querylargedatasets.nBenefitsincluderecoveryfromsystemcrashes,concurrentaccess,quickapplicationdevelopment,dataintegrityandsecurity.nLevelsofabstractiongivedataindependence.nADBMStypicallyhasalayeredarchitecture.nDBAsholdresponsiblejobsandarewell-paid!nDBMSR&Disoneofthebroadest,mostexcitingareasinCS.nWefocusonRelationalDBMS:qmaintain/querystructureddata