Chapter 4 ER Modeling PDF

Chapter 4 Entity Relationship (ER) Modeling  Learning Objectives After completing this chapter, you will be able to: Identify the main characteristics of entity relationship components Describe how relationships between entities are defined, refined, and incorporated into the datab...

Chapter 4 Entity Relationship (ER) Modeling  Learning Objectives After completing this chapter, you will be able to: Identify the main characteristics of entity relationship components Describe how relationships between entities are defined, refined, and incorporated into the database design process See how ERD components affect database design and implementation Understand that real-world database design often requires the reconciliation of conflicting goals © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 2 protected website for classroom use.  The Entity Relationship Model (ERM) Forms the basis of an entity relationship diagram (ERD) Conceptual database as viewed by end user Database’s main components Entities Attributes Relationships © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 3 protected website for classroom use.  Entities Object of interest to the end user Refers to the entity set and not to a single entity occurrence ERM corresponds to a table—not to a row—in the relational environment ERM refers to a table row as an entity instance or entity occurrence In Chen, Crow’s Foot, and UML notations, an entity is represented by a rectangle that contains the entity’s name The entity name, a noun, is usually written in all capital letters © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 4 protected website for classroom use.  Attributes (1 of 7) Characteristics of entities Required attribute: must have a value and cannot be left empty Optional attribute: does not require a value and can be left empty Domain: set of possible values for a given attribute Identifier: one or more attributes that uniquely identify each entity instance Composite identifier: primary key 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 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 5 protected website for classroom use. Attributes (2 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 6 protected website for classroom use. Attributes (3 of 9) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 7 protected website for classroom use. Attributes (3 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 8 protected website for classroom use.  Attributes (4 of 7) Requirements of multivalued attributes Create several new attributes, one for each component of the original multivalued attribute Develop a new entity composed of the original multivalued attribute’s components Derived attribute: attribute whose value is calculated from other attributes Derived using an algorithm © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 9 protected website for classroom use. Attributes (6 of 9) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 10 protected website for classroom use. Attributes (5 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 11 protected website for classroom use. Attributes (5 of 10) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 12 protected website for classroom use. Attributes (6 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 13 protected website for classroom use.  Attributes (7 of 7) Table 4.2 Advantages and Disadvantages of Storing Derived Attributes Derived Attribute: Stored Derived Attribute: Not Stored Advantage Saves CPU processing cycles Saves storage space Saves data access time Computation always yields Data value is readily available current value Can be used to keep track of historical data Disadvantage Requires constant maintenance Uses CPU processing cycles to ensure derived value is Increases data access time current, especially if any values Adds coding complexity to used in the calculation change queries © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 14 protected website for classroom use.  Relationships, Connectivity, and Cardinality Association between entities that always operate in both directions Participants: entities that participate in a relationship Connectivity: describes the relationship classification Include 1:1, 1:M, and M:N Cardinality: expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity In the ERD, cardinality is indicated by placing the appropriate numbers beside the entities, using the format (x, y) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 15 protected website for classroom use.  16 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.  Existence Dependence Existence dependence Entity exists in the database only when it is associated with another related entity occurrence Existence independence Entity exists apart from all of its related entities Referred to as a strong entity or regular entity © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 17 protected website for classroom use.  Relationship Strength Weak (non-identifying) relationship Primary key of the related entity does not contain a primary key component of the parent entity Strong (identifying) relationships Primary key of the related entity contains a primary key component of the parent entity © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 18 protected website for classroom use. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 19 protected website for classroom use. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 20 protected website for classroom use.  Weak Entities (1 of 3) Conditions of a weak entity 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 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 21 protected website for classroom use. Weak Entities (2 of 3) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 22 protected website for classroom use. Weak Entities (3 of 3) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 23 protected website for classroom use.  Relationship Participation (1 of 5) Optional participation One entity occurrence does not require a corresponding entity occurrence in a particular relationship Mandatory participation One entity occurrence requires a corresponding entity occurrence in a particular relationship © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 24 protected website for classroom use. Relationship Participation (2 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 25 protected website for classroom use. Relationship Participation (3 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 26 protected website for classroom use. Relationship Participation (4 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 27 protected website for classroom use. Relationship Participation (5 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 28 protected website for classroom use.  Relationship Degree (1 of 2) Indicates the number of entities or participants associated with a relationship Unary relationship: association is maintained within a single entity Binary relationship: two entities are associated Ternary relationship: three entities are associated Recursive relationship: relationship exists within a single entity type © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 29 protected website for classroom use. Relationship Degree (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 30 protected website for classroom use. Recursive Relationships (of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 31 protected website for classroom use.  Recursive Relationships (1 of 2) Relationship can exist between occurrences of the same entity set Naturally, such a condition is found within a unary relationship - Common in manufacturing industries One common pitfall when working with unary relationships is to confuse participation with referential integrity Similar because they are both implemented through constraints on the same set of attributes © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 32 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 33 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 34 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 35 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 36 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 37 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 38 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 39 protected website for classroom use.  Associative (Composite) Entities (1 of 2) Used to represent an M:N relationship between two or more entities Has 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 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 40 protected website for classroom use. Recursive Relationships (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 41 protected website for classroom use. Associative (Composite) Entities (2 of 3) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 42 protected website for classroom use. Associative (Composite) Entities (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 43 protected website for classroom use.  Developing an ER Diagram (1 of 11) Activities involved in building and ERD 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 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 44 protected website for classroom use.  Developing an ER Diagram Can you come up with 9 to 10 entities in typical university? Now, start developing an ERD diagram for this university. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 45 protected website for classroom use. Developing an ER Diagram (2 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 46 protected website for classroom use. Developing an ER Diagram (3 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 47 protected website for classroom use. Developing an ER Diagram (4 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 48 protected website for classroom use. Developing an ER Diagram (5 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 49 protected website for classroom use. Developing an ER Diagram (6 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 50 protected website for classroom use. Developing an ER Diagram (7 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 51 protected website for classroom use. Developing an ER Diagram (8 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 52 protected website for classroom use. Developing an ER Diagram (9 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 53 protected website for classroom use. Developing an ER Diagram (10 of 11) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 54 protected website for classroom use.  Developing an ER Diagram (11 of 11) Table 4.4 Components of the ERM Entity Relationship Connectivity Entity SCHOOL operates 1:M DEPARTMENT DEPARTMENT has 1:M STUDENT DEPARTMENT employs 1:M PROFESSOR DEPARTMENT offers 1:M COURSE COURSE generates 1:M CLASS SEMESTER includes 1:M CLASS PROFESSOR is dean of 1:1 SCHOOL PROFESSOR chairs 1:1 DEPARTMENT PROFESSOR teaches 1:M CLASS PROFESSOR advises 1:M STUDENT STUDENT enrolls in M:N CLASS BUILDING contains 1:M ROOM ROOM is used for 1:M CLASS Note: ENROLL is the composite entity that implements the M:N relationship “STUDENT enrolls in CLASS.” © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 55 protected website for classroom use. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 56 protected website for classroom use. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 57 protected website for classroom use. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 58 protected website for classroom use.  Database Design Challenges: Conflicting Goals (1 of 2) Database designers must often make design compromises that are triggered by conflicting goals Database design must conform to design standards - Minimize data redundancies - Avoid Nulls to the greatest extent possible - Results? High processing speed (means minimal access time) may limit the number and complexity of logically desirable relationships - In many organizations, particularly those that generate large numbers of transactions, high processing speeds are often a top priority in database design. - Avoid 1:1 is-a relationships and combine the two tables to avoid the additional relationship although a “perfect” design might use a 1:1 relationship to avoid nulls: Using dummy entries to avoid the nulls. - Also, we may include derived attributes in the design. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 59 protected website for classroom use.  Database Design Challenges: Conflicting Goals (1 of 2) Database designers must often make design compromises that are triggered by conflicting goals Maximum information generation may lead to loss of clean design structures and high transaction speed © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 60 protected website for classroom use. Database Design Challenges: Conflicting Goals (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 61 protected website for classroom use.  Summary The ERM uses ERDs to represent the conceptual database as viewed by the end user Connectivity describes the relationship classification (1:1, 1:M, or M:N) In the ERM, an M:N relationship is valid at the conceptual level ERDs may be based on many different ERMs Unified Modeling Language (UML) class diagrams are used to represent the static data structures in a data model Database designers, no matter how well they can produce designs that conform to all applicable modeling conventions, are often forced to make design compromises © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password- 62 protected website for classroom use.