Relationships

What are Relationships?

Relationships are associations or connections between two or more entities in a database. They represent how different entities interact with or relate to each other, capturing the way objects are connected in the real world.

In ER modeling, relationships are crucial because they define how data in one entity set is linked to data in another, allowing us to model complex interactions and dependencies.

Purpose of Relationships

Relationships serve several important functions in database design:

1. Data Connection: Link related data across different entity sets
2. Information Context: Provide context for how entities interact
3. Integrity Enforcement: Help maintain referential integrity
4. Query Support: Enable joining related data for retrieval
5. Real-world Representation: Model how objects connect in the real world
6. Normalization: Help organize data to minimize redundancy

Components of Relationships

A relationship in ER modeling consists of several components:

1. Participating Entities

• The entity sets involved in the relationship
• At least two entities participate in most relationships

2. Relationship Name

• A verb or verb phrase describing the association
• Should clearly express how entities interact

3. Degree

• The number of entity sets participating in the relationship

4. Cardinality

• The numerical relationship between entity instances
• Defines how many instances of one entity can relate to instances of another

5. Participation Constraint

• Specifies whether all or only some entity instances participate in the relationship

6. Attributes (in some cases)

• Some relationships may have their own attributes
• Describes properties of the relationship itself, not of the participating entities

Types of Relationships Based on Degree

The degree of a relationship refers to the number of entity sets that participate in it:

1. Unary Relationship (Degree 1)

Definition:

• Also called recursive or self-referencing relationship
• Relates entities within the same entity set
• Forms a relationship between instances of the same entity type

Representation:

• A relationship line that loops back to the same entity
• Usually includes role names to distinguish the roles

Examples:

• Employee “manages” Employee
• Person “is married to” Person
• Part “is a component of” Part
• Course “is a prerequisite for” Course

2. Binary Relationship (Degree 2)

Definition:

• Involves exactly two entity sets
• The most common type of relationship
• Connects instances from two different entity sets

Representation:

• A line or diamond connecting two entity rectangles
• Labeled with the relationship name

Examples:

• Student “enrolls in” Course
• Employee “works in” Department
• Customer “places” Order
• Author “writes” Book

3. Ternary Relationship (Degree 3)

Definition:

• Involves exactly three entity sets
• Represents an association that cannot be decomposed into binary relationships
• Links instances from three different entity sets

Representation:

• A diamond with lines connecting to three entity rectangles
• Sometimes represented with a triangle in some notations

Examples:

• Supplier “supplies” Part “to” Project
• Doctor “prescribes” Medication “to” Patient
• Professor “teaches” Subject “to” Student

4. n-ary Relationship (Degree > 3)

Definition:

• Involves more than three entity sets
• Relatively uncommon in practice
• Used for complex relationships that cannot be broken down

Representation:

• A diamond with lines connecting to multiple entity rectangles

Examples:

• Supplier “supplies” Part “for” Project “during” Time Period
• Doctor “prescribes” Medication “to” Patient “in” Hospital “on” Date

Types of Relationships Based on Cardinality

Cardinality defines the numerical relationship between entity instances:

1. One-to-One (1:1)

Definition:

• Each entity in the first set is related to at most one entity in the second set
• Each entity in the second set is related to at most one entity in the first set

Representation:

• In Chen notation: “1” on both ends
• In Crow’s Foot: Bar (|) on both ends of the relationship line

Examples:

• Person “has” Passport
• Country “has” Capital City
• Employee “has” Office
• Student “has” Student ID Card

Implementation:

• Usually implemented by placing a foreign key in either table
• Often the foreign key is placed in the table corresponding to the entity with total participation

2. One-to-Many (1:N)

Definition:

• Each entity in the first set can be related to many entities in the second set
• Each entity in the second set is related to at most one entity in the first set

Representation:

• In Chen notation: “1” on one end, “N” on the other
• In Crow’s Foot: Bar (|) on the “one” side, crow’s foot (<) on the “many” side

Examples:

• Department “employs” many Employees
• Customer “places” many Orders
• Course “has” many Students
• Country “contains” many Cities

Implementation:

• Implemented by placing a foreign key in the “many” side table
• The foreign key references the primary key of the “one” side table

3. Many-to-Many (M:N)

Definition:

• Each entity in the first set can be related to many entities in the second set
• Each entity in the second set can be related to many entities in the first set

Representation:

• In Chen notation: “M” on one end, “N” on the other
• In Crow’s Foot: Crow’s foot (<) on both ends of the relationship line

Examples:

• Student “enrolls in” many Courses / Course “has” many Students
• Employee “works on” many Projects / Project “has” many Employees
• Author “writes” many Books / Book “written by” many Authors
• Actor “acts in” many Movies / Movie “has” many Actors

Implementation:

• Implemented using an intermediary (junction) table
• The junction table contains foreign keys to both participating tables
• Often includes additional attributes that describe the relationship

Types of Relationships Based on Participation Constraints

Participation constraints specify whether all or only some entity instances are involved in a relationship:

1. Total (Mandatory) Participation

Definition:

• Every entity in the entity set must participate in the relationship
• Specifies that each entity instance is involved in at least one relationship instance

Representation:

• In Chen notation: Double line connecting the entity to the relationship
• In Crow’s Foot: A perpendicular line at the relationship end (mandatory)

Examples:

• Every Order must contain at least one Order Line (Order has total participation in “contains” relationship)
• Every Employee must belong to a Department (Employee has total participation in “belongs to” relationship)

2. Partial (Optional) Participation

Definition:

• Some entities in the entity set may not participate in the relationship
• Specifies that entity instances might not be involved in any relationship instance

Representation:

• In Chen notation: Single line connecting the entity to the relationship
• In Crow’s Foot: A circle at the relationship end (optional)

Examples:

• Not every Customer has placed an Order (Customer has partial participation in “places” relationship)
• Not every Employee manages a Department (Employee has partial participation in “manages” relationship)

Relationship Attributes

Some relationships may have their own attributes that don’t belong to any of the participating entities:

Definition:

• Properties that describe the relationship itself
• Not attributes of the participating entities
• Provide information about how entities are related

Examples:

• In a Student “enrolls in” Course relationship:

  • “Registration_Date”: When the student registered for the course
  • “Grade”: The grade received by the student
  • “Section”: The specific section the student is enrolled in

• In an Employee “works on” Project relationship:

  • “Hours_Worked”: Time spent by the employee on the project
  • “Role”: The employee’s role in the project
  • “Start_Date”: When the employee began working on the project

Implementation:

• In one-to-one or one-to-many relationships, attributes can sometimes be placed in one of the entity tables
• In many-to-many relationships, attributes are placed in the junction table

Role Names in Relationships

Role names clarify the part played by each entity in a relationship:

Definition:

• Labels that specify the role of an entity in a relationship
• Particularly important in recursive relationships
• Helps distinguish different functions of the same entity type

Examples:

• In a recursive Employee “manages” Employee relationship:
  • “Manager” and “Subordinate” roles
• In a Person “is married to” Person relationship:
  • “Husband” and “Wife” roles
• In a Flight “connects” Airport relationship:
  • “Departure” and “Arrival” roles

Identifying vs. Non-identifying Relationships

This distinction is particularly important for weak entities:

Identifying Relationship

Definition:

• A relationship where one entity (weak entity) is identified by its association with another entity (owner entity)
• The primary key of the weak entity includes the primary key of its owner
• Essential for the existence of the weak entity

Representation:

• In Chen notation: Double diamond
• In IDEF1X: Solid line

Examples:

• Order “contains” Order Line Item (Order Line Item is identified by Order ID + Line Number)
• Employee “has” Dependent (Dependent is identified by Employee ID + Dependent Name)

Non-identifying Relationship

Definition:

• A relationship where the child entity has its own primary key
• The child entity can exist independently of the parent entity
• Foreign key does not form part of the primary key

Representation:

• In Chen notation: Regular diamond
• In IDEF1X: Dashed line

Examples:

• Department “employs” Employee (Employee has its own primary key)
• Customer “places” Order (Order has its own primary key)

Representing Relationships in ER Diagrams

Relationships are represented differently in various ER notation styles:

Chen Notation

• Relationships shown as diamond shapes connecting entity rectangles
• Relationship name written inside the diamond
• Cardinality written as “1”, “M”, “N” near the lines
• Double lines indicate total participation
• Single lines indicate partial participation

Crow’s Foot Notation

• Relationships shown as lines connecting entity rectangles
• Relationship name written along the line
• Cardinality indicated by symbols at the ends of lines:
  • One: Perpendicular line (|)
  • Many: Crow’s foot (<)
  • Zero or one: Circle with perpendicular line (o|)
  • Zero or many: Circle with crow’s foot (o<)
  • One or many: Perpendicular line with crow’s foot (|<)
• Solid lines indicate mandatory participation
• Dashed lines indicate optional participation

IDEF1X Notation

• Solid lines for identifying relationships
• Dashed lines for non-identifying relationships
• “P” label indicates parent in identifying relationship
• Dots and circles indicate cardinality and participation

Resolving Many-to-Many Relationships

Many-to-many relationships are typically resolved during implementation:

Process:

1. Create a new junction (associative) entity
2. Establish one-to-many relationships between each original entity and the junction entity
3. Move relationship attributes to the junction entity
4. Define a composite primary key in the junction entity using the primary keys from both original entities

Example:

• Original: Student “enrolls in” Course (M:N)
• Resolved:
  • Student “has” Enrollment (1:N)
  • Course “has” Enrollment (1:N)
  • Enrollment entity contains Student_ID, Course_ID, and attributes like Enrollment_Date, Grade

Relationship Design Considerations

When designing relationships, consider these factors:

1. Relationship Necessity

• Is the relationship essential to the system’s requirements?
• Does it provide valuable information?
• Will it be used in queries or reports?

2. Relationship Direction

• In which direction does the relationship need to be navigated?
• Will queries typically go from entity A to B, B to A, or both?

3. Business Rules

• What business rules govern the relationship?
• Are there constraints on how entities can be associated?
• Are there time-based or conditional aspects to the relationship?

4. Performance Implications

• How will the relationship affect query performance?
• Will it create large result sets when joined?
• Is denormalization warranted for performance reasons?

5. Referential Integrity

• What happens when a related entity is deleted?
• What happens when a key value is updated?
• What referential integrity constraints should be applied?

Common Relationship Design Errors

Avoid these common errors in relationship design:

1. Incorrect Cardinality

• Misinterpreting the real-world relationship
• Not accounting for all possible scenarios
• Example: Treating a potentially many-to-many relationship as one-to-many

2. Missing Relationships

• Failing to identify important connections between entities
• Not capturing all required associations
• Example: Not relating Order to Customer

3. Redundant Relationships

• Creating direct relationships that can be derived through other relationships
• Adding unnecessary complexity
• Example: Creating a direct Author-to-Publisher relationship when it can be derived through Book

4. Inappropriate Relationship Attributes

• Placing attributes on relationships that actually belong to one of the entities
• Failing to identify legitimate relationship attributes
• Example: Putting “Grade” as a Course attribute instead of as a Student-Course relationship attribute

5. Relationship Cycles

• Creating unnecessary circular relationships
• Creating ambiguous paths between entities
• Example: A→B→C→A when simpler relationships would suffice

Relationships are fundamental to database design, as they define how data is connected across different tables. By properly modeling relationships, database designers can create systems that accurately represent real-world connections while providing efficient data access and maintaining integrity.