ER Models: Entity Relationship, Relationships & Cardinality

This article is a term explanation for Entity-Relationship models – including relationships, cardinalities and practical examples.

In a Nutshell

ER models are graphical representations for visualizing database structures that describe entities, relationships and their cardinalities.

Compact Technical Description

Entity-Relationship modeling is a method for conceptual design of databases. It visualizes the data structure before technical implementation takes place.

Main components:

• Entities: Objects from the real world (customer, product, order)
• Relationships: Relationships between entities (customers → order → products)
• Attributes: Properties of entities (name, price, date)
• Cardinalities: Number of relationships (1:1, 1:n, n:m)

Notations:

• Chen Notation: Rectangles for entities, diamonds for relationships
• Crow’s Foot: Modern notation with crow’s feet for cardinalities
• UML Class Diagrams: Extended representation with methods

ER models serve as communication tools between developers, database administrators and domain experts. They form the basis for logical data modeling and subsequent normalization.

Exam-Relevant Key Points

• Entities: Real objects with unique identification
• Relationships: Relationships between entities with cardinalities
• Cardinalities: 1:1, 1:n, n:m relationships define the number of connections
• Attributes: Properties of entities (simple, composite, derived)
• Chen Notation: Classic ER notation with rectangles and diamonds
• Crow’s Foot: Modern notation with intuitive cardinality representation
• IHK-relevant: Important for database design and modeling
• Normalization: ER models form the basis for the normalization process

Core Components

1. Entity Type: Set of similar entities with the same attributes
2. Weak Entity: Entity without unique identification, dependent on another entity
3. Relationship Type: Association between two or more entity types
4. Attribute: Property of an entity or relationship
5. Primary Key: Unique identifier of an entity
6. Foreign Key: Reference to primary key of another entity
7. Cardinality: Maximum and minimum number of relationship instances
8. Participation: Whether an entity must participate in a relationship (total/partial)

Practical Examples

1:1 Relationship

-- Each employee has exactly one workstation
CREATE TABLE Mitarbeiter (
    mitarbeiter_id INT PRIMARY KEY,
    name VARCHAR(100),
    geburtsdatum DATE
);

CREATE TABLE Arbeitsplatz (
    arbeitsplatz_id INT PRIMARY KEY,
    gebaeude VARCHAR(50),
    etage INT,
    raum INT,
    mitarbeiter_id INT UNIQUE, -- 1:1 Relationship
    FOREIGN KEY (mitarbeiter_id) REFERENCES Mitarbeiter(mitarbeiter_id)
);

1:n Relationship

-- One customer can have many orders
CREATE TABLE Kunden (
    kunden_id INT PRIMARY KEY,
    name VARCHAR(100),
    adresse VARCHAR(200)
);

CREATE TABLE Bestellungen (
    bestell_id INT PRIMARY KEY,
    bestelldatum DATE,
    gesamtbetrag DECIMAL(10,2),
    kunden_id INT, -- 1:n Relationship
    FOREIGN KEY (kunden_id) REFERENCES Kunden(kunden_id)
);

n:m Relationship (with Junction Table)

-- Students can enroll in many courses, courses can have many students
CREATE TABLE Studenten (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE Kurse (
    kurs_id INT PRIMARY KEY,
    kursname VARCHAR(100),
    credits INT
);

-- Junction Table for n:m relationship
CREATE TABLE StudentenKurse (
    student_id INT,
    kurs_id INT,
    belegdatum DATE,
    note DECIMAL(3,1),
    PRIMARY KEY (student_id, kurs_id),
    FOREIGN KEY (student_id) REFERENCES Studenten(student_id),
    FOREIGN KEY (kurs_id) REFERENCES Kurse(kurs_id)
);

Complex ER Model (Library Example)

-- Entities with different relationships
CREATE TABLE Autoren (
    autor_id INT PRIMARY KEY,
    name VARCHAR(100),
    geburtsjahr INT
);

CREATE TABLE Buecher (
    buch_id INT PRIMARY KEY,
    titel VARCHAR(200),
    isbn VARCHAR(20) UNIQUE,
    erschienenjahr INT
);

CREATE TABLE Verlage (
    verlag_id INT PRIMARY KEY,
    name VARCHAR(100),
    sitz VARCHAR(100)
);

CREATE TABLE Kunden (
    kunden_id INT PRIMARY KEY,
    name VARCHAR(100),
    mitgliedsdatum DATE
);

-- Relationships
-- n:m: Authors write books
CREATE TABLE BuchAutoren (
    buch_id INT,
    autor_id INT,
    PRIMARY KEY (buch_id, autor_id),
    FOREIGN KEY (buch_id) REFERENCES Buecher(buch_id),
    FOREIGN KEY (autor_id) REFERENCES Autoren(autor_id)
);

-- n:1: Books are published by publishers
ALTER TABLE Buecher ADD verlag_id INT;
ALTER TABLE Buecher ADD FOREIGN KEY (verlag_id) REFERENCES Verlage(verlag_id);

-- 1:n: Customers borrow books
CREATE TABLE Ausleihen (
    ausleih_id INT PRIMARY KEY,
    kunden_id INT,
    buch_id INT,
    ausleihdatum DATE,
    rueckgabedatum DATE,
    FOREIGN KEY (kunden_id) REFERENCES Kunden(kunden_id),
    FOREIGN KEY (buch_id) REFERENCES Buecher(buch_id)
);

ER Notations Comparison

Chen Notation

[ KUNDE ] ---< bestellt >--- [ PRODUKT ]
   |                          |
   |1                        |n
   |                          |
[ADRESSE]                [KATEGORIE]

Crow’s Foot Notation

KUNDE ||--o{ BESTELLPOSITION } ||--| PRODUKT
  |                               |
  |                               |
ADRESSE                         KATEGORIE

Cardinality Symbols

• | : Exactly one
• O : Zero or one
• } : Zero or more
• |{ : Exactly one or more

Attribute Types

Simple Attributes

name VARCHAR(100)        -- Simple
preis DECIMAL(10,2)      -- Simple
datum DATE              -- Simple

Composite Attributes

-- Address as composite attribute
strasse VARCHAR(100),
hausnummer VARCHAR(10),
plz VARCHAR(5),
ort VARCHAR(100)

Derived Attributes

-- Calculated values
alter INT AS (YEAR(CURRENT_DATE) - YEAR(geburtsdatum)),
gesamtpreis DECIMAL(10,2) AS (menge * einzelpreis)

Multivalued Attributes

-- Multiple values per entity
CREATE TABLE Telefonnummern (
    kunden_id INT,
    telefonnummer VARCHAR(20),
    PRIMARY KEY (kunden_id, telefonnummer),
    FOREIGN KEY (kunden_id) REFERENCES Kunden(kunden_id)
);

Advantages and Disadvantages

Advantages of ER Models

• Visualization: Intuitive representation of complex data structures
• Communication: Common language for domain experts and developers
• Documentation: Good documentation of database structure
• Planning: Foundation for database implementation
• Quality: Early detection of design flaws

Disadvantages

• Complexity: Becomes unclear with very large systems
• Abstraction: Implementation details not visible
• Maintenance: Changes require model adjustments
• Learning Curve: Requires understanding of notations

Common Exam Questions

1. What is the difference between 1:n and n:m relationships? 1:n: One record on the left can have many records on the right. n:m: Many records on the left can have many records on the right (requires junction table).

2. When do you need a junction table? For n:m relationships, since databases do not support direct n:m relationships.

3. What are weak entities? Entities without their own primary key that are dependent on other entities.

4. Explain Chen Notation! Rectangles for entities, diamonds for relationships, ovals for attributes.

Most Important Sources

1. https://de.wikipedia.org/wiki/Entity-Relationship-Modell
2. https://www.gatech.edu/coe/cse/er-diagrams
3. https://www.lucidchart.com/pages/er-diagrams

---

Recommended Reading: Databases