ER Models: Entities, Relationships & Cardinality

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

In a Nutshell

ER models are diagrams used to visualize database structures, showing entities, relationships between them, and their cardinality constraints.

Technical Overview

Entity-Relationship modeling is a method for designing databases at the conceptual level. It visualizes data structure before implementation begins.

Core components:

• Entities: Objects from the real world (customer, product, order)
• Relationships: Associations between entities (customers → place → orders)
• Attributes: Properties of entities (name, price, date)
• Cardinality: The number of relationship instances (1:1, 1:n, n:m)

Common notations:

• 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 operations

ER models serve as a communication bridge between developers, database administrators, and business stakeholders. They form the foundation for logical data modeling and subsequent normalization.

Key Exam Topics

• Entities: Real-world objects with unique identification
• Relationships: Associations between entities defined by cardinality
• Cardinality: 1:1, 1:n, n:m relationships defining connection counts
• Attributes: Entity properties (simple, composite, derived)
• Chen notation: Classic ER notation using rectangles and diamonds
• Crow’s Foot: Modern notation with intuitive cardinality representation
• Exam relevance: Essential for database design and modeling
• Normalization: ER models provide the foundation for normalization processes

Core Concepts

1. Entity Type: A set of similar entities sharing the same attributes
2. Weak Entity: An entity without its own unique identifier, dependent 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: The unique identifier for an entity
6. Foreign Key: A reference to the 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 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 (with 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 Notations Compared

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)
);

Strengths and Weaknesses

Advantages of ER Models

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

Disadvantages

• Complexity: Can become difficult to read for large systems
• Abstraction: Implementation details remain hidden
• Maintenance: Changes require model updates
• Learning curve: Understanding notation requires study

Common Exam Questions

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

2. When do you need a junction table? For n:m relationships, since relational databases cannot directly support many-to-many associations without an intermediate table.

3. What are weak entities? Entities without their own primary key, dependent on another entity for identification.

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

Key References

1. https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model
2. https://www.gatech.edu/coe/cse/er-diagrams
3. https://www.lucidchart.com/pages/er-diagrams

---

Recommended Reading: Databases