Vorbereitung – Projektszenario

Bevor wir uns den JOINs zuwenden, erstellen wir eine neue Datenbank für unser Bestellsystem. Dieses Szenario ermöglicht es uns, die verschiedenen Beziehungstypen, die in relationalen Datenbanken vorkommen, praxisnah abzubilden.

Wechseln Sie in phpMyAdmin und erstellen Sie eine neue Datenbank mit dem Namen bestellsystem. Wählen Sie wieder die Kollation utf8mb4_0900_ai_ci. Führen Sie dann die folgenden SQL-Befehle nacheinander aus.

Schritt 1: Tabelle kunden

CREATE TABLE kunden (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    registrierungsdatum DATE NOT NULL
);

INSERT INTO kunden (vorname, nachname, email, registrierungsdatum) VALUES
('Anna', 'Schmidt', 'anna.schmidt@email.de', '2024-01-10'),
('Benjamin', 'Weber', 'ben.weber@email.de', '2024-01-15'),
('Claudia', 'Meyer', 'claudia.meyer@email.de', '2024-02-01'),
('David', 'Wagner', 'david.wagner@email.de', '2024-02-20'),
('Elena', 'Fischer', 'elena.fischer@email.de', '2024-03-05');

CREATE TABLE kunden (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    registrierungsdatum DATE NOT NULL
);

INSERT INTO kunden (vorname, nachname, email, registrierungsdatum) VALUES
('Anna', 'Schmidt', 'anna.schmidt@email.de', '2024-01-10'),
('Benjamin', 'Weber', 'ben.weber@email.de', '2024-01-15'),
('Claudia', 'Meyer', 'claudia.meyer@email.de', '2024-02-01'),
('David', 'Wagner', 'david.wagner@email.de', '2024-02-20'),
('Elena', 'Fischer', 'elena.fischer@email.de', '2024-03-05');

Schritt 2: Tabelle produkte

CREATE TABLE produkte (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    preis DECIMAL(10,2) NOT NULL,
    lagerbestand INT NOT NULL
);

INSERT INTO produkte (name, preis, lagerbestand) VALUES
('Laptop Pro', 1299.00, 15),
('Mobiltelefon X', 699.00, 30),
('Kopfhörer Wireless', 89.90, 50),
('Maus ergonomisch', 29.99, 100),
('Tastatur mechanisch', 79.90, 25),
('Monitor 27 Zoll', 349.00, 10);

CREATE TABLE produkte (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    preis DECIMAL(10,2) NOT NULL,
    lagerbestand INT NOT NULL
);

INSERT INTO produkte (name, preis, lagerbestand) VALUES
('Laptop Pro', 1299.00, 15),
('Mobiltelefon X', 699.00, 30),
('Kopfhörer Wireless', 89.90, 50),
('Maus ergonomisch', 29.99, 100),
('Tastatur mechanisch', 79.90, 25),
('Monitor 27 Zoll', 349.00, 10);

Schritt 3: Tabelle bestellungen

Die Bestellungen bilden die Verbindung zwischen Kunden und Produkten. Eine Bestellung gehört zu genau einem Kunden (über kunden_id), kann aber mehrere Produkte enthalten. Um diese n:m-Beziehung (viele-zu-viele) abzubilden, benötigen wir eine zusätzliche Tabelle, die Bestellungen und Produkte verknüpft.

CREATE TABLE bestellungen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kunden_id INT NOT NULL,
    bestelldatum DATETIME NOT NULL,
    status VARCHAR(50) DEFAULT 'offen',
    FOREIGN KEY (kunden_id) REFERENCES kunden(id) ON DELETE RESTRICT
);

INSERT INTO bestellungen (kunden_id, bestelldatum, status) VALUES
(1, '2024-02-15 10:30:00', 'geliefert'),
(1, '2024-03-01 14:15:00', 'offen'),
(2, '2024-02-20 09:45:00', 'geliefert'),
(3, '2024-02-25 16:20:00', 'versendet'),
(4, '2024-03-05 11:00:00', 'offen');

CREATE TABLE bestellungen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kunden_id INT NOT NULL,
    bestelldatum DATETIME NOT NULL,
    status VARCHAR(50) DEFAULT 'offen',
    FOREIGN KEY (kunden_id) REFERENCES kunden(id) ON DELETE RESTRICT
);

INSERT INTO bestellungen (kunden_id, bestelldatum, status) VALUES
(1, '2024-02-15 10:30:00', 'geliefert'),
(1, '2024-03-01 14:15:00', 'offen'),
(2, '2024-02-20 09:45:00', 'geliefert'),
(3, '2024-02-25 16:20:00', 'versendet'),
(4, '2024-03-05 11:00:00', 'offen');

Schritt 4: Tabelle bestellpositionen (Verknüpfungstabelle)

Diese Tabelle löst die n:m-Beziehung zwischen Bestellungen und Produkten auf. Jede Zeile repräsentiert ein bestimmtes Produkt in einer bestimmten Bestellung.

CREATE TABLE bestellpositionen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bestellung_id INT NOT NULL,
    produkt_id INT NOT NULL,
    menge INT NOT NULL,
    einzelpreis DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (bestellung_id) REFERENCES bestellungen(id) ON DELETE CASCADE,
    FOREIGN KEY (produkt_id) REFERENCES produkte(id) ON DELETE RESTRICT
);

INSERT INTO bestellpositionen (bestellung_id, produkt_id, menge, einzelpreis) VALUES
(1, 1, 1, 1299.00),
(1, 3, 2, 89.90),
(2, 2, 1, 699.00),
(3, 4, 3, 29.99),
(3, 5, 1, 79.90),
(4, 6, 2, 349.00),
(5, 3, 1, 89.90);

CREATE TABLE bestellpositionen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bestellung_id INT NOT NULL,
    produkt_id INT NOT NULL,
    menge INT NOT NULL,
    einzelpreis DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (bestellung_id) REFERENCES bestellungen(id) ON DELETE CASCADE,
    FOREIGN KEY (produkt_id) REFERENCES produkte(id) ON DELETE RESTRICT
);

INSERT INTO bestellpositionen (bestellung_id, produkt_id, menge, einzelpreis) VALUES
(1, 1, 1, 1299.00),
(1, 3, 2, 89.90),
(2, 2, 1, 699.00),
(3, 4, 3, 29.99),
(3, 5, 1, 79.90),
(4, 6, 2, 349.00),
(5, 3, 1, 89.90);

Mit diesen vier Tabellen haben wir ein vollständiges, konsistentes Bestellsystem aufgesetzt. Wir können nun die Beziehungen analysieren und JOINs praktisch erproben.

Beziehungstypen verstehen

Bevor wir SQL-JOINs einsetzen, ist es wichtig, die verschiedenen Arten von Beziehungen zwischen Tabellen zu verstehen. Diese bestimmen, wie wir die Tabellen verknüpfen müssen.

1:n-Beziehung (Eins-zu-Viele)

Dies ist der häufigste Beziehungstyp. Ein Datensatz in Tabelle A kann mit mehreren Datensätzen in Tabelle B verknüpft sein, aber jeder Datensatz in Tabelle B gehört zu genau einem Datensatz in Tabelle A.

In unserem Bestellsystem:

• Ein Kunde kann mehrere Bestellungen aufgeben.
• Eine Bestellung gehört zu genau einem Kunden.

Die kunden_id in der Tabelle bestellungen ist der Fremdschlüssel, der diese Beziehung abbildet.

n:m-Beziehung (Viele-zu-Viele)

Hier kann ein Datensatz in Tabelle A mit mehreren Datensätzen in Tabelle B verknüpft sein und umgekehrt. Diese Beziehung lässt sich nicht direkt mit zwei Tabellen abbilden. Sie benötigt eine Verknüpfungstabelle (auch Brückentabelle oder Junction Table genannt).

In unserem Bestellsystem:

• Eine Bestellung kann mehrere Produkte enthalten.
• Ein Produkt kann in mehreren Bestellungen vorkommen.

Die Tabelle bestellpositionen ist diese Verknüpfungstabelle. Sie enthält Fremdschlüssel sowohl zu bestellungen als auch zu produkte und speichert zusätzlich attributive Daten wie menge und einzelpreis.