Entity-Relationship Model (ERM) IV. Entity Relationship Modeling
Entity-Relationship Model (ERM) IV. Entity Relationship Modeling Basis of an Entity-Relationship Diagram (ERD) A design technique Diagrams entities sets (with attributes) and the relationship between the entity sets. Recall previous definitions Entity refers to the entity set and not a single entity occurrence 2 Entities and Attributes: Chen and Crow’s Foot Entity-Relationship Model (ERM) E-R diagrams are the deliverables of the design phase of information system development Depicts conceptual DB as viewed by end user We create an ERD to get agreement on the conceptual view, then we translate the ERD into the relational model for implementation 3 4 1
Attributes Multivalued Attribute in an Entity Identifier: Primary key Composite identifier: composed of more than one attribute Composite attribute: Attribute that can be subdivided to yield additional attributes Simple attribute: Attribute that cannot be subdivided Single-valued attribute: Attribute that has only a single value Multivalued attributes: Attributes that have many values 5 Representing Attributes 6 Representing Attributes Composite attributes Attributes should be atomic Simple attribute for each field Customer (accountId, lastName, firstName, street, city, state, zipcode) Multi-valued attributes Customer (accountId, lastName, firstName, street, city, state, zipcode,spouseUser,siblingUser,childUser) Represent as single-valued attribute? What’s the key? Represent as several new attributes, one for each component of the original multivalued attribute Represent as a separate table accountId lastName firstName street city state zipcode otherUser accountId otherUser 104 Breaux Carroll 76 Main St. Apopka FL 30458 Judy Breaux 104 Judy Breaux 104 Breaux Carroll 76 Main St. Apopka FL 30458 Cyrus Lambeaux 104 Cyrus Lambeaux 104 Breaux Carroll 76 Main St. Apopka FL 30458 Jean Deaux 104 Jean Deaux 7 8 2
Advantages and Disadvantages of Storing Derived Attributes Derived Attributes Derived attribute: Attribute whose value is calculated from other attributes Derived using an algorithm 9 Connectivity and Cardinality in an ERD Relationships 10 Associations between entities that always operate in both directions Participants: Entities that participate in a relationship Connectivity: Describes the relationship classification Cardinality: Expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity 11 12 3
Relationship Participation Also called modality constraints Optional participation (“may”) Crow’s Foot Symbols One entity occurrence does not require a corresponding entity occurrence in a particular relationship Mandatory participation (“must”) One entity occurrence requires a corresponding entity occurrence in a particular relationship 13 CLASS is Optional to COURSE 15 14 COURSE and CLASS in a Mandatory Relationship 16 4
Review Associative Entities 17 Representing a M:N Relationship in the Relational Model Also known as composite or bridge entities Used to represent an M:N relationship between two or more entities In the relational model, not necessarily the ERM Is in a 1:M relationship with the parent entities Composed of the primary key attributes of each parent entity May also contain additional attributes that play no role in connective process Intersection data 18 A Composite Entity in an ERD 19 20 5
Three Types of Relationship Degree in the ERM Relationship Degree Indicates the number of entities or participants associated with a relationship Unary relationship: Association is maintained within a single entity Recursive relationship: Relationship exists between occurrences of the same entity set Binary relationship: Two entities are associated Ternary relationship: Three entities are associated 21 Unary One-to-Many Relationships Salesperson (also a sales manager) 22 One-to-Many Unary Relationship A salesperson reports to exactly one sales manager, but each salesperson who does serve as a sales manager typically has several salespersons reporting to him. There is a one-to-many relationship within salespersons. Salesperson 23 Salesperson Number 137 142 170 186 198 204 267 285 323 361 388 411 439 446 483 Salesperson Commission Year Sales Manager Name Percentage Of Hire Number Baker 10 1995 186 Smith 15 2001 137 Taylor 18 1992 439 Adams 15 2001 Wang 20 1990 267 Dickens 10 1998 267 Perez 22 2000 285 Costello 10 1996 McNamara 15 1995 137 Carlyle 20 2001 483 Goldberg 20 1997 483 Davidson 18 1992 137 Warren 10 1996 186 Albert 10 2001 483 Jones 15 1995 285 SALESPERSON Relation Requires the addition of one column to the relation representing the single entity involved in the unary relationship. 24 6
General Hardware Company’s Product Set Wrench Mode l A (#11) Deluxe Wrench Set (#43) Wrench Mode l B (#14 ) Supreme Tool Set (#53) Wrench Mode l C (#17 ) Master Wrench Set (#44) Wrench Mode l D (#19) Hammer Model A (#22) Hammer Model B (#24) Deluxe Hammer Set (#48 ) Grand Tool Set (#56) Hammer Model C (#28) Drill Mod el A (#31 ) Drill Mod el B (#35 ) Figure 6.5 Gen eral Hardware Co. produ ct bill of materials. Tools and sets of tools are sold. Many-to-many nature of products. Also: prerequisite example from Oracle lab 25 Unary Many-to-Many Relationship: New Relation Assembly Part 43 11 43 14 44 11 44 17 44 19 48 22 48 24 48 28 53 43 53 48 53 31 56 44 56 48 56 35 Modified Product Relation Product Product Number Name 11 Wrench Model A 14 Wrench Model B 17 Wrench Model C 19 Wrench Model D 22 Hammer Model A 24 Hammer Model B 28 Hammer Model C 31 Drill Model A 35 Drill Model B 43 Deluxe Wrench Set 44 Master Wrench Set 48 Deluxe Hammer Set 53 Supreme Tool Set 56 Grand Tool Set PRODUCT relation Unit Price 12.50 13.75 11.62 15.80 17.50 18.00 19.95 31.25 38.50 23.95 35.00 51.00 100.00 109.95 Product Numbers have been reduced to 2 digits for simplicity. Every individual unit item and every set of tools has its own row in the relation because every item and set is available for sale. 26 Ternary Relationships Just as a binary many-to-many relationship requires the creation of an additional relation in a relational database, so does a unary many-tomany relationship Involves three different entity types. Both Assembly and Part are foreign keys linking to the Product Number column of the PRODUCT relation 27 28 7
General Hardware Co.: Ternary Relationship Salesperson Salesperson Commission Year Number Name Percentage Of Hire 137 Baker 10 1995 186 Adams 15 2001 204 Dickens 10 1998 361 Carlyle 20 2001 (a) SALESPERSON relation. Customer Number 0121 0839 0933 1047 1525 1700 1826 2198 2267 Customer Name HQ City Main St. Hardware New York Jane’s Stores Chicago ABC Home Stores Los Angeles Acme Hardware Store Los Angeles Fred’s Tool Stores Atlanta XYZ Stores Washington City Hardware New York Western Hardware New York Central Stores New York (b) CUSTOMER relation Salesperson Number 137 361 137 204 186 137 361 204 204 Existence Dependence Product Product Unit Price Number Name 16386 Wrench 12.95 19440 Hammer 17.50 21765 Drill 32.99 24013 Saw 26.25 26722 Pliers 11.50 (c) PRODUCT relation. Customer Product Number Number Date Quantity 0839 24013 2/21/2002 25 1700 16386 2/27/2002 70 2267 19440 3/1/2002 40 1047 19440 3/1/2002 15 0839 26722 3/12/2002 35 1700 16386 3/17/2002 65 0121 21765 3/21/2002 40 2267 19440 4/03/2002 30 0839 19440 4/17/2002 20 (d) SALES relation. Entity exists in the Entity exists apart from database only when it all of its related entities is associated with Referred to as a strong another related entity entity or regular occurrence entity 30 Modeling Weak Entities Also called a dependent entity Conditions Existence independence 29 Weak Entity Existence dependence Existence-dependent Has a primary key that is partially or totally derived from parent entity in the relationship Database designer determines whether an entity is weak based on business rules Customer Employee has no key of its own Is defined by relationship to “owner class” Customer Distinguished by a diagonal hash mark in each corner of its attribute area. Employee Number is a discriminator, or partial key 31 8
Implementing Weak Entities Customer Number 0121 0839 0933 1047 1525 1700 1826 2198 2267 Customer Salesperson Name Number Main St. Hardware 137 Jane’s Stores 186 ABC Home Stores 137 Acme Hardware Store 137 Fred’s Tool Stores 361 XYZ Stores 361 City Hardware 137 Western Hardware 204 Central Stores 186 CUSTOMER relation. HQ City New York Chicago Los Angeles Los Angeles Atlanta Washington New York New York New York Another Example Customer Employee Employee Number Number Name Title 0121 27498 Smith Co-Owner 0121 30441 Garcia Co-Owner 0933 25270 Chen VP Sales 0933 30441 Levy Sales Manager 0933 48285 Morton President 1525 33779 Baker Sales Manager 2198 27470 Smith President 2198 30441 Jones VP Sales 2198 33779 Garcia VP Personnel 2198 35268 Kaplan Senior Accountant CUSTOMER EMPLOYEE relation. Customer Customer Employee 33 In the Relational Model 34 Good Reading Bookstores 35 36 9
Good Reading Bookstores World Music Association Publisher Year Name City Country Telephone Founded PUBLISHER Relation Author Author Year Year Number Name Born Died AUTHOR Relation Book Book Publication Publisher Number Name Year Pages Name BOOK Relation Customer Customer Number Name Street City State Country CUSTOMER Relation Book Author Number Number WRITING Relation Book Customer Number Number Date Price Quantity SALE Relation 37 World Music Association 38 Lucky Rent-A-Car O rc h e stra M u s ic N am e C ity C o u n try D irec to r O R C H E S T R A R e la tio n A n n u a l O rc h es tra M u sicia n M u sicia n N um ber N am e In stru m e n t S alary N a m e M U S IC IA N R elatio n M u sicia n N u m b e r D eg re e U n iv ers ity Y ea r D E G R E E R e la tio n C o m p o se r D ate O f N am e C o u n try B irth C O M P O S E R R e la tio n C o m p o sitio n C o m p o se r N am e N am e Y ea r C O M P O S IT IO N R elatio n O rc h e stra C o m p o sitio n C o m p o s er N am e N am e N am e Y e a r P ric e R E C O R D IN G R ela tio n 39 40 10
Lucky Rent-A-Car Developing an ER Diagram M anufacturer M an ufactu rer Sales R ep Sales R ep C oun try N am e Telephone N am e M A N U FA C T U R E R R elation M anufacturer C ar Serial M o del Y ear C lass N am e N u m b er C A R R elation C ar Serial R epair R epair N u m b er N u m b er D ate Procedure M ileage T im e M A IN TE N A N C E R elation C ustom er C usto m er C usto m er C u stom er N u m b er N am e A ddress Telep hone C U ST O M E R R elation C ar Serial C usto m er R ental R eturn T otal N u m b er N um b er D ate D ate C ost R EN TA L R elation 41 Create a detailed narrative of the organization’s description of operations Identify business rules based on the descriptions Identify main entities and relationships from the business rules Develop the initial ERD Identify the attributes and primary keys that adequately describe entities Revise and review ERD 42 11
1 IV. Entity Relationship Modeling 2 Entity-Relationship Model (ERM) Basis of an Entity-Relationship Diagram (ERD) A design technique Diagrams entities sets (with attributes) and the relationship between the entity sets. Recall previous definitions Entityrefers to the entity set and not a single entity occurrence E-R diagrams are the deliverablesof the
Deloitte Professor of ERM Associate Director Executive Director ERM Initiative ERM Initiative ERM Initiative The ERM Initiative in the Poole College of Management at North Carolina State University provides thought leadership on enterprise risk management (ERM) and its integration with strategic planning and corporate governance, with a focus .
Surveys were conducted to understand the current practices of enterprise risk management (ERM) stakeholder engagement. Depending on the level of ERM maturity within an organization, challenges still exist to improve ERM buy-in. The benefit of risk management is difficult to measure. The value that ERM can bring to
A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. Employees ssn name lot
5. Explain how the entity relationship (ER) model helped produce a more structured relational database design environment. An entity relationship model, also known as an ERM, helps identify the database's main entities and their relationships. Because the ERM components are graphically represented, their role is more easily understood.
This article explores the challenges face when implementing ERM and offers solutions for firms struggling with the concepts and execution. We draw upon Hydro One’s experience in achieving ERM as a best practice case study. The company’s ERM methods have been and documented extensively. With over 15 years of ERM success, Hydro
perspectives on a practical enterprise risk management (ERM) approach for national risk management, that is, ERM at the federal1 government level. This is based on the author’s Consulting experience in both the private and public sectors Value-based ERM approach—a synthesis of value-based management and enterprise risk
D4033-EN ERM-WIFI User Manual 4 2.0 USE: The Digi-Star ERM-WIFI is designed exclusively for use with Digi-Star scale indicators, accessories, and associated agricultural weighing equipment. The Digi-Star ERM-WIFI device allows Apple and Android devices to wirelessly
Additif très dangereux E249 : Nitrite de potassium . Conservateur chimique. Risques : essoufflements, vertiges, maux de tête, chez les nourrissons les nitrites peuvent provoquer la mort par asphyxie car ils empêchent les globules rouges de transporter l'oxygène, cancérigène. Très répandu dans les charcuteries, les salaisons, le foie gras et le bacon traité, MÊME DANS LES .
