Special Relationships in ER Diagrams

Remember where we left off:

Reasonable assumptions:

  • Every CEO runs a Studio
  • If a Studio ceases to exist, the CEO ceases to be CEO
  • If a CEO resigns or is fired, the Studio still exists

Goal

How do we model special types of relationships in ER diagrams that go beyond standard entities and relationships? Two main categories we consider here:

  1. Weak Entity Sets
  2. Hierarchies (Is-a / Subclass relationships)

Weak Entity Sets

A weak entity set cannot be uniquely identified by its own attributes. It is identified by combining:

  • the key of an owner entity, and
  • a partial key (discriminator) belonging to the weak entity.

Every weak entity must have an owner, so it has total participation in its identifying relationship.

This often occurs in “part-of” relationships.

Key properties of weak entity sets:

  • Must participate in a many-to-one relationship with the supporting entity set
  • Referential integrity is enforced on this relationship
  • Weak entity key = owner key + weak entity’s partial key

Notation in ER diagrams:

  • Weak entity set → double rectangle
  • Supporting relationship → double diamond
  • Key of the weak entity → derived from the supporting relationship

Think of a another example of a weak entity set, write it down in ER format

Hierarchies (Subclasses / Inheritance)

Sometimes entity sets are related through “is-a” relationships, forming hierarchies.

Properties:

  • Subclasses inherit attributes and keys from superclasses
  • Subclasses may have additional attributes of their own
  • Two main interpretations in ER design:

Entity sets can have inheritance and form hierarchies when their key attributes come from super-entity sets. This happens in “is a” hierarchies.

1. ER Hierarchies

  • An entity may belong to multiple subclasses simultaneously
  • Components exist in all subclasses to which they belong
  • Example: LeapFrog may exist as both Product and EdProduct

2. Object-Oriented Hierarchies

  • Each object belongs to exactly one class
  • Subclasses inherit from superclasses, forming a tree
  • Notation: Is-a triangle, pointing to the superclass

Draw the Notre Dame Computer Science Department in ER.

Design Principles

Be Faithful

  • Your design should reflect reality
  • Misrepresenting reality leads to poor queries and inconsistent data

Avoid Redundancy

  • Redundant data wastes space and encourages inconsistencies
  • Example: don’t list Company twice
  • Redundancy can lead to deletion anomalies (e.g., removing a product may inadvertently delete company information if stored redundantly)

Example:

Don’t list company twice.

Would lose company address if the company didn’t make some product for some time (deletion anomaly like from the first day)

Entity Sets vs Attributes

An entity set should satisfy at least one of these:

  1. It has non-key attributes, i.e., more than just a name
  2. It is the “many” in a many-to-one or many-to-many relationship

Don’t Overuse Weak Entity Sets

  • New designers sometimes make everything weak, relying on other entities to supply keys
  • Example: treating SSN as the key for everything
  • It is better to create local keys (CRN, EmployeeID, etc.) when possible

When to use weak entity sets:

  • When no global authority exists to generate unique identifiers
  • Example: player numbers in football teams worldwide – hard to assign globally unique IDs

Summary of Special Relationships

By now, students should be comfortable with:

  • Basic elements: entity, attribute, entity set
  • Relationships: binary, multiway, converting multiway to binary
  • Roles in relationships, attributes on relationships
  • Subclasses / is-a hierarchies
  • Constraints: many-one, one-one, many-many, key, single-valued, referential integrity, domain, general
  • Weak entity sets: notation, rules, examples
  • Design principles: faithful design, avoid redundancy, appropriate use of entity sets

ER Diagrams are foundational. Careful design upfront saves significant pain later. Bad early modeling choices propagate outward into code, queries, APIs, analytics, and existing data. Schema is difficult to change once software, queries, reports, and data depend on it, changing its meaning becomes expensive.

Updates to the schema: relatively rare. Rather painful. Why?