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
GPAmight be decimal numbers between and ).
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_CODEandCLASS_SECTIONto 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_DOBorSTU_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_AGEfrom the stored birthdateEMP_DOBand system date, or derivingSTU_GPAfrom earned grade points and total credit hours).
Graphical Notation and Examples of Attributes

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_PHONEattached toSTUDENT).Crow's Foot Model: Attributes are listed inside the lower section of the entity box below the entity title.

Representing Multivalued Attributes:
Chen Model: Represented by a double-walled oval (e.g.,
CAR_COLORattached toCAR).Relational Database Constraint: Relational databases cannot directly store multivalued attributes in a single field without violating First Normal Form ().

Implementation Strategies for Multivalued Attributes:
Method 1 (Attribute Splitting): Replace the single multivalued attribute with several new atomic attributes, one for each possible component (e.g., replacing
CAR_COLORwithCAR_TOPCOLOR,CAR_TRIMCOLOR, andCAR_BODYCOLOR). This works well when the maximum number of multi-values is fixed and known.Method 2 (New Entity Creation): Create a separate entity composed of the multivalued attribute's components, linked back to the original entity with a relationship. This is required when the number of values is dynamic or unbounded.

Derived Attribute Representation:
Chen Model: Represented using a dashed oval connected to the entity (e.g.,
EMP_AGEattached toEMPLOYEEvia a dashed line, whileEMP_NUM,EMP_LNAME,EMP_FNAME,EMP_INITIAL, andEMP_DOBuse 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
PROFESSORteachesaCLASS, and aCLASSis taught byaPROFESSOR).
Connectivity and Cardinality
Connectivity:
Describes the relationship classification category indicating the structural ratio of entity occurrences.
Three primary connectivity types: One-to-One (), One-to-Many (), and Many-to-Many ().
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 .
The first parameter specifies the minimum number of associated entity instances.
The second parameter specifies the maximum number of associated entity instances.

Analysis of Connectivity and Cardinality Notation:
In the relationship
PROFESSOR teaches CLASS:On the
PROFESSORside: Cardinality indicates that each class must be taught by exactly one professor (minimum , maximum ). Connectivity symbol is double vertical lines||.On the
CLASSside: Cardinality indicates that a professor must teach at least class and at most 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.

Non-Identifying Table Structures (
Ch04_TinyCollege):COURSEtable: Primary Key =CRS_CODE. Attributes =DEPT_CODE,CRS_DESCRIPTION,CRS_CREDIT.CLASStable: Primary Key =CLASS_CODE. Foreign Key =CRS_CODE(FK1). Attributes =CLASS_SECTION,CLASS_TIME,ROOM_CODE,PROF_NUM.Because
CLASSuses its own synthetic keyCLASS_CODEas PK andCRS_CODEis 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.

Identifying Table Structures (
Ch04_TinyCollege_Alt):COURSEtable: Primary Key =CRS_CODE.CLASStable: Composite Primary Key =(CRS_CODE, CLASS_SECTION)whereCRS_CODEacts as both PK and FK1. Attributes =CLASS_TIME,ROOM_CODE,PROF_NUM.Because the parent key
CRS_CODEforms part of the child's PK, the relationship is strong (identifying).
Weak Entities
A weak entity is defined by two mandatory conditions:
Existence Dependence: The entity is existence-dependent on its parent entity.
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.


Detailed Example:
EMPLOYEEandDEPENDENT(Ch04_ShortCo):Entity structures:
EMPLOYEEtable: PK =EMP_NUM. Attributes =EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_DOB,EMP_HIREDATE.DEPENDENTtable: Composite PK =(EMP_NUM, DEP_NUM)whereEMP_NUMis PK/FK1 andDEP_NUMis partial key / discriminator. Attributes =DEP_FNAME,DEP_DOB.Chen Model Notation:
DEPENDENTis drawn inside a double-lined rectangle, and connected via a double-lined diamond<has>.Sample Data Rows:
EMPLOYEE1001 (Jeanine J Califante) has dependents 1 (Annelise) and 2 (Jorge).EMPLOYEE1003 (Herman H Washington) has dependent 1 (Suzanne).EMPLOYEE1006 (Jorge G Ortega) has dependent 1 (Carlos).EMPLOYEE1008 (Barbara A Brzenski) has dependents 1 (Michael), 2 (George), and 3 (Katherine).EMPLOYEEinstances 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 (denoted visually by a small circle
oon the relationship line).


Example:
PROFESSOR teaches CLASSwith cardinality to . A professor may teach classes (e.g., on research assignment), makingCLASSoptional toPROFESSOR.Example:
COURSE generates CLASSwith cardinality to . 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 or greater (denoted visually by a vertical line
|on the relationship line).

Example:
COURSE generates CLASSwith cardinality to . Business rule dictates that every course in the database must have at least active class section generated.
Relationship Degrees and Recursive Relationships
Relationship degree indicates the number of entity types (participants) participating in a relationship.

Classifications of Relationship Degree
Unary Relationship:
An association maintained within a single entity set (degree = ).
Example:
EMPLOYEEmanagesEMPLOYEE.
Binary Relationship:
An association maintained between two distinct entity sets (degree = ). Most common relationship type in ER modeling.
Example:
PROFESSORteachesCLASS.
Ternary Relationship:
An association maintained simultaneously among three distinct entity sets (degree = ).
Conceptual Representation: A central diamond
<prescribes>connectingDOCTOR,PATIENT, andDRUG.

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 binary relationships:DOCTORwritesPRESCRIPTION().PATIENTreceivesPRESCRIPTION().DRUGappears inPRESCRIPTION().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 date12-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.



Types and Implementations of Recursive Relationships:
Recursive Relationship ("EMPLOYEE is married to EMPLOYEE"):
Implementation in
EMPLOYEE_V1table (Ch04_PartCo): PK =EMP_NUM. FK =EMP_SPOUSEreferencingEMP_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.
Recursive Relationship ("EMPLOYEE manages EMPLOYEE"):
Implementation in
EMPLOYEE_V2table (Ch04_PartCo): PK =EMP_CODE. FK =EMP_MANAGERreferencingEMP_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.
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 relationship between two or more parent entities.
Relational implementation: An relationship cannot be directly implemented in relational databases. It is converted into two 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).

Example:
STUDENTenrolls inCLASS:Parents:
STUDENT(PK =STU_NUM) andCLASS(PK =CLASS_CODE).Associative Entity:
ENROLL.Keys in
ENROLL: Composite Primary Key(CLASS_CODE, STU_NUM)whereCLASS_CODEis PK/FK1 andSTU_NUMis PK/FK2.Descriptive Attribute:
ENROLL_GRADE.Relationships created:
STUDENTis written inENROLL() andCLASSis found inENROLL().
Developing an Entity Relationship Diagram (ERD)
The systematic process of constructing an enterprise ERD consists of six standard iterative steps:
Construct a detailed narrative describing organizational operations.
Extract business rules from the operational narrative.
Identify main entity sets and relationships among them from business rules.
Develop the initial conceptual ERD model.
Identify attributes and primary keys that fully describe each entity.
Review, refine, and normalize the ERD structure.
Step-by-Step Construction: Tiny College Case Study

Segment 1:
Rules: A professor is dean of a school ( optional/mandatory). A school operates multiple departments ( mandatory).
ERD:
PROFESSORis dean ofSCHOOL() connected toDEPARTMENTviaSCHOOLoperatesDEPARTMENT().

Segment 2:
Rule: A department offers academic courses.
ERD:
DEPARTMENToffersCOURSE( weak non-identifying relationship).COURSEPK =CRS_CODE, FK1 =DEPT_CODE.

Segment 3:
Rule: Courses generate class sections, and semesters include class sections.
ERD:
COURSEgeneratesCLASS().SEMESTERincludesCLASS().CLASSFK1 =CRS_CODE, FK2 =SEMESTER_CODE.

Segment 4:
Rules: A department employs professors (). A professor chairs a department ().
ERD: Dual relationships between
PROFESSORandDEPARTMENT:DEPARTMENTemploysPROFESSOR( mandatory to optional).PROFESSORchairsDEPARTMENT( optional to optional).

Segment 5:
Rule: A professor teaches class sections.
ERD:
PROFESSORteachesCLASS( optional participation onCLASSside).CLASSFK =PROF_NUM.

Segment 6:
Rules: A student enrolls in class sections (max classes per student, max students per class).
Cardinality:
STUDENTtoENROLL,CLASStoENROLL.ERD: Resolved into associative entity
ENROLLwith attributesENROLL_DATEandENROLL_GRADE.

Segment 7:
Rule: Each department has students assigned as majors.
ERD:
DEPARTMENThasSTUDENT( optional).STUDENTFK1 =DEPT_CODE.

Segment 8:
Rule: Professors serve as academic advisors to students.
ERD:
PROFESSORadvisesSTUDENT( optional).STUDENTFK2 =PROF_NUM.

Completed Tiny College ERD Integration:
Integrates
SCHOOL,DEPARTMENT,PROFESSOR,STUDENT,COURSE,SEMESTER,CLASS,ENROLL,BUILDING, andROOM.Added physical spatial relationships:
BUILDINGcontainsROOM() andROOMis used forCLASS().

Summary Table of ERM Components (Table 4.4):
Entity | Relationship | Connectivity | Entity |
|---|---|---|---|
| operates |
| |
| has |
| |
| employs |
| |
| offers |
| |
| generates |
| |
| includes |
| |
| is dean of |
| |
| chairs |
| |
| teaches |
| |
| advises |
| |
| enrolls in |
| |
| contains |
| |
| is used for |
|
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.

Trade-off Case Study: Implementing Recursive Relationship ("is married to"):
First Implementation (Single Table with FK):
Table
EMPLOYEE_V1containsEMP_NUM(PK),EMP_LNAME,EMP_FNAME,EMP_SPOUSE(FK referencingEMP_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) andMARRIED_V1(EMP_NUM,EMP_SPOUSE).Pros: Eliminates nulls completely from
EMPLOYEEtable. 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 on04-Mar-03, Marriage 2 on02-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 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
PKandFK.Crow's Foot connectivity symbol: Represents implied 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:
Existence Dependence: The entity must be existence-dependent on its parent entity (it cannot exist in the database without a parent instance).
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.