ER Models: Entity Relationship, Relationships & Cardinality

This guide explains Entity-Relationship models in detail, covering relationships, cardinality, and practical examples.

In a Nutshell

ER models are visual representations used to describe database structures, showing entities, relationships between them, and their cardinality.

Core Concept

Entity-Relationship modeling is a method for designing databases at a conceptual level. It visualizes your data structure before you write any implementation code.

Main components:

• Entities: Real-world objects (Customer, Product, Order)
• Relationships: Associations between entities (customers → place → orders)
• Attributes: Properties of entities (Name, Price, Date)
• Cardinality: How many instances relate to each other (1:1, 1:n, n:m)

Notation styles:

• Chen notation: Rectangles for entities, diamonds for relationships
• Crow’s Foot: Modern notation using crow’s foot symbols for cardinality
• UML class diagrams: Extended representation with methods and operations

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

Key Points for Study

• Entities: Real objects with unique identification
• Relationships: Associations between entities with defined cardinality
• Cardinality: 1:1, 1:n, and n:m relationships define how many instances can connect
• Attributes: Properties of entities (simple, composite, derived)
• Chen notation: Classic ER notation using rectangles and diamonds
• Crow’s Foot: Modern notation with intuitive cardinality representation
• Database design: Essential for designing and modeling databases
• Normalization: ER models provide the foundation for the normalization process

Core Components

1. Entity Type: A collection of similar entities sharing the same attributes
2. Weak Entity: An entity without its own unique identifier, depending on another entity
3. Relationship Type: An association between two or more entity types
4. Attribute: A property of an entity or relationship
5. Primary Key: A unique identifier for an entity
6. Foreign Key: A reference to the primary key of another entity
7. Cardinality: The maximum and minimum number of relationship instances
8. Participation: Whether an entity must participate in a relationship (total or partial)

Practical Examples

1:1 Relationship

-- Each employee has exactly one workspace
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 place 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 (Using Junction Table)

-- Students can enroll in many courses, courses 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 various 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 Notation 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
• |{ : One or more

Attribute Types

Simple Attributes

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

Composite Attributes

-- Address as a 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: Shared language for domain experts and developers
• Documentation: Clear documentation of database structure
• Planning: Foundation for database implementation
• Quality: Early detection of design flaws

Disadvantages

• Complexity: Large systems can become difficult to read
• Abstraction: Implementation details remain hidden
• Maintenance: Changes require model updates
• Learning curve: Understanding different notations takes time

Common Exam Questions

1. What’s the difference between 1:n and n:m relationships? In a 1:n relationship, one record on the left can relate to many on the right. In n:m, many records on the left can relate to many on the right (requires a junction table).

2. When do you need a junction table? For n:m relationships, since databases don’t directly support many-to-many connections without an intermediary table.

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

4. Explain Chen notation. Rectangles represent entities, diamonds represent relationships, and ovals represent attributes.

Key Resources

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