课程《Database-Principles数据库原理》课件

DatabasePrinciples

数据库原理.DatabasePrinciples

数据库原理.1TextbooksRequired:AFirstCourseinDatabaseSystems,byJeffreyD.UllmanJenniferWidom.Recommended:萨师煊,王珊.数据库系统概论(第三版).高等教育出版社,2000年BooksonSQLServer2000Youmayalsowant:Booksondesignandprogramming:Powerbuilder,PowerDesigner,UML,VB,Delphi,VC,JAVA,C#,ASP,PHP,JSPetc.2.TextbooksRequired:2.WhyUsingEnglishTextbook?MorecomputerEnglishwordsMoreadvancedandnewestinformationBetterinformationretrievalcapabilityMoreinternationalcommunicationchance3.WhyUsingEnglishTextbook?MoWhattolearninthisunit?Basictheories(basic)Systemanddatabasedesign(designer)ApplicationProgramming(programmer)Databasemanagementandmaintenance(databasemanager)4.Whattolearninthisunit?BaSyllabusBackgroundandhistoryofdatabasemanagementsystemsFundamentalsofusingadatabasemanagementsystems:SQL2000.(*extracurricular)DatabasemodelingRelationalmodel:theoreticalbackgroundandoperationsDatabaseLanguageSQLFundamentalsofdatabasemanagementsystems:transactionsandsecurityetc.Databasesystemdesignandpractice5.SyllabusBackgroundandhistoryChapter1

TheWorldsofDatabaseSystemsEvolutionofDatabaseSystemsArchitectureofDBMSandDBSFutureofDatabaseSystems.Chapter1

TheWorldsofDataba61.1EvolutionofDatabaseSystems1.1.1SomeBasicnotionsDataDatabase(DB)DatabaseManagementSystem(DBMS)DatabaseSystem(DBS).DatabaseAdministrator(DBA)7.1.1EvolutionofDatabaseSystBasicNotionsDataDistinctpiecesofinformationrepresentedinaformsuitableforprocessingbycomputer.Datacanexistinavarietyofforms--asdigitalnumbers,text,image,sound,videoandetc.Database

(DB)

Inessence,it’snothingmorethanacollectionofinformationthatexistsoveralongperiodoftime.Incommonparlance,itreferstoacollectionofdatamanagedbyadatabasemanagementsystem(DBMS)orjustdatabasesystem(DBS).8.BasicNotionsData8.ExampleofDataandDatabase9.ExampleofDataandDatabase9.DBMSDatabaseManagementSystem(DBMS)

Acollectionofprogramsthatenablesyoutostore,modify,andextractinformationfromadatabase.TherearemanydifferenttypesofDBMSs,rangingfromsmallsystemsthatrunonpersonalcomputerstohugesystemsthatrunonmainframes.10.DBMSDatabaseManagementSystemBasicfunctionsofDBMSDatadefinitionAllowcreatingnewdatabasesandspecifytheirschema(logicalstructureofthedata),viadatadefinitionlanguage(DDL).DatamanipulationAllowefficientaccesstothedata,suchasqueryingandmodifyingthedata,viaquerylanguageordatamanipulationlanguage(DML).OperationmanagementofDBSupportthestorageofverylargeamountsofdataoveralongperiodoftime,assuringdataintegrity,keepingdatasecurefromaccidentorunauthorizeduse.Supportconcurrent,

atomicaccesstodata.CreationandmaintenanceofDBSuchasinputtingandtransferringofData,DBbackupandrecovery,performancemonitoring,auditandanalysisetc.11.BasicfunctionsofDBMSDatadeDatabaseSystem(DBS)DatabaseSystem(DBS).DB,DBMS,DevelopmentTools,DBapplications,DBAdministrator(DBA)andusers12DBOSDBMSDevelopingToolsDBapplicationsUserUserDBAHardwareOperatingSystemDBMS,CompilingSystemDevelopingToolsApplicationSystemsDBMSinComputerSystem.DatabaseSystem(DBS)DatabaseDatabaseAdministratorDBAPersonwhoisresponsibleformanagementandmaintenanceofDB.TasksDatabasecontentsandstructuresStoragestructureandaccessmethodsDatabasesecurityandintegrationMonitorandimprovedatabaseperformance13.DatabaseAdministratorDBAHistoryofDatamanagementFilesystemsProblems:limitedsupporttodefinitionofdataschema,nodirectlysupporttoDML(DataManipulateLanguage),nosupporttoefficientconcurrent,secureaccessetc.EarlyDBMSThefirstcommercialDBMSappearedinthelate1960’s,evolvedfromfilesystems.TheearlyDBMS’susedseveraldatamodelsfordescribingstructureofDB:hierarchicalmodelandnetworkmodel.Problems:nosupporttohigh-levelquerylanguages.HistoryofDatamanagemeHistoryofDatamanagement(cont.）RelationalDBMSAfamouspaperbyTedCoddin1970,“Arelationalmodelforlargeshareddatabanks”inComm.ACM.Dataorganizedastablescalledrelations.UnliketheuserofearlyDBS,userofarelationalsystemwon’tbeconcernedwiththestoragestructure,andqueriesareexpressedinaveryhigh-levellanguage(SQL).TodayusedinmostDBMS's.15.HistoryofDatamanagement(coRelationalModelBasedontables,suchas:

acct#namebalance12345Sally1000.2134567Sue285.48………16Attributes(columnheaders)Tuples(rows).RelationalModelBasedontableQueryLanguages

SELECTManager

FROMEmployee,Department

WHEREE="ClarkKent”

ANDEmployee.Dept=Department.Dept;17EmployeeNameDeptDepartmentDeptManager.QueryLanguages SELECTManagerHistoryofDataModelDatamodel:abstractionofdatafeaturesinrealworldintocomputerworld.18Hierarchicalmodel60’s70's80's90’snowRelationalmodelChoiceformostnewapplicationsObjectBasesmodelKnowledgeBasesmodelNetworkmodel.HistoryofDataModelDatamodeRelationalmodelornot?Relationalmodelisgoodfor:Largeamountsofdata—>simpleoperationsNavigateamongsmallnumberofrelationsDifficultApplicationsforrelationalmodel:VLSIDesign(CADingeneral)CASE:Computer-AidedSoftwareEngineeringGraphicalDataWherenumberof"relations"islarge,relationshipsarecomplexObjectDataModelLogicDataModel19.Relationalmodelornot?RelatiOtherDataModelsObjectdatamodel Usuallyfindobjectsviaexplicitnavigation,alsoquerylanguageinsomesystemsComplexObjects–NestedStructure(pointersorreferences)Encapsulation,setofMethods/AccessfunctionsObjectIdentityInheritance–DefiningnewclasseslikeoldclassesLogicdatamodelProlog,DatalogMorepowerthanrelational20.OtherDataModelsObjectdatamTheDBMSMarketplaceRelationalDBMScompanies–Oracle,Sybase–areamongthelargestsoftwarecompaniesintheworld.IBMoffersitsrelationalDB2system.WithIMS,anonrelationalsystem,IBMisbysomeaccountsthelargestDBMSvendorintheworld.MicrosoftoffersSQL-Server,plusMicrosoftAccessforthecheapDBMSonthedesktop,answeredby“lite”systemsfromothercompetitors.Relationalcompaniesalsochallengedby“object-orientedDB”companies.Butcounteredwith“object-relational”systems,whichretaintherelationalcorewhileallowingtypeextensionasinOOsystems.21.TheDBMSMarketplaceRelational1.2ArchitectureofDBMSMajorcomponentsofaDBMS22DataMetadataStorageManagerQueryProcessorTransactionManagerSchemaModificationsQueriesModificationsSchema(logicalstructureofthedata);Metadata(dataofdata).1.2ArchitectureofDBMSMajorArchitectureofDBSThreeSchemaArchitectureofDBSExternalSchema:userschemaorsubschemaDefinesoneviewofthedataasseenbyaspecificsetofapplicationorendusers.TheremaybemanyexternalschemasinaDB.Schema:conceptualschema,logicalschemaDefinesdatafromperspectivesystemsdesigner;Independentofendusers&datastoragemechanismThereisonlyoneconceptualschemainaDB.InternalSchema:storageschemaDefineshowdataisorganized,storedandmanipulatedinsidethesystem.Totallydependentonparticularimplementation.ThereisonlyoneinternalschemainaDB.23.ArchitectureofDBSThreeSchemThreeSchemaArchitecture(cont.)24ApplicationAExternalSchema1SchemaInternalSchemaDBApplicationBApplicationDApplicationEApplicationCExternalSchema2ExternalSchema3.ThreeSchemaArchitecture(conIndependenceofDataandProgramLogicalIndependenceViaReflectionof

ExternalSchema/SchemaOneSchemacorrespondstomanyExternalschemas;everyEx-SchemahasanEx-Schema/SchemaReflection.WhenSchemachanges,DBAchangestheEx-Schema/Schemareflections,soapplicationprogramsneedn’tbeenchanged.PhysicalIndependenceViaReflectionof

Schema/InternalSchemaSchema/InternalSchemaReflectionisunique.WhenInternalSchemachanges,DBAchangestheSchema/InternalSchemaReflection,soapplicationprogramsneedn’tbeenchanged.25.IndependenceofDataandProgrThreeAspectstoStudyingDBMS'sModelinganddesignofdatabasesAllowsexplorationofissuesbeforecommittingtoanimplementation.ApplicationprogrammingSQL:queriesandDBoperationslikeupdate.HostlanguageandEmbeddedSQLDBMSimplementationStorage,queryprocessing,transaction,…26.ThreeAspectstoStudyingDBMSReadingGuideAFirstCourseinDatabaseSystems:Required:1.1Recommended:1.2,1.5数据库系统概论推荐：第一章27.ReadingGuideAFirstCourseinExercises(Required,inEnglish)Explainthefollowingnotionsandgiveanexampletoshowthemclearly.DB,DBMS,DBS以下选做：(推荐作业，中文完成)逻辑独立性和物理独立性是什么含义？在数据库系统架构中是如何保证这两个独立性的？(建议作业，中文完成)对数据库技术发展趋势、主流数据库产品、流行数据库开发和设计技术进行调研，撰写小论文，和同学分组讨论。28.Exercises(Required,inEnglishChapter2

DatabaseModelingDataModelsConceptModel&DataModelEntity-Relationshipdiagrams29.Chapter2

DatabaseModelingDatWhatareDataModel?Computersimulationoftherealworld.Toabstract,denoteandtransacttheinformationintherealworld.30.WhatareDataModel?ComputersDataModelshould:SimulatetherealworldcomparativelytrulyBeeasytounderstandBeeasytobeimplementedbycomputer31.DataModelshould:SimulatethAbstractionandModelingTwostepsoftheabstractionandmodeling:Theobjectiveobjectsintherealworldisabstractedtoaconceptmodel.ConceptmodelisconvertedtoadatamodelthatsupportedbysomeDBMS.Theconceptmodelisamiddlelayerbetweentherealworldandthemachineworld.32现实世界机器世界概念模型数据模型客观对象信息世界.AbstractionandModelingTwostTheclassificationofthemodelsConceptmodelAlsocalledinformationmodel.Modelingdatafromtheviewpointofusers.amiddlelayerbetweentherealworldandthemachineworld.Mainlyusedforadatabasedesign.Thelanguageinwhichthedatabasedesignersanduserscommunicatewitheachother.Itshouldbesimple,clearandeasytounderstandbyusers.33.TheclassificationofthemodeDatamodelModelingdatafromtheviewpointofcomputer.MainlyusedforaDBMSrealization.TraditionaldatamodelsHierarchicalModelNetworkModelRelationalModelMainfactorsofthedatamodelDatastructureDataoperationIntegrityconstraints34.Datamodel34.数据模型的组成要素数据结构指所研究数据集合及数据间的联系是对系统静态特性的描述数据操作对数据库中各类数据允许执行的操作及有关的操作规则检索、更新（包括插入、删除、修改）是对系统动态特性的描述数据的约束条件一组数据及其联系所具有的制约规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容35.数据模型的组成要素数据结构35.E-RModelTheconceptmodelisrepresentedbyEntity-Relationship（E-R）diagrams.

TheE/RmodelDescribeskindsofdataandhowtheyconnected.Canbeconvertedtorelationalmodellater.36.E-RModelTheconceptmodelis概念模型基本概念实体（Entities)客观存在并可相互区别的事物，可具体，可抽象例：职工、学生、系属性（Attribute）实体具有的某一特性例：学生实体有学号、姓名、性别、出生年份等属性码（Key）唯一标识实体的属性集例如学号是学生实体的码域（Domain）某属性的取值范围实体集（EntitySet）同型实体的集合如全体学生37.概念模型基本概念实体（Entities)37.概念模型的基本概念实体间的联系（Relationship）现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系例张山选修数据库课程。学生实体和课程之间通过选修联系两个实体之间的联系：一对一联系(1:1)一对多联系(1:n)多对一联系(n:1)多对多联系(m:n)38.概念模型的基本概念实体间的联系（Relationship）3EntitySetsEntity=“thing”orobject.Entityset=collectionofsimilarentities.Similartoaclassinobject-orientedlanguages.Attribute=propertyof(theentitiesof)anentityset.Attributesaresimplevalues,egersorcharacterstrings.39.EntitySetsEntity=“thing”orE/RDiagramsInanentity-relationshipdiagram:Entityset=rectangle.Attribute=oval,withalinetotherectanglerepresentingitsentityset.40.E/RDiagramsInanentity-relatExampleEntitysetStarshastwoattributes,nameandaddress.Eachstarhasvaluesforthesetwoattributes.41Starsnameaddress.Example41Starsnameaddress.RelationshipsArelationshipconnectstwoormoreentitysets.Itisrepresentedbyadiamond,withlinestoeachoftheentitysetsinvolved.42.RelationshipsArelationshipcoExample43FilmtypeP41Thearrowpointingtoentitysetstudiosindicatesthateachmovieisownedbyauniquestudio.StudiosaddressnameStarsaddressnameMoviestitlelengthyearStars_inOwns.Example43FilmtypeP41ThearrowRelationshipSetThecurrent“value”ofanentitysetisthesetofentitiesthatbelongtoit.Example:thesetofallstarsinourdatabase.The“value”ofarelationshipisasetoflistsofcurrentlyrelatedentities,onefromeachoftherelatedentitysets.44.RelationshipSetThecurrent“vExampleFortherelationshipStars_in,wemighthavearelationshipsetlike:MoviesStarsBasicInstinctSharonStoneTotalRecallArnoldSchwarzeneggerTotalRecallSharonStone45.ExampleFortherelationshipStMultiwayRelationshipsSometimes,weneedarelationshipthatconnectsmorethantwoentitysets.46.MultiwayRelationshipsSometimeATypicalRelationshipSetStudiosMoviesstarsUniversalStudioKingKongJackBlack47.ATypicalRelationshipSetStudMany-ManyRelationshipsInamany-manyrelationship,anentityofeithersetcanbeconnectedtomanyentitiesoftheotherset.E.g.,astarcanappearinmanymovies;amoviecanhavemorethanonestar.48.Many-ManyRelationshipsInamaMany-OneRelationshipsSomebinaryrelationshipsaremany-onefromoneentitysettoanother.Eachentityofthefirstsetisconnectedtoatmostoneentityofthesecondset.Butanentityofthesecondsetcanbeconnectedtozero,one,ormanyentitiesofthefirstset.49.Many-OneRelationshipsSomebinExampleOwns,fromMoviestoStudiosismany-one.Amovieisownedbyatmostonestudio.Butastudioownsanynumberofmovies.50.ExampleOwns,fromMoviestoStOne-OneRelationshipsInaone-onerelationship,eachentityofeitherentitysetisrelatedtoatmostoneentityoftheotherset.Example:RelationshipRunsbetweenentitysetsStudiosandPresidents.Apresidentcanrunonlyonestudioandastudiohasonlyonepresident.51.One-OneRelationshipsInaone-Representing“Multiplicity”Showamany-onerelationshipbyanarrowenteringthe“one”side.Showaone-onerelationshipbyarrowsenteringbothentitysets.52.Representing“Multiplicity”Sho实体联系图示一对一、一对多、多对一、多对多53.实体联系图示一对一、一对多、多对一、多对多53.AttributesonRelationshipsSometimesitisusefultoattachanattributetoarelationship.Thinkofthisattributeasapropertyoftuplesintherelationshipset.54.AttributesonRelationshipsSomExample55MoviesstarsContractssalaryItisappropriatetoassociateasalarywiththe(star,movie,studio)tripleintherelationshipsetfortheContractsrelationship.studios.Example55MoviesstarsContractssKeysAkey

isasetofattributesforoneentitysetsuchthatnotwoentitiesinthissetagreeonalltheattributesofthekey.Itisallowedfortwoentitiestoagreeonsome,butnotall,ofthekeyattributes.Wemustdesignateakeyforeveryentityset.KeysinE/RDiagrams:

Underlinethekeyattribute(s)56.KeysAkeyisasetofattribuExample:aMulti-attributeKey57Coursesdeptnumberhoursroom

Notethathoursandroomcouldalsoserveasakey,butwemustselectonlyonekey..Example:aMulti-attributeKeyDesignTechniquesAvoidredundancy.Don’tuseanentitysetwhenanattributewilldo.58.DesignTechniquesAvoidredundaAvoidingRedundancyRedundancyoccurswhenwesaythesamethingintwoormoredifferentways.Redundancywastesspaceand(moreimportantly)encouragesinconsistency.Thetwoinstancesofthesamefactmaybecomeinconsistentifwechangeoneandforgettochangetheother.59.AvoidingRedundancyRedundancyExample:Good60Thisdesigngivesthenameofeachstudioexactlyonce.MoviesStudiosOwnstitlenameaddryear.Example:Good60ThisdesigngivExample:Bad61MoviesStudiosOwnstitleThisdesignstatesthenameofeachstudiotwice:addryearStudioName.Example:Bad61MoviesStudiosOwnExample:Bad62Thisdesignrepeatsthestudio’saddressonceforeachmovieandlosestheaddressiftherearetemporarilynomovieforastudio.MoviestitleyearStudioNameStudioAddr.Example:Bad62ThisdesignrepeEntitySetsVersusAttributesAnentitysetshouldsatisfyatleastoneofthefollowingconditions:Itismorethanthenameofsomething;ithasatleastonenonkeyattribute. orItisthe“many”inamany-oneormany-manyrelationship.63.EntitySetsVersusAttributesAExample:Good64MoviesStudioOwnstitleStudiodeservestobeanentitysetbecauseofthenonkeyattributeaddr.Moviesdeservestobeanentitysetbecauseitisthe“many”ofthemany-onerelationshipOaddr.Example:Good64MoviesStudioOwnExample:Good65MoviestitleThereisnoneedtomakethestudioanentityset,becausewerecordnothingaboutstudiosbesidestheirname.StudioName.Example:Good65MoviestitleTherExample:Bad66MoviesStudiosOwnstitleSincetheStudiosisnothingbutaname,andisnotatthe“many”endofanyrelationship,.Example:Bad66MoviesStudiosOwn两个实体型间的联系67班级班级-班长班长111:1联系班级组成学生1n1:n联系课程选修学生mnm:n联系.两个实体型间的联系67班级班级-班长班长111:1联系班级多个实体型间的联系多个实体型间的一对多联系若实体集E1，E2，...，En存在联系，对于实体集Ej（j=1，2，...，i-1，i+1，...，n）中的给定实体，最多只和Ei中的一个实体相联系，则Ei与E1，E2，...，Ei-1，Ei+1，...，En之间是一对多联系实例：课程、教师与参考书三个实体型多个实体型间的一对一联系多个实体型间的多对多联系68课程教员参考书讲授1mn.多个实体型间的联系多个实体型间的一对多联系68课程教员参课堂思考三个实体间的联系判断联系种类69供应商项目零件供应mnp.课堂思考三个实体间的联系69供应商项目零件供应mnp同一实体集内各实体间的联系一对多联系实例职工实体集内部具有领导与被领导的联系某一职工（干部）“领导”若干名职工，一个职工仅被另外一个职工直接领导这是一对多的联系一对一联系多对多联系70职工领导1n.同一实体集内各实体间的联系一对多联系70职工领导1OtherExample71矩形：表示实体集菱形：表示联系集线：连接实体集与联系集或属性与实体集椭圆：表示属性下划线：主码属性.OtherExample71矩形：表示实体集.学生实体例学生实体及属性72学生学号姓名性别出生年月入学时间系.学生实体例学生实体及属性72学生学号姓名性别出生年月入学时间联系及其属性例73供应商供应项目零件供应量课程选修学生mn成绩.联系及其属性例73供应商供应项目零件供应量课程选修学实例练习实体系、班级、学生、教研室、教师、课程给出概念模型：E－R图74.实例练习实体74.ReadingGuideRequired:2.2,2.5Recommended:2.3,2.8,《数据库系统概论》第一章75.ReadingGuideRequired:2.2,2.5ExercisesTomanagethematerialsofafactory,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.ExercisesTomanagethemateriaChapter3

TheRelationalDataModelRelationalModelFunctionalDependencies77.Chapter3

TheRelationalDataContents3.1BasicsoftheRelationalModel3.3FromE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExercise78.Contents3.1BasicsoftheRelaThethingsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopularRDBMSDB2,Oracle,Ingres,Sybase,Informix,…79.Thethingsyoushouldknow…The3.1BasicoftheRelationalModel

Therelationalmodelgivesusasinglewaytorepresentdata:asatwo-dimensionaltablecalledarelation.

80.3.1BasicoftheRelationalMoAttributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofentriesinthecolumnbelow.81.Attributes(属性)81.Schemas（模式）Thenameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowtheschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.82.Schemas（模式）82.Tuples（元组）Therowsofarelation,

otherthantheheaderrowcontainingtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoappearmorethanonceinagivenrelation.83.Tuples（元组）83.Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.84.Domains（域）84.EquivalentRepresentationsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.85.EquivalentRepresentationsofRelationInstance（关系实例）Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.86.RelationInstance（关系实例）86.AnExampleofRelationInstanceRelation:Person(Name,Address,Telephone)RelationInstance:Name Address TelephoneBob 123MainSt 555-1234Bob 128MainSt 555-1235Pat 123MainSt 555-1235Harry 456MainSt 555-2221Sally 456MainSt 555-2221Sally 456MainSt 555-2223Pat 12StateSt 555-123587.AnExampleofRelationInstancMore…Relation(Instance)=asetoftuplesDatabase

=collectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr)88.More…Relation(Instance)=aNameAddrTelN1A1T1N2A2T2N3A3T3 N4T4N5T5T6T789NameAddrTelN1A1T1N1A1T2N1A1T3...N1A1T7N1A2T1N1A3T1N2A1T1TupleDomainComponentAttribute.89NameAddrTelTupleDomaIntegrityConstrainofRelationsEntityConstrainTheattributesbelongtokeycannotbesetasNULL.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaforeignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.90.IntegrityConstrainofRelatio补充：关系的完整性实体完整性参照完整性用户定义完整性

实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持91★.补充：关系的完整性实体完整性91★.实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）92.实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取参照完整性93例１：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图外码参照关系被参照关系例２：学生，课程，学生与课程之间的多对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系参照图

被参照关系参照关系学生关系专业关系专业号学生关系选修关系课程关系学号课程号主码？外码？.参照完整性93例１：学生实体与专业实体间的关系：外码参照关系参照完整性定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）规则：参照关系R中每个元组在外码F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值94例３：学生（学号，姓名，性别，专业号，年龄，班长）参照关系被参照关系外码.参照完整性定义：外码94例３：学生（学号，姓名，性别，专业号用户定义完整性用户定义的、具体应用中的数据必须满足的约束条件成绩：0－100之间身份证、身份证和生日对应关系

95.用户定义完整性95.3.2FromE/RDiagramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarelation.Createarelationofthesamenameandwiththesamesetofattributes.96.3.2FromE/RDiagramstoRelat97Movies(title,year,length,filmType)Stars(name,address)Studios(name,address)Example.97Movies(title,year,length,FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasthefollowingattributes;ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.Iftherelationshiphasattributes,thenthesearealsoattributesofrelationR.98.FromE/RRelationshipstoRelaExample:99Owns(title,year,studioname)Stars-in(title,year,starName).Example:99Owns(title,year,sE-R图向关系模型的转换原则一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并100补充：.E-R图向关系模型的转换原则一个实体转换为一个关系模式，实体101如公司部门管理系统的E-R图及其转换成的关系模式部门项目职工电话包括承担n1n1办公室号，面积项目号，预算费参与mn办公室包含1n包括n1部门号，预算费，领导人职工号电话号码，说明职工号，姓名，办公电话分担任务部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号，项目号，分担任务).101如公司部门管理系统的E-R图及其转换成的关系模式部门项ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.102.ExerciseRequired:Convertthe3.5FunctionalDependenciesDefinitionofFunctionalDependency(函数依赖)X->AisanassertionaboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->AholdsinR.”ConventionX,Y,Zrepresentsetsofattributes;

A,B,C,…representsingleattributes.103.3.5FunctionalDependenciesDefExampleMovies(title,year,length,filmType,studioName,starname)Wecanassertthethreedependencies:title,year->lengthtitle,year->filmTypetitle,year->studioName104.Example104.FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.Butsometimesforconvenientwecancombinethem.Example:title,year->lengthtitle,year->filmTypetitle,year->starNamebecometitle,year->length,filmType,starNameMultipleattributeonleftmaybeessential.Example:title,year->length105.FD’s