数据库管理系统概述英文版课件:1 introduction to databases.pptx
《数据库管理系统概述英文版课件:1 introduction to databases.pptx》由会员分享,可在线阅读,更多相关《数据库管理系统概述英文版课件:1 introduction to databases.pptx(40页珍藏版)》请在冰豆网上搜索。
![数据库管理系统概述英文版课件:1 introduction to databases.pptx](https://file1.bdocx.com/fileroot1/2023-3/26/46becf27-f19c-49df-b8ef-fd0bb7686776/46becf27-f19c-49df-b8ef-fd0bb76867761.gif)
CourseDatabaseManagementSystemsOverview,COMP231,1,CourseDetails,COMP231,2,TextbookDatabaseSystemConcepts,McGrawHill(5thEdition)A.Silberschatz,H.Korth,andS.Sudarshan.,Reference,DatabaseManagementSystemsMcGrawHill(3rdEdition)RaghuRamakrishnanandJohannesGehrke.,CourseDetails,COMP231,3,GradingScheme:
AssignmentandExperiment30%FinalExam70%,CourseOutline,COMP231,4,ERModelRelationalModelandRelationalAlgebraSQLFunctionalDependenciesandRelationalDatabaseDesignStorageandFileSystemsTreeandHashIndexesQueryProcessingandImplementationofRelationalOperatorsQueryOptimizationTransactionsConcurrencyControlProtocolsDatabaseRecovery,WhatisaDatabase?
COMP231,5,Acollectionofdata,typicallydescribingtheactivitiesofoneormorerelatedorganizations数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度的特点、是与应用程序彼此独立的数据集合,WhatisaDatabase?
COMP231,6,Databasesplayacriticalroleinalmostallareas,Banking:
alltransactionsAirline:
reservation,schedulesUniversities:
registration,gradesSales:
customers,products,purchasesManufacturing:
production,inventory,orders,supplychain供应链Humanresources人力资源:
employeerecords,salaries,taxdeductions,WhatisaDatabase?
COMP231,7,Adatabasecanbeofanysizeandofvaryingcomplexity.,Forexample,thelistofnamesandaddressoffriendsThebookcatalog目录ofalargelibrarymaycontainhalfamillionrecordsAdatabaseofmuchgreatersizeandcomplexityismaintainedbythegovernmenttokeeptrackofthetaxinformationfiledbytaxpayers.,WhatisaDatabase?
Aschemaisthedefinitionofadatabase.Itdefinesthemeaningofdata.Aninstanceofadatabaseisthecollectionofdatainthedatabaseataparticularpointoftime(snap-shot).Forexample,intheabove,theschemais“StudentName,ID,Age,Gender,EntranceYear,Grade”.Theremainingrowsinthetablemakeupaninstanceofthedatabase.,COMP231,8,WhatisaDBMS?
数据库管理系统(DBMS),COMP231,9,DBMSDatabaseManagementSystem数据库管理系统(DatabaseManagementSystem)是位于用户与操作系统之间的一层数据管理软件。
ADBMSisacollectionofsoftwareprogramstoenableuserstocreate,maintain维护andutilizeju:
tlaz使用adatabase.在不引起混淆的情况下常常把数据库系统简称为数据库,WhatisaDBMS?
DBMSInsertrecordsDeleterecordsUpdaterecordsQueryrecords,COMP231,10,CommercialDBMS,WhatisaDBMS1.?
Worlds2ndlargestsoftware,company2.CEO,LarryEllison,worlds2nd,richest,Worlds2ndlargestafterInformixacquisition,COMP231,11,WhatisaDBMS?
CommercialDBMS,AccesscomeswithMSOffice,JohnChen,grownupinHK,AcquiredbyIBMin2001,COMP231,12,WhydoweneedaDBMS?
ToreduceapplicationdevelopmenttimeSupposewearegivenacollectionofrawfileswhichoccupy500GB,Whatarethedrawbacks?
COMP231,13,DrawbackofFileSystem,DataRedundancy数据冗余andInconsistencyE.g.,considerabankapplication,addressofacustomerinthefileof“saving-accounts”and,thefileof“checking-accounts”AgooddesignofDBMScanavoiddataredundancyandinconsistency.,COMP231,14,DrawbackofFileSystem,DifficultyinaccessingdataNeedtowriteanewprogramtocarryouteachnewtask,ItiseasytoobtaindatawithDBMS,COMP231,15,DrawbackofFileSystem,Integrityproblems完整性E.g.,considerabankapplication,Thebalancecannotbebelow$1000Thedayofamonthcannotexceed31,DBMScanchecktheintegrityautomatically,COMP231,16,COMP231,17,DrawbackofFileSystem,4.Atomicityofupdates,E.g.,considerabankapplication,Wewanttotransfer$100fromaccountAtoaccountBSteps:
Step1:
Wededuct$100fromaccountA(A-100),Step2:
Then,weincrement$100inaccountB(B+100)IfthesystemcrashesatStep1,thenStep2cannotbeexecuted,DBMSmakessurethatStep1andStep2canbeexecutedtogetherevenwithacrash(Wecalltheexecutionisatomic.),18,DrawbackofFileSystem,5.ConcurrentAccess并发存取bymultipleusers,UncontrolledconcurrentaccessescanleadtoinconsistenciesE.g.,considerabankapplication,Thereisanaccountsharedby2customersAandBCustomersAandBwithdraw$1000concurrently,DBMSmakessurethattheconcurrentaccesscannotleadtothisproblem,Read5000,Read5000,Write4000COMP231,5000-10005000-1000,Write4000,A,B,COMP231,19,DrawbackofFileSystem,SecurityProblemsE.g.,considerabankapplicationWedonotwantsystemprogrammerstohavepermissionstoreadsomedata(e.g.,AndyLaussavingaccountandJoeyYungssavingaccount)Needalotofefforttore-writeaprogramforthispermissionsystem,DBMScanenforcethatdifferentusershavedifferentpermissionstoaccessdifferentpartsofthedata,AdvantagesofDBMS,COMP231,20,WiththeuseofDBMS,wehavethefollowingadvantages,DataindependenceEfficientdataaccessDataintegrityandsecurityDataadministrationConcurrentaccessandcrashrecovery故障修复,Overall:
Reducedapplicationdevelopmenttime,DataModels,Adatamodelisacollectionoftoolsorconceptsfordescribingdata,themeaningofdata,datarelationshipsanddataconstraintsObject-basedlogicalmodels基于对象的逻辑模型Entity-RelationshipModel(ERModel),id,Borrower,Customer,Loan,Customer-name,Customer-street,Customer-city,Loan-number,amount,COMP231,21,DataModelsThreedifferentrecord-basedlogicalmodelsareusuallyused.Theyare:
HierarchicalModel,NetworkModel,网状模型RelationalModel,Record-basedLogicalModelsRelationalModel关系模型,Mainconcept:
relation,basicallyatablewithrowsandcolumns.AcolumnisalsocalledafieldorattributeOthermodelssuchastheNetworkModel,HierarchicalModel,层次模型object-orientedmodel,面向对象objected-relationalmodelWewillfocusonthedominantRelationalmodel.Adescriptionofdataintermsofadatamodeliscalledaschema.图表,COMP231,22,DataAbstraction,COMP231,23,Hidecertaindetailsofhowdataisstoredandmaintained,Physicallevel:
物理层howandwheredataareactuallystored,lowleveldatastructuresarespecifiedatthislevelConceptuallevel(logicallevel):
逻辑层describeswhatdatashouldbestoredinthedatabase,andrelationshipandsemanticsofthedataViewlevel:
视图层Relevantpartialviewofthedatabasetobeparticulartypeofusers,DataAbstraction,LogicalLevelPhysicalLevel,ViewLevelView1View2View3,Viewn,COMP231,24,COMP231,25,DataAbstraction,ConceptualLevel,PhysicalarrangementofrecordsbyanindextreeD43389,C73334,F89328,A34455,C23444,E32399,G29021,DataAbstraction,Aviewwhenpostingthegradestoallstudents,Viewsofacompanywithdataonemployees,departments,products,Payrollsection:
viewonemployees,departments,salariesSalesdepartment:
viewonproducts,prices,sales,customersPurchasingdepartment:
viewonparts,withpricing,COMP231,26,DataIndependence,COMP231,27,PhysicalDataIndependenceChangeofphysicaldatabaseschemaswithoutchangeofconceptualdatabaseschemasorapplicationprogramsLogicaldataindependenceChangeofconceptualschemaswithoutchangeofexternalviews,Whatlanguagesdoesthecomputerspeak?
COMP231,28,DataDefinitionLanguage(DDL)资料定义语言,由CREATE、ALTER与DROP三个语法所组成,Alanguagethatspecifiesdataschemas,DataManipulationLanguage(DML)数据操纵语言,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除,Alanguagetofacilitatetheretrieval,updateofdatainthedatabase,Forretrieval,wequerythedatabasewiththequerylanguage,whichispartoftheDML,COMP231,29,ALTER是负责数据库对象修改的指令,相较于CREATE需要定义完整的数据对象参数,ALTER则是可依照要修改的幅度来决定使用的参数,因此使用上并不会太困难,例如:
ALTERTABLEdoc_exaADDcolumn_bVARCHAR(20)NULL;-在資料表doc_exa中加入一個新的欄位,名稱為column_b,資料型別為varchar(20),允許NULL值;ALTERTABLEdoc_exbDROPCOLUMNcolumn_b;-在資料表doc_exb中移除column_b欄位。
目前大多数的DBMS都支持对数据库对象的DDL操作,部分数据库(如PostgreSQL)可把DDL放在交易指令中,也就是它可以被撤回(Rollback),COMP231,30,Peoplewhodealwithdatabases,COMP231,31,DatabaseAdministrator(DBA):
Person(s)whohascentralcontroloverthedatabaseandisresponsibleforthefollowingtasks,Schemadefinition/modificationStoragestructuredefinition/modificationAuthorizationofdataaccessIntegrityconstraintspecificationMonitoringperformanceRespondingtochangesinrequirements,Peoplewhodealwithdatabases,COMP231,32,ApplicationProgrammersEmbedDMLcallsinprogramwritteninahostlanguage(e.g.,Cobol,C,Java).(DMLstandsfordatamanipulationlanguage),e.g.,programsthatgeneratespayrollchecks,transferfundsbetweenaccounts,SophisticatedusersFormrequestindatabasequerylanguageNaveusersInvokesoneofthepermanentapplicationprogramsthathavebeenwrittenpreviouslye.g.transfertransferfundbetweenaccounts,StructureofaDBMS,QueryOptimizationandExecutionRelationalOperatorsFilesandAccessMethodsBufferManagement缓冲器DiskSpaceManagement,COMP231,33,History,COMP231,34,ThefirstDBMSwasdesignedbyBachmanatGEinearly1960sIn1970CoddatIBMproposedanewdatarepresentationframeworkcalledtherelationaldatamodel.TheSQLqueryforrelationaldatabases,developedaspartofIBMsSystemRproject,wasstandardizedinthelate1980s.Thecurrentstandard,SQL-92,wasadoptedbyANSI(AmericanNationalStandardsInstitute)andISO(InternationalStandardsOrganization).,History,COMP231,35,Inlate1980sand1990s,severalvendors(e.g.,IBMsDB2,Oracle8)haveextendedtheirsystemswiththeabilitytostorenewdatatypessuchasimagesandtext.Specializedsystemsdevelopedfordatawarehouses,consolidatingdatafromseveraldatabases.EnteringtheInternetAge,anewmarkuplanguageXMLisproposedfordataaccessthroughaWebbrowser.Asmoreandmoredataarecollected,companiesarealsointerestedtomineusefulinformationfromtheirdata.,ACID,是指数据库管理系统(DBMS)在写入/更新资料的过程中,为保证事务(transaction)是正确可靠的,所必须具备的四个特性:
原子性(atomicity,或称不可分割性)、一致性(consistency)、隔离性(isolation,又称独立性)、持久性(durability),COMP231,36,原子性:
一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
COMP231,37,一致性:
在事务开始之前和事务结束以后,数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
COMP231,38,隔离性:
当两个或者多个事务并发访问(此处访问指查询和修改的操作)数据库的同一数据时所表现出的相互关系。
事务隔离分为不同级别,包括读未提交(Readuncommitted)、读提交(readcommitted)、可重复读(repeatableread)和串行化(Serializable)。
COMP231,39,持久性:
在事务完成以后,该事务对数据库所作的更改便持久地保存在数据库之中,并且是完全的