TOPIC 4

Database Systems: Design, Implementation, and Management Notes

Chapter Overview

• Module: Entity Relationship (ER) Modeling
• Authors: Coronel, Carlos and Morris, Steven
• Edition: 14th Edition, 2023

Chapter Objectives

By the end of this chapter, you should be able to:

• Identify the main characteristics of entity relationship components
  • Understand entities, attributes, and relationships
• Describe how relationships between entities are defined
  • Recognize how entities relate to one another and the nature of these relationships
• Explain how ERD (Entity Relationship Diagram) components affect database design and implementation
  • Learn the implications of ERD components in practical database scenarios
• Describe how real-world database design often requires the reconciliation of conflicting goals
  • Acknowledge the balance between different design specifications in documentation

Key Concepts

Attributes

• Attributes are properties or characteristics of entities.
• Multiple pages (5-8) emphasize the importance of attributes in relation to entities and must be thoroughly understood for effective database design.

Relationships

• Participants in a Relationship:
  • The entities that are part of a relationship are referred to as participants.
  • Each relationship is assigned a name that typically describes the nature of the relationship, often expressed as either an active or passive verb.
  • Directional Operations: All relationships between entities function bidirectionally, implying that each relationship can be understood from both entities' perspectives.

Connectivity and Cardinality

• Discusses the structural aspects of relationships:
  • Connectivity refers to how entities are linked within a relationship, impacting the database schema structure.
  • Cardinality: Defines how many instances of one entity relate to instances of another entity.

Existence Dependence vs. Independence

• Relationships are categorized based on whether an entity's existence is dependent on its relationship with another entity.
  • Existence-Dependent Entity: Cannot exist without its related entity.
  • Existence-Independent Entity: Can exist independently.

Relationship Strength

• The strength of a relationship indicates the dependency:
  • Weak Entities:
    • An entity is termed weak if:
      1. It is existence-dependent on another entity.
      2. Its primary key is derived from the parent entity.
    • The weak entity concept is crucial for adequately designing relationships.

Relationship Participation

• Optional Participation: An entity occurrence may or may not relate to another entity's occurrence in a relationship.
• Mandatory Participation: Every occurrence of one entity must correspond to an occurrence of another entity in the relationship.

Relationship Degree

• Definition: Indicates how many entities participate in a relationship. Varieties of relationships include:
  • Unary Relationship: Involves a single entity type.
  • Binary Relationship: Involves two distinct entity types.
  • Ternary Relationship: Involves three distinct entity types.
  • Recursive Relationship: A relationship between occurrences of the same entity type.

Recursive Relationships

• A recursive relationship implies associations within the same entity set.
• Important to differentiate recursive relationships from participation constraints.

Associative (Composite) Entities

• Used to represent many-to-many (M:N) relationships between two or more entities:
  • Called composite or bridge entities, serving as a one-to-many (1:M) relationship with each related entity.
  • Composed of primary key attributes from parent entities, may also contain additional attributes.

Example of Developing ER Diagrams

• Real-world scenarios (pages 33-41):
  • Illustrates how entities (professors, departments, classes, and students) are related and how an Entity Relationship Diagram captures these relationships:
    • A professor could become a dean of a school, where each school manages multiple departments.
    • A department offers many courses, but each course belongs to only one department.
    • A course is offered multiple times as classes, each within a specific semester.
    • A professor works in a department, where only one professor can be the department head.
    • A student can take many classes; each class may host many students—an ENROLL table connects these entities.
    • Each department can have many students, but each student belongs to only one department.
    • A professor can advise many students, but each student has only one advisor.
    • Each building houses many rooms, used for multiple classes.

Conflicting Goals in Database Design

• Database design frequently involves trade-offs between simplicity, flexibility, and data integrity (pages 43-44).
  • Designers must reconcile these conflicting goals to create viable and useful database systems.