A first course in Database Systems(1)VIP

.,AFirstCourseinDatabaseSystems,Departmentofcomputersciencewithoutallowingtheactionofoneusertoaffectotherusers;withoutallowingsimultaneousaccessestocorruptthedataaccidentally.,.,1.1TheEvolutionofDatabaseSystems,Databasesystemsevolvedfromfilesystems.,DataredundancyandinconsistencyMultiplefileformats,duplicationofinformationindifferentfiles.DifficultyinaccessingdataNeedtowriteanewprogramtocarryouteachnewtask.Dataisolationmultiplefilesandformats.IntegrityproblemsIntegrityconstraints(e.g.accountbalance0)become“buried”inprogramcoderatherthanbeingstatedexplicitly.Hardtoaddnewconstraintsorchangeexistingones.,.,1.1TheEvolutionofDatabaseSystems,AtomicityofupdatesFailuresmayleavedatabaseinaninconsistentstatewithpartialupdatescarriedout.Example:Transferoffundsfromoneaccounttoanothershouldeithercompleteornothappenatall.ConcurrentaccessbymultipleusersConcurrentaccessedneededforperformance.Uncontrolledconcurrentaccessescanleadtoinconsistencies.Example:Twopeoplereadingabalanceandupdatingitatthesametime.SecurityproblemsHardtoprovideuseraccesstosome,butnotall,data.,.,1.1TheEvolutionofDatabaseSystems,Late1960sand1970s:HarddisksallowdirectaccesstodataNetworkandhierarchicaldatamodelsinwidespreaduseTheearlyDBMSdidntsupporthigh-levelquerylanguages.,.,1.1TheEvolutionofDatabaseSystems,1970:TedCodddefinestherelationaldatamodelWouldwintheACMTuringAwardforthisworkIBMResearchbeginsSystemRprototypeMainidea:organizeallthedataindatabaseastablescalledrelations.Queriescouldbeexpressedinaveryhigh-levellanguagetoincreasetheefficiencyofdatabaseprogrammers.,.,1.1TheEvolutionofDatabaseSystems,1980s:ResearchrelationalprototypesevolveintocommercialsystemsSQLbecomesindustrialstandardParallelanddistributeddatabasesystemsObject-orienteddatabasesystems,1990s:Largedecisionsupportanddata-mining(数据挖掘)applicationsLargemulti-terabytedatawarehousesEmergenceofWebcommerce,2000s:XMLandXQuerystandardsAutomateddatabaseadministration,.,1.2OverviewofaDatabaseManagementSystem,ADBMSisapowerfultoolforcreatingandmanaginglargeamountsofdataefficientlyandallowingittopersistoverlongperiodsoftime,safely.,.,1.2OverviewofaDatabaseManagementSystem,.,1.2OverviewofaDatabaseManagementSystem,Application1,Application2,.,Applicationn,DBMS,database,DatabaseManagementSystem,.,1.2OverviewofaDatabaseManagementSystem,.,Storagemanager,Buffermanager,read/writepages,buffers,index/file/recordmanger,pagecommands,data,metadata,indexs,Executionengine,index,file,andrecordrequests,Querycompiler,Queryplan,Queries,updates,User/application,Transactionmanager,DDLcompiler,DDLcommands,Databaseadministrator,Transactioncommands,Loggingandrecovery,Concurrencycontrol,Locktable,logpages,metadata,Metadata,statistics,单线框系统成分双线框内存中的数据结构实线控制和数据流虚线数据流,.,1.2OverviewofaDatabaseManagementSystem,Twotypesofuser:ConventionalusersandapplicationprogramsAdatabaseadministrator(DBA)ThreetypesofinputstotheDBMS:Queries:questionsaboutthedatatwoways:SQL,APIModifications:operationstomodifythedataSchemamodifications:commandstochangetheschemaofthedatabaseorcreateanewdatabasebydatabaseadministrators,DDLcommands,.,1.2OverviewofaDatabaseManagementSystem,Queryprocessor：QuerycompilerTranslatesthequeryintoaninternalformcalledaqueryplan.Aqueryparser(分析器)AquerypreprocessorAqueryoptimizer(优化器)ExecutionengineItgetsthedatafromthedatabaseintobuffersinordertomanipulatethatdata.Itneedstointeractwiththeschedulertoavoidaccessingdatathatislocked,andwiththelogmanagertomakesurethatalldatabasechangesareproperlylogged.,.,1.2OverviewofaDatabaseManagementSystem,Storage:Aplacewheredataisstored,containsdataandmetadataData:thecontentsofthedatabaseitself.Metadata(元数据):thedatabaseschemathatdescribesthestructureof,andconstraintson,thedatabase.Indexes:datastructuresthatsupportefficientaccesstothedata.Indexesarepartofthestoreddata,andadescriptionofwhichattributeshaveindexesispartofthemetadata.,.,1.2OverviewofaDatabaseManagementSystem,Storagemanagerandbuffermanager:managesthereadingandwritingofinformationincludesthestoragemanagerandthebuffermanager:storagemanager:keepstrackofthelocationoffilesonthediskandobtainstheblockorblockscontainingafileonrequestfromthebuffermanager.block：4K-16Kbuffermanager:partitiontheavailablemainmemoryintobuffers.page：1K-4K,.,1.2OverviewofaDatabaseManagementSystem,Transaction:agroupofoperationsthatmustappeartohavebeenexecutedtogethersequentially,asaunit.Properties:ACIDAtomicity:eitherallofatransactionbeexecutedornoneofitis.Consistency:keepconsistentstate.Isolation:Whentwoormoretransactionsrunconcurrently,theireffectsmustbeisolatedfromoneanother.Durability(持久性):Ifatransactionhascompleteditswork,itseffectshouldnotgetlostwhilethesystemfail,evenifitfailsimmediatelyafterthetransactioncompletes.,.,1.2OverviewofaDatabaseManagementSystem,Transactionmanager:beresponsiblefortheintegrityofthesystemLoggingConcurrencycontrol:lockDeadlockresolution,.,1.2OverviewofaDatabaseManagementSystem,Client/Server(C/S)architecture,DBClient,DBClient,DBServer,JavaC/C+PowerBuilderDelphiVB,MSAccessSybaseASAMySQLOracleIBMDB2,sqlrequest,sqlrequest,reply,reply,Client:theprocesssendingqueriesorothercommands.Server:theprocessexecutingqueriesorothercommands,.,1.3OutlineofDatabase-SystemsStudies,Ideasrelatedtodatabasesystemcanbedividedintothreebroadcategories：Design：Howtobuildausefuldatabase?Requirementanalysis,datamodeling,relationalmodeling.Programming：Howtoexpressqueriesandotheroperationsondatabase?Iimplementation：HowtobuildaDBMS?,.,1.3.1DatabaseDesign,Chapter2and3coverdesign.Chapter2:TheEntity-RelationshipDataModelChapter3:TheRelationalDataModel,.,1.3.3Example,Wellbuildamarketingdatabasesystemforasalecompany(supermarket).Itwillmanageallthefollowinginformation:1.Managealldepartmentsinformationinthecompany(suchasShanghaisaledepartment,JiangSusaledepartment).Alsomanageeverysalesmaninformationinthosedepartmentsincludingexclusiveemployeenumber,IDcardnumber,andsomeprivateinformation(suchasname,gender,birthdayandphonenumber).Bythewayoneofsalesmenwillactasthedepartmentmanagerinhisdepartment.,.,1.3.3Example,2.Manageagroupofcustomers:name,province,city,companyname,phonenumber.3.Managealltheproductsinformation:manufacturers(e.g.Chunlan,Hailer),types(e.g.motorcycle,airconditioner),specifications(e.g.MT125,RE1500),prices,descriptions.4.Managesalesorderwhichrecordeachdealhasbeendone.Notes:everysalesordercontainsanuniqueorderNo.,signdate,acorrespondingcustomer,asalesman,andatleastonekindofproducts.Anyproductintheordershouldhaveitsquantityandunitpricewhichwillbeusedtocalculatethetotalprices.,.,.,RelationalDataModel：Customer(custid,name,prov,city,phone,unit)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,singlecost,quantity),.,1.3.1DatabaseDesign,Chapter5through8coverdatabaseprogramming.Chapters5RelationalAlgebraChapters6TheDatabas