ER Modeling

Chapter 4: Entity Relationship (ER) Modeling

• This chapter covers Entity Relationship (ER) modeling for database systems
• It introduces how relationships between entities are defined, refined, and incorporated into database design
• ERD components affect database design and implementation
• ER modeling tools and their symbols are explained
• Real-world database design often requires reconciling conflicting goals

The Entity Relationship (ER) Model

• The ER model forms the basis for an ER diagram
• An ERD represents the conceptual database from the user's perspective
• ERDs depict the three main components of the ER model: entities, attributes, and relationships

Entities

• Refers to an entity set, not a single entity occurrence
• Corresponds to a table in the relational environment
• Represented by a rectangle containing the entity's name (written in capital letters)

Attributes

• Characteristics of entities
• Chen model: attributes are represented by ovals connected to the entity rectangle
• Crow's Foot model: attributes are written in an attribute box below the entity rectangle
• Each oval contains the attribute's name

Domains

• Attributes have a domain, which is the set of possible values for that attribute
• Attributes may share a domain

Primary Keys

• Underlined in ER diagrams and table structure shorthand
• Ideally a single attribute, but can be composite (multiple attributes) forming the key

Attributes (Continued):

• Composite attributes: composed of multiple simple attributes
• Simple attributes: single, indivisible attributes
• Single-value attributes: attributes with only one value per entity instance
• Multivalued attributes: attributes with multiple values per entity instance

Resolving Multivalued Attribute Problems

• Although conceptual models can handle multivalued attributes, relational databases should not implement them directly
• Within the original entity, create several new attributes, one for each component of the original multivalued attribute
• Create a new entity composed of the original attribute's components

Relationship Strength

• Existence dependence: an entity's existence depends on the existence of another entity
• Existence independence: an entity can exist independently of related entities
• Weak (non-identifying) relationships: one entity is not existence-dependent on another
• Strong (identifying) relationships: related entities are existence-dependent

Relationship Participation

• Optional: one entity occurrence does not require a corresponding entity occurrence in a relationship
• Mandatory: one entity occurrence requires a corresponding entity occurrence in a relationship

Relationship Degree

• Unary relationship: association maintained within a single entity
• Binary relationship: association between two entities
• Ternary relationship: association between three entities

Recursive Relationships

• Relationship can exist between occurrences of the same entity set
• Found within a unary relationship

Developing an ER Diagram

• Database design is an iterative, not a linear, process
• Based on repetition and procedures

Entity Supertypes and Subtypes

• Generalization hierarchy: depicts relationships between higher-level and lower-level entities
• Supertype entity: contains shared attributes
• Subtype entity: contains unique attributes

Nulls Created by Unique Attributes

• Null values can arise when using unique attributes and their relevant data is not known

Completeness Constraint

• Specifies whether every entity supertype occurrence must also be a member of at least one subtype
• Partial completeness: Not every supertype occurrence must be a member
• Total completeness: Every supertype occurrence must be a member

Summary

• ER models use diagrams to represent conceptual databases, showing entities, relationships, and attributes
• Uses connectivity and cardinality rules to define relationships
• ER models can include supertypes and subtypes
• Designers must often comprise between desirable design choices