DeptofComputerScienceUCDavis2EntityRelationshipModel ECS165AWQ1116WhatdoesConceptualDesignincludeIdeas0Highleveldesign0Relationaldatabaseschema0RelationalDBMS 15EntityRelationsh ID: 953489
Download Pdf The PPT/PDF document "ECS165AWQ11152ConceptualModelingusingthe..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
ECS-165AWQ'11152.ConceptualModelingusingtheEntity-RelationshipModelContentsBasicconcepts:entitiesandentitytypes,attributesandkeys,relationshipsandrelationshiptypesEntity-Relationshipschema(akaERdiagram)ConstraintsonrelationshiptypesDesignchoicesEnhancedEntity-RelationshipmodelfeaturesStepsindesigninganERschemaTranslationofanERschematotables Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1116WhatdoesConceptualDesigninclude?Ideas�!High-leveldesign�!Relationaldatabaseschema�!RelationalDBMS Entity-Relationshipmodelisusedintheconceptualdesignofadatabase(+conceptuallevel,conceptua
lschema)Designisindependentofallphysicalconsiderations(DBMS,OS,...).Questionsthatareaddressedduringconceptualdesign:{Whataretheentitiesandrelationshipsofinterest(mini-world)?{Whatinformationaboutentitiesandrelationshipsamongentitiesneedstobestoredinthedatabase?{Whataretheconstraints(orbusinessrules)that(must)holdfortheentitiesandrelationships?AdatabaseschemaintheERmodelcanberepresentedpictorially(Entity-Relationshipdiagram) Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1117EntityTypes,EntitySets,AttributesandKeysEntity:real-worldobjectorthingwithanindependentexistenceandwhichisdistinguishablefromotherobject
s.Examplesareaperson,car,customer,product,gene,booketc.Attributes:anentityisrepresentedbyasetofattributes(itsdescriptiveproperties),e.g.,name,age,salary,priceetc.Attributevaluesthatdescribeeachentitybecomeamajorpartofthedataeventuallystoredinadatabase.Witheachattributeadomainisassociated,i.e.,asetofpermittedvaluesforanattribute.Possibledomainsareinteger,string,date,etc.EntityType:Collectionofentitiesthatallhavethesameattributes,e.g.,persons,cars,customersetc.EntitySet:Collectionofentitiesofaparticularentitytypeatanypointintime;entitysetistypicallyreferredtousingthesamenameasentitytype. Dept.ofComputerScienceUCDavis2.Entity
-RelationshipModel ECS-165AWQ'1118KeyattributesofanEntityTypeEntitiesofanentitytypeneedtobedistinguishable.Asuperkeyofanentitytypeisasetofoneormoreattributeswhosevaluesuniquelydetermineeachentityinanentityset.Acandidatekeyofanentitytypeisaminimal(intermsofnumberofattributes)superkey.Foranentitytype,severalcandidatekeysmayexist.Duringconceptualdesign,oneofthecandidatekeysisselectedtobetheprimarykeyoftheentitytype. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1119Relationships,RelationshipTypes,andRelationshipSetsRelationship(instance):associationamongtwoormoreentities,e.g.,\customer'Smith'orderspr
oduct'PC42'"RelationshipType:collectionofsimilarrelationshipsAnn-aryrelationshiptypeRlinksnentitytypesE1;:::;En.EachrelationshipinarelationshipsetRofarelationshiptypeinvolvesentitiese12E1;:::;en2EnRf(e1;:::;en)je12E1;:::;en2Engwhere(e1;:::;en)isarelationship.Degreeofarelationship:referstothenumberofentitytypesthatparticipateintherelationshiptype(binary,ternary,...).Roles:Thesameentitytypecanparticipatemorethanonceinarelationshiptype. Rolelabelsclarifysemanticsofarelationship,i.e.,thewayinwhichanentityparticipatesinarelationship.;recursiverelationship. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel reports_toEMPLOY
EESsupervisorsubordinate ECS-165AWQ'1120RelationshipAttributes:Arelationshiptypecanhaveattributesdescribingpropertiesofarelationship.\customer'Smith'orderedproduct'PC42'onJanuary11,2005,for$2345".Theseareattributesthatcannotbeassociatedwithparticipatingentitiesonly,i.e.,theymakeonlysenseinthecontextofarelationship.Notethatarelationshipdoesnothavekeyattributes!Theidenticationofaparticularrelationshipinarelationshipsetoccursthroughthekeysofparticipatingentities. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1121ExampleofanEntity-RelationshipDiagram Customers-Suppliers-ProductsEntity-RelationshipDiagramRe
ctanglesrepresententitytypesEllipsesrepresentattributesDiamondsrepresentrelationshiptypesLineslinkattributestoentitytypesandentitytypestorelationshiptypesPrimarykeyattributesareunderlinedEmptyCircleattheendofalinelinkinganattributetoanentitytyperepresentsanoptional(null)attribute(notmentionedintextbook)Notintheabovediagram,butlaterinexamples:DoubleEllipsesrepresentmulti-valuedattributes Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel offersPriceSAddressChainCAddressCUSTOMERSordersAccountPRODUCTSSUPPLIERSQuantityFNameLNameProdnameSName ECS-165AWQ'1122ConstraintsonRelationshipTypesLimitthenumberofpossibleco
mbinationsofentitiesthatmayparticipateinarelationshipset.Therearetwotypesofconstraints:cardinalityratioandparticipationconstraintsVeryusefulconceptindescribingbinaryrelationshiptypes.Forbinaryrelationships,thecardinalityratiomustbeoneofthefollowingtypes:Many-To-Many(default) Meaning:Anemployeecanworkinmanydepartments(0),andadepartmentcanhaveseveralemployeesMany-To-One Meaning:Anemployeecanworkinatmostonedepartment(1),andadepartmentcanhaveseveralemployees. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1123One-To-Many Meaning:Anemployeecanworkinmanydepartments(0),butadepartmentcanhaveatmostonee
mployee.One-To-One Meaning:Anemployeecanworkinatmostonedepartment,andadepartmentcanhaveatmostoneemployee. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1124ConstraintsonRelationshipTypes(cont.)Amany-onerelationshiptype(andthecounterpartone-many)isalsooftencalledafunctionalrelationship.Cardinalityratioofarelationshipcana ecttheplacementofarelationshipattribute.E.g.,incaseofamany-onerelationshiptype,onecanplacearelationshipattributeataparticipatingentitytype.Participationconstraint:specieswhethertheexistenceofanentitye2EdependsonbeingrelatedtoanotherentityviatherelationshiptypeR.total:eachentitye2Emustpar
ticipateinarelationship,itcannotexistwithoutthatparticipation(totalparticipationakaexistencedependency). partial:default;eachentitye2Ecanparticipateinarelationship Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1125Insteadofacardinalityratioorparticipationconstraint,moreprecisecardinalitylimits(akadegreeconstraintsintextbook)canbeassociatedwithrelationshiptypes: Eachentitye12E1mustparticipateinrelationshipsetRatleastmin1andatmostmax1times(analogousfore22E2).FrequentlyusedcardinalitiesRelationship (min1;max1) (min2;max2) pictorialnotation many-to-many (0;) (0;) many-to-one (0;1) (0;) one-to-one (0;1) (0;1)
Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel (min , max )E111(min , max )22E2R ECS-165AWQ'1126DesignChoicesforERConceptualDesignItispossibletodeneentitiesandtheirrelationshipsinanumberofdi erentways(inthesamemodel!).Shouldareal-worldconceptbemodeledasanentitytype,attribute,orrelationshiptype?Is\Address"anattributeoranentitytype?Decisiondependsupontheuseonewantstomakeofaddressinformation.Ifoneisinterestedinthestructure,e.g.,(City,Street,Zip-Code),Addressmustbemodeledasanentitytype(orasacomplexattribute).Shouldaconceptbemodeledasanentitytypeorrelationshiptype? Hereasuppliercannoto erthesameproductfordi eren
tprices!Why?Modelingpriceasanentitytyperesolvesthisproblem: Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel offersPriceSUPPLIERSPRODUCTS offersSUPPLIERSPRODUCTSPRICEAmount ECS-165AWQ'1127EnhancedERModelingConceptsAlthoughmostpropertiesofentitiesandrelationshipscanbeexpressedusingthebasicmodelingconstructs,someofthemarecostlyanddiculttoexpress(andtounderstand).That'swhytherearesomeextensionstotheERmodel.Subclasses,Superclasses,andInheritanceInsomecases,anentitytypehasnumeroussubgroupingsofitsentitiesthataremeaningfulandneedtoberepresentedexplicitlybecauseoftheirsignicancetotheDBapplication. Relationshipsandattribute
sofsuperclassareinheritedtosubclass(inparticularprimarykeyattribute(s));subclasscanhaveadditionalattributesandrelationshipsAnentitycannotexistmerelybybeingamemberofonlyasubclass. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ISAPERSONGPASTUDENTMajorNameSSN ECS-165AWQ'1128SpecializationProcessofdeningasetofsubclassesofanentitytype(top-down) HOURLY EMPSisasubclassofEMPLOYEESandthusinheritsitsattributesandrelationships(sameforCONTRACT EMPS).Generalization:Reverseprocessofspecialization(bottom-up);identifycommonfeaturesofentitytypesandgeneralizethemintosinglesuperclass(includingprimarykey!) Dept.ofComputerScienceUCDa
vis2.Entity-RelationshipModel ISAEMPLOYEESSocialSNHOURLY_EMPSAddressNameCONTRACT_EMPSContractnoHoursWages ISACARTRUCKTonageAxelsMaxSpeedNoOfPassengersVEHICLEVehicleNoPriceLicensePlate ECS-165AWQ'1129ConstraintsonSpecializationdisjointnessandtotalityconstraintsdisjoint,total/partial:eachentityinthesuperclassmust/canbeinexactlyonesubclassThedisjointnessconstraintisindicatedbytheword\disjoint"rightnexttotheISAtriangleThetotalityconstraintisindicatedbydoublelinesleadingfromthesuperclasstotheISAtriangleoverlappingconstraintsoverlapping,total(...mustbeinatleastonesubclass)Inthiscase,onlydoublelinesleadingtotheISAtriangleareused.overlappin
g,partial(...canbeinatleastonesubclass)Note:specialrulesarerequiredtopropagatedeletionsfromsuperclass/subclass(implementedlater).Notethatforgeneralization,eachentityinthesuperclassmustbelongtoexactlyonesubclass Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1130StepsinDesigninganEntity-RelationshipSchema[Step1]Identifyentitytypes(entitytypevs.attribute)[Step2]Identifyrelationshiptypes[Step3]Identifyandassociateattributeswithentityandrelationshiptypes[Step4]Determineattributedomains[Step5]Determineprimarykeyattributesforentitytypes[Step6]Associate(rened)cardinalityratio(s)withrelationshiptypes[Step7]Designgenerali
zation/specializationhierarchiesincludingconstraints(includesnaturallanguagestatementsaswell) Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1131TranslationofERSchemaintoTablesAnERschemacanberepresentedbyacollectionoftableswhichrepresentcontentsofthedatabase(instance).Primarykeysallowentitytypesandrelationshiptypestobeexpresseduniformlyastables.Foreachentityandrelationshiptype,auniquetablecanbederivedwhichisassignedthenameofthecorrespondingentityorrelationshiptype.Eachtablehasanumberofcolumnsthatcorrespondtotheattributesandwhichhaveuniquenames.AnattributeofatablehasthesamedomainastheattributeintheERsche
ma.TranslatinganERschemaintoacollectionoftablesisthebasisforderivingarelationaldatabaseschemafromanERdiagram. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1132TranslatingEntityTypesintoTablesGivenanentitytypeE1with(atomic)attributesA1;:::;AnandassociateddomainsD1;:::;Dn.TranslationoftheentitytypeCUSTOMERSintotableCUSTOMERS FName LName CAddress Account Michael Smith SanFrancisco 10,000 George Jones NewYork 2,000 Kent Clark LosAngeles -4,600 ... ... ... ... InSQL:createtableCustomers(FNamechar(40),LNamechar(40),CAddresschar(70),Accountreal);Arowinsuchatablecorrespondstoanentityfromtheentityset. Dept.ofC
omputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1133TranslatingRelationshipTypesintoTablesAmany-manyrelationshiptypeisrepresentedasatablewithcolumnsfortheprimarykeyattributesoftheparticipatingentitytypes,andanydescriptiveattributesoftherelationshiptype.Example:Relationshiptypeo ers Prodname SName Price PC42 Hal-Mart 2,100 MacIV Sears 2,500 ... ... ... ProdnameandSNamearetheprimarykeyattributesoftheentitytypesSUPPLIERSandPRODUCTS.Translationofone-manyandmany-one(functional)andone-onerelationshiptypesintotablescanbeoptimized;notableforrelationshiptypenecessary! Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS
-165AWQ'1134TranslatingSubclasses/SuperclassesintoTablesMethod1:Formatableforthesuperclassandformatableforeachsubclass.Includetheprimarykeyattributesofthesuperclassineachsuchtable.Example:Employees(SocialSN,Name,Address)Hourly_Emps(SocialSN,Hours,Wages)Contract_Emps(SocialSN,ContractNo)Method2:Formatableforeachsubclassandincludeallattributesofthesuperclass.Hourly_Emps(SocialSN,Name,Address,Hours,Wages)Contract_Emps(SocialSN,Name,Address,Contractno)Method2hasnotableforthesuperclassEMPLOYEES.Method3:UsenullvaluesEmployees(SocialSN,Name,Address,Hours,Wages,ContractNo)HourlyemployeeswillhaveanullvalueforContractNo.Contractemployees
willhavenullvaluesforHoursandWages. Dept.ofComputerScienceUCDavis2.Entity-RelationshipModel ECS-165AWQ'1135SummaryofConceptualDesignConceptualdesignfollowsrequirementsanalysis,yieldsahighleveldescriptionofdatatobestored(conceptuallevel).ERmodelisapopularmodelforconceptualdesign,constructsareexpressive,closetothewaypeoplethinkaboutapplications;supportedbymanyCASEtools.Basicconstructsareentities,relationships,andattributesSomeadditionalconstructs:ISAhierarchies,cardinalityratios,...TherearemanyvariationsonERmodelconstructs.SeveralkindsofintegrityconstraintscanbeexpressedintheERmodel:keyconstraints,structuralconstra
ints,constraintsonspecializations{SomeofthemcanbeexpressedinSQLwhentranslatingentityandrelationshiptypesintotables{NotallconstraintscanbeexpressedintheERmodel{Constraintsplayanimportantroleindeterminingagooddatabasedesignforanapplicationdomain.ERdesignissubjective:Therearemanywaystomodelagivenscenario!Analyzingalternativeschemasisimportant!Entitytypevs.attribute,entitytypevs.relationshiptype,binaryvs.n-aryrelationshiptype,useofIS-A,generalizationandspecialization,...EnsuringagooddatabasedesignincludesanalyzingandfurtherreningrelationalschemaobtainedthroughtranslatingERschema. Dept.ofComputerScienceUCDavis2.Entity-RelationshipMo