Slide 2Slide 3Slide 4Slide 5Entity-Relationship (E-R) ModelClass HierarchiesAggregationConceptual design using the ER modelUNARY RELATIONSHIPBINARY RELATIONSHIPTERNARY RELATIONSHIPQUARTERY RELATIONSHIPSlide 14Additional features of the ER modelAn example of Key Constraints on ManageSlide 17Weak EntitiesSlide 19Slide 20Entity versus AttributeEntity versus RelationshipBinary versus Ternary RelationshipUsing a Ternary Relationship instead of AggregationONE TO MANY RELATIONSHIPMANY TO ONE RELATIONSHIPONE TO ONE RELATIONSHIPE-R Diagram with Composite, Multivalued, and Derived AttributesWEAK ENTITY SETSDISCRIMINATORE-R DIAGRAM WITH A WEAK ENTITY SETSlide 32Slide 33Slide 34Slide 35Entity/Relationship ModelRelationshipsRelationship SetMultiway RelationshipsSlide 40Slide 41Beers-Bars-Drinkers ExampleMultiplicity of RelationshipsExample: Drinkers Have Favorite BeersOne-One RelationshipsAttributes on RelationshipsSlide 47Converting Multiway to 2-WayRolesMore Design IssuesSubclassesE/R SubclassesDifferent Subclass ViewpointsMultiple InheritanceProblemsKeysExampleExample: A Multiattribute Key1–21–31–41–51–6Entity-Relationship (E-R) ModelCOURSE#CTITILE PROF SCHEDROOMCOURSEENROLLSTUDENTSTUIDSNAMEMAJORCREDITSGRADE1–7Class HierarchiesEmployeesssnnamelotISAHourlyEmpsContractEmpsHourlywageHoursworked contractid * Sometimes it’s natural to classify the entities in an entity set into subclass, then we will use Class Hierarchies.1–8 Aggregationnamessn lotEmployeesmonitors untilsinceSponsorsDepartmentProjectspidStarted-onpbudgetdnamedid budget * A relationship set is anassociation betweenentity sets. Sometimes we have to model a relationship between a collection of entities and relationships, then we need to use aggregation.1–9Conceptual design using the ER model•Should a concept be modeled as an entity or an attribute ?•Should a concept be modeled as an entity or a relationship ?•What are the relationships ? Should we use binary or ternary relationships ?•Should we use aggregation ?1–10UNARY RELATIONSHIP•Unary relationship : is an associate of 1 entity set. * NoteNote : an entity set is a group of related entities.Student* Entity set = * Relation = Dr. Lee* Attribute =1–11BINARY RELATIONSHIP• Binary relationship : is an associate of 2 entities sets .EmployeesWorks_InDepartment* Entity set =* Relation = * Attribute =ssn name lotdidbudgetdname1–12TERNARY RELATIONSHIPWorks_InEmployees DepartmentLocationsTernary relationship : is an associate of 3 entities sets .* Entity set = * Relation = ssnnamelot diddnamebudgetcapadd* Attribute =1–13QUARTERY RELATIONSHIPEmployeesWorks_InLocationsDepartmentProductQuartery relationship : is an associate of 4 entities sets.* Entity set =* Relation = * Attribute =1–141–15Additional features of the ER model•In the ER model allow us to draw important distinctions about the data.•One of the features of the ER model is : Key Constraints Note Note : A key constraints is also known as Primary key. A primary key is a candidate key selected to uniquely identify all other attribute values in any given row, can not contain null entries.1–16An example of Key Constraints on ManageManagesDepartmentEmployeesssnnamelotSince dnamedidbudgetssn = Social Security Number.did = Department id.1–171–18Weak EntitiesA weak entity : is an entity of which is existence depends on other entities.PolicyDepartmentEmployeesssnnamelotcostPname---------age1–191–201–21Entity versus AttributeWorks-In2DepartmentEmployeesssnnamelotfromdnamedidbudgetto* Entity set =* Relation = * Attribute =1–22Entity versus RelationshipManagesDepartmentEmployeesssnnameapptnumdidbudgetdnameMgr-Apptslotsincedbudget1–23Binary versus Ternary RelationshipCoversDependentsEmployeesssnnamePname---------agePolicieslotpolicyid cost1–24Using a Ternary Relationship instead of AggregationSponsorsDepartmentsProjectspidStarted-ondidbudgetpbudgetdnameEmployeesnamessnlot1–25 ONE TO MANY RELATIONSHIP customercustomer-namecustomer-citycustomer-idborrowercustomer-streetloanloan-numberamount1–26 MANY TO ONE RELATIONSHIP customercustomer-namecustomer-citycustomer-idborrowercustomer-streetloanloan-numberamount1–27 ONE TO ONE RELATIONSHIP customercustomer-namecustomer-citycustomer-idborrowercustomer-streetloanloan-numberamount1–28E-R Diagram with Composite, Multivalued, and Derived Attributes Customerdate-of-birthphone-numbernamecustomer-idfirst-nameagelast-namemiddle-namezip-codestatecityaddressstreetStreet-number apartment-numberstreet-name1–29WEAK ENTITY SETS An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship. The identifying relationship is many to one from the weak entity set to the identifying entity set, and the participation of the weak entity set in the relationship is total.1–30DISCRIMINATOR The discriminator of a weak entity set is a set of attributes that allows this distinction to be made. For example, the discriminator of a weak entity set payment is the attribute payment-number, since, for each loan a payment number uniquely identifies one single payment for that loan. The discriminator of a weak entity set is also called the partial key of the entity set. Note: although each payment entity is distinct, payments for different loans may share the same payment-number. Thus, payment entity set does not have a primary key; it is a weak entity set. The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set’s discriminator.1–31 E-R DIAGRAM WITH A WEAK ENTITY SETIn a E-R diagrams, a doubly outlined box indicates a weak entity set, and a doubly outlined diamond indicates the corresponding identifying relationship. We underline the discriminator of a weak entity set with a dashed line. loanE-R diagram with a weak entity setloan-payment paymentpayment-datepayment-amountpayment-numberamountloan-number1–321–331–341–351–36StudentsEntity/Relationship ModelDiagrams to represent designs.•Entity like object, = “thing.”•Entity set like class = set of “similar”
View Full Document
Unlocking...