Projekt: Bibliotheks-DB

Zeit fuer dein erstes grosses Projekt! Du baust eine komplette Datenbank fuer eine Bibliothek - von der Planung ueber die Tabellenerstellung bis hin zu komplexen Abfragen und Reports. Dabei wendest du alle SQL-Techniken an, die du bisher gelernt hast.

Projektueberblick

Unsere Bibliotheks-Datenbank soll folgendes verwalten:

• Buecher mit Titel, ISBN, Verlag und Erscheinungsjahr
• Autoren mit Vor- und Nachname
• Mitglieder der Bibliothek mit Kontaktdaten
• Ausleihen - wer hat welches Buch wann ausgeliehen und zurueckgegeben
• Kategorien fuer die thematische Einteilung

Schritt 1: Datenbankdesign

ER-Modell

[autoren] N ──── M [buecher] 1 ──── N [ausleihen] N ──── 1 [mitglieder]
                      │
                      N
                      │
                      M
                      │
                  [kategorien]

Beziehungen:

• Ein Buch kann mehrere Autoren haben, ein Autor kann mehrere Buecher haben (N:M)
• Ein Buch kann in mehreren Kategorien sein (N:M)
• Ein Mitglied kann viele Ausleihen haben (1:N)
• Jede Ausleihe betrifft genau ein Buch und ein Mitglied

Schritt 2: Tabellen erstellen

-- Autoren
CREATE TABLE autoren (
    id INTEGER PRIMARY KEY,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    geburtsjahr INTEGER,
    nationalitaet VARCHAR(50)
);

-- Kategorien
CREATE TABLE kategorien (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    beschreibung TEXT
);

-- Buecher
CREATE TABLE buecher (
    id INTEGER PRIMARY KEY,
    titel VARCHAR(300) NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    verlag VARCHAR(200),
    erscheinungsjahr INTEGER,
    seitenanzahl INTEGER,
    exemplare INTEGER DEFAULT 1 CHECK (exemplare >= 0),
    verfuegbar INTEGER DEFAULT 1 CHECK (verfuegbar >= 0)
);

-- Buch-Autor Verknuepfung (N:M)
CREATE TABLE buch_autor (
    buch_id INTEGER NOT NULL REFERENCES buecher(id) ON DELETE CASCADE,
    autor_id INTEGER NOT NULL REFERENCES autoren(id) ON DELETE CASCADE,
    PRIMARY KEY (buch_id, autor_id)
);

-- Buch-Kategorie Verknuepfung (N:M)
CREATE TABLE buch_kategorie (
    buch_id INTEGER NOT NULL REFERENCES buecher(id) ON DELETE CASCADE,
    kategorie_id INTEGER NOT NULL REFERENCES kategorien(id) ON DELETE CASCADE,
    PRIMARY KEY (buch_id, kategorie_id)
);

-- Mitglieder
CREATE TABLE mitglieder (
    id INTEGER PRIMARY KEY,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    telefon VARCHAR(20),
    mitglied_seit DATE DEFAULT CURRENT_DATE,
    aktiv BOOLEAN DEFAULT TRUE
);

-- Ausleihen
CREATE TABLE ausleihen (
    id INTEGER PRIMARY KEY,
    buch_id INTEGER NOT NULL REFERENCES buecher(id),
    mitglied_id INTEGER NOT NULL REFERENCES mitglieder(id),
    ausleihdatum DATE DEFAULT CURRENT_DATE,
    faellig_am DATE NOT NULL,
    rueckgabedatum DATE,
    verlaengert BOOLEAN DEFAULT FALSE
);

Schritt 3: Indizes erstellen

CREATE INDEX idx_ausleihen_buch ON ausleihen(buch_id);
CREATE INDEX idx_ausleihen_mitglied ON ausleihen(mitglied_id);
CREATE INDEX idx_ausleihen_faellig ON ausleihen(faellig_am);
CREATE INDEX idx_buecher_titel ON buecher(titel);
CREATE INDEX idx_autoren_nachname ON autoren(nachname);

Schritt 4: Testdaten einfuegen

-- Autoren
INSERT INTO autoren (id, vorname, nachname, geburtsjahr, nationalitaet) VALUES
(1, 'Johann Wolfgang', 'von Goethe', 1749, 'deutsch'),
(2, 'Franz', 'Kafka', 1883, 'oesterreichisch'),
(3, 'Thomas', 'Mann', 1875, 'deutsch'),
(4, 'Hermann', 'Hesse', 1877, 'deutsch'),
(5, 'Friedrich', 'Schiller', 1759, 'deutsch'),
(6, 'Cornelia', 'Funke', 1958, 'deutsch'),
(7, 'Michael', 'Ende', 1929, 'deutsch'),
(8, 'Patrick', 'Sueskind', 1949, 'deutsch');

-- Kategorien
INSERT INTO kategorien (id, name, beschreibung) VALUES
(1, 'Roman', 'Erz??hlende Prosa'),
(2, 'Lyrik', 'Gedichte und poetische Werke'),
(3, 'Drama', 'Theaterstuecke und Dramen'),
(4, 'Kinderbuch', 'Buecher fuer Kinder und Jugendliche'),
(5, 'Klassiker', 'Klassische Literatur'),
(6, 'Fantasy', 'Phantastische Literatur');

-- Buecher
INSERT INTO buecher (id, titel, isbn, verlag, erscheinungsjahr, seitenanzahl, exemplare, verfuegbar) VALUES
(1, 'Faust', '9783150000014', 'Reclam', 1808, 224, 3, 2),
(2, 'Die Verwandlung', '9783150099155', 'Reclam', 1915, 72, 2, 1),
(3, 'Der Zauberberg', '9783596294848', 'Fischer', 1924, 1008, 2, 2),
(4, 'Der Steppenwolf', '9783518366752', 'Suhrkamp', 1927, 288, 2, 1),
(5, 'Die Raeuber', '9783150000151', 'Reclam', 1781, 144, 2, 2),
(6, 'Tintenherz', '9783791504650', 'Dressler', 2003, 576, 3, 3),
(7, 'Die unendliche Geschichte', '9783522176842', 'Thienemann', 1979, 428, 3, 2),
(8, 'Das Parfum', '9783257228007', 'Diogenes', 1985, 320, 2, 0),
(9, 'Siddhartha', '9783518366783', 'Suhrkamp', 1922, 128, 2, 2),
(10, 'Die Leiden des jungen Werther', '9783150000670', 'Reclam', 1774, 176, 1, 1);

-- Buch-Autor Verknuepfungen
INSERT INTO buch_autor (buch_id, autor_id) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
(6, 6), (7, 7), (8, 8), (9, 4), (10, 1);

-- Buch-Kategorie Verknuepfungen
INSERT INTO buch_kategorie (buch_id, kategorie_id) VALUES
(1, 3), (1, 5), (2, 1), (2, 5), (3, 1), (3, 5),
(4, 1), (4, 5), (5, 3), (5, 5), (6, 4), (6, 6),
(7, 4), (7, 6), (8, 1), (9, 1), (9, 5), (10, 1), (10, 5);

-- Mitglieder
INSERT INTO mitglieder (id, vorname, nachname, email, mitglied_seit) VALUES
(1, 'Laura', 'Hoffmann', 'laura@example.com', '2025-01-15'),
(2, 'Stefan', 'Wagner', 'stefan@example.com', '2025-03-20'),
(3, 'Maria', 'Fischer', 'maria@example.com', '2025-06-01'),
(4, 'Thomas', 'Schroeder', 'thomas@example.com', '2025-09-10'),
(5, 'Julia', 'Neumann', 'julia@example.com', '2026-01-05');

-- Ausleihen
INSERT INTO ausleihen (id, buch_id, mitglied_id, ausleihdatum, faellig_am, rueckgabedatum) VALUES
(1, 1, 1, '2026-01-20', '2026-02-20', '2026-02-15'),
(2, 4, 1, '2026-02-01', '2026-03-01', '2026-02-28'),
(3, 8, 2, '2026-02-10', '2026-03-10', NULL),
(4, 8, 3, '2026-02-15', '2026-03-15', NULL),
(5, 2, 3, '2026-03-01', '2026-04-01', NULL),
(6, 7, 4, '2026-03-05', '2026-04-05', NULL),
(7, 1, 5, '2026-03-08', '2026-04-08', NULL),
(8, 6, 2, '2026-03-10', '2026-04-10', NULL),
(9, 3, 1, '2026-03-12', '2026-04-12', NULL);

Schritt 5: Abfragen und Reports

Alle Buecher mit Autoren

SELECT
    b.titel,
    a.vorname || ' ' || a.nachname AS autor,
    b.erscheinungsjahr,
    b.verlag
FROM buecher b
JOIN buch_autor ba ON b.id = ba.buch_id
JOIN autoren a ON ba.autor_id = a.id
ORDER BY a.nachname, b.titel;

Buecher nach Kategorie

SELECT
    k.name AS kategorie,
    COUNT(*) AS anzahl_buecher,
    ROUND(AVG(b.seitenanzahl), 0) AS avg_seiten
FROM kategorien k
JOIN buch_kategorie bk ON k.id = bk.kategorie_id
JOIN buecher b ON bk.buch_id = b.id
GROUP BY k.name
ORDER BY anzahl_buecher DESC;

Aktuell ausgeliehene Buecher

SELECT
    b.titel,
    m.vorname || ' ' || m.nachname AS ausgeliehen_von,
    a.ausleihdatum,
    a.faellig_am,
    CASE
        WHEN a.faellig_am < CURRENT_DATE THEN 'UEBERFAELLIG'
        WHEN a.faellig_am <= CURRENT_DATE + 7 THEN 'bald faellig'
        ELSE 'OK'
    END AS status
FROM ausleihen a
JOIN buecher b ON a.buch_id = b.id
JOIN mitglieder m ON a.mitglied_id = m.id
WHERE a.rueckgabedatum IS NULL
ORDER BY a.faellig_am;

Meistausgeliehene Buecher

SELECT
    b.titel,
    a.vorname || ' ' || a.nachname AS autor,
    COUNT(au.id) AS mal_ausgeliehen
FROM buecher b
JOIN buch_autor ba ON b.id = ba.buch_id
JOIN autoren a ON ba.autor_id = a.id
LEFT JOIN ausleihen au ON b.id = au.buch_id
GROUP BY b.titel, a.vorname, a.nachname
ORDER BY mal_ausgeliehen DESC
LIMIT 5;

Aktivste Mitglieder

SELECT
    m.vorname || ' ' || m.nachname AS mitglied,
    COUNT(a.id) AS ausleihen_gesamt,
    COUNT(CASE WHEN a.rueckgabedatum IS NULL THEN 1 END) AS aktuell_ausgeliehen,
    MIN(a.ausleihdatum) AS erste_ausleihe,
    MAX(a.ausleihdatum) AS letzte_ausleihe
FROM mitglieder m
LEFT JOIN ausleihen a ON m.id = a.mitglied_id
GROUP BY m.vorname, m.nachname
ORDER BY ausleihen_gesamt DESC;

Verfuegbarkeitsreport

SELECT
    b.titel,
    b.exemplare AS gesamt,
    b.verfuegbar,
    b.exemplare - b.verfuegbar AS ausgeliehen,
    CASE
        WHEN b.verfuegbar = 0 THEN 'Nicht verfuegbar'
        WHEN b.verfuegbar = 1 THEN 'Letztes Exemplar'
        ELSE 'Verfuegbar'
    END AS status
FROM buecher b
ORDER BY b.verfuegbar ASC;

Schritt 6: Views erstellen

-- View: Buecherkatalog mit Autoreninfo
CREATE VIEW v_katalog AS
SELECT
    b.id,
    b.titel,
    b.isbn,
    GROUP_CONCAT(a.vorname || ' ' || a.nachname, ', ') AS autoren,
    b.verlag,
    b.erscheinungsjahr,
    b.seitenanzahl,
    b.verfuegbar,
    b.exemplare
FROM buecher b
JOIN buch_autor ba ON b.id = ba.buch_id
JOIN autoren a ON ba.autor_id = a.id
GROUP BY b.id, b.titel, b.isbn, b.verlag, b.erscheinungsjahr, b.seitenanzahl, b.verfuegbar, b.exemplare;

-- View: Aktuelle Ausleihen
CREATE VIEW v_aktuelle_ausleihen AS
SELECT
    a.id AS ausleihe_id,
    b.titel AS buch,
    m.vorname || ' ' || m.nachname AS mitglied,
    m.email,
    a.ausleihdatum,
    a.faellig_am,
    CASE
        WHEN a.faellig_am < CURRENT_DATE THEN 'UEBERFAELLIG'
        ELSE 'aktiv'
    END AS status
FROM ausleihen a
JOIN buecher b ON a.buch_id = b.id
JOIN mitglieder m ON a.mitglied_id = m.id
WHERE a.rueckgabedatum IS NULL;

-- Nutzung:
SELECT * FROM v_aktuelle_ausleihen WHERE status = 'UEBERFAELLIG';

Schritt 7: Transaktionen fuer Geschaeftsprozesse

Buch ausleihen

BEGIN;

-- Pruefen, ob das Buch verfuegbar ist
-- SELECT verfuegbar FROM buecher WHERE id = 6;
-- Nur fortfahren, wenn verfuegbar > 0

-- Ausleihe anlegen
INSERT INTO ausleihen (id, buch_id, mitglied_id, ausleihdatum, faellig_am)
VALUES (10, 6, 4, CURRENT_DATE, CURRENT_DATE + 30);

-- Verfuegbarkeit reduzieren
UPDATE buecher SET verfuegbar = verfuegbar - 1 WHERE id = 6;

COMMIT;

Buch zurueckgeben

BEGIN;

-- Rueckgabedatum setzen
UPDATE ausleihen
SET rueckgabedatum = CURRENT_DATE
WHERE id = 3 AND rueckgabedatum IS NULL;

-- Verfuegbarkeit erhoehen
UPDATE buecher SET verfuegbar = verfuegbar + 1 WHERE buch_id = (
    SELECT buch_id FROM ausleihen WHERE id = 3
);

COMMIT;

Schritt 8: Fortgeschrittene Analysen

Window Function: Ausleih-Ranking pro Mitglied

SELECT
    m.vorname || ' ' || m.nachname AS mitglied,
    b.titel,
    a.ausleihdatum,
    ROW_NUMBER() OVER (
        PARTITION BY m.id ORDER BY a.ausleihdatum
    ) AS ausleihe_nr
FROM ausleihen a
JOIN mitglieder m ON a.mitglied_id = m.id
JOIN buecher b ON a.buch_id = b.id;

Buch-Popularitaet mit Ranking

SELECT
    b.titel,
    COUNT(a.id) AS ausleihen,
    RANK() OVER (ORDER BY COUNT(a.id) DESC) AS rang
FROM buecher b
LEFT JOIN ausleihen a ON b.id = a.buch_id
GROUP BY b.titel
ORDER BY rang;

Was kommt als Naechstes?

Im naechsten Projekt baust du eine Online-Shop Datenbank mit noch mehr Komplexitaet - Warenkorb, Zahlungen und Versand.

Zusammenfassung

• Du hast eine komplette Bibliotheks-Datenbank entworfen und umgesetzt
• N:M-Beziehungen mit Zwischentabellen (Buch-Autor, Buch-Kategorie)
• Indizes fuer haeufig genutzte Abfragen
• Views fuer wiederverwendbare Abfragen
• Transaktionen fuer sichere Geschaeftsprozesse (Ausleihe/Rueckgabe)
• Window Functions fuer Rankings und Analysen
• CASE WHEN fuer Status-Logik

Uebungen

1. Suche: Schreibe eine Abfrage, die Buecher nach Titel oder Autorname sucht (LIKE).
2. Report: Erstelle einen monatlichen Ausleih-Report.
3. Ueberfaellig: Finde alle ueberfaelligen Ausleihen mit Mitglied-Kontaktdaten.
4. Empfehlung: Finde Buecher, die von Mitgliedern ausgeliehen wurden, die auch Buch X ausgeliehen haben (einfaches Empfehlungssystem).
5. Erweiterung: Fuege eine Tabelle vormerkungen hinzu, damit Mitglieder ein Buch vormerken koennen, wenn es nicht verfuegbar ist.