数据库管理系统概述英文版课件：2 ER Diagram.pptx

数据库管理系统概述英文版课件：2 ER Diagram.pptx

《数据库管理系统概述英文版课件：2 ER Diagram.pptx》由会员分享，可在线阅读，更多相关《数据库管理系统概述英文版课件：2 ER Diagram.pptx（52页珍藏版）》请在冰点文库上搜索。

COMP231,COMP231,1,ERDiagram,Entity-RelationshipModel,COMP231,2,Entity-Relationship（ER）modelisapopularconceptualdatamodel.ThismodelisusedinthedesignofdatabaseapplicationsThemodeldescribesdatatobestoredandtheconstraintsoverthedata.E-Rmodelviewstherealworldasacollectionofentitiesandrelationshipsamongentities.,Outline,EntityRelationshipBinaryrelationshipWeakEntity/StrongEntityClassHierarchyRelationshipNon-Binaryrelationship,COMP231,3,Entitiesandattributes,COMP231,4,AnentityisanobjectintherealworldthatisdistinguishablefromotherobjectsE.g.,AclassroomAteacherTheaddressoftheteacher,Entitiesandattributes,Anentityisdescribedusingasetofattributeswhosevaluesareusedtodistinguishoneentityfromanotherofsametype,name=ChanTaiMan,COMP231,5,address=25,SiuRoad,Shatinage=55phone=1234-5667,Entitiesandattributes,Anentitysetisacollectionofentitiesofthesametype,e1（ChanTaiMan,55,）e2（CheungTin,20,）e3（WongKing,33,）,COMP231,6,Entitiesandattributes,Allentriesinagivenentitysethavethesameattributes（thevaluesmaybedifferent）.employee=（name,address,age,phone）employee1employee2,name=ChanTaiManaddress=25,SiuRoad,Shatin,age=55phone=1234-5667,name=CheungTinaddress=25,BigStreet,Shatin,COMP231,7,age=20phone=2338-7779,COMP231,8,ERDiagram,TheERmodelcanbepresentedgraphicallybyanERdiagram,age,Employee,name,address,phone,entity,attribute,COMP231,9,Differentattributetypes,SimpleattributeCompositeattributeMulti-valuedattributeDerivedattribute,Simpleattribute,Simpleattributecontainsasinglevalue,age,name,address,phone,Employee,attribute,COMP231,entity,10,Compositeattribute,CompositeattributeContainsseveralcomponents,age,name,address,phone,street,city,country,Employee,attribute,COMP231,entity,11,Multi-valuedattribute,Multi-valuedattributeContainsmorethanonevalue,age,name,address,phone,street,city,country,Doubleovals,Employee,attribute,COMP231,entity,12,Derivedattribute,DerivedattributeComputedfromotherattributese.g.,agecanbecomputedfromdataofbirthandthecurrentdate,age,name,address,phone,street,city,country,Onedashedoval,Employee,attribute,DateofBirthCOMP231,entity,13,COMP231,14,Keyattributes,Employee,Key数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。

AsetofattributesthatcanuniquelyidentityanentityE.g.,HKIDHKIDname,underline,COMP231,15,Keyattributes,Employee,CompositeKeyTwoormoreattributesareusedtoserveasakeyE.g.,NameorAddressalonecannotuniquelyidentifyanemployee,buttogethertheycanNameAddress,Keyattributes,AnentitymayhavemorethanonekeyE.g.,HKIDandName,Addressbotharetwokeys,Employee,Address,Name,HKID,IsHKID,Name,Addressakey?

COMP231,16,Keyattributes,Aminimalsetofattributesthatuniquelyidentifiesanentityiscalledacandidatekey.E.g.,HKIDandName,AddressbotharetwocandidatekeysIsHKID,Name,COMP231,17,Addressacandidatekey?

Iftherearemanycandidatekeys,weshouldchooseonecandidatekeyastheprimarykey.,Keyattributes,Employee,Sometimes,artificialkeyscanbecreatedE.g.,ifthereisnoHKIDstoredinEmployee,wecancreateanewattributecalled“EmpID”NameGender,EmpID,COMP231,18,Outline,EntityRelationshipBinaryrelationshipWeakEntity/StrongEntityClassHierarchyRelationshipNon-Binaryrelationship,COMP231,19,Relationship,COMP231,20,ArelationshipisanassociationamongseveralentitiesThedegreereferstothenumberofentitysetsthatparticipateinarelationshipset.Relationshipsetsthatinvolvetwoentitysetsarebinary（ordegreetwo）.Relationshipsamongmorethantwoentitysetsarerare.（Wewilldiscusslaterindetails）,BinaryRelationship,Employeesworkindepartments“Work_in”isarelationshipbetweenEmployeesandDepartmentsItmeansthatanemployeeworksinmultipledepartmentsandviceversa,EmpID,Work_in,Employees,Departments,Name,did,dname,COMP231,21,BinaryRelationship,EmpID,Work_in,Employees,Departments,Name,did,dname,Arelationshipcanalsohaveattributeswhichareusedtodescribetherecordinformationabouttherelationship（insteadoftheinformationofeachindividualentity）.StartdateoftheemploymentStart_date,COMP231,22,RecursiveRelationship,RecursiveRelationshipEntitysetsofarelationshipneednotbedistinctSometimes,arelationshipmightinvolvetwoentitiesinthesameentitysetE.g.,Employeesrelatedtoemployees,EmpID,Employees,Name,Reports_to,supervisor,Subordinate下级,COMP231,23,RecursiveRelationship,Sinceemployeesreporttootheremployees,Everyrelationshipin“Reports_To”isofform（emp1,emp2）wherebothemp1andemp2areentitiesinemployees.However,theyplaydifferentroles.emp1reportstoemp2,whichisreflectintheroleindicatorssupervisorandsubordinateinthediagram,EmpID,Employees,Name,Reports_to,supervisor,COMP231,24,subordinate,Constraints,COMP231,25,Themodeldescribesdatatobestoredandtheconstraintsoverthedata,Constraints,Themappingofabinaryrelationshipcanbeclassifiedintothefollowingcases:

Many-to-Many,1-to-1,COMP231,26,1-toMany,Many-to-1,One-to-manyrelationship,One-to-many（1-to-Many）relationshipAnentityinBcanbeassociatedwithatmostoneentityinAAB,1-toMany,COMP231,27,COMP231,28,One-to-manyrelationship,AloanisassociatedwithatmostonecustomerviaborrowerAcustomerisassociatedwithseveral（including0）AcusBloanloansviaborrower,1-toMany,Key=customer-id,loan-number,Notacandidatekey!

Candidatekey=?

COMP231,29,Many-to-onerelationship,Many-to-one（Many-to-1）relationshipSimilarto1-to-manyAB,Many-to-1,COMP231,30,Many-to-onerelationship,Aloanisassociatedwithseveral（including0）customersviaborrower,Acustomerisassociatedwithatmostoneloanvia,borrower,Many-to-1Key=customer-id,loan-number,A,B,Notacandidatekey!

Candidatekey=?

COMP231,31,One-to-onerelationship,One-to-one（1-to-1）relationshipAnentityinAisrelatedtoatmostoneentityinBAnentityinBisrelatedtoatmostoneentityinA.AB,1-to-1,32,One-to-onerelationship,1-to-1COMP231,A,AcustomerisassociatedwithatmostoneloanviatherelationshipborrowerAloanisassociatedwithatmostonecustomerviaborrowerB,Key=customer-id,loan-number,Notacandidatekey!

Candidatekey=?

COMP231,33,Many-to-manyRelationship,Many-to-manyRelationshipAnentityinAisassociatedwithanynumberofentitiesinBAnentityinBisassociatedwithanynumberofentitiesinAThatis,thereisnorestrictioninthemapping.AB,Many-to-Many,COMP231,34,Many-to-manyRelationship,Acustomerisassociatedwithseveral（possibly0）loansviaborrowerAloanisassociatedwithseveral（possibly0）,customersviaborrower,Many-to-ManKyey=customer-id,loan-number,A,B,Candidatekey!

ParticipationConstraint,Theaboveconstraints（e.g.,1-to-many）tellsusthatacustomerborrowssomeloans.Anaturalquestiontoaskistowhethereveryloanisborrowedbyatleastonecustomer.Supposethateachloanisborrowedbyatleastonecustomer.Suchaconstraintiscalledaparticipationconstraint.,A,1-toMany,COMP231,35,B,ParticipationConstraint,Wecanclassifyparticipationinrelationshipsasfollows.,TotalEachentityintheentitysetmustbeassociatedinatleastonerelationshipPartialEachentityintheentitysetmay（ormaynot）beassociatedinarelationship,A,1-toMany,COMP231,36,B,ParticipationConstraint,TheparticipationofloaninborroweristotalEveryloanmustbeborrowedbyacustomerTheparticipationofcustomerinborrowispartialSomecustomersmay（ormaynot）borrowloans,COMP231,37,Outline,EntityRelationshipBinaryrelationshipWeakEntity/StrongEntityClassHierarchyRelationshipNon-Binaryrelationship,COMP231,38,WeakEntities,COMP231,39,StrongEntityAnentitycanbeuniquelyidentifiedbysomeattributesrelatedtothisentityE.g.,EmployeehasanattributeEmpID（whichcanbeusedtouniquelyidentifyeachemployee）WeakEntityAnentitycannotbeuniquelyidentifiedbyallattributesrelatedtothisentity,WeakEntities,COMP231,40,ExampleSupposeemployeescanpurchaseinsurancepoliciestocovertheirdependents.TheattributeofthedependentsentitysetarepnameandageTheattributepnamecannotuniquelyidentifyadependentDependentisaweakentityset.Adependentcanonlybeidentifiedbyconsideringsomeofitsattributesinconjunctionwiththeprimarykeyofemployee（identifyingentityset）.Thesetofattributesthatuniquelyidentifyaweakentityforagivenownerentityiscalledadiscriminatororpartialkey.,WeakEntities,EmpID,pname,Policy,age,Employee,DependentAdependentcannotbeuniquelyidentifiedby“pname”.Note:

AchildmaynotbeoldenoughtohaveaHKIDnumberEvenifhe/shehasaHKIDnumber,thecompanymaynotbeinterestedinkeepingitinthedatabase.,Identifyingentityset=EmployeeDiscriminator=pnameKey=EmpID,pname,COMP231,41,WeakEntities,EmpID,pname,Policy,age,Employee,DependentDefinition:

IfaweakentitysetWisdependentonastrongentitysetE,wesaythatEownsW.E.g.,EmployeeownsDependent,Identifyingentityset=EmployeeDiscriminator=pnameKey=EmpID,pname,COMP231,42,Apaymentitselfcannotbeidentifiedby“payment-number”loanownspayment,loan-number,payment-number,loan-payment,amount,loan,payment,amount,Identifyingentityset=loanDiscriminator=payment-number,COMP231,43,WeakEntitiesKey=loan-number,payment-number,Outline,EntityRelationshipBinaryrelationshipWeakEntity/StrongEntityClassHierarchyRelationshipNon-Binaryrelationship,COMP231,44,ClassHierarchy,Hour_wages,Contract_ID,Contract_Emps,ISA,Employees,Sometimes,itisnaturaltoclassifytheentitiesinanentitysetintosubclassesnameIDaddress,Hourly_Emps,Hour_worked,COMP231,45,ClassHierarchy,COMP231,46,Attributesareinheritedbytheentitysetinthesubclass.E.g.,theattributesdefinedforanHourly_EmpsentityaretheattributesforEmployeesplusthatofHourly_EmpsAclasshierarchycanbeviewedinoneofthetwoways.Aclassisspecializedintosubclasses.The