精品课程《DatabasePrinciples数据库原理》PPT

,DatabasePrinciples数据库原理,Textbooks,Required:AFirstCourseinDatabaseSystems,byJeffreyD.UllmanJenniferWidom.Recommended:萨师煊,王珊.数据库系统概论(第三版).高等教育出版社,2000年BooksonSQLServer2000Youmayalsowant:Booksondesignandprogramming:Powerbuilder,PowerDesigner,UML,VB,Delphi,VC,JAVA,C#,ASP,PHP,JSPetc.,2,WhyUsingEnglishTextbook?,MorecomputerEnglishwordsMoreadvancedandnewestinformationBetterinformationretrievalcapabilityMoreinternationalcommunicationchance,3,Whattolearninthisunit?,Basictheories(basic)Systemanddatabasedesign(designer)ApplicationProgramming(programmer)Databasemanagementandmaintenance(databasemanager),4,Syllabus,BackgroundandhistoryofdatabasemanagementsystemsFundamentalsofusingadatabasemanagementsystems:SQL2000.(*extracurricular)DatabasemodelingRelationalmodel:theoreticalbackgroundandoperationsDatabaseLanguageSQLFundamentalsofdatabasemanagementsystems:transactionsandsecurityetc.Databasesystemdesignandpractice,5,Chapter1TheWorldsofDatabaseSystems,EvolutionofDatabaseSystemsArchitectureofDBMSandDBSFutureofDatabaseSystems,1.1EvolutionofDatabaseSystems,1.1.1SomeBasicnotionsDataDatabase(DB)DatabaseManagementSystem(DBMS)DatabaseSystem(DBS).DatabaseAdministrator(DBA),7,BasicNotions,DataDistinctpiecesofinformationrepresentedinaformsuitableforprocessingbycomputer.Datacanexistinavarietyofforms-asdigitalnumbers,text,image,sound,videoandetc.Database(DB)Inessence,itsnothingmorethanacollectionofinformationthatexistsoveralongperiodoftime.Incommonparlance,itreferstoacollectionofdatamanagedbyadatabasemanagementsystem(DBMS)orjustdatabasesystem(DBS).,8,ExampleofDataandDatabase,9,DBMS,DatabaseManagementSystem(DBMS)Acollectionofprogramsthatenablesyoutostore,modify,andextractinformationfromadatabase.TherearemanydifferenttypesofDBMSs,rangingfromsmallsystemsthatrunonpersonalcomputerstohugesystemsthatrunonmainframes.,10,BasicfunctionsofDBMS,DatadefinitionAllowcreatingnewdatabasesandspecifytheirschema(logicalstructureofthedata),viadatadefinitionlanguage(DDL).DatamanipulationAllowefficientaccesstothedata,suchasqueryingandmodifyingthedata,viaquerylanguageordatamanipulationlanguage(DML).OperationmanagementofDBSupportthestorageofverylargeamountsofdataoveralongperiodoftime,assuringdataintegrity,keepingdatasecurefromaccidentorunauthorizeduse.Supportconcurrent,atomicaccesstodata.CreationandmaintenanceofDBSuchasinputtingandtransferringofData,DBbackupandrecovery,performancemonitoring,auditandanalysisetc.,11,DatabaseSystem(DBS),DatabaseSystem(DBS).DB,DBMS,DevelopmentTools,DBapplications,DBAdministrator(DBA)andusers,12,DBMSinComputerSystem,DatabaseAdministrator,DBAPersonwhoisresponsibleformanagementandmaintenanceofDB.TasksDatabasecontentsandstructuresStoragestructureandaccessmethodsDatabasesecurityandintegrationMonitorandimprovedatabaseperformance,13,1.1.2HistoryofDatamanagement,FilesystemsProblems:limitedsupporttodefinitionofdataschema,nodirectlysupporttoDML(DataManipulateLanguage),nosupporttoefficientconcurrent,secureaccessetc.EarlyDBMSThefirstcommercialDBMSappearedinthelate1960s,evolvedfromfilesystems.TheearlyDBMSsusedseveraldatamodelsfordescribingstructureofDB:hierarchicalmodelandnetworkmodel.Problems:nosupporttohigh-levelquerylanguages.,14,HistoryofDatamanagement(cont.）,RelationalDBMSAfamouspaperbyTedCoddin1970,“Arelationalmodelforlargeshareddatabanks”inComm.ACM.Dataorganizedastablescalledrelations.UnliketheuserofearlyDBS,userofarelationalsystemwontbeconcernedwiththestoragestructure,andqueriesareexpressedinaveryhigh-levellanguage(SQL).TodayusedinmostDBMSs.,15,RelationalModel,Basedontables,suchas:,16,Attributes(columnheaders),QueryLanguages,SELECTManagerFROMEmployee,DepartmentWHEREE=ClarkKent”ANDEmployee.Dept=Department.Dept;,17,HistoryofDataModel,Datamodel:abstractionofdatafeaturesinrealworldintocomputerworld.,18,Relationalmodelornot?,Relationalmodelisgoodfor:LargeamountsofdatasimpleoperationsNavigateamongsmallnumberofrelationsDifficultApplicationsforrelationalmodel:VLSIDesign(CADingeneral)CASE:Computer-AidedSoftwareEngineeringGraphicalDataWherenumberofrelationsislarge,relationshipsarecomplexObjectDataModelLogicDataModel,19,OtherDataModels,ObjectdatamodelUsuallyfindobjectsviaexplicitnavigation,alsoquerylanguageinsomesystemsComplexObjectsNestedStructure(pointersorreferences)Encapsulation,setofMethods/AccessfunctionsObjectIdentityInheritanceDefiningnewclasseslikeoldclassesLogicdatamodelProlog,DatalogMorepowerthanrelational,20,TheDBMSMarketplace,RelationalDBMScompaniesOracle,Sybaseareamongthelargestsoftwarecompaniesintheworld.IBMoffersitsrelationalDB2system.WithIMS,anonrelationalsystem,IBMisbysomeaccountsthelargestDBMSvendorintheworld.MicrosoftoffersSQL-Server,plusMicrosoftAccessforthecheapDBMSonthedesktop,answeredby“lite”systemsfromothercompetitors.Relationalcompaniesalsochallengedby“object-orientedDB”companies.Butcounteredwith“object-relational”systems,whichretaintherelationalcorewhileallowingtypeextensionasinOOsystems.,21,1.2ArchitectureofDBMS,MajorcomponentsofaDBMS,22,Schema(logicalstructureofthedata);Metadata(dataofdata),ArchitectureofDBS,ThreeSchemaArchitectureofDBSExternalSchema:userschemaorsubschemaDefinesoneviewofthedataasseenbyaspecificsetofapplicationorendusers.TheremaybemanyexternalschemasinaDB.Schema:conceptualschema,logicalschemaDefinesdatafromperspectivesystemsdesigner;IndependentofenduserseveryEx-SchemahasanEx-Schema/SchemaReflection.WhenSchemachanges,DBAchangestheEx-Schema/Schemareflections,soapplicationprogramsneedntbeenchanged.PhysicalIndependenceViaReflectionofSchema/InternalSchemaSchema/InternalSchemaReflectionisunique.WhenInternalSchemachanges,DBAchangestheSchema/InternalSchemaReflection,soapplicationprogramsneedntbeenchanged.,25,ThreeAspectstoStudyingDBMSs,ModelinganddesignofdatabasesAllowsexplorationofissuesbeforecommittingtoanimplementation.ApplicationprogrammingSQL:queriesandDBoperationslikeupdate.HostlanguageandEmbeddedSQLDBMSimplementationStorage,queryprocessing,transaction,26,ReadingGuide,AFirstCourseinDatabaseSystems:Required:1.1Recommended:1.2,1.5数据库系统概论推荐：第一章,27,Exercises,(Required,inEnglish)Explainthefollowingnotionsandgiveanexampletoshowthemclearly.DB,DBMS,DBS以下选做：(推荐作业，中文完成)逻辑独立性和物理独立性是什么含义？在数据库系统架构中是如何保证这两个独立性的？(建议作业，中文完成)对数据库技术发展趋势、主流数据库产品、流行数据库开发和设计技术进行调研，撰写小论文，和同学分组讨论。,28,Chapter2DatabaseModeling,DataModelsConceptModelamoviecanhavemorethanonestar.,48,Many-OneRelationships,Somebinaryrelationshipsaremany-onefromoneentitysettoanother.Eachentityofthefirstsetisconnectedtoatmostoneentityofthesecondset.Butanentityofthesecondsetcanbeconnectedtozero,one,ormanyentitiesofthefirstset.,49,Example,Owns,fromMoviestoStudiosismany-one.Amovieisownedbyatmostonestudio.Butastudioownsanynumberofmovies.,50,One-OneRelationships,Inaone-onerelationship,eachentityofeitherentitysetisrelatedtoatmostoneentityoftheotherset.Example:RelationshipRunsbetweenentitysetsStudiosandPresidents.Apresidentcanrunonlyonestudioandastudiohasonlyonepresident.,51,Representing“Multiplicity”,Showamany-onerelationshipbyanarrowenteringthe“one”side.Showaone-onerelationshipbyarrowsenteringbothentitysets.,52,实体联系图示,一对一、一对多、多对一、多对多,53,AttributesonRelationships,Sometimesitisusefultoattachanattributetoarelationship.Thinkofthisattributeasapropertyoftuplesintherelationshipset.,54,Example,55,Movies,stars,Contracts,salary,Itisappropriatetoassociateasalarywiththe(star,movie,studio)tripleintherelationshipsetfortheContractsrelationship.,studios,Keys,Akeyisasetofattributesforoneentitysetsuchthatnotwoentitiesinthissetagreeonalltheattributesofthekey.Itisallowedfortwoentitiestoagreeonsome,butnotall,ofthekeyattributes.Wemustdesignateakeyforeveryentityset.KeysinE/RDiagrams:Underlinethekeyattribute(s),56,Example:aMulti-attributeKey,57,Courses,dept,number,hours,room,Notethathoursandroomcouldalsoserveasakey,butwemustselectonlyonekey.,DesignTechniques,Avoidredundancy.Dontuseanentitysetwhenanattributewilldo.,58,AvoidingRedundancy,Redundancyoccurswhenwesaythesamethingintwoormoredifferentways.Redundancywastesspaceand(moreimportantly)encouragesinconsistency.Thetwoinstancesofthesamefactmaybecomeinconsistentifwechangeoneandforgettochangetheother.,59,Example:Good,60,Thisdesigngivesthenameofeachstudioexactlyonce.,Example:Bad,61,Movies,Studios,Owns,title,Thisdesignstatesthenameofeachstudiotwice:asanattributeandasarelatedentity.,name,addr,year,StudioName,Example:Bad,62,Thisdesignrepeatsthestudiosaddressonceforeachmovieandlosestheaddressiftherearetemporarilynomovieforastudio.,EntitySetsVersusAttributes,Anentitysetshouldsatisfyatleastoneofthefollowingconditions:Itismorethanthenameofsomething;ithasatleastonenonkeyattribute.orItisthe“many”inamany-oneormany-manyrelationship.,63,Example:Good,64,Movies,Studio,Owns,title,Studiodeservestobeanentitysetbecauseofthenonkeyattributeaddr.Moviesdeservestobeanentitysetbecauseitisthe“many”ofthemany-onerelationshipOwns.,name,addr,Example:Good,65,Movies,title,Thereisnoneedtomakethestudioanentityset,becausewerecordnothingaboutstudiosbesidestheirname.,StudioName,Example:Bad,66,Movies,Studios,Owns,title,SincetheStudiosisnothingbutaname,andisnotatthe“many”endofanyrelationship,itshouldnotbeanentityset.,name,两个实体型间的联系,67,多个实体型间的联系,多个实体型间的一对多联系若实体集E1，E2，.，En存在联系，对于实体集Ej（j=1，2，.，i-1，i+1，.，n）中的给定实体，最多只和Ei中的一个实体相联系，则Ei与E1，E2，.，Ei-1，Ei+1，.，En之间是一对多联系实例：课程、教师与参考书三个实体型多个实体型间的一对一联系多个实体型间的多对多联系,68,课堂思考,三个实体间的联系判断联系种类,69,供应商,项目,零件,供应,同一实体集内各实体间的联系,一对多联系实例职工实体集内部具有领导与被领导的联系某一职工（干部）“领导”若干名职工，一个职工仅被另外一个职工直接领导这是一对多的联系一对一联系多对多联系,70,OtherExample,71,矩形：表示实体集菱形：表示联系集线：连接实体集与联系集或属性与实体集椭圆：表示属性下划线：主码属性,学生实体例,学生实体及属性,72,联系及其属性例,73,实例练习,实体系、班级、学生、教研室、教师、课程给出概念模型：ER图,74,ReadingGuide,Required:2.2,2.5Recommended:2.3,2.8,数据库系统概论第一章,75,Exercises,Tomanagethematerialsofafactory,pleasegivetheE-Rdiagrams.Suchentitysetsareinvolvedasbelow:Warehouse:withattributesasNumber,areaandtelephoneAccessory:withattributesasnumber,Name,Specification,priceanddescriptionSupplier:withattributesasNumber,name,addr,telephoneandaccountProject:withattributesasnumberandbudgetEmployee:withattributesasnumber,nameandageTherelationshipsamongtheseentitysetsarelistedasbelow:Akindofaccessorycanbedepositedinmorethanonewarehouse.Awarehousecanaccommodatemorethanonekindofaccessory.Therecanbemorethanoneemployeeinawarehouse,andaemployeecanworkinonlyonewarehouse.Someemployeecanleaderothers.Asuppliercansupplyvariousaccessoriestovariousprojects.Aprojectcanuseaccessoriessuppliedbyvarioussuppliers.Akindofaccessorycanbesuppliedbyvarioussupplier.,76,Chapter3TheRelationalDataModel,RelationalModelFunctionalDependencies,77,Contents,3.1BasicsoftheRelationalModel3.3FromE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExercise,78,Thethingsyoushouldknow,ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopularRDBMSDB2,Oracle,Ingres,Sybase,Informix,79,3.1BasicoftheRelationalModel,Therelationalmodelgivesusasinglewaytorepresentdata:asatwo-dimensionaltablecalledarelation.,80,Attributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofentriesinthecolumnbelow.,81,Schemas（模式）Thenameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowtheschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.,82,Tuples（元组）Therowsofarelation,otherthantheheaderrowcontainingtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoappearmorethanonceinagivenrelation.,83,Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.,84,EquivalentRepresentationsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.,85,RelationInstance（关系实例）Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.,86,AnExampleofRelationInstance,Relation:Person(Name,Address,Telephone)RelationInstance:NameAddressTelephoneBob123MainSt555-1234Bob128MainSt555-1235Pat123MainSt555-1235Harry456MainSt555-2221Sally456MainSt555-2221Sally456MainSt555-2223Pat12StateSt555-1235,87,More,Relation(Instance)=asetoftuplesDatabase=collectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr),88,NameAddrTelN1A1T1N2A2T2N3A3T3N4T4N5T5T6T7,89,NameAddrTelN1A1T1N1A1T2N1A1T3.N1A1T7N1A2T1N1A3T1N2A1T1,Tuple,Domain,Component,Attribute,IntegrityConstrainofRelations,EntityConstrainTheattributesbelongtokeycannotbesetasNULL.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaforeignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.,90,补充：关系的完整性,实体完整性参照完整性用户定义完整性实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持,91,实体完整性,规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）,92,参照完整性,93,例：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图,例：学生，课程，学生与课程之间的多对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系参照图,参照关系,学生关系专业关系,专业号,学生关系选修关系课程关系,学号,课程号,主码？外码？,参照完整性,定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）规则：参照关系R中每个元组在外码F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值,94,例：学生（学号，姓名，性别，专业号，年龄，班长）,参照关系,被参照关系,外码,用户定义完整性,用户定义的、具体应用中的数据必须满足的约束条件成绩：0100之间身份证、身份证和生日对应关系,95,3.2FromE/RDiagramstoRelation,FromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarelation.Createarelationofthesamenameandwiththesamesetofattributes.,96,97,Movies(title,year,length,filmType)Stars(name,address)Studios(name,address),Example,FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasthefollowingattributes;ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.Iftherelationshiphasattributes,thenthesearealsoattributesofrelationR.,98,Example:,99,Owns(title,year,studioname),Stars-in(title,year,starName),E-R图向关系模型的转换原则,一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并,100,补充：,101,如公司部门管理系统的E-R图及其转换成的关系模式,部门,项目,职工,电话,包括,承担,n,1,n,1,办公室号，面积,项目号，预算费,参与,m,n,办公室,包含,1,n,包括,n,1,部门号，预算费，领导人职工号,电话号码，说明,职工号，姓名，办公电话,分担任务,部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号，项目号，分担任务),Exercise,Required:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.,102,3.5FunctionalDependencies,DefinitionofFunctionalDependency(函数依赖)X-AisanassertionaboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X-AholdsinR.”ConventionX,Y,Zrepresentsetsofattributes;A,B,C,representsingleattributes.,103,ExampleMovies(title,year,length,filmType,studioName,starname)Wecanassertthethreedependencies:title,year-lengthtitle,year-filmTypetitle,year-studioName,104,FDsWithMultipleAttributesNoneedforFDswithmultipleattributeonright.Butsometimesforconvenientwecancombinethem.Example:title,year-lengthtitle,year-filmTypetitle,year-starNamebecometitle,year-length,filmType,starNameMultipleattributeonleftmaybeessential.Example:title,year-length,105,TrivialDependenciesAfunctionaldependencyA1A2An-Bissaidtobetrivial(平凡)ifBisoneoftheAs,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFunctionalDependenciestitle,year-titleisatrivialdependency.,106,Transitive(传递)FunctionalDependenciesSupposewehavearelationRwiththreeattributesA,B,andC,theFDsA-BandB-CbothholdforR.ThenitiseasytoseethattheFDA-calsoholdsforR,SoCissaidtodependonAtransitively,viaB,107,KeysofRelationsfromFDsviewWesayasetofoneormoreattributesA1,A2,Anisakeyforrelationif:Thoseattributesfunctionallydetermineallotherattributesoftherelation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2,An.NopropersubsetofA1,A2,AnfunctionallydeterminesallotherattributesofR;i.e.,akeymustbeminimal,108,ExampleAttributestitle,year,starNameformakeyfortheMovierelationofFig.above.Sometimesarelationhasmorethanonekey.Ifso,itiscommontodesignoneofthekeysastheprimarykey.,109,SuperKeysSuperkeysatisfiesthefirstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;ExampleAttributesettitle,year,starNameformakeyfortheMovie.Anysupersetofthisattributeset,suchastitle,year,starName,lengthisasuperkey.,110,RulesfordiscoveringKeysofRelationFirstrule:Iftherelationcomesfromanentitysetthenthekeyfortherelationisthekeyattributesofthisentityset.Secondrule:IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthekeyforR.Therearethreecases:Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.Iftherelationshipismany-onefromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.Iftherelationshipisone-one,thenthekeyattributesforeitheroftheconnectedentitysetsarekeyattributesofR.Thus,thereisnotauniquekeyforR.,111,ExampleOwns:Itisamany-onerelationbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMovies.Owns(title,year,studioName)Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.SoallattributesoftheresultingrelationarekeyattributesStars-in(title,year,starName),112,3.7DesignofRelationalDatabaseSchema,Anomalies(异常)Problemoccurwhenwetrytocramtoomuchintoasinglerelationarecalledanomalies.Redundancy:Informationmayberepeatedunnecessarilyinseveraltuples.InsertionAnomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.DeletionAnomalies:Ifasetofvaluesbecomesempty,wemayloseotherinformationasasideeffect.UpdateAnomalies:Wemaychangeinformationinonetuplebutleavethesameinformationu