《数据库系统概念》课件第一章

Chapter1:IntroductionChapter1:Introduction1DatabaseManagementSystem(DBMS)DBMScontainsinformationaboutaparticularenterpriseCollectionofinterrelateddataSetofprogramstoaccessthedataAnenvironmentthatisbothconvenientandefficienttouseDatabaseApplications:Banking:transactionsAirlines:reservations,schedulesUniversities:registration,gradesSales:customers,products,purchasesOnlineretailers:ordertracking,customizedrecommendationsManufacturing:production,inventory,orders,supplychainHumanresources:employeerecords,salaries,taxdeductionsDatabasescanbeverylarge.DatabasestouchallaspectsofourlivesDatabaseManagementSystem(DBUniversityDatabaseExampleApplicationprogramexamplesAddnewstudents,instructors,andcoursesRegisterstudentsforcourses,andgenerateclassrostersAssigngradestostudents,computegradepointaverages(GPA)andgeneratetranscriptsIntheearlydays,databaseapplicationswerebuiltdirectlyontopoffilesystemsUniversityDatabaseExampleAppDrawbacksofusingfilesystemstostoredataDataredundancyandinconsistencyMultiplefileformats,duplicationofinformationindifferentfilesDifficultyinaccessingdataNeedtowriteanewprogramtocarryouteachnewtaskDataisolation—multiplefilesandformatsIntegrityproblemsIntegrityconstraints(e.g.,accountbalance>0)become“buried”inprogramcoderatherthanbeingstatedexplicitlyHardtoaddnewconstraintsorchangeexistingonesDrawbacksofusingfilesystemDrawbacksofusingfilesystemstostoredata(Cont.)AtomicityofupdatesFailuresmayleavedatabaseinaninconsistentstatewithpartialupdatescarriedoutExample:TransferoffundsfromoneaccounttoanothershouldeithercompleteornothappenatallConcurrentaccessbymultipleusersConcurrentaccessneededforperformanceUncontrolledconcurrentaccessescanleadtoinconsistenciesExample:Twopeoplereadingabalance(say100)andupdatingitbywithdrawingmoney(say50each)atthesametimeSecurityproblemsHardtoprovideuseraccesstosome,butnotall,dataDatabasesystemsoffersolutionstoalltheaboveproblemsDrawbacksofusingfilesystemLevelsofAbstractionPhysicallevel:describeshowarecord(e.g.,customer)isstored.Logicallevel:describesdatastoredindatabase,andtherelationshipsamongthedata. type

instructor=record

ID:string;

name:string;

dept_name:string;

salary:integer;end;Viewlevel:applicationprogramshidedetailsofdatatypes.Viewscanalsohideinformation(suchasanemployee’ssalary)forsecuritypurposes.LevelsofAbstractionPhysicalViewofDataAnarchitectureforadatabasesystemViewofDataAnarchitecturefoInstancesandSchemasSimilartotypesandvariablesinprogramminglanguagesSchema

–thelogicalstructureofthedatabaseExample:ThedatabaseconsistsofinformationaboutasetofcustomersandaccountsandtherelationshipbetweenthemAnalogoustotypeinformationofavariableinaprogramPhysicalschema:databasedesignatthephysicallevelLogicalschema:databasedesignatthelogicallevelInstance–theactualcontentofthedatabaseataparticularpointintimeAnalogoustothevalueofavariablePhysicalDataIndependence–theabilitytomodifythephysicalschemawithoutchangingthelogicalschemaApplicationsdependonthelogicalschemaIngeneral,theinterfacesbetweenthevariouslevelsandcomponentsshouldbewelldefinedsothatchangesinsomepartsdonotseriouslyinfluenceothers.InstancesandSchemasSimilartDataModelsAcollectionoftoolsfordescribingDataDatarelationshipsDatasemanticsDataconstraintsRelationalmodelEntity-Relationshipdatamodel(mainlyfordatabasedesign)Object-baseddatamodels(Object-orientedandObject-relational)Semistructureddatamodel(XML)Otheroldermodels:NetworkmodelHierarchicalmodelDataModelsAcollectionoftooRelationalModelRelationalmodel(Chapter2)ExampleoftabulardataintherelationalmodelColumnsRowsRelationalModelRelationalmodASampleRelationalDatabaseASampleRelationalDatabaseDataManipulationLanguage(DML)LanguageforaccessingandmanipulatingthedataorganizedbytheappropriatedatamodelDMLalsoknownasquerylanguageTwoclassesoflanguagesProcedural

–userspecifieswhatdataisrequiredandhowtogetthosedataDeclarative(nonprocedural)

–userspecifieswhatdataisrequiredwithoutspecifyinghowtogetthosedataSQListhemostwidelyusedquerylanguageDataManipulationLanguage(DMDataDefinitionLanguage(DDL)SpecificationnotationfordefiningthedatabaseschemaExample: createtable

instructor(

ID

char(5),

namevarchar(20),

dept_namevarchar(20),

salary

numeric(8,2))DDLcompilergeneratesasetoftabletemplatesstoredinadatadictionaryDatadictionarycontainsmetadata(i.e.,dataaboutdata)DatabaseschemaIntegrityconstraintsPrimarykey(IDuniquelyidentifiesinstructors)Referentialintegrity(referencesconstraintinSQL)e.g.dept_namevalueinanyinstructortuplemustappearindepartmentrelationAuthorizationDataDefinitionLanguage(DDL)SQLSQL:widelyusednon-procedurallanguageExample:FindthenameoftheinstructorwithID22222

select name

from instructor

where

instructor.ID=‘22222’Example:FindtheIDandbuildingofinstructorsinthePhysicsdept.selectinstructor.ID,department.building

frominstructor,department

whereinstructor.dept_name=department.dept_nameand

department.dept_name=‘Physics’

ApplicationprogramsgenerallyaccessdatabasesthroughoneofLanguageextensionstoallowembeddedSQLApplicationprograminterface(e.g.,ODBC/JDBC)whichallowSQLqueriestobesenttoadatabaseChapters3,4and5SQLSQL:widelyusednon-procedDatabaseDesignTheprocessofdesigningthegeneralstructureofthedatabase:LogicalDesign–Decidingonthedatabaseschema.Databasedesignrequiresthatwefinda“good”collectionofrelationschemas.Businessdecision–Whatattributesshouldwerecordinthedatabase?ComputerSciencedecision–Whatrelationschemasshouldwehaveandhowshouldtheattributesbedistributedamongthevariousrelationschemas?PhysicalDesign–Decidingonthephysicallayoutofthedatabase

DatabaseDesignTheprocessofDatabaseDesign?Isthereanyproblemwiththisdesign?DatabaseDesign?IsthereanypDesignApproachesNormalizationTheory(Chapter8)Formalizewhatdesignsarebad,andtestforthemEntityRelationshipModel(Chapter7)ModelsanenterpriseasacollectionofentitiesandrelationshipsEntity:a“thing”or“object”intheenterprisethatisdistinguishablefromotherobjectsDescribedbyasetofattributesRelationship:anassociationamongseveralentitiesRepresenteddiagrammaticallybyanentity-relationshipdiagram:DesignApproachesNormalizationTheEntity-RelationshipModelModelsanenterpriseasacollectionofentitiesandrelationshipsEntity:a“thing”or“object”intheenterprisethatisdistinguishablefromotherobjectsDescribedbyasetofattributesRelationship:anassociationamongseveralentitiesRepresenteddiagrammaticallybyanentity-relationshipdiagram:Whathappenedtodept_nameofinstructorandstudent?TheEntity-RelationshipModelMObject-RelationalDataModelsRelationalmodel:flat,“atomic”valuesObjectRelationalDataModelsExtendtherelationaldatamodelbyincludingobjectorientationandconstructstodealwithaddeddatatypes.Allowattributesoftuplestohavecomplextypes,includingnon-atomicvaluessuchasnestedrelations.Preserverelationalfoundations,inparticularthedeclarativeaccesstodata,whileextendingmodelingpower.Provideupwardcompatibilitywithexistingrelationallanguages.Object-RelationalDataModelsRXML:ExtensibleMarkupLanguageDefinedbytheWWWConsortium(W3C)OriginallyintendedasadocumentmarkuplanguagenotadatabaselanguageTheabilitytospecifynewtags,andtocreatenestedtagstructuresmadeXMLagreatwaytoexchangedata,notjustdocumentsXMLhasbecomethebasisforallnewgenerationdatainterchangeformats.Awidevarietyoftoolsisavailableforparsing,browsingandqueryingXMLdocuments/dataXML:ExtensibleMarkupLanguaStorageManagementStoragemanagerisaprogrammodulethatprovidestheinterfacebetweenthelow-leveldatastoredinthedatabaseandtheapplicationprogramsandqueriessubmittedtothesystem.Thestoragemanagerisresponsibletothefollowingtasks:InteractionwiththefilemanagerEfficientstoring,retrievingandupdatingofdataIssues:StorageaccessFileorganizationIndexingandhashingStorageManagementStoragemanaQueryProcessing1. Parsingandtranslation2. Optimization3. EvaluationQueryProcessing1. ParsingandQueryProcessing(Cont.)AlternativewaysofevaluatingagivenqueryEquivalentexpressionsDifferentalgorithmsforeachoperationCostdifferencebetweenagoodandabadwayofevaluatingaquerycanbeenormousNeedtoestimatethecostofoperationsDependscriticallyonstatisticalinformationaboutrelationswhichthedatabasemustmaintainNeedtoestimatestatisticsforintermediateresultstocomputecostofcomplexexpressionsQueryProcessing(Cont.)AlternTransactionManagement Whatifthesystemfails?Whatifmorethanoneuserisconcurrentlyupdatingthesamedata?AtransactionisacollectionofoperationsthatperformsasinglelogicalfunctioninadatabaseapplicationTransaction-managementcomponentensuresthatthedatabaseremainsinaconsistent(correct)statedespitesystemfailures(e.g.,powerfailuresandoperatingsystemcrashes)andtransactionfailures.Concurrency-controlmanagercontrolstheinteractionamongtheconcurrenttransactions,toensuretheconsistencyofthedatabase.

TransactionManagement WhatifDatabaseUsersandAdministratorsDatabaseDatabaseUsersandAdministratDatabaseSystemInternalsDatabaseSystemInternalsDatabaseArchitectureThearchitectureofadatabasesystemsisgreatlyinfluencedbytheunderlyingcomputersystemonwhichthedatabaseisrunning:CentralizedClient-serverParallel(multi-processor)Distributed

DatabaseArchitectureThearchiHistoryofDatabaseSystems1950sandearly1960s:DataprocessingusingmagnetictapesforstorageTapesprovidedonlysequentialaccessPunchedcardsforinputLate1960sand1970s:HarddisksalloweddirectaccesstodataNetworkandhierarchicaldatamodelsinwidespreaduseTedCodddefinestherelationaldatamodelWouldwintheACMTuringAwardforthisworkIBMResearchbeginsSystemRprototypeUCBerkeleybeginsIngresprototypeHigh-performance(fortheera)transactionprocessingHistoryofDatabaseSystems195History(cont.)1980s:ResearchrelationalprototypesevolveintocommercialsystemsSQLbecomesindustrialstandardParallelanddistributeddatabasesystemsObject-orienteddatabasesystems1990s:Largedecisionsupportanddata-miningapplicationsLargemulti-terabytedatawarehousesEmergenceofWebcommerceEarly2000s:XMLandXQuerystandardsAutomateddatabaseadministrationLater2000s:GiantdatastoragesystemsGoogleBigTable,YahooPNuts,Amazon,..History(cont.)1980s:EndofChapter1EndofChapter1Figure1.02Figure1.02Figure1.04Figure1.04Figure1.06Figure1.06Chapter1:IntroductionChapter1:Introduction34DatabaseManagementSystem(DBMS)DBMScontainsinformationaboutaparticularenterpriseCollectionofinterrelateddataSetofprogramstoaccessthedataAnenvironmentthatisbothconvenientandefficienttouseDatabaseApplications:Banking:transactionsAirlines:reservations,schedulesUniversities:registration,gradesSales:customers,products,purchasesOnlineretailers:ordertracking,customizedrecommendationsManufacturing:production,inventory,orders,supplychainHumanresources:employeerecords,salaries,taxdeductionsDatabasescanbeverylarge.DatabasestouchallaspectsofourlivesDatabaseManagementSystem(DBUniversityDatabaseExampleApplicationprogramexamplesAddnewstudents,instructors,andcoursesRegisterstudentsforcourses,andgenerateclassrostersAssigngradestostudents,computegradepointaverages(GPA)andgeneratetranscriptsIntheearlydays,databaseapplicationswerebuiltdirectlyontopoffilesystemsUniversityDatabaseExampleAppDrawbacksofusingfilesystemstostoredataDataredundancyandinconsistencyMultiplefileformats,duplicationofinformationindifferentfilesDifficultyinaccessingdataNeedtowriteanewprogramtocarryouteachnewtaskDataisolation—multiplefilesandformatsIntegrityproblemsIntegrityconstraints(e.g.,accountbalance>0)become“buried”inprogramcoderatherthanbeingstatedexplicitlyHardtoaddnewconstraintsorchangeexistingonesDrawbacksofusingfilesystemDrawbacksofusingfilesystemstostoredata(Cont.)AtomicityofupdatesFailuresmayleavedatabaseinaninconsistentstatewithpartialupdatescarriedoutExample:TransferoffundsfromoneaccounttoanothershouldeithercompleteornothappenatallConcurrentaccessbymultipleusersConcurrentaccessneededforperformanceUncontrolledconcurrentaccessescanleadtoinconsistenciesExample:Twopeoplereadingabalance(say100)andupdatingitbywithdrawingmoney(say50each)atthesametimeSecurityproblemsHardtoprovideuseraccesstosome,butnotall,dataDatabasesystemsoffersolutionstoalltheaboveproblemsDrawbacksofusingfilesystemLevelsofAbstractionPhysicallevel:describeshowarecord(e.g.,customer)isstored.Logicallevel:describesdatastoredindatabase,andtherelationshipsamongthedata. type

instructor=record

ID:string;

name:string;

dept_name:string;

salary:integer;end;Viewlevel:applicationprogramshidedetailsofdatatypes.Viewscanalsohideinformation(suchasanemployee’ssalary)forsecuritypurposes.LevelsofAbstractionPhysicalViewofDataAnarchitectureforadatabasesystemViewofDataAnarchitecturefoInstancesandSchemasSimilartotypesandvariablesinprogramminglanguagesSchema

–thelogicalstructureofthedatabaseExample:ThedatabaseconsistsofinformationaboutasetofcustomersandaccountsandtherelationshipbetweenthemAnalogoustotypeinformationofavariableinaprogramPhysicalschema:databasedesignatthephysicallevelLogicalschema:databasedesignatthelogicallevelInstance–theactualcontentofthedatabaseataparticularpointintimeAnalogoustothevalueofavariablePhysicalDataIndependence–theabilitytomodifythephysicalschemawithoutchangingthelogicalschemaApplicationsdependonthelogicalschemaIngeneral,theinterfacesbetweenthevariouslevelsandcomponentsshouldbewelldefinedsothatchangesinsomepartsdonotseriouslyinfluenceothers.InstancesandSchemasSimilartDataModelsAcollectionoftoolsfordescribingDataDatarelationshipsDatasemanticsDataconstraintsRelationalmodelEntity-Relationshipdatamodel(mainlyfordatabasedesign)Object-baseddatamodels(Object-orientedandObject-relational)Semistructureddatamodel(XML)Otheroldermodels:NetworkmodelHierarchicalmodelDataModelsAcollectionoftooRelationalModelRelationalmodel(Chapter2)ExampleoftabulardataintherelationalmodelColumnsRowsRelationalModelRelationalmodASampleRelationalDatabaseASampleRelationalDatabaseDataManipulationLanguage(DML)LanguageforaccessingandmanipulatingthedataorganizedbytheappropriatedatamodelDMLalsoknownasquerylanguageTwoclassesoflanguagesProcedural

–userspecifieswhatdataisrequiredandhowtogetthosedataDeclarative(nonprocedural)

–userspecifieswhatdataisrequiredwithoutspecifyinghowtogetthosedataSQListhemostwidelyusedquerylanguageDataManipulationLanguage(DMDataDefinitionLanguage(DDL)SpecificationnotationfordefiningthedatabaseschemaExample: createtable

instructor(

ID

char(5),

namevarchar(20),

dept_namevarchar(20),

salary

numeric(8,2))DDLcompilergeneratesasetoftabletemplatesstoredinadatadictionaryDatadictionarycontainsmetadata(i.e.,dataaboutdata)DatabaseschemaIntegrityconstraintsPrimarykey(IDuniquelyidentifiesinstructors)Referentialintegrity(referencesconstraintinSQL)e.g.dept_namevalueinanyinstructortuplemustappearindepartmentrelationAuthorizationDataDefinitionLanguage(DDL)SQLSQL:widelyusednon-procedurallanguageExample:FindthenameoftheinstructorwithID22222

select name

from instructor

where

instructor.ID=‘22222’Example:FindtheIDandbuildingofinstructorsinthePhysicsdept.selectinstructor.ID,department.building

frominstructor,department

whereinstructor.dept_name=department.dept_nameand

department.dept_name=‘Physics’

ApplicationprogramsgenerallyaccessdatabasesthroughoneofLanguageextensionstoallowembeddedSQLApplicationprograminterface(e.g.,ODBC/JDBC)whichallowSQLqueriestobesenttoadatabaseChapters3,4and5SQLSQL:widelyusednon-procedDatabaseDesignTheprocessofdesigningthegeneralstructureofthedatabase:LogicalDesign–Decidingonthedatabaseschema.Databasedesignrequiresthatwefinda“good”collectionofrelationschemas.Businessdecision–Whatattributesshouldwerecordinthedatabase?ComputerSciencedecision–Whatrelationschemasshouldwehaveandhowshouldtheattributesbedistributedamongthevariousrelationschemas?PhysicalDesign–Decidingonthephysicallayoutofthedatabase

DatabaseDesignTheprocessofDatabaseDesign?Isthereanyproblemwiththisdesign?DatabaseDesign?IsthereanypDesignApproachesNormalizationTheory(Chapter8)Formalizewhatdesignsarebad,andtestforthemEntityRelationshipModel(Chapter7)ModelsanenterpriseasacollectionofentitiesandrelationshipsEntity:a“thing”or“object”intheenterprisethatisdistinguishablefromotherobjectsDescribedbyasetofattributesRelationship:anassociationamongseveralentitiesRepresenteddiagrammaticallybyanentity-relationshipdiagram:DesignApproachesNormalizationTheEntity-RelationshipModelModelsanenterpriseasacollectionofentitiesandrelationshipsEntity:a“thing”or“object”intheenterprisethatisdistinguishablefromotherobjectsDescribedbyasetofattributesRelationship:anassociationamongseveralentitiesRepresenteddiagrammaticallybyanentity-relationshipdiagram:Whathappenedtodept_nameofinstructorandstudent?TheEntity-RelationshipModelMObject-RelationalDataModelsRelationalmodel:flat,“atomic”valuesObjectRelationalDataModelsExtendtherelationaldatamodelbyincludingobjectorientationandconstructstodealwithaddeddatatypes.Allowattributesoftuplestohavecomplextypes,includingnon-atomicvaluessuchasnestedrelations.Preserverelationalfoundations,inparticularthedeclarativeaccesstodata,whileextendingmodelingpower.Provideupwardcompatibilitywithexistingrelationallanguages.Object-RelationalDataModelsRXML:ExtensibleMarkupLanguageDefinedbytheWWWConsortium(W3C)OriginallyintendedasadocumentmarkuplanguagenotadatabaselanguageTheabilitytospecifynewtags,andtocreatenestedtagstructuresmadeXMLagreatwaytoexchangedata,notjustdocumentsXMLhasbecomethebasisforallnewgenerationdatainterchangeformats.Awidevarietyoftoolsisavailableforparsing,browsingandqueryingXMLdocuments/dataXML:ExtensibleMarkupLanguaStorageManagementStoragemanagerisaprogrammodulethatprovidestheinterfacebetweenthelow-leveldatastoredinthedatabaseandtheapplicationprogramsandqueriessubmittedtothesystem.Thestoragemanagerisresponsibletothefollowingtasks:InteractionwiththefilemanagerEfficientstoring,retrievingandupdatingofdataIssues:StorageaccessFileorganizationIndexingandhashingStorageManagementStoragemanaQueryProcessing1. Parsingandtranslation2. Optimization3. EvaluationQueryProcessing1. ParsingandQueryProcessing(Cont.)AlternativewaysofevaluatingagivenqueryEquivalentexpressionsDifferentalgorithmsforeachoperationCostdifferencebetweenagoodandabadwayofevaluatingaquerycanbeenormo