Module 4: Entity Relationship (ER) Modeling

Chapter Objectives and Overview

  • Identify the main characteristics of entity relationship components including entities, attributes, relationships, connectivity, and cardinality.

  • Describe how relationships between entities are defined, categorized by degree, connectivity, and participation.

  • Explain how ERD components affect database design and physical implementation in relational database management systems.

  • Describe how real-world database design often requires the reconciliation of conflicting goals such as design standards, processing performance, and information richness.

Main Components of the Entity Relationship Model

  • The Entity Relationship Model (ERM) forms the foundation of the Entity Relationship Diagram (ERD).

  • An ERD represents the conceptual database schema as viewed from the end-user perspective, abstracting physical storage mechanisms.

  • The three primary building blocks depicted in an ERD are:

    • Entities

    • Attributes

    • Relationships

Entities and Entity Sets

  • An entity represents a real-world object, person, place, event, or concept of interest to the end user.

  • Distinguishing entity set from entity occurrence:

    • An entity in the ERM refers to the entire entity set (the collection of all instances), not to a single entity occurrence.

    • In a relational database environment, an entity corresponds to an entire table.

    • An individual row within a table corresponds to an entity instance or entity occurrence.

  • Graphical Notations:

    • In Chen, Crow's Foot, and Unified Modeling Language (UML) notations, an entity is represented visually by a rectangle containing the entity name.

    • Entity names are nouns and are conventionally written in all capital letters (e.g., STUDENT, COURSE, EMPLOYEE).

Entity Attributes

  • Attributes are specific characteristics, properties, or features that describe an entity.

Attribute Classification and Properties

  • Required vs. Optional Attributes:

    • Required Attribute: An attribute that must contain a valid data value; it cannot be left empty or assigned a null value (e.g., student last name or course code).

    • Optional Attribute: An attribute that does not require a value and may be left blank or assigned a null value (e.g., employee middle initial or student phone number).

  • Attribute Domain:

    • The set of all possible permitted values for a given attribute.

    • Defines the permissible data type, format, and value constraints (e.g., domain for GPA might be decimal numbers between 0.000.00 and 4.004.00).

  • Identifiers and Keys:

    • Identifier: One or more attributes that uniquely identify each entity instance (corresponds to a primary key in relational tables).

    • Composite Identifier: A primary key composed of more than one attribute (e.g., combining CRS_CODE and CLASS_SECTION to uniquely identify a specific course offering).

  • Simple vs. Composite Attributes:

    • Simple Attribute: An attribute that cannot be subdivided further into meaningful sub-components (e.g., EMP_AGE, STU_GPA).

    • Composite Attribute: An attribute that can be subdivided into multiple sub-components to yield distinct additional attributes (e.g., an address composed of street, city, state, and zip code, or a full name composed of first name, middle initial, and last name).

  • Single-Valued vs. Multivalued Attributes:

    • Single-Valued Attribute: An attribute that holds only one single value for any given entity occurrence (e.g., EMP_DOB or STU_NUM).

    • Multivalued Attribute: An attribute that can hold multiple values for a single entity instance (e.g., a car having multiple colors such as body, trim, and top, or an employee holding multiple degrees/certifications).

  • Derived Attributes:

    • An attribute whose value is not explicitly stored in the database, but is computed/derived dynamically from other stored attributes or system values using an algorithm or formula (e.g., deriving EMP_AGE from the stored birthdate EMP_DOB and system date, or deriving STU_GPA from earned grade points and total credit hours).

Graphical Notation and Examples of Attributes

The Attributes of the STUDENT Entity: Chen and Crow's Foot
  • Chen Model vs. Crow's Foot Model for Attributes:

    • Chen Model: Attributes are represented as ovals attached to the entity rectangle by lines. The primary key attribute is underlined (e.g., STU_LNAME, STU_FNAME, STU_INITIAL, STU_EMAIL, STU_PHONE attached to STUDENT).

    • Crow's Foot Model: Attributes are listed inside the lower section of the entity box below the entity title.

A Multivalued Attribute in An Entity
  • Representing Multivalued Attributes:

    • Chen Model: Represented by a double-walled oval (e.g., CAR_COLOR attached to CAR).

    • Relational Database Constraint: Relational databases cannot directly store multivalued attributes in a single field without violating First Normal Form (1NF1\text{NF}).

Splitting the Multivalued Attribute into New Attributes
  • Implementation Strategies for Multivalued Attributes:

    1. Method 1 (Attribute Splitting): Replace the single multivalued attribute with several new atomic attributes, one for each possible component (e.g., replacing CAR_COLOR with CAR_TOPCOLOR, CAR_TRIMCOLOR, and CAR_BODYCOLOR). This works well when the maximum number of multi-values is fixed and known.

    2. Method 2 (New Entity Creation): Create a separate entity composed of the multivalued attribute's components, linked back to the original entity with a 1:M1:M relationship. This is required when the number of values is dynamic or unbounded.

Depiction of a Derived Attribute
  • Derived Attribute Representation:

    • Chen Model: Represented using a dashed oval connected to the entity (e.g., EMP_AGE attached to EMPLOYEE via a dashed line, while EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, and EMP_DOB use solid lines).

    • Crow's Foot Model: Derived attributes are listed inside the entity box, often noted in database documentation.

Relationships and Structure

  • Relationships express logical associations between two or more entities.

  • Entity participants: Entities that participate in a specific relationship are referred to as participants.

  • Naming Conventions: Relationships are identified by names that describe the interaction, expressed as active or passive verbs (e.g., teaches, generates, offers, advises).

  • Directionality: Relationships between entities always operate in both directions (e.g., a PROFESSOR teaches a CLASS, and a CLASS is taught by a PROFESSOR).

Connectivity and Cardinality

  • Connectivity:

    • Describes the relationship classification category indicating the structural ratio of entity occurrences.

    • Three primary connectivity types: One-to-One (1:11:1), One-to-Many (1:M1:M), and Many-to-Many (M:NM:N).

  • Cardinality:

    • Expresses the minimum and maximum number of entity occurrences associated with a single occurrence of the related entity.

    • Formatting in ERD: Displayed beside the participating entity using the interval format (x,y)(x,y).

    • The first parameter xx specifies the minimum number of associated entity instances.

    • The second parameter yy specifies the maximum number of associated entity instances.

Connectivity and Cardinality in an ERD
  • Analysis of Connectivity and Cardinality Notation:

    • In the relationship PROFESSOR teaches CLASS:

    • On the PROFESSOR side: Cardinality (1,1)(1,1) indicates that each class must be taught by exactly one professor (minimum 11, maximum 11). Connectivity symbol is double vertical lines ||.

    • On the CLASS side: Cardinality (1,4)(1,4) indicates that a professor must teach at least 11 class and at most 44 classes. Connectivity symbol is a crow's foot with a single bar.

Existence Dependence

  • Existence-Dependent Entity:

    • An entity that can exist in the database only when it is associated with another related parent entity occurrence.

    • Structurally characterized by having a mandatory foreign key attribute that cannot accept null values.

  • Existence-Independent Entity:

    • An entity that can exist in the database independently without relying on any related entity instances.

    • Referred to as a strong entity or regular entity.

Relationship Strength

  • Weak (Non-Identifying) Relationship:

    • Occurs when the primary key of the related (child) entity does not contain a primary key component of the parent entity.

    • The child entity's primary key is completely independent, and the foreign key referencing the parent is just a regular attribute.

    • Represented in Crow's Foot notation by a dashed line between entities.

A Weak (Non-Identifying) Relationship Between COURSE and CLASS
  • Non-Identifying Table Structures (Ch04_TinyCollege):

    • COURSE table: Primary Key = CRS_CODE. Attributes = DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT.

    • CLASS table: Primary Key = CLASS_CODE. Foreign Key = CRS_CODE (FK1). Attributes = CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM.

    • Because CLASS uses its own synthetic key CLASS_CODE as PK and CRS_CODE is merely an FK, the relationship is non-identifying (weak).

    • Strong (Identifying) Relationship:

  • Occurs when the primary key of the child entity contains a primary key component inherited from the parent entity.

  • The foreign key referencing the parent is part of the child's composite primary key.

  • Represented in Crow's Foot notation by a solid line between entities.

A Strong (Identifying) Relationship Between COURSE and CLASS
  • Identifying Table Structures (Ch04_TinyCollege_Alt):

    • COURSE table: Primary Key = CRS_CODE.

    • CLASS table: Composite Primary Key = (CRS_CODE, CLASS_SECTION) where CRS_CODE acts as both PK and FK1. Attributes = CLASS_TIME, ROOM_CODE, PROF_NUM.

    • Because the parent key CRS_CODE forms part of the child's PK, the relationship is strong (identifying).

Weak Entities

  • A weak entity is defined by two mandatory conditions:

    1. Existence Dependence: The entity is existence-dependent on its parent entity.

    2. Inherited Primary Key: The entity has a primary key that is partially or totally derived from the parent entity in the relationship.

  • Determination of weak entity status is governed by organization business rules.

A Weak Entity in an ERDA Weak Entity in a Strong Relationship
  • Detailed Example: EMPLOYEE and DEPENDENT (Ch04_ShortCo):

    • Entity structures:

    • EMPLOYEE table: PK = EMP_NUM. Attributes = EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB, EMP_HIREDATE.

    • DEPENDENT table: Composite PK = (EMP_NUM, DEP_NUM) where EMP_NUM is PK/FK1 and DEP_NUM is partial key / discriminator. Attributes = DEP_FNAME, DEP_DOB.

    • Chen Model Notation: DEPENDENT is drawn inside a double-lined rectangle, and connected via a double-lined diamond <has>.

    • Sample Data Rows:

    • EMPLOYEE 1001 (Jeanine J Califante) has dependents 1 (Annelise) and 2 (Jorge).

    • EMPLOYEE 1003 (Herman H Washington) has dependent 1 (Suzanne).

    • EMPLOYEE 1006 (Jorge G Ortega) has dependent 1 (Carlos).

    • EMPLOYEE 1008 (Barbara A Brzenski) has dependents 1 (Michael), 2 (George), and 3 (Katherine).

    • EMPLOYEE instances 1002, 1004, 1005, 1007 have no dependents.

Relationship Participation

  • Participation refers to whether an entity instance must participate in a relationship instance.

  • Optional Participation:

    • One entity occurrence does not require a corresponding entity occurrence in a particular relationship.

    • Represented by a minimum cardinality of 00 (denoted visually by a small circle o on the relationship line).

An Optional CLASS Entity in the Relationship "PROFESSOR teaches CLASS"CLASS is Optional to COURSE
  • Example: PROFESSOR teaches CLASS with cardinality (1,1)(1,1) to (0,3)(0,3). A professor may teach 00 classes (e.g., on research assignment), making CLASS optional to PROFESSOR.

  • Example: COURSE generates CLASS with cardinality (1,1)(1,1) to (0,N)(0,N). A course listed in the catalog does not necessarily have active class sections assigned during a given semester.

    • Mandatory Participation:

  • One entity occurrence requires a corresponding entity occurrence in a given relationship.

  • Represented by a minimum cardinality of 11 or greater (denoted visually by a vertical line | on the relationship line).

COURSE and CLASS in a Mandatory Relationship
  • Example: COURSE generates CLASS with cardinality (1,1)(1,1) to (1,N)(1,N). Business rule dictates that every course in the database must have at least 11 active class section generated.

Relationship Degrees and Recursive Relationships

  • Relationship degree indicates the number of entity types (participants) participating in a relationship.

Three Types of Relationship Degree

Classifications of Relationship Degree

  • Unary Relationship:

    • An association maintained within a single entity set (degree = 11).

    • Example: EMPLOYEE manages EMPLOYEE.

  • Binary Relationship:

    • An association maintained between two distinct entity sets (degree = 22). Most common relationship type in ER modeling.

    • Example: PROFESSOR teaches CLASS.

  • Ternary Relationship:

    • An association maintained simultaneously among three distinct entity sets (degree = 33).

    • Conceptual Representation: A central diamond <prescribes> connecting DOCTOR, PATIENT, and DRUG.

The Implementation of a Ternary Relationship
  • Logical Implementation of Ternary Relationship (Ch04_Clinic):

    • Relational databases cannot directly implement M:N ternary relationships; they must be resolved into an intermediate bridge table (PRESCRIPTION) connected via three 1:M1:M binary relationships:

    • DOCTOR writes PRESCRIPTION (1:M1:M).

    • PATIENT receives PRESCRIPTION (1:M1:M).

    • DRUG appears in PRESCRIPTION (1:M1:M).

    • Table schemas and sample records:

    • DRUG: DRUG_CODE (PK), DRUG_NAME, DRUG_PRICE.

    • PATIENT: PAT_NUM (PK), PAT_TITLE, PAT_LNAME, PAT_FNAME, PAT_INITIAL, PAT_DOB, PAT_AREACODE, PAT_PHONE.

    • DOCTOR: DOC_ID (PK), DOC_LNAME, DOC_FNAME, DOC_INITIAL, DOC_SPECIALTY.

    • PRESCRIPTION: Attributes = DOC_ID (FK), PAT_NUM (FK), DRUG_CODE (FK), PRES_DOSAGE, PRES_DATE.

    • Example record: Doctor 32445 (Annelise Jorgensen) prescribed drug DRZ (Druzocholar Cryptolene) to patient 102 (Rhett Vandam) with dosage "2 tablets every four hours -- 50 tablets total" on date 12-Nov-22.

Recursive Relationships

  • A recursive relationship is a relationship that exists between occurrences within a single entity type (a unary relationship).

  • Clarification on Participation vs. Referential Integrity:

    • Common pitfall: Confusing relationship participation with referential integrity constraints.

    • While both rely on foreign key attributes defined on the same table, participation dictates optionality/mandatoriness of the association, while referential integrity guarantees foreign key values match valid primary keys.

An ER Representation of Recursive RelationshipsThe 1:1 Recursive Relationship "EMPLOYEE Is Married to EMPLOYEE"Implementation of the 1:M Recursive Relationship "EMPLOYEE manages EMPLOYEE"
  • Types and Implementations of Recursive Relationships:

    • 1:11:1 Recursive Relationship ("EMPLOYEE is married to EMPLOYEE"):

    • Implementation in EMPLOYEE_V1 table (Ch04_PartCo): PK = EMP_NUM. FK = EMP_SPOUSE referencing EMP_NUM.

    • Data examples:

      • Employee 345 (James Ramirez) has spouse 347 (Louise Ramirez).

      • Employee 346 (Anne Jones) has spouse 349 (Anton Shapiro).

      • Employee 348 (Robert Delaney) has null spouse.

    • 1:M1:M Recursive Relationship ("EMPLOYEE manages EMPLOYEE"):

    • Implementation in EMPLOYEE_V2 table (Ch04_PartCo): PK = EMP_CODE. FK = EMP_MANAGER referencing EMP_CODE.

    • Data examples:

      • Employee 102 (Orincona) has null manager (top supervisor).

      • Employees 101 (Waddell), 103 (Jones), 104 (Reballoh), 105 (Robertson), and 106 (Deltona) all report to manager 102.

    • M:NM:N Recursive Relationship ("COURSE is prerequisite to COURSE"):

    • A course can have many prerequisite courses, and a course can be a prerequisite for many other courses.

Associative (Composite) Entities

  • An associative entity (also known as a composite or bridge entity) is used in ER modeling to represent an M:NM:N relationship between two or more parent entities.

  • Relational implementation: An M:NM:N relationship cannot be directly implemented in relational databases. It is converted into two 1:M1:M relationships linked through an associative entity.

  • Structure of Associative Entity:

    • Primary Key: Composed of the primary key attributes inherited from each parent entity (a composite primary key).

    • Foreign Keys: Inherited attributes act as foreign keys referencing the respective parent entities.

    • Additional Attributes: May contain operational or transaction attributes that belong specifically to the relationship instance (e.g., grade, date).

A Composite Entity in an ERD
  • Example: STUDENT enrolls in CLASS:

    • Parents: STUDENT (PK = STU_NUM) and CLASS (PK = CLASS_CODE).

    • Associative Entity: ENROLL.

    • Keys in ENROLL: Composite Primary Key (CLASS_CODE, STU_NUM) where CLASS_CODE is PK/FK1 and STU_NUM is PK/FK2.

    • Descriptive Attribute: ENROLL_GRADE.

    • Relationships created: STUDENT is written in ENROLL (1:M1:M) and CLASS is found in ENROLL (1:M1:M).

Developing an Entity Relationship Diagram (ERD)

  • The systematic process of constructing an enterprise ERD consists of six standard iterative steps:

    1. Construct a detailed narrative describing organizational operations.

    2. Extract business rules from the operational narrative.

    3. Identify main entity sets and relationships among them from business rules.

    4. Develop the initial conceptual ERD model.

    5. Identify attributes and primary keys that fully describe each entity.

    6. Review, refine, and normalize the ERD structure.

Step-by-Step Construction: Tiny College Case Study

The First Tiny College ERD Segment
  • Segment 1:

    • Rules: A professor is dean of a school (1:11:1 optional/mandatory). A school operates multiple departments (1:M1:M mandatory).

    • ERD: PROFESSOR is dean of SCHOOL (1:11:1) connected to DEPARTMENT via SCHOOL operates DEPARTMENT (1:M1:M).

The Second Tiny College ERD Segment
  • Segment 2:

    • Rule: A department offers academic courses.

    • ERD: DEPARTMENT offers COURSE (1:M1:M weak non-identifying relationship). COURSE PK = CRS_CODE, FK1 = DEPT_CODE.

The Third Tiny College ERD Segment
  • Segment 3:

    • Rule: Courses generate class sections, and semesters include class sections.

    • ERD: COURSE generates CLASS (1:M1:M). SEMESTER includes CLASS (1:M1:M). CLASS FK1 = CRS_CODE, FK2 = SEMESTER_CODE.

The Fourth Tiny College ERD Segment
  • Segment 4:

    • Rules: A department employs professors (1:M1:M). A professor chairs a department (1:11:1).

    • ERD: Dual relationships between PROFESSOR and DEPARTMENT:

    • DEPARTMENT employs PROFESSOR (1:M1:M mandatory to optional).

    • PROFESSOR chairs DEPARTMENT (1:11:1 optional to optional).

The Fifth Tiny College ERD Segment
  • Segment 5:

    • Rule: A professor teaches class sections.

    • ERD: PROFESSOR teaches CLASS (1:M1:M optional participation on CLASS side). CLASS FK = PROF_NUM.

The Sixth Tiny College ERD Segment
  • Segment 6:

    • Rules: A student enrolls in class sections (max 66 classes per student, max 3535 students per class).

    • Cardinality: STUDENT (0,6)(0,6) to ENROLL, CLASS (0,35)(0,35) to ENROLL.

    • ERD: Resolved M:NM:N into associative entity ENROLL with attributes ENROLL_DATE and ENROLL_GRADE.

The Seventh Tiny College ERD Segment
  • Segment 7:

    • Rule: Each department has students assigned as majors.

    • ERD: DEPARTMENT has STUDENT (1:M1:M optional). STUDENT FK1 = DEPT_CODE.

The Eighth Tiny College ERD Segment
  • Segment 8:

    • Rule: Professors serve as academic advisors to students.

    • ERD: PROFESSOR advises STUDENT (1:M1:M optional). STUDENT FK2 = PROF_NUM.

The Completed Tiny College ERD
  • Completed Tiny College ERD Integration:

    • Integrates SCHOOL, DEPARTMENT, PROFESSOR, STUDENT, COURSE, SEMESTER, CLASS, ENROLL, BUILDING, and ROOM.

    • Added physical spatial relationships: BUILDING contains ROOM (1:M1:M) and ROOM is used for CLASS (1:M1:M).

Components of the ERM
  • Summary Table of ERM Components (Table 4.4):

Entity

Relationship

Connectivity

Entity

SCHOOL

operates

1:M1:M

DEPARTMENT

DEPARTMENT

has

1:M1:M

STUDENT

DEPARTMENT

employs

1:M1:M

PROFESSOR

DEPARTMENT

offers

1:M1:M

COURSE

COURSE

generates

1:M1:M

CLASS

SEMESTER

includes

1:M1:M

CLASS

PROFESSOR

is dean of

1:11:1

SCHOOL

PROFESSOR

chairs

1:11:1

DEPARTMENT

PROFESSOR

teaches

1:M1:M

CLASS

PROFESSOR

advises

1:M1:M

STUDENT

STUDENT

enrolls in

M:NM:N

CLASS

BUILDING

contains

1:M1:M

ROOM

ROOM

is used for

1:M1:M

CLASS

Real-World Database Design Challenges and Trade-Offs

  • Real-world database design is rarely a straightforward linear process and requires balancing competing operational goals:

    • Adherence to Design Standards: High normalization standards minimize redundancy and structural anomalies.

    • Processing Speed: Complex joins across highly normalized tables can severely impair high-volume transaction processing speed.

    • Information Generation: Maximizing historical tracking and flexible reporting can create bloated designs.

Various Implementations of the 1:1 Recursive Relationship
  • Trade-off Case Study: Implementing 1:11:1 Recursive Relationship ("is married to"):

    • First Implementation (Single Table with FK):

    • Table EMPLOYEE_V1 contains EMP_NUM (PK), EMP_LNAME, EMP_FNAME, EMP_SPOUSE (FK referencing EMP_NUM).

    • Pros: Minimal tables, fast joins. Cons: High proportion of null foreign keys for unmarried employees.

    • Second Implementation (Separate Table for Relationship):

    • Tables EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME) and MARRIED_V1 (EMP_NUM, EMP_SPOUSE).

    • Pros: Eliminates nulls completely from EMPLOYEE table. Cons: Requires additional table join to query marital status.

    • Third Implementation (Historical Marriage Tracking Model):

    • Tables:

      • EMPLOYEE: EMP_NUM (PK), EMP_LNAME, EMP_FNAME.

      • MARRIAGE: MAR_NUM (PK), MAR_DATE. (e.g., Marriage 1 on 04-Mar-03, Marriage 2 on 02-Feb-99).

      • MARPART: MAR_NUM (FK), EMP_NUM (FK). Bridge table connecting employees to marriage events.

    • Pros: Preserves historical marriage records over time. Cons: Increases operational complexity and storage footprint.

Knowledge Check Activities and Solutions

Notation Summary Diagram
  • Notation Summary Reference:

    • Simplified Crow's Foot entity box: Contains entity title box only with no attribute component listed.

    • Crow's Foot entity box: Contains title compartment plus attribute list compartment, explicitly labeling PK and FK.

    • Crow's Foot connectivity symbol: Represents implied (0,N)(0,N) cardinality.

    • Relationship line styles: Dashed line indicates a weak (non-identifying) relationship. Solid line indicates a strong (identifying) relationship.

Activity 4-1

  • Question: What two conditions must be met before an entity can be classified as a weak entity?

  • Answer: To be classified as a weak entity, the following two conditions must be satisfied:

    1. Existence Dependence: The entity must be existence-dependent on its parent entity (it cannot exist in the database without a parent instance).

    2. Inherited Primary Key: The entity must inherit at least part of its primary key from its parent entity (its primary key is partially or totally derived from the parent entity).

Activity 4-2

  • Question: How is a relationship between entities indicated in an ERD?

  • Answer: A relationship between entities in an ERD is indicated by a line drawn directly between the connecting related entities. In Crow's Foot notation, line patterns (solid vs. dashed) denote relationship strength, while line end symbols (crow's feet, bars, circles) denote connectivity and cardinalities.