数据库管理系统概述英文版课件：1 introduction to databases.pptx

数据库管理系统概述英文版课件：1 introduction to databases.pptx

《数据库管理系统概述英文版课件：1 introduction to databases.pptx》由会员分享，可在线阅读，更多相关《数据库管理系统概述英文版课件：1 introduction to databases.pptx（40页珍藏版）》请在冰豆网上搜索。

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,持久性：

在事务完成以后，该事务对数据库所作的更改便持久地保存在数据库之中，并且是完全的