数据库管理系统概述英文版课件:2 ER DiagramPPT推荐.pptx
《数据库管理系统概述英文版课件:2 ER DiagramPPT推荐.pptx》由会员分享,可在线阅读,更多相关《数据库管理系统概述英文版课件:2 ER DiagramPPT推荐.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