課程《Database-Principles數(shù)據(jù)庫(kù)原理》課件

DatabasePrinciples

數(shù)據(jù)庫(kù)原理.DatabasePrinciples

數(shù)據(jù)庫(kù)原理.1TextbooksRequired:AFirstCourseinDatabaseSystems,byJeffreyD.UllmanJenniferWidom.Recommended:薩師煊,王珊.數(shù)據(jù)庫(kù)系統(tǒng)概論(第三版).高等教育出版社,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“l(fā)ite”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數(shù)據(jù)庫(kù)系統(tǒng)概論推薦：第一章27.ReadingGuideAFirstCourseinExercises(Required,inEnglish)Explainthefollowingnotionsandgiveanexampletoshowthemclearly.DB,DBMS,DBS以下選做：(推薦作業(yè)，中文完成)邏輯獨(dú)立性和物理獨(dú)立性是什么含義？在數(shù)據(jù)庫(kù)系統(tǒng)架構(gòu)中是如何保證這兩個(gè)獨(dú)立性的？(建議作業(yè)，中文完成)對(duì)數(shù)據(jù)庫(kù)技術(shù)發(fā)展趨勢(shì)、主流數(shù)據(jù)庫(kù)產(chǎn)品、流行數(shù)據(jù)庫(kù)開(kāi)發(fā)和設(shè)計(jì)技術(shù)進(jìn)行調(diào)研，撰寫(xiě)小論文，和同學(xué)分組討論。28.Exercises(Required,inEnglishChapter2

DatabaseModelingDataModelsConceptModel&DataModelEntity-Relationshipdiagrams29.Chapter2

DatabaseModelingDatWhatareDataModel?Computersimulationoftherealworld.Toabstract,denoteandtransacttheinformationintherealworld.30.WhatareDataModel?ComputersDataModelshould:SimulatetherealworldcomparativelytrulyBeeasytounderstandBeeasytobeimplementedbycomputer31.DataModelshould:SimulatethAbstractionandModelingTwostepsoftheabstractionandmodeling:Theobjectiveobjectsintherealworldisabstractedtoaconceptmodel.ConceptmodelisconvertedtoadatamodelthatsupportedbysomeDBMS.Theconceptmodelisamiddlelayerbetweentherealworldandthemachineworld.32現(xiàn)實(shí)世界機(jī)器世界概念模型數(shù)據(jù)模型客觀對(duì)象信息世界.AbstractionandModelingTwostTheclassificationofthemodelsConceptmodelAlsocalledinformationmodel.Modelingdatafromtheviewpointofusers.amiddlelayerbetweentherealworldandthemachineworld.Mainlyusedforadatabasedesign.Thelanguageinwhichthedatabasedesignersanduserscommunicatewitheachother.Itshouldbesimple,clearandeasytounderstandbyusers.33.TheclassificationofthemodeDatamodelModelingdatafromtheviewpointofcomputer.MainlyusedforaDBMSrealization.TraditionaldatamodelsHierarchicalModelNetworkModelRelationalModelMainfactorsofthedatamodelDatastructureDataoperationIntegrityconstraints34.Datamodel34.數(shù)據(jù)模型的組成要素?cái)?shù)據(jù)結(jié)構(gòu)指所研究數(shù)據(jù)集合及數(shù)據(jù)間的聯(lián)系是對(duì)系統(tǒng)靜態(tài)特性的描述數(shù)據(jù)操作對(duì)數(shù)據(jù)庫(kù)中各類(lèi)數(shù)據(jù)允許執(zhí)行的操作及有關(guān)的操作規(guī)則檢索、更新（包括插入、刪除、修改）是對(duì)系統(tǒng)動(dòng)態(tài)特性的描述數(shù)據(jù)的約束條件一組數(shù)據(jù)及其聯(lián)系所具有的制約規(guī)則，用以限定符合數(shù)據(jù)模型的數(shù)據(jù)庫(kù)狀態(tài)以及狀態(tài)的變化，以保證數(shù)據(jù)的正確、有效、相容35.數(shù)據(jù)模型的組成要素?cái)?shù)據(jù)結(jié)構(gòu)35.E-RModelTheconceptmodelisrepresentedbyEntity-Relationship（E-R）diagrams.

TheE/RmodelDescribeskindsofdataandhowtheyconnected.Canbeconvertedtorelationalmodellater.36.E-RModelTheconceptmodelis概念模型基本概念實(shí)體（Entities)客觀存在并可相互區(qū)別的事物，可具體，可抽象例：職工、學(xué)生、系屬性（Attribute）實(shí)體具有的某一特性例：學(xué)生實(shí)體有學(xué)號(hào)、姓名、性別、出生年份等屬性碼（Key）唯一標(biāo)識(shí)實(shí)體的屬性集例如學(xué)號(hào)是學(xué)生實(shí)體的碼域（Domain）某屬性的取值范圍實(shí)體集（EntitySet）同型實(shí)體的集合如全體學(xué)生37.概念模型基本概念實(shí)體（Entities)37.概念模型的基本概念實(shí)體間的聯(lián)系（Relationship）現(xiàn)實(shí)世界中事物內(nèi)部以及事物之間的聯(lián)系在信息世界中反映為實(shí)體內(nèi)部的聯(lián)系和實(shí)體之間的聯(lián)系例張山選修數(shù)據(jù)庫(kù)課程。學(xué)生實(shí)體和課程之間通過(guò)選修聯(lián)系兩個(gè)實(shí)體之間的聯(lián)系：一對(duì)一聯(lián)系(1:1)一對(duì)多聯(lián)系(1:n)多對(duì)一聯(lián)系(n:1)多對(duì)多聯(lián)系(m:n)38.概念模型的基本概念實(shí)體間的聯(lián)系（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實(shí)體聯(lián)系圖示一對(duì)一、一對(duì)多、多對(duì)一、多對(duì)多53.實(shí)體聯(lián)系圖示一對(duì)一、一對(duì)多、多對(duì)一、多對(duì)多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兩個(gè)實(shí)體型間的聯(lián)系67班級(jí)班級(jí)-班長(zhǎng)班長(zhǎng)111:1聯(lián)系班級(jí)組成學(xué)生1n1:n聯(lián)系課程選修學(xué)生mnm:n聯(lián)系.兩個(gè)實(shí)體型間的聯(lián)系67班級(jí)班級(jí)-班長(zhǎng)班長(zhǎng)111:1聯(lián)系班級(jí)多個(gè)實(shí)體型間的聯(lián)系多個(gè)實(shí)體型間的一對(duì)多聯(lián)系若實(shí)體集E1，E2，...，En存在聯(lián)系，對(duì)于實(shí)體集Ej（j=1，2，...，i-1，i+1，...，n）中的給定實(shí)體，最多只和Ei中的一個(gè)實(shí)體相聯(lián)系，則Ei與E1，E2，...，Ei-1，Ei+1，...，En之間是一對(duì)多聯(lián)系實(shí)例：課程、教師與參考書(shū)三個(gè)實(shí)體型多個(gè)實(shí)體型間的一對(duì)一聯(lián)系多個(gè)實(shí)體型間的多對(duì)多聯(lián)系68課程教員參考書(shū)講授1mn.多個(gè)實(shí)體型間的聯(lián)系多個(gè)實(shí)體型間的一對(duì)多聯(lián)系68課程教員參課堂思考三個(gè)實(shí)體間的聯(lián)系判斷聯(lián)系種類(lèi)69供應(yīng)商項(xiàng)目零件供應(yīng)mnp.課堂思考三個(gè)實(shí)體間的聯(lián)系69供應(yīng)商項(xiàng)目零件供應(yīng)mnp同一實(shí)體集內(nèi)各實(shí)體間的聯(lián)系一對(duì)多聯(lián)系實(shí)例職工實(shí)體集內(nèi)部具有領(lǐng)導(dǎo)與被領(lǐng)導(dǎo)的聯(lián)系某一職工（干部）“領(lǐng)導(dǎo)”若干名職工，一個(gè)職工僅被另外一個(gè)職工直接領(lǐng)導(dǎo)這是一對(duì)多的聯(lián)系一對(duì)一聯(lián)系多對(duì)多聯(lián)系70職工領(lǐng)導(dǎo)1n.同一實(shí)體集內(nèi)各實(shí)體間的聯(lián)系一對(duì)多聯(lián)系70職工領(lǐng)導(dǎo)1OtherExample71矩形：表示實(shí)體集菱形：表示聯(lián)系集線：連接實(shí)體集與聯(lián)系集或?qū)傩耘c實(shí)體集橢圓：表示屬性下劃線：主碼屬性.OtherExample71矩形：表示實(shí)體集.學(xué)生實(shí)體例學(xué)生實(shí)體及屬性72學(xué)生學(xué)號(hào)姓名性別出生年月入學(xué)時(shí)間系.學(xué)生實(shí)體例學(xué)生實(shí)體及屬性72學(xué)生學(xué)號(hào)姓名性別出生年月入學(xué)時(shí)間聯(lián)系及其屬性例73供應(yīng)商供應(yīng)項(xiàng)目零件供應(yīng)量課程選修學(xué)生mn成績(jī).聯(lián)系及其屬性例73供應(yīng)商供應(yīng)項(xiàng)目零件供應(yīng)量課程選修學(xué)實(shí)例練習(xí)實(shí)體系、班級(jí)、學(xué)生、教研室、教師、課程給出概念模型：E－R圖74.實(shí)例練習(xí)實(shí)體74.ReadingGuideRequired:2.2,2.5Recommended:2.3,2.8,《數(shù)據(jù)庫(kù)系統(tǒng)概論》第一章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（關(guān)系實(shí)例）Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.86.RelationInstance（關(guān)系實(shí)例）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補(bǔ)充：關(guān)系的完整性實(shí)體完整性參照完整性用戶(hù)定義完整性

實(shí)體完整性和參照完整性是關(guān)系模型必須滿(mǎn)足的，被稱(chēng)作關(guān)系的不變性，由關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)自動(dòng)支持91★.補(bǔ)充：關(guān)系的完整性實(shí)體完整性91★.實(shí)體完整性規(guī)則：若屬性A是基本關(guān)系R的主屬性，則屬性A不能取空值說(shuō)明：基本關(guān)系的主碼中的任何屬性都不能取空值，而不僅是主碼整體不能取空值依據(jù)：現(xiàn)實(shí)世界的實(shí)體是唯一可分的例：學(xué)生（學(xué)號(hào)，姓名，性別，專(zhuān)業(yè)號(hào)，年齡）課程（課程號(hào)，課程名，學(xué)分）選修（學(xué)號(hào)，課程號(hào)，成績(jī)）92.實(shí)體完整性規(guī)則：若屬性A是基本關(guān)系R的主屬性，則屬性A不能取參照完整性93例１：學(xué)生實(shí)體與專(zhuān)業(yè)實(shí)體間的關(guān)系：學(xué)生（學(xué)號(hào)，姓名，性別，專(zhuān)業(yè)號(hào)，年齡）專(zhuān)業(yè)（專(zhuān)業(yè)號(hào)，專(zhuān)業(yè)名）關(guān)系參照?qǐng)D外碼參照關(guān)系被參照關(guān)系例２：學(xué)生，課程，學(xué)生與課程之間的多對(duì)多聯(lián)系：學(xué)生（學(xué)號(hào)，姓名，性別，專(zhuān)業(yè)號(hào)，年齡）課程（課程號(hào)，課程名，學(xué)分）選修（學(xué)號(hào)，課程號(hào)，成績(jī)）關(guān)系參照?qǐng)D

被參照關(guān)系參照關(guān)系學(xué)生關(guān)系專(zhuān)業(yè)關(guān)系專(zhuān)業(yè)號(hào)學(xué)生關(guān)系選修關(guān)系課程關(guān)系學(xué)號(hào)課程號(hào)主碼？外碼？.參照完整性93例１：學(xué)生實(shí)體與專(zhuān)業(yè)實(shí)體間的關(guān)系：外碼參照關(guān)系參照完整性定義：外碼設(shè)F是參照關(guān)系R的一個(gè)或一組屬性，但不是R的碼，若F與被參照關(guān)系S的主碼相對(duì)應(yīng)，則稱(chēng)F是R的外碼（詳細(xì)定義見(jiàn)教材P54）規(guī)則：參照關(guān)系R中每個(gè)元組在外碼F上的值必須為：或者取空值（F的每個(gè)屬性值均為空值）或者等于S中某個(gè)元組的主碼值94例３：學(xué)生（學(xué)號(hào)，姓名，性別，專(zhuān)業(yè)號(hào)，年齡，班長(zhǎng)）參照關(guān)系被參照關(guān)系外碼.參照完整性定義：外碼94例３：學(xué)生（學(xué)號(hào)，姓名，性別，專(zhuān)業(yè)號(hào)用戶(hù)定義完整性用戶(hù)定義的、具體應(yīng)用中的數(shù)據(jù)必須滿(mǎn)足的約束條件成績(jī)：0－100之間身份證、身份證和生日對(duì)應(yīng)關(guān)系

95.用戶(hù)定義完整性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圖向關(guān)系模型的轉(zhuǎn)換原則一個(gè)實(shí)體轉(zhuǎn)換為一個(gè)關(guān)系模式，實(shí)體的屬性就是關(guān)系的屬性，實(shí)體的碼就是關(guān)系的碼對(duì)實(shí)體間的聯(lián)系一個(gè)1:1聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與任意對(duì)應(yīng)的關(guān)系模式合并一個(gè)1:n聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與n端對(duì)應(yīng)的關(guān)系模式合并一個(gè)m:n聯(lián)系轉(zhuǎn)換為一個(gè)關(guān)系模式三個(gè)或三個(gè)以上實(shí)體間的一個(gè)多元聯(lián)系可以轉(zhuǎn)換為一個(gè)關(guān)系模式具有相同碼的關(guān)系模式可以合并100補(bǔ)充：.E-R圖向關(guān)系模型的轉(zhuǎn)換原則一個(gè)實(shí)體轉(zhuǎn)換為一個(gè)關(guān)系模式，實(shí)體101如公司部門(mén)管理系統(tǒng)的E-R圖及其轉(zhuǎn)換成的關(guān)系模式部門(mén)項(xiàng)目職工電話(huà)包括承擔(dān)n1n1辦公室號(hào)，面積項(xiàng)目號(hào)，預(yù)算費(fèi)參與mn辦公室包含1n包括n1部門(mén)號(hào)，預(yù)算費(fèi)，領(lǐng)導(dǎo)人職工號(hào)電話(huà)號(hào)碼，說(shuō)明職工號(hào)，姓名，辦公電話(huà)分擔(dān)任務(wù)部門(mén)(部門(mén)號(hào)，部門(mén)預(yù)算費(fèi)，領(lǐng)導(dǎo)人職工號(hào))職工(職工號(hào)，姓名，辦公電話(huà)，部門(mén)號(hào))辦公室(辦公室號(hào)，面積，部門(mén)號(hào))項(xiàng)目(項(xiàng)目號(hào)，項(xiàng)目預(yù)算費(fèi)，部門(mén)號(hào))電話(huà)(電話(huà)號(hào)碼，說(shuō)明，辦公室號(hào))項(xiàng)目承擔(dān)情況(職工號(hào)，項(xiàng)目號(hào)，分擔(dān)任務(wù)).101如公司部門(mén)管理系統(tǒng)的E-R圖及其轉(zhuǎn)換成的關(guān)系模式部門(mén)項(xiàng)ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.102.ExerciseRequired:Convertthe3.5FunctionalDependenciesDefinitionofFunctionalDependency(函數(shù)依賴(lài))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