Module 4 - Entity Relationship Modeling

Module 4: Entity Relationship (ER) Modeling

1. Introduction to ER Modeling

  • Database Systems, 9th Edition: Focus on the Entity Relationship (ER) model.

  • Main topics covered:

    • Characteristics of ER components

    • Definition and structuring of relationships between entities

    • Impact of ERD components on database design and implementation

    • Reconciliation of conflicting goals in database design.

2. Objectives of the Chapter

  • Understand main characteristics of ER components.

  • Learn how relationships are defined and refined in database design.

  • Recognize how ERD components affect design and implementation.

  • Address the need to reconcile conflicting design goals.

3. The Entity Relationship Model (ERM)

  • Conceptual Database Representation:

    • ER model is the foundation for ER diagrams (ERDs).

    • ERDs illustrate main components: entities, attributes, and relationships.

4. Entities

  • Definition: Refers to an entity set rather than a single occurrence.

  • Environmental Context:

    • Corresponds to a table, not a row, in a relational database.

  • Representation:

    • In Chen and Crow’s Foot notations, an entity is represented by a rectangle with its name in capital letters.

5. Attributes

  • Characteristics that define entities.

  • Representation in Notations:

    • Chen Notation: Attributes shown as ovals linked to the entity rectangle.

    • Crow's Foot Notation: Attributes listed in a box below the entity rectangle.

6. Required and Optional Attributes

  • Required Attributes: Must have values.

  • Optional Attributes: Values may be left empty.

  • Domains: Set of possible values for an attribute; attributes can share domains.

  • Identifiers: Attributes that uniquely seal each entity instance.

  • Composite Identifiers: Primary keys formed from multiple attributes.

7. Types of Attributes (Continuation)

  • Composite Attributes: Can be subdivided.

  • Simple Attributes: Cannot be subdivided.

  • Single-Value Attributes: Only a single value possible.

  • Multi-Valued Attributes: Can hold multiple values.

8. Restrictions on Relationships

  • Avoid implementing M:N relationships directly; create new attributes or new entities.

  • Derived Attributes: Values calculated from other attributes; don't need to be stored in the database.

9. Relationships

  • Definition: An association between two entities, operating in both directions.

  • Classification: Relationships can be:

    • One-to-Many (1:M)

    • Many-to-One (M:1)

    • Many-to-Many (M:N) conditions need careful consideration.

10. Connectivity and Cardinality

  • Connectivity: Classification of relationship types.

  • Cardinality: Represents minimum and maximum occurrences associated with each entity.

  • Established through business rules.

11. Existence Dependence

  • Existence-Dependent: An entity exists only with a related entity.

  • Existence-Independence: An entity can exist without any related entities.

12. Relationship Strength

  • Weak Relationships: The primary key of related entity lacks the parent entity's primary key.

  • Strong Relationships: Related entity's primary key includes the parent entity's primary key component.

13. Relationship Participation

  • Optional Participation: Entity does not require a corresponding entity occurrence.

  • Mandatory Participation: Entity occurrence necessitates a corresponding entity occurrence.

14. Relationship Degree

  • Indicates the number of entities in a relationship:

    • Unary: One entity.

    • Binary: Two entities.

    • Ternary: Three entities.

15. Associative Entities

  • Also called bridge entities, employing M:N relationships.

  • Comprised of primary keys from interconnected entities and may include extra attributes.

16. Developing an ER Diagram

  • Process:

    • Narrative detailing operations.

    • Identifying entities/relationships aligned with business rules.

    • Creating, revising, and reviewing the ERD through iterations.

17. Database Design Challenges

  • Conflicting goals in design:

    • Standards vs. processing speed vs. information requirements.

    • Ensuring design meets all query and reporting requirements.

18. Summary

  • ER Model fundamentals:

    • ERD components: Entities, relationships, attributes.

    • Importance of connectivity, cardinality, and mapping M:N relationships into 1:M.

    • Use of UML class diagrams to represent data structures.

    • Acknowledge compromises needed in design.