《数据库系统原理英》PPT课件

Chapter1Introduction,February2011,DatabaseSystemConcepts-Chapter1Introduction-,2,MainPartsinThisChapter,DatabaseSystemApplicationsPurposeofDatabaseSystemsViewofDataDatabaseLanguagesRelationalDatabaseDatabaseDesignDataStorageandQueryingTransactionManagementDatabaseArchitecturesDatabaseUsersandAdministratorsHistoryofDatabaseSystemsAppendix:Data,Information,Knowledge,etc.,February2011,DatabaseSystemConcepts-Chapter1Introduction-,3,1.0DB,DBMS,DBS,DefinitionsinthetextbookDatabase(DB)acollectionofinterrelateddata,storedinsystemsasfilesDatabasemanagementsystem(DBMS)DB,oracollectionofinterrelateddatasetofprogramstoaccessthedatainDBDatabasesystem(DBS)havingthesamedefinitionasDBMSinthetextbookthetermDBSandDBMSareusedinterchangeablyinthetextbook,February2011,DatabaseSystemConcepts-Chapter1Introduction-,4,1.0DB,DBMS,DBS(cont.),DBMSprovidesanenvironmentthatisbothconvenientandefficientforstoreandretrieveinformationdefinitionofstructuresforstorageofinformationdatamanipulationmechanismsdatasafetymechanismsDefinitionsinsomeothertextbooksFig.1.0.1Database(DB)acollectionofinterrelateddata,storedinsystemsasfiles,February2011,DatabaseSystemConcepts-Chapter1Introduction-,5,1.0DB,DBMS,DBS(cont.),Definitionsinsomeothertextbooks(cont.)Databasemanagementsystem(DBMS)asystem/mechanismtomanagedatainDBor:setofprogramstoaccessthedatainDBDatabasesystem(DBS)DB+DBMS+Users/AdministersDatabaseapplicationsystemDB+DBMS+Applicationprograms+Users/Administers,DBMS,Applicationprogram1,DBfile,DBquerylanguagee.g.SQL,API,e.g.ODBC,JDBC,Users,DBMS,DB,DBS/DBAS,Fig.1.0.1DBSandDBAS,Applicationprogram1,February2011,DatabaseSystemConcepts-Chapter1Introduction-,7,DatabasesandDBStouchallaspectsofourlivesBanking:alltransactionsAirlines:reservations,schedulesUniversities:registration,gradesSales:customers,products,purchasesManufacturing:production,inventory,orders,supplychainHumanresources:employeerecords,salaries,taxdeductions,1.2PurposeofDatabaseSystems,February2011,DatabaseSystemConcepts-Chapter1Introduction-,8,Intheearlydays,datamanagementapplicationswerebuiltontopoffilesystemsE.g.Saving-bankenterpriseanumberofapplicationprogramstoaccessdatafilesAprogramtodebit（将.记入借方）orcredit（将.记入贷方）anaccount(帐户）AprogramtoaddanewaccountAprogramtofindthebalance（收付差额，余额）ofanaccountAprogramtogeneratemonthlystatements,1.2PurposeofDatabaseSystems(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,9,DatamanagementonthebasisoffilesystemsExamplecustomer_record=inCustomer_FileFSaccessneedsfilename,itemname,lengthofrecord,lengthofitem,positionofitemintherecord,storagestructure,accessmethod,.Demeritapplicationprogramsarenotindependentofthefilesystem(oroperatingsystem)dependencyamongapplicationprogramsanddata(程序与数据的非独立/依存性）,1.2PurposeofDatabaseSystems(cont.),Fig.1.0.2WhyDBSneeded,secondarystorage(e.g.disk,tape)anddataonit(address:cylinder,track,sector),memorycontrollers(i.e.diskcontroller),onthebasisofsecondarystoragesphysicalstructures,suchasstoragestructures,accessmethod,accessprograms(I),directaccessthroughdiskcontroller,(I),accessthroughOSfilesystem,(II),accessthroughdatabasesystem(DBS),(III),accessprograms(II),accessprograms(III),I/Osystem:drivers,filesystem:fileslogicalstructuresandaccessmethods,DBMS:relationaltableslogicalstructuresandaccessmethods,systemcallsrelatedtofilemanagement,DBSAPI,SelectNameFromStudentWhereS#=100,DBSlogicalstructures:relations/tuples,DBMS,DBuser,Logicaladdress:#Record,Record1,Recordk,block1,OS:Logicalfile,Recordi,Recordm,Blockn,Physicaladdress：platter/track/sector,block1,blocki,blockk,PhysicalFileondisk,DBfiles,block,OS：filesystemandI/Osubsystem,viewofdatamodel,viewoffile,Fig.1.0.3Databaseaccess,February2011,DatabaseSystemConcepts-Chapter1Introduction-,12,Drawbacksofusingfilesystemstostoredatadataredundancy（冗余）andinconsistency（不一致性）multiplefileformats,duplication（副本）ofinformationindifferentfilesdifficultyindataaccessneedtowriteanewprogramtocarryouteachnewtaskdataisolation（独立性）multiplefilesandformats,1.2PurposeofDatabaseSystems(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,13,integrity（完整性）problemsdatavaluesshouldsatisfyconsistencyconstraintsintegrityconstraints(e.g.customer_age0)becomepartofprogramcodehardtoaddnewconstraintsorchangeexistingonesatomicityproblemsfailuresmayleavedatabaseinaninconsistentstatewithpartialupdatescarriedoute.g.transferoffundsfromoneaccounttoanothershouldeithercompleteornothappenatall,1.2PurposeofDatabaseSystems(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,14,concurrentaccessanomaliesuncontrolledconcurrentaccessescanleadtoinconsistenciese.g.twopersonsreadanaccountandupdateitatthesametimesecurityproblemsDatabasesystemsoffersolutionstoalltheaboveproblems,1.2PurposeofDatabaseSystems(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,15,1.3ViewofData,BasicconceptsinDBStheoryviewofdata(数据视图),dataabstractdatamodel,dataschema(模式),schemainstancelogical/physicalisolation1.3.1DataabstractionDataViewdatafeaturesfromsomeviewpointse.g.aboutstudent,hisname,sex,age,.Tosimplifyand“divideandconquer”thedesignofcomplexdataapplicationsystemsdescribes/designapplicationdatainthreelevels,andobtainsthreetypesofschema,Fig.1.1Thethreelevelsofdataabstraction,February2011,DatabaseSystemConcepts-Chapter1Introduction-,17,ViewleveldescribedatafromhowthedataitemsinDBareusedbydifferentusersseveralviewsforonedatumdescriptionresultsexternalschema（外模式）=view,setofviewsdescriptionprocedure/LogicalDBdesignviewabstractionmerits:applicationprogramsareprogrammedaccordingtoviews.Viewscanalsohideinformation(e.g.,salary)forsecuritypurposes.,1.3.1Dataabstraction(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,18,E.g.BankingApplicationAreasinFig.6.25inpage240frommorethanoneviewpoints,viewintegrationview1:customerview2:customerview3:customerview4:loanview5:accountview6:.,1.3.1Dataabstraction(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,19,Logicallevelimplementation-oriented,describeshowthedataitemsarestoredinDBS(e.g.asrelationaltables),andwhatrelationshipsexistamongthosedata:tablecustomername:string;street:string;city:integer;descriptionresultslogicalschema(逻辑模式）,e.g.relationaltablesdescriptionprocedure/LogicalDBdesignlogicalabstractionMerits:hidingphysicalimplementationdetails,1.3.1Dataabstraction(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,20,Physicalleveldescribeshowthedata(e.g.,customerrecord/table)areactuallystoredinfiles(orinsecondarystorage)descriptionresultsphysical/internalschema(物理模式，内模式）i.e.storagestructureandaccessmethods,suchasindex,physicalblocks,accessmethodsforsecondarymemory,etc.descriptionprocedure/PhysicalDBdesignphysicalabstraction,1.3.1Dataabstraction(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,21,Aninstanceofthedatabase(数据库实例)thecollectionofinformationstoredinthedatabaseataparticularmoment(P7inthetextbook)theactualcontentofthedatabaseataparticularpointintimeSchemathestructureofthedatabaseoveralldesignofthedatabase?(P7inthetextbook)physicalschemadatabasedesignatthephysicallevellogicalschemadatabasedesignatthelogicallevel,1.3.2InstancesandSchemas,February2011,DatabaseSystemConcepts-Chapter1Introduction-,22,Datadescriptions/abstractionsinthreelevelsmustobeythreetypesofspecification,i.e.threetypesofdatamodelsDefinitionofdatamodel(P8)acollectionofconceptualtoolsfordescribingdatadatarelationshipsdatasemanticsdataconstraints程序=数据结构+算法,integrityconstraints,1.3.3DataModel,February2011,DatabaseSystemConcepts-Chapter1Introduction-,23,AnotherdefinitionofdatamodelDBS中对数据组织与管理/使用方式的抽象描述，包括数据组织的语法定义，如数据项、数据项间的联系数据组织的语义定义，如完整性约束数据操作(note:onlyinsomedatamodels,e.g.relationaldatamodel)Relationalmodel(关系模型)usingacollectionoftablestorepresentbothdataandtherelationshipsamongthosedataasetofoperationsontablesarealsodefined(2.2/2.3/2.4)e.g.Fig.1.4,1.3.3DataModel(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,24,Entity-Relationshipmodel(实体-联系模型)usingtheentityandtherelationshiptomodeltheobjectandtheassociationamongobjectse.g.Fig.1.3NetworkmodelandhierarchicalmodelonlyusedforearlyDBSOthermodelsobject-orientedmodel(Chapter9)semi-structureddatamodels,e.g.XML(Chapter10),1.3.3DataModel(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,25,Datamodel,dataschema(ofdatamodel),instanceofschemaE.g.relationaldatamodel:R=relationaldataschema:customer=instanceofschema:similartodatatypesdeclaration,variablesofdatatypes,andvaluesofvariablese.g.Fig.1.2,1.3.3DataModel(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,26,Datamodelscanbeclassifiedasobject-basedmodel(基于对象的模型)record-basedmodel(基于记录的模型)physicaldatamodel(物理数据模型)Formoredetailsaboutcategoryofdatamodels,refertoAppendixB,1.3.3DataModel(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,27,3种数据抽象、3种模式DBS中2种数据独立性概念/逻辑模式内模式间的映射与physicaldataindependence外模式概念模式间的映射与logicaldataindependencerefertoFig.1.0.4,1.3.4DataIndependence,LogicaldataindependencewhenDBSslogicalschemasatlogicallevel(DBMS-oriented)change,application-orientedexternalschemaorapplicationprogramsdonotchangee.g.customer=isextendedintocustomer=,用户A1,宿主语言+DML,用户A2,用户B1,用户B2,外部视图A,外部视图B,概念/逻辑视图,外模式,概念/逻辑模式,内模式,DBMS,外模式/概念模式映射A,外模式/概念模式映射B,概念模式/内模式映射,宿主语言+DML,宿主语言+DML,宿主语言+DML,Fig.1.0.4,February2011,DatabaseSystemConcepts-Chapter1Introduction-,29,Physicaldataindependence(P7)applicationprogramsdonotdependonDBSsphysicalschema,i.e.whenphysicalschema(e.g.index,block-size)change,applicationprogramsremainunchangedtheabilitytomodifythephysicalschemawithoutchangingtheexternal/logicalschemas,onwhichapplicationprogramsdepend,1.3.4DataIndependence(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,30,1.4DatabaseLanguage,DatabaseLanguagesas(human-machine)interfacesdatamanipulationlanguage,DMLdatadefinitionlanguage,DDL1.4.1DatamanipulationlanguageDMListhelanguagethatenablesuserstoaccessormanipulatethedataasorganizedbytheappropriatedatamodelTypesofDBSaccessretrieve,insert,delete,modify,February2011,DatabaseSystemConcepts-Chapter1Introduction-,31,TwotypesofDMLprocedural(过程性)userspecifieswhatdataisrequiredandhowtogetthosedatanonprocedural(说明性，declarative)userspecifieswhatdataisrequiredwithoutspecifyinghowtogetthosedataQuery(查询）astatementrequestingtheretrievalofinformationDMLalsoknownasquerylanguage,1.4.1DatabaseManipulationLanguage(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,32,SQListhemostwidelyusednon-proceduralquerylanguage,othersareQE,DatalogExamplefindthenameofthecustomerwithcustomer-id192-83-7465selectcustomer.customer-namefromcustomerwherecustomer.customer-id=192-83-7465,1.4.1DatabaseManipulationLanguage(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,33,DDLisusedforspecifyingthedatabaseschemaandadditionalpropertiesofthedataE.g.createtableaccount(account-numberchar(10),balanceinteger)DDLcanalsobeusedtodefineintegrityconstraintsinDBdomainintegrity,referentialintegrity,assertions,authorization,etc.Thestructures/schemasofthedatabasedefinedbyDDLisstoredinthedatadictionary/directorymetadata,i.e.,dataaboutdata,isstored,1.4.2DatabaseDefinitionLanguage,February2011,DatabaseSystemConcepts-Chapter1Introduction-,34,DatastorageanddefinitionlanguageasubsetofDDL,definingphysicalschemas,i.e.,storagestructureandaccessmethods,whichareusuallyhiddenformusers,1.4.2DatabaseDefinitionLanguage(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,35,Applicationprogramsgenerallyaccessdatabasesintwomethodsapplicationprograminterfaces(API,e.g.ODBC,JDBC,ADO,),whichallowSQLqueriestobesenttoadatabaseFig.1.0.5embedDMLcallwithinhostlanguage(宿主语言,suchasCandJava),1.4.3DBAccessforApplicationPrograms,February2011,DatabaseSystemConcepts-Chapter1Introduction-,36,Capplicationprograms,Javaapplicationprograms,Otherlanguageapplicationprograms,ODBC,JDBC,DBS,DDL+DML,API,Fig.1.0.5DBSaccessinterfaces,1.4.3DBAccessforApplicationPrograms(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,37,1.5RelationalDatabase,Refertochapter2,February2011,DatabaseSystemConcepts-Chapter1Introduction-,38,1.6DatabaseDesign,1.6.1Designprocess从保持dataindependence（数据无关性/独立性）角度出发，根据datamodels所定义的数据规范形式，在view、logical、physical三个层次，采用三种dataabstraction方法，通过DB概念设计、DBS逻辑设计、DBS物理设计三个阶段，构造面向具体应用领域的DBS的externalschema、logicalschema、internalschema的集合,从而得到conceptualDBS、logicalDBS、physicalDBS的设计结果AsdescribedinFig.1.0.6conceptualdesignlogicaldesignphysicaldesign,Fig.1.0.6DataabstractionandSchema,February2011,DatabaseSystemConcepts-Chapter1Introduction-,40,BankingEnterprisethebankisorganizedintobrancheseachbranchisdescribedbythecityislocatedin,thenameandtheassetsabankcustomerisillustratedbyhiscustomer_idandname,thestreetandcitythebankofferstwotypesofaccounts,whicharedepictedbyaccount_numberandbalancethesavingsaccount:aninterestratethecheckingaccount:overdraft,1.6.2-1.6.4AnExample,February2011,DatabaseSystemConcepts-Chapter1Introduction-,41,thebankprovidesitscustomerswithloans,whicharedepictedbyloan_number,balance_name,andamountBankemployeesareidentifiedbytheiremployee_id,telephone_number,employee_street,employee_cityConceptualDBdesignE-Rdiagrame.g.Fig.1.3,1.6.2-1.6.4AnExample(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,42,Fig.1.3Asampleentity-relationshipmodel,1.6.2-1.6.4AnExample(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,43,LogicalDBdesigndatabasedesigninE-Rmodelusuallyconvertedtodesignintherelationalmodel,i.etherelationaltablesandviewstheconvertedrelationaltablesarethennormalized(规范化)e.g.Fig.1.4andFig.1.5,1.6.2-1.6.4AnExample(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,44,Attributes,ThecustomertableFig.1.4ASampleRelationalDatabase,schema,instances,1.6.2-1.6.4AnExample(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,45,Fig.1.4ASampleRelationalDatabase,1.6.2-1.6.4AnExample(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,46,Fig1.6DBS=User/DBA+DBMS+DBDBMSqueryprocessingstoragemanagerDBfilesapplicationdatadatadictionary/directoryindicesstatisticdata,1.11DBSArchitecture,Fig.1.6DBSArchitecture,user,DBMS,DB,February2011,DatabaseSystemConcepts-Chapter1Introduction-,48,DBAS(DatabaseApplicationSystems)DB+DBMS+Applicationprograms+Users/DBA实现数据处理功能DBSDB+DBMS+User/DBA实现数据管理功能信息/数据管理（management）数据的分类、收集、组织、编码、存储、检索和维护信息/数据处理（processing）对数据进行分类、收集、组织、存储，进而从已有数据出发，抽取或推导出新的数据/信息,1.11DatabaseApplicationSystem,February2011,DatabaseSystemConcepts-Chapter1Introduction-,49,信息/数据处理与信息/数据管理的区别数据管理侧重于对数据如何进行组织和存储，并根据用户需要提供对数据的访问支持数据处理除了具有数据管理功能外，还可以对通过数据管理得到的数据进行进一步深加工，从中获取新的数据、信息例如，在一个商品零售系统中，可以利用数据库系统的数据管理功能存储各种商品基本信息（如商品数量、销售额等）并为各类用户提供对各类商品信息的查询功能。当从数据库系统中得到这些商品信息后，可以采用一些统计分析工具、数据挖掘（DataMining）工具进一步进行分析处理，得到有关那些商品属于热销商品、热销商品销售销售额等新信息。这种统计分析属于数据处理范畴,1.11DatabaseApplicationSystem(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,50,1.11DatabaseApplicationSystemArchitecture,Fig.1.7InDBAS,theapplicationprogramsareresponsibleforinformationordataprocessing,e.g.dataminingTwo-tierarchitecturee.g.clientprogramsusingODBC/JDBCtocommunicatewithadatabaseThree-tierarchitecturee.g.web-basedapplications,andapplicationsbuiltusing“middleware”,February2011,DatabaseSystemConcepts-Chapter1Introduction-,51,Fig.1.7Two-tierandthree-tierarchitectures,1.11DatabaseApplicationSystemArchitecture(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,52,InDBS,DBMSprovidesdatamanagementmechanismsforusers,thatis,datastorageanddataqueryingDBMSconsistsoftwomodulesstoragemanagerqueryprocessorStoragemanagerisaprogrammodulethatprovidestheinterfacebetweenthelow-leveldatastoredinthedatabaseandtheapplicationprogramsandqueriessubmittedtothesystemThestoragemanagerisresponsiblefortranslatingDMLstatementsintolow-levelfilecommandsefficientstoring,retrievingandupdatingofdata,1.8DataStorageandQuerying,February2011,DatabaseSystemConcepts-Chapter1Introduction-,53,Themodulesinthestoragemanagerareauthorizationandintegritymanager,transactionmanager,filemanager,andbuffermanagerThequeryprocessorincludesDDLinterpreterDMLcompilerQueryoptimizationQueryevaluationengine,1.8DataStorageandQuerying(cont.),C,Pascalprograms,词法/语法/语义分析,中间代码生成,(中间)代码优化,目标代码生成,程序编译/编译器,程序执行/,OS,进程管理,account_A:=account_A-50;write(account_A);read(account_B);account_B:=account_B+50;write(account_B);integrityconstrainttotalamountofaccount_Aandaccount_Bremainsunchangedbeforeandafterthetransactionexecution,1.9TransactionManagement,February2011,DatabaseSystemConcepts-Chapter1Introduction-,56,Transactionpropertiesatomicity(原子性),consistency(一致性),isolation(独立性),durability(持久性)Transaction-managementcomponentinDBMSensuresthatthedatabaseremainsinaconsistent/correctstatedespitesystemfailures(e.g.,powerfailuresandoperatingsystemcrashes)andtransactionfailures,bymeansofconcurrencyandcontrolfailurerecovery.controlstheinteractionamongtheconcurrenttransactions,toensuretheconsistencyofthedatabase,1.9TransactionManagement,February2011,DatabaseSystemConcepts-Chapter1Introduction-,57,1.12DatabaseUserandAdministrators,1.12.1DatabaseusersanduserinterfacesUsersaredifferentiatedbythewaytheyexpecttointeractwiththesystem,fourcategoriesofusers,fourcategoriesofinterfaces:Fig.1.6naiveusersinvokeoneofthepermanentapplicationprogramsthathavebeenwrittenpreviouslye.g.peopleaccessingdatabaseovertheweb,banktellers,clericalstaffapplicationprogrammersinteractwithsystemthroughDMLcallssophisticatedusersformrequestsinadatabasequerylanguageortools,andsubmitthesequeriestothequeryprocessor,February2011,DatabaseSystemConcepts-Chapter1Introduction-,58,specializeduserswritespecializeddatabaseapplicationsthatdonotfitintothetraditionaldataprocessingframework,1.12.2DatabaseAdministrator(DBA)TheresponsibilitiesofDBAinclude,butnotlimitedtoschemadefinitionschemaandphysicalorganizationmodificationgrantingofauthorizationfordataaccessroutinemaintenance,1.12DatabaseUserandAdministrators(cont.),February2011,DatabaseSystemConcepts-Chapter1Introduction-,59,AppendixAData,Information,Knowledge,1.0.1DefinitionsinMicr