Section 3.2: ERD Notations & Relational Modeling

Entity-Relationship Diagrams (ERDs) and Relational Modeling

An Entity-Relationship Diagram (ERD) is a visual model that represents the entities within a business domain and the relationships between them. First proposed by Peter Chen in 1976, the entity-relationship model has evolved into the standard blueprint for relational database design. To build and interpret these diagrams, data management professionals must understand keys, relationship types, cardinality, and notation standards.

---

Key Types and Integrity Constraints

Relational databases rely on specific key types to enforce database integrity rules. Integrity rules ensure that the data stored remains accurate, consistent, and valid.

• Primary Key (PK): A primary key is an attribute (or group of attributes) that uniquely identifies a single row in a table. Primary keys are mandatory, must be unique, and cannot contain null values. This requirement is known as Entity Integrity.
• Candidate Key: A candidate key is any attribute or set of attributes that could potentially serve as the primary key because it is unique and non-null.
• Alternate Key (AK): Also known as a secondary unique identifier, an alternate key is a candidate key that was not chosen as the primary key. For example, if a table uses Customer ID (an auto-generated integer) as the Primary Key, the customer's Email Address or Tax ID would be defined as an Alternate Key. It is enforced physically using a UNIQUE constraint.
• Foreign Key (FK): A foreign key is an attribute in a table that references the primary key or alternate key of another table (or the same table, in a recursive relationship). Foreign keys establish the link between tables and enforce Referential Integrity (RI), ensuring that a record cannot reference a non-existent parent record.
• Surrogate Key: A system-generated, artificial unique identifier (such as an auto-incrementing integer or a UUID) that has no inherent business meaning. It is used instead of a natural key to simplify joins and insulate the schema from business logic changes.
• Natural Key: A primary key consisting of attributes that exist in the real world and have business meaning (e.g., Vehicle Identification Number or ISBN).

---

Cardinality and Optionality

In relational modeling, relationships between entities are defined by two key dimensions: cardinality and optionality.

1. Cardinality (Maximum Value): The maximum number of times an instance in one entity can associate with instances in a related entity. Cardinality is expressed as either One (1) or Many (N or M).
2. Optionality or Modality (Minimum Value): The minimum number of times an instance in one entity must associate with instances in a related entity. Optionality is expressed as either Zero (0, indicating the relationship is optional) or One (1, indicating the relationship is mandatory).

---

Identifying vs. Non-Identifying Relationships

A critical distinction in relational modeling is how child entities inherit identity from parent entities:

• Identifying Relationship: Occurs when the child entity cannot exist or be uniquely identified without the parent entity. In this case, the primary key of the parent entity is inherited as a foreign key by the child entity and becomes part of the child's composite primary key.
  • Example: An Order Item cannot exist without an Order. The primary key of Order Item is a composite of Order ID (PK/FK) and Item Line Number (PK).
• Non-Identifying Relationship: Occurs when the child entity is independent of the parent. The primary key of the parent is inherited by the child as a standard foreign key attribute, but it does not form part of the child's primary key.
  • Example: A Customer places an Order. The Order table contains Customer ID as a foreign key to link it to the customer, but Order is uniquely identified by its own Order ID. If the customer is deleted, the order could theoretically exist, or the relationship is optional.

---

Comparison of ERD Notations

There are three primary notations used to represent data models in Entity-Relationship Diagrams. DAMA candidates must recognize the visual styles and rules of each.

1. Information Engineering (IE) / Crow's Foot Notation

IE notation is the most popular style for logical and physical data modeling due to its readability. It represents cardinality and optionality using symbols at the ends of relationship lines (often called "Crow's Foot" notation):

• Mandatory One (1:1): Two vertical hash marks crossing the relationship line.
• Mandatory Many (1:N): A hash mark followed by a "crow's foot" (three diverging lines).
• Optional One (0:1): A circle followed by a vertical hash mark.
• Optional Many (0:N): A circle followed by a crow's foot.

2. IDEF1X (Integration Definition for Information Modeling)

IDEF1X is a highly structured, formal modeling language that was adopted as a federal standard in the United States. It has strict rules, particularly regarding relationship types:

• Identifying Relationships: Drawn as a solid line. The child entity is represented as a rounded rectangle (dependent entity).
• Non-Identifying Relationships: Drawn as a dashed line. The child entity is represented as a square-cornered rectangle (independent entity).
• Cardinality Indicators: IDEF1X uses a black dot at the end of a line to represent "many." An optional relationship contains a small circle near the parent or child. It also uses letters like Z (zero or one) and P (one or more) to declare exact constraints.

3. Unified Modeling Language (UML)

UML is primarily used for object-oriented software engineering but can be adapted for data modeling.

• Entities are represented as Classes containing attributes and operations (methods).
• Relationships are represented as Associations.
• Multiplicity (cardinality and optionality combined) is written textually next to the class box (e.g., 0..* for optional many, 1..1 for mandatory one, 1..* for mandatory many).

---

Advanced Relational Modeling Structures

Beyond standard tables and relationships, data modelers must occasionally construct advanced patterns:

1. Recursive Relationships: Also known as self-referencing relationships, these occur when an entity has a relationship with itself. For instance, in an Employee table, a column named Manager Employee ID is a foreign key referencing the Employee ID primary key within the same table. This structure is common for representing hierarchies (e.g., organization charts, bill of materials).
2. Supertype and Subtype Relationships: These represent generalization/specialization hierarchies where a general entity (the Supertype) shares common attributes with one or more specific entities (the Subtypes).
   • Example: A supertype Party contains attributes common to all parties (Party ID, Address). The subtypes Individual (attributes: Date of Birth, Gender) and Organization (attributes: Tax ID, Incorporation Date) inherit all attributes from Party.
   • Implementation Options:
     • Single Table (Table-per-Hierarchy): All supertype and subtype attributes are flattened into a single physical table, with a discriminator column (e.g., Party Type) indicating which subtype a row belongs to. This improves query speed but results in many nullable columns.
     • Separate Tables (Table-per-Type): The supertype and each subtype are mapped to individual physical tables. The subtype tables share the same primary key as the supertype, linked via a 1:1 identifying relationship. This is highly normalized but requires costly joins.