Schulbibliothek

Geschätzte Lektüre: 15 Minuten 258 Ansichten

Datenbank für eine Schulbibliothek entwerfen

Szenario:
Eine Schulbibliothek möchte eine Datenbank anlegen, in der folgendes verwaltet wird:

  • Bücher (Titel, Autor, ISBN, Erscheinungsjahr, Anzahl Exemplare)
  • Schüler (Name, Klasse, E-Mail, Ausweisnummer)
  • Ausleihen (welcher Schüler hat wann welches Buch ausgeliehen? Wann ist die Rückgabe fällig? Wann wurde es tatsächlich zurückgegeben?)

Aufgabenstellung:

  1. Entwerfen Sie die Datenbank: Zeichnen Sie ein ER-Diagramm und eine tabellarische Skizze für die benötigten Tabellen.
  2. Geben Sie für jede Tabelle einen sinnvollen Primärschlüssel an.
  3. Erklären Sie, wo ein Fremdschlüssel benötigt wird.
  4. Schreiben Sie die SQL-CREATE-Tabellen-Befehle für alle drei Tabellen (vereinfacht, mit 3–4 Spalten pro Tabelle).

Lösung – ER-Diagramm – tabellarische Skizze

Entitäten:
— Buch (BuchID, Titel, Autor, ISBN, Erscheinungsjahr, Exemplare)
— Schüler (SchuelerID, Name, Klasse, Email, Ausweisnummer)
— Ausleihe (AusleihID, BuchID, SchuelerID, Ausleihdatum, Faellig_am, Rueckgabe_datum)

Beziehungen:
— Ein Schüler kann mehrere Bücher ausleihen → 1:n zwischen Schüler und Ausleihe
— Ein Buch kann mehrfach ausgeliehen werden → 1:n zwischen Buch und Ausleihe

Primärschlüssel:
— Buch: BuchID
— Schüler: SchuelerID
— Ausleihe: AusleihID

Fremdschlüssel:
— In der Tabelle Ausleihe:
• BuchID verweist auf Buch(BuchID)
• SchuelerID verweist auf Schueler(SchuelerID)

SQL-CREATE-Befehle

CREATE TABLE Buch (
    BuchID INT PRIMARY KEY,
    Titel VARCHAR(100),
    Autor VARCHAR(80),
    ISBN VARCHAR(13)
);

CREATE TABLE Schueler (
    SchuelerID INT PRIMARY KEY,
    Name VARCHAR(80),
    Klasse VARCHAR(10),
    Ausweisnummer VARCHAR(20)
);

CREATE TABLE Ausleihe (
    AusleihID INT PRIMARY KEY,
    BuchID INT,
    SchuelerID INT,
    Ausleihdatum DATE,
    Faellig_am DATE,
    FOREIGN KEY (BuchID) REFERENCES Buch(BuchID),
    FOREIGN KEY (SchuelerID) REFERENCES Schueler(SchuelerID)
);

ER-Diagramm und Tabellen-Skizze (zum Verständnis)

Entität Buch:

SpalteTypPrimärschlüssel?
buch_id (einfache Nummer)INT✅ Ja (auto)
titelVARCHAR(100)
autorVARCHAR(80)
isbnVARCHAR(20)

Entität Schüler:

SpalteTypPrimärschlüssel?
schueler_id (einfache Nummer)INT✅ Ja (auto)
nameVARCHAR(80)
klasseVARCHAR(10)
ausweisnummerVARCHAR(30)

Beziehung Ausleihe (verbindet Buch und Schüler):

SpalteTypFremdschlüssel?
ausleihe_idINT✅ Primärschlüssel (auto)
buch_idINT✅ Fremdschlüssel → Buch
schueler_idINT✅ Fremdschlüssel → Schüler
ausleihdatumDATE
rueckgabe_faelligDATE
tatsaechlich_zurueckDATE(darf leer sein)

Warum Fremdschlüssel?
Ein Schüler kann viele Bücher ausleihen, ein Buch kann mehrmals ausgeliehen werden (aber zu unterschiedlichen Zeiten).
In der Ausleihe speichern wir nur die IDs von Buch & Schüler – das sind die Fremdschlüssel.

-- =============================================
-- SCHULBIBLIOTHEK - Komplettes Datenbank-Setup
-- =============================================

-- 1. Datenbank verwenden (falls du sie schon hast)
USE Schulbibliothek;

-- =============================================
-- TABELLEN LÖSCHEN (falls vorhanden - für sauberen Neustart)
-- =============================================
DROP TABLE IF EXISTS Ausleihe;
DROP TABLE IF EXISTS Buch;
DROP TABLE IF EXISTS Schueler;

-- =============================================
-- TABELLE 1: Bücher
-- =============================================
CREATE TABLE Buch (
    buch_id INT PRIMARY KEY AUTO_INCREMENT,
    titel VARCHAR(100) NOT NULL,
    autor VARCHAR(80) NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    erscheinungsjahr INT,
    anzahl_exemplare INT DEFAULT 1
);

-- =============================================
-- TABELLE 2: Schüler
-- =============================================
CREATE TABLE Schueler (
    schueler_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL,
    klasse VARCHAR(10) NOT NULL,
    email VARCHAR(100),
    ausweisnummer VARCHAR(30) UNIQUE NOT NULL
);

-- =============================================
-- TABELLE 3: Ausleihen (mit Fremdschlüsseln)
-- =============================================
CREATE TABLE Ausleihe (
    ausleihe_id INT PRIMARY KEY AUTO_INCREMENT,
    buch_id INT NOT NULL,
    schueler_id INT NOT NULL,
    ausleihdatum DATE NOT NULL,
    rueckgabe_faellig DATE NOT NULL,
    tatsaechlich_zurueck DATE,
    
    -- Fremdschlüssel Definitionen
    FOREIGN KEY (buch_id) REFERENCES Buch(buch_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (schueler_id) REFERENCES Schueler(schueler_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- =============================================
-- FIKTIVE DATEN: BÜCHER (10 Bücher)
-- =============================================
INSERT INTO Buch (titel, autor, isbn, erscheinungsjahr, anzahl_exemplare) VALUES
('Harry Potter und der Stein der Weisen', 'J.K. Rowling', '978-3551354013', 1997, 3),
('Der Herr der Ringe: Die Gefährten', 'J.R.R. Tolkien', '978-3608939761', 1954, 2),
('Die Verwandlung', 'Franz Kafka', '978-3150099009', 1915, 1),
('Tschick', 'Wolfgang Herrndorf', '978-3499256365', 2010, 4),
('Das Parfum', 'Patrick Süskind', '978-3257202281', 1985, 2),
('Die unendliche Geschichte', 'Michael Ende', '978-3522217008', 1979, 1),
('Momo', 'Michael Ende', '978-3522179801', 1973, 2),
('Der Vorleser', 'Bernhard Schlink', '978-3257234053', 1995, 1),
('Krabat', 'Otfried Preußler', '978-3522202400', 1971, 2),
('Emil und die Detektive', 'Erich Kästner', '978-3791530057', 1929, 3);

-- =============================================
-- FIKTIVE DATEN: SCHÜLER (8 Schüler)
-- =============================================
INSERT INTO Schueler (name, klasse, email, ausweisnummer) VALUES
('Lena Schmidt', '7a', 'lena.schmidt@schulemail.de', 'S-2024-001'),
('Felix Wagner', '7b', 'felix.wagner@schulemail.de', 'S-2024-002'),
('Mia Hoffmann', '8a', 'mia.hoffmann@schulemail.de', 'S-2024-003'),
('Jonas Becker', '8b', 'jonas.becker@schulemail.de', 'S-2024-004'),
('Emma Fischer', '9a', 'emma.fischer@schulemail.de', 'S-2024-005'),
('Paul Weber', '7a', 'paul.weber@schulemail.de', 'S-2024-006'),
('Laura Schulz', '9b', 'laura.schulz@schulemail.de', 'S-2024-007'),
('Lukas Meier', '8a', 'lukas.meier@schulemail.de', 'S-2024-008');

-- =============================================
-- FIKTIVE DATEN: AUSLEIHEN (15 Ausleihen - einige zurückgegeben, einige noch offen)
-- =============================================
INSERT INTO Ausleihe (buch_id, schueler_id, ausleihdatum, rueckgabe_faellig, tatsaechlich_zurueck) VALUES

-- Ausleihe 1: Lena leiht Harry Potter (zurückgegeben)
(1, 1, '2026-04-01', '2026-04-15', '2026-04-14'),

-- Ausleihe 2: Felix leiht Herr der Ringe (noch nicht zurück)
(2, 2, '2026-04-10', '2026-04-24', NULL),

-- Ausleihe 3: Mia leiht Tschick (zurückgegeben)
(4, 3, '2026-03-20', '2026-04-03', '2026-04-02'),

-- Ausleihe 4: Jonas leiht Das Parfum (verspätet, noch nicht zurück)
(5, 4, '2026-03-25', '2026-04-08', NULL),

-- Ausleihe 5: Emma leiht Momo (zurückgegeben)
(7, 5, '2026-04-05', '2026-04-19', '2026-04-18'),

-- Ausleihe 6: Paul leiht Krabat (noch nicht zurück)
(9, 6, '2026-04-12', '2026-04-26', NULL),

-- Ausleihe 7: Laura leiht Die Verwandlung (pünktlich zurück)
(3, 7, '2026-03-15', '2026-03-29', '2026-03-29'),

-- Ausleihe 8: Lukas leiht Emil und die Detektive (zurückgegeben)
(10, 8, '2026-04-02', '2026-04-16', '2026-04-15'),

-- Ausleihe 9: Lena leiht Die unendliche Geschichte (noch nicht zurück)
(6, 1, '2026-04-08', '2026-04-22', NULL),

-- Ausleihe 10: Felix leiht Harry Potter (2. Exemplar, noch nicht zurück)
(1, 2, '2026-04-09', '2026-04-23', NULL),

-- Ausleihe 11: Mia leiht Der Vorleser (zurückgegeben)
(8, 3, '2026-03-28', '2026-04-11', '2026-04-10'),

-- Ausleihe 12: Jonas leiht Tschick (noch nicht zurück)
(4, 4, '2026-04-11', '2026-04-25', NULL),

-- Ausleihe 13: Emma leiht Harry Potter (3. Exemplar, zurückgegeben)
(1, 5, '2026-03-18', '2026-04-01', '2026-03-30'),

-- Ausleihe 14: Paul leiht Herr der Ringe (verspätet, noch nicht zurück)
(2, 6, '2026-03-22', '2026-04-05', NULL),

-- Ausleihe 15: Laura leiht Krabat (zurückgegeben)
(9, 7, '2026-04-03', '2026-04-17', '2026-04-16');

-- =============================================
-- ZUSÄTZLICHE TEST-QUERIES (zum Ausprobieren)
-- =============================================

-- 1. Alle aktuellen Ausleihen (nicht zurückgegebene Bücher)
SELECT 
    s.name AS Schueler,
    s.klasse,
    b.titel AS Buch,
    a.ausleihdatum,
    a.rueckgabe_faellig,
    DATEDIFF(CURDATE(), a.rueckgabe_faellig) AS Tage_ueberfaellig
FROM Ausleihe a
JOIN Schueler s ON a.schueler_id = s.schueler_id
JOIN Buch b ON a.buch_id = b.buch_id
WHERE a.tatsaechlich_zurueck IS NULL
ORDER BY a.rueckgabe_faellig;

-- 2. Meistausgeliehene Bücher
SELECT 
    b.titel,
    COUNT(a.ausleihe_id) AS ausgeliehen_count
FROM Buch b
LEFT JOIN Ausleihe a ON b.buch_id = a.buch_id
GROUP BY b.buch_id
ORDER BY ausgeliehen_count DESC
LIMIT 5;

-- 3. Schüler mit den meisten Ausleihen
SELECT 
    s.name,
    s.klasse,
    COUNT(a.ausleihe_id) AS anzahl_ausleihen
FROM Schueler s
LEFT JOIN Ausleihe a ON s.schueler_id = a.schueler_id
GROUP BY s.schueler_id
ORDER BY anzahl_ausleihen DESC;

-- 4. Prüfen ob Fremdschlüssel existieren (siehe vorherige Erklärung)
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'Schulbibliothek'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 5. Überfällige Bücher (heute, 25.04.2026)
SELECT 
    s.name,
    b.titel,
    a.rueckgabe_faellig,
    DATEDIFF('2026-04-25', a.rueckgabe_faellig) AS tage_verspaetet
FROM Ausleihe a
JOIN Schueler s ON a.schueler_id = s.schueler_id
JOIN Buch b ON a.buch_id = b.buch_id
WHERE a.tatsaechlich_zurueck IS NULL 
  AND a.rueckgabe_faellig < '2026-04-25';
Dieses Dokument teilen

Schulbibliothek

Oder Link kopieren

INHALT

Abonnieren

×
Cancel