DatabaseSystems數(shù)據(jù)庫(kù)管理系統(tǒng)教學(xué)課件

ReferencesRaghuRamakrishnan/JohannesGehrke,DatabaseManagementSystems,2ndEd.,清華大學(xué)出版社/McGraw-Hill,2000C.J.Date,數(shù)據(jù)庫(kù)系統(tǒng)導(dǎo)論（第7版）,機(jī)械工業(yè)出版社WhatisDBMS?NeedforinformationmanagementAverylarge,integratedcollectionofdata.Modelsreal-worldenterprise.Entities(e.g.,students,courses)Relationships(e.g.,LiuWeiistaking70420403)ADatabaseManagementSystem(DBMS)

isasoftwarepackagedesignedtostoreandmanagedatabases.WhyUseaDBMS?Dataindependenceandefficientaccess.Dataintegrityandsecurity.Uniformdataadministration.Concurrentaccess,recoveryfromcrashes.ReplicationcontrolReducedapplicationdevelopmenttime.FileSystemsprogram1datadescription1program2datadescription2program3datadescription3File1File2File3DatabaseManagementdatabaseDBMSApplicationprogram1(withdatasemantics)Applicationprogram2(withdatasemantics)Applicationprogram3(withdatasemantics)descriptionmanipulationcontrolWhyStudyDatabases??Shiftfromcomputationtoinformationatthe“l(fā)owend”:accesstophysicalworldatthe“highend”:scientificapplicationsDatasetsincreasingindiversityandvolume.Digitallibraries,interactivevideo,HumanGenomeproject,e-commerce,sensornetworks...needforDBMS/dataservicesexplodingDBMSencompassesseveralareasofCSOS,languages,theory,AI,multimedia,logic?DatabaseSystemsKeyTermscont.DatabaseSystemAdatabase,adatabasemanagementsystemandappropriatehardwareandpersonnel.number,DesignationHoursworked,PayrateInsurance,PensionDatabaseSystem:HardwareSetofphysicaldevicesonwhichadatabaseresides.Itconsistsofoneormorecomputers,diskdrives,CRTterminals,printers,tapedrives,connectingcablesandotherauxiliaryandconnectinghardware.DatabaseSystem:Hardwarecont.ClientClientServerTAPEPrinterDatabaseSystem:SoftwareAdatabasesoftwareincludestwotypesofsoftwareGeneral-purposedatabasemanagementsoftware,usuallycalledthedatabasemanagementsystem(DBMS)ApplicationsoftwarethatusesDBMSfacilitiestomanipulatethedatabasetoachieveaspecificbusinessfunction,suchasprovidingreportsordocuments,whichcanbeusedbyusers.DatabaseSystem:Softwarecont.ApplicationsoftwareisgenerallywrittenstandardprogramminglanguagesuchasC,oritmaybewritteninalanguage(commonlycalledafourth-generationlanguage)suppliedwiththeDBMS.TheseprogramsutilisethecommandlanguageoftheDBMSandmakeuseoftheinformationcontainedinthedatadictionary.DatabaseSystem:Softwarecont.UserInterfaceLanguage,menusandotherfacilitiesbywhichusersinteractwithvarioussystemcomponents,suchasapplicationprograms,theDBMSComputer-aidedsoftwareengineering(CASE)toolsAutomatedtoolsusedtodesigndatabasesandapplicationprograms.DataDictionary/Directory(Repository)Asubsystemthatkeepstrackofthedefinitionsofalldataitemsinthedatabase.relationshipsthatexistsbetweenvariousdatastructures.indexesthatareusedtoaccessdataquickly.screenandreportformatdefinitionsthatmaybeusedbyvariousapplicationprograms.DataDictionaryDefinitionsofdataitemsinthedatabaseincludes:Elementary-leveldataitems(fields),groupandrecord-leveldatastructures,andfilesorrelationaltables.Datathatdescribethepropertiesorcharacteristicsofotherdata.Someofthesepropertiesincludedatadefinitions,datastructuresandrulesorconstraints.Itemname,thedatatype,length,minimumandmaximumallowablevalues(whereappropriate)andabriefdescriptionofeachdataitem.Metadataallowdatabasedesignersanduserstounderstandwhatdataexist,whatthedatamean.Datawithoutclearmeaningcanbeconfusing,misinterpretedorerroneous.MetadataE.g.DataItem ValueName Type Length MinMax DescriptionName Character 30 EmployeeNameID Number 9 EmployeeNo.Dept Character 10 Dept.No.Age Integer 2 1860 EmployeeAgeName Character 15 Dept.NameManager Number 9 Mgr.Emp.No.EmployeeNo.(ID)uniqueManagerisanemployeeoftheorganisationMetadataDatabaseSystem:PeopleTwodifferenttypesofpeople(usersandpractitioners)areconcernedwiththedatabase.Userswhoneedinformationfromthedatabasetocarryouttheirprimarybusinessresponsibilitye.g.Executives,managers,staff,clericalpersonnelDatabaseSystem:Peoplecont.Practitionerspeopleresponsibleforthedatabasesystemanditsassociatedapplicationsoftware.e.g.Databaseadministrators,analysts,programmers,databaseandsystemdesigners,informationsystemsmanagers.ComponentsofaDatabaseSystemFourcomponents:People,H/W,S/W,DataPractitioners(analystsanddatabasedesigners)inconsultationwithusersidentifydataneedsanddesigndatabasestructurestoaccommodatetheseneeds.ThedatabasestructuresarespecifiedtotheDBMSthroughthedatadictionary.ComponentsofaDBScont.Usersenterdataintothesystembyfollowingspecifiedprocedures.Theentereddataaremaintainedonhardwaremediasuchasdisksandtapes.Applicationprogrammesthataccessthedatabasearewrittenbypractitionersanduserstoberunoncomputers.ComponentsofaDBScont.DATABASEDATADICTIONARY/DIRECTORYApplicationProgramApplicationProgramApplicationProgramDBMSComputerTerminalsPRINTERAnalystUserKeyTermscont.DatabaseManagementSystemSystemssoftwarethatfacilitatesthemanagementofadatabase.E.g.Oracle,Access,SQLServerDatabaseManagementSystem(DBMS)DBMSAnapplicationsoftwarethatorganisesdataintorecordsinoneormoredatabasesandallowsorganising,accessingandsortingofthedatainavarietyofformats.DBMScont.RelationalDBMSMostcommontypeofDBMS.Dataelementsarestoredindifferenttablesmadeupofrowsandcolumns.Relatesdataindifferenttablesthroughtheuseofcommondataelement(s).ComponentsofaDBMSDATABASEDATADICTIONARY/DIRECTORYUSERPROGRAMMERDataDictionary/DirectorySubsystemSecurityandIntegritySubsystemDatabaseAccessSubsystemUserQueryandReportingFacilitiesApplicationProgramDevelopmentFacilitiesDBMSUSERPROGRAMMERAtimesharingcomputersystemAlooselyortightlycoupledmultiprocessorsystemAdatabasesystemwhichresidesatoneofthenodesofanetworkofcomputers-thisisacentralizeddatabaseonanetworknodeWhatisnotaDDBS?DataSecurityThedatabaseisavaluableresourceneedingprotection.TheDBMSprovidesdatabasesecuritybylimitingaccesstothedatabasetoauthorisedpersonnel.Authoriseduserswillgenerallyberestrictedastotheparticulardatatheycanaccessandwhethertheycanupdateit.DataSecurityAccessisoftencontrolledbypasswordsandbydataviews,whicharedefinitionsofrestrictedportionsofthedatabase.1Dataitems234DataIntegrityTheintegrityandconsistencyofthedatabaseareprotectedviaconstraintsonvaluesthatdataitemscanhaveandbybackupandrecoverycapabilitiesprovidedwithintheDBMS.Dataconstraintdefinitionsaremaintainedinthedatadictionary.DataIntegrityBackupandrecoveryaresupportedbysoftwarethatautomaticallylogschangestothedatabaseandprovidesforameansofrecoveringthecurrentstateofthedatabaseincaseofsystemfailure.ConcurrentDataAccessOneofthechieffunctionsoftheDBMSistosupporttheaccess,retrievalandupdateofdatainthedatabase.TheDBMSprovidesthephysicalmechanismsallowingmultipleuserstoaccessavarietyofrelateddataquicklyandefficiently.User-OrientedDataManipulationDBMSprovidesuser-orienteddatamanipulationtools.Easy-to-usequerylanguagesallowuserstoformulatequeriesandrequestone-timereportsdirectlyfromthedatabase.Oftenquerylanguageswillcontainfacilitiestoformattheresultsofqueriesasreports.DataManipulationReportgeneratorshavemorepowerfulreportingfacilitiesthanthoseinthequerylanguage.ApplicationDevelopmentTheDBMScommonlyprovidessignificantassistancetotheapplicationprogrammer.Toolsforscreen,menuandreportgenerationapplicationgeneratorscompilersdataandviewdefinitionfacilitiesApplicationDevelopmentModerndatabasesystemsprovidelanguagecomponentsthataremuchmorepowerfulthanthoseoftraditionallanguages,makingtheprogrammingprocessitselfconsiderablymoreefficient.Developer2000/PowerBuilderforOracleVisualBasicforMicrosoftSQLserverDatabaseApplicationsDatabasesrangefromthoseforasingleuserwithadesktopcomputertothoseonmainframecomputerswiththousandsofusers.PersonaldatabasesWorkgroupdatabasesDepartmentaldatabasesEnterprisedatabasesPersonalComputerDatabasesDesignedtosupportoneuserwithastandardalonePC.E.g.asalespersonkeepingtrackofthiscustomerinformationwithcontactdetails.WorkgroupDatabasesArelativelysmallteamofpeople(lessthan25)whocollaborateonthesameprojectorapplication.E.g.asoftwaredevelopmentteammaintainingalistofsoftwareobjects.DepartmentDatabasesAdepartmentisafunctionalunitofanorganisation.Itislargerthanaworkgroup.Departmentdatabasesaredesignedtosupportthevariousfunctionsandactivitiesofadepartment.E.g.apersonneldatabasethatisdesignedtotrackdataconcerningemployees,jobs,skillsandjobassignments.EnterpriseDatabasesAnenterpriseisonewhosescopeistheentireorganisationorenterprise.Suchdatabasesareintendedtosupportorganisation-wideoperationsanddecisionmaking.E.g.alargehealthcareorganisationthatoperatesagroupofmedicalcentre'sincludinghospitals,clinicsandnursinghomes.EnterpriseDatabasescont.Anenterprisedatabasedoessupportinformationneedsfrommanydepartments.Themostimportanttypeofenterprisedatabasetodayiscalledadatawarehouse.DatawarehouseAnintegrateddecisionsupportdatabasewhosecontentisderivedfromthevariousoperationaldatabases.DatabaseEnvironmentMarketingPurchasingAccountingMarketingSalesAdvertisingAccountingAccountsReceivableAccountsPayableCorporateDatabase3LevelANSI/SPARCArchitectureMadedatabasesmoreindependentofapplicationBecameastandardfortheorganisationofDBMS3LevelsExternalUser’sandApplication’sviewofdataConceptualLogicalDataModelPhysicalPhysicaldataModelExternal(Sub)SchemadefinestheexternalviewofdataasseenbyauserorprogramConceptualSchemadefinesthelogicalviewofdataasseenbyallusersandprogramsPhysical(Internal)SchemadefinesthephysicalviewofdataasseenbyaDBMSuserauseri/programjprogramxsub-schemaasub-schemaisub-schemazconceptualschemaphysicalschema3LevelArchitectureDatabasesName(20characters)Address(40characters) NID(10char)Designation(15char)PhysicalViewdatabaseA.B.C.DeSilva|222,GalleRoad,Colombo| 650370690V|SeniorLecturerTheDBMSmustknowexactphysicallocationprecisephysicalstructureEmployeerecordPhysicalViewPhysicalviewprovidesthediskdrives,physicaladdresses,indexesandpointers.PhysicalDatabaseDesignistheresponsibilityoftheDatabaseAdministrator(DBA).Nouserisconcernedwiththisview.

PhysicaldevicestocontainthedataAccessmethodstoretrieveandupdatedatamaintainandimprovedatabaseperformancedatabaseLogicalViewAddress

222,GalleRoad,ColomboTheuser/applicationmustknowexistencelogicalreferenceEmployeeName

A.B.C.DeSilvaDesignationSeniorLecturerNID

650370690VLogicalViewThisisasinglelogicaldescriptionofalldataelementsandtheirrelationships.Itistheresultoftheconceptualdesignwhichinvolvesanalysisofallusersinformationneedsanddatadefinitionofdataitemsneedstomeetthem.SQLCREATETABLEstatementisusedtodefinethedataelements.databaseExternalViewDepartment

Dept.ofComputerScienceTheuser/applicationseeauthoriseddataownformatLecturerName

A.B.C.DeSilvaDesignationSeniorLecturerAge

35ExternalViewThisconsistsofuserviewsofthedatabase.Eachdefinableusergroupwillhaveitsownviewofthedatabase.Eachoftheseviewsgivesauser-orienteddescriptionofthedataelementsandrelationshipsofwhichtheviewiscomposed.Alogicaldescriptionofsomeportionofthedatabasethatisrequiredbyausertoperformsometask.E.g.Benefitapplicationuserwillviewpartoftheemployeedata,excludingdatasuchasdateofbirthandsalary.UserViewExternalViewItcanbederiveddirectlyfromtheconceptualschema.Thecollectionofalluserviewsistheexternallevel.SQLCREATEVIEWstatementisusedtocreatetheseviewsandSQLGRANTstatementisusedtorestrictitsusetoausergroup.ExternalViewsAllowstohideunauthoriseddatae.g.salary,dob

provideuserview

e.g.viewemployeename,designation,departmentdatatakenfromemployeeanddepartmentfilesderivenewattributese.g.agederived

from

dob

or

nidExternalViewcont.ExternalViewsAllowsto

changeunitofmeasuremente.g.showageinyearsormonthsdefinesecuritylevelse.g.updateaccesstoemployeefileread-onlytodepartmentfileExternalViewcont.MappingbetweenLevelsDBMSmaportranslatefromoneleveltoanother.External ConceptualConceptual PhysicalDatabaseexistsinrealityonlyattheinternallevel.userauseri/programjprogramxsub-schemaasub-schemaisub-schemazconceptualschemaphysicalschemaDatabasesDDLSDDLDML/SQLPractitioners:DBAUsersDataDefinitionLanguage(DDL)-isthelanguagecomponentofaDBMSthatdefineseachdataelementasitappearsinthedatabase.Sub-SchemaDataDefinitionLanguage(SDDL)-isthelanguagecomponentofaDBMSthatdefinesdataelementsasitshouldappeartotheendusersandprogrammers.Practitioners:DBADataManipulationLanguage(DML)-isalanguageassociatedwithaDBMSthatisemployedbyendusersandprogrammerstomanipulatedatainthedatabase.StructuredQueryLanguage(SQL)

-pronouncedassequel,isthestandarddatamanipulationforrelationalDBMSs.UserCharacteristicsofEarlyDBMSCentralisedStorageManagementTransactionManagementRecoveryFacilitiesSystemMaintainedAccessPathsCharacteristicsofModernDBMSQueryprocessingandoptimisationTransactionmanagementConcurrencycontrolDatabaserecoveryDatabasesecurityandauthorisationDistributeddatabasesDatawarehousinganddataminingFunctionofaDBMSFunctionsofaDBMSareconcernedwithprovidingefficientflexibledataprocessingcapabilitieswithoutcompromisingdatavalidity.MainFunctionsare:ShareddataControlredundancyDataintegrityDatasecurityCapabilitiesofaDBMSCapabilitiesvaryconsiderable,butbasicoperationsare:DatadefinitionDataentryDatamanipulationDatadisplayAdvantagesProgram-dataindependenceMinimaldataredundancyImproveddataconsistencyImproveddatasharingIncreasedproductivityofapplicationdevelopmentEnforcementofstandardsImproveddataqualityImproveddataaccessibilityandresponsivenessReducedprogrammaintenanceDatabaseApproachTheseparationofdatadescriptions(metadata)fromtheapplicationprogramsthatusethedata.Inthedatabaseapproachdatadescriptionsarestoredinacentrallocationcalledthedatadictionary.Thispropertyallowsanorganisation’sdatatochangeandevolve(withinlimits)withoutchangingtheapplicationprogramthatprocessthedata.Program-Data/DataIndependenceDatafilesareintegratedintoasingle,logicalstructure.Eachprimaryfactisrecorded(ideally)inonlyoneplaceinthedatabase.E.g.Employeedatanotwiththepayrollandbenefitfiles.Note:Dataredundancyisnoteliminatedentirely.Somedataitemswillappearinmorethanoneplace(e.g.employeeno.)torepresenttherelationshipwithothers.MinimalDataRedundancyByeliminating(orcontrolling)dataredundancy,wegreatlyreducetheopportunitiesforinconsistency.E.g.employeeaddressisstoredonlyonceandhencewecannothavedisagreementonthestoredvalues.

Also,updatingdatavaluesisgreatlysimplifiedandhaveavoidthewastedstoragespace.ImprovedDataConsistencyAdatabaseisdesignedasasharedcorporateresource.Authorisedusersaregrantedpermissiontousethedatabase,andeachuser(orgroupofusers)isprovidedoneormoreuserviewstofacilitatethisuse.E.g.employeedatacommontopayroll,benefitapplicationswillbesharedamongdifferentusers.ImprovedDataSharingAmajoradvantageofthedatabaseapproachisthatitgreatlyreducesthecostandtimefordevelopingnewbusinessapplications.Programmercouldconcentrateonthespecificfunctionsrequiredforthenewapplication,withouthavingtoworryaboutdesignorlow-levelimplementationdetails;asrelateddatahavealreadybeendesignedandimplemented.IncreasedProductivityofApplicationDevelopmentDBMSprovidesanumberofhigh-levelproductivitytoolssuchasformsandreportgenerationsandhigh-levellanguagesthatautomatesomeoftheactivitiesofdatabasedesignandimplementation.IncreasedProductivityofApplicationDevelopmentcont.Whenthedatabaseapproachisimplementedwithfullmanagementsupport,thedatabaseadministrationfunctionshouldbegrantedsingle-pointauthorityandresponsibilityforestablishingandenforcingdatastandards.Standardsincludenamingconventions,dataqualitystandardsanduniformproceduresforaccessing,updatingandprotectingdata.PowerfulsetoftoolsfordevelopingandenforcingthesestandardsareavailableforsomeDBMS.EnforcementofStandardsAnumberoftoolsandprocessesareavailabletoimprovedataquality.DatabasedesignerscanspecifyintegrityconstraintsthatareenforcedbytheDBMS.Oneoftheobjectivesofadatawarehouseenvironmentistocleanupoperationaldatabeforetheyareplacedinthedatawarehouse.ConstraintArulethatcannotbeviolatedbydatabaseusers.ImprovedDataQualityWithrelationaldatabase,enduserswithoutprogrammingexperiencecanoftenretrieveanddisplaydata,evenwhenitcrossestraditionaldepartmentalboundaries.English-likequerylanguageSQLandquerytoolssuchasQuery-By-Exampleprovidesuchfacilities.ImprovedDataAccessibilityandResponsivenessStoreddataarechangedfrequentlyforvarietyofreasonssuchasnewdataitemstypesareadded,anddataformatschange(e.g.dateformatfromtwo-digittofourdigit).Dataindependenceallowstoreducetheprogrammaintenancetime.ReducedProgramMaintenanceDisadvantagesDBMSaremorevulnerablethanfile-basedsystembecauseofthecentralisednatureofalargeintegrateddatabase.Ifafailureoccurstherecoveryprocessismorecomplexandsometimesmayresultsinlosttransactions.Hardware,softwareandpersonnelcostarehigherforDBMS.DatabaseApproachDataModelsAdatamodel

isacollectionofconceptsfordescribingdata.A

schema

isadescriptionofaparticularcollectionofdata,usingtheagivendatamodel.Therelationalmodelofdata

isthemostwidelyusedmodeltoday.Mainconcept:relation,basicallyatablewithrowsandcolumns.Everyrelationhasaschema,whichdescribesthecolumns,orfields.LevelsofAbstractionManyviews,singleconceptual(logical)schema

andphysicalschema.Viewsdescribehowusersseethedata.ConceptualschemadefineslogicalstructurePhysicalschemadescribesthefilesandindexesused.

SchemasaredefinedusingDDL;dataismodified/queriedusingDML.PhysicalSchemaConceptualSchemaView1View2View3Example:UniversityDatabaseConceptualschema:

Students(sid:string,name:string,login:string, age:integer,gpa:real)Courses(cid:string,cname:string,credits:integer)Enrolled(sid:string,cid:string,grade:string)Physicalschema:Relationsstoredasunorderedfiles.IndexonfirstcolumnofStudents.ExternalSchema(View):Course_info(cid:string,enrollment:integer)DataIndependenceApplicationsinsulatedfromhowdataisstructuredandstored.Logicaldataindependence:Protectionfromchangesinlogicalstructureofdata.Physicaldataindependence:Protectionfromchangesinphysicalstructureofdata.

OneofthemostimportantbenefitsofusingaDBMS!ConcurrencyControlConcurrentexecutionofuserprogramsisessentialforgoodDBMSperformance.Becausediskaccessesarefrequent,andrelativelyslow,itisimportanttokeeptheCPUhummingbyworkingonseveraluserprogramsconcurrently.Interleavingactionsofdifferentuserprogramscanleadtoinconsistency:e.g.,checkisclearedwhileaccountbalanceisbeingcomputed.DBMSensuressuchproblemsdon’tarise:userscanpretendtheyareusingasingle-usersystem.Transaction:AnExecutionUnitofaDBKeyconceptistransaction,whichisanatomic

sequenceofdatabaseactions(reads/writes).Eachtransaction,executedcompletely,mustleavetheDBinaconsistentstate

ifDBisconsistentwhenthetransactionbegins.Userscanspecifysomesimpleintegrityconstraints

onthedata,andtheDBMSwillenforcetheseconstraints.Beyondthis,theDBMSdoesnotreallyunderstandthesemanticsofthedata.(e.g.,itdoesnotunderstandhowtheinterestonabankaccountiscomputed).Whynot?Thus,ensuringthatatransaction(runalone)preservesconsistencyisultimatelytheuser’sresponsibility!SchedulingConcurrentTransactionsDBMSensuresthatexecutionof{T1,...,Tn}isequivalenttosomeserialexecutionT1’...Tn’.Beforereading/writinganobject,atransactionrequestsalockontheobject,andwaitstilltheDBMSgivesitthelock.Alllocksarereleasedattheendofthetransaction.(Strict2PLlockingprotocol.)Idea:IfanactionofTi(say,writingX)affectsTj(whichperhapsreadsX),oneofthem,sayTi,willobtainthelockonXfirstandTjisforcedtowaituntilTicompletes;thiseffectivelyordersthetransactions.WhatifTjalreadyhasalockonYandTilaterrequestsalockonY?Whatisitcalled?Whatwillhappen?EnsuringAtomicityDBMSensuresatomicity

(all-or-nothingproperty)evenifsystemcrashesinthemiddleofaXact.Idea: