PMG2426 1/DB Beleg/Klara

Wiki-Quelltext dieser Seite.

<language>SQL</language>
<html>
<head>
    <title>Bibliotheksverwaltungssystem</title>
</head>
<body>
    <h1>Bibliotheksverwaltungssystem</h1>
    
    <h2>Ziel</h2>
    <p>Die Datenbank dient der effizienten Verwaltung einer Schulbibliothek. Sie ermöglicht die Verwaltung von Büchern und Autoren, Schülermanagement, Ausleihverfolgung sowie automatische Überfälligkeitskontrolle mit Mahngebührenberechnung. Ziel ist die Reduktion administrativen Aufwands, Vermeidung von Doppelverleihen durch Foreign Key Constraints und schnelle Abfrage verfügbarer Bücher. Die Themenwahl Bibliothek ist praxisnah, demonstriert komplexe n:m-Beziehungen und eignet sich hervorragend zur Darstellung von JOIN-Operationen, Aggregationen und Normalisierungsprinzipien in realem Kontext.</p>

    <h2>ERM</h2>
    <img src="Tante_klala_Erdplus.jpg" alt="ERM-Diagramm" width="600">
[[Bild:Tante_klala_Erdplu.s.jpg | center]]
   
<h2>Kardinalitäten</h2>
<p>Kardinalitäten im Bibliotheksprojekt: Ein AUTOR schreibt mehrere BÜCHER (1:n-Beziehung), ein SCHÜLER leiht mehrere Bücher aus (1:n), BÜCHER und SCHÜLER stehen über die AUSLEIHE-Tabelle in n:m-Beziehung. Dadurch werden Mehrfachzuordnungen sauber aufgelöst.</p>

<h2>Normalform</h2>
    <p>Normalformen des Projekts: Das System erfüllt 1NF (atomare Werte in allen Spalten), 2NF (vollständige PK-Abhängigkeiten), 3NF (keine transitiven Abhängigkeiten wie "Autor → Nationalität → Geburtsjahr") und BCNF (jeder Determinant ist Kandidatenschlüssel). Dadurch entsteht keine Datenredundanz, Foreign Keys sichern Referenzintegrität, und Abfragen laufen performant.</p>

    <h2>Reflexion</h2>
    <p>Stärken: Indizes, Views. Schwäche: Keine Triggers. Verbesserung: Buch-Exemplare.</p>

    <h2>Quellen</h2>
    <p> https://erdplus.com/ </p>
</body>
</html>


<code id="69830ae6bd747">CREATE TABLE IF NOT EXISTS AUTOR (
Autor_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Vorname VARCHAR(50) NOT NULL,
Nachname VARCHAR(50) NOT NULL,
Geburtsjahr INT,
Nationalitaet VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS BUCH (
Buch_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Titel VARCHAR(200) NOT NULL,
ISBN VARCHAR(17),
Erscheinungsjahr INT,
Seitenzahl INT,
Kategorie VARCHAR(50),
Autor_ID INTEGER NOT NULL,
FOREIGN KEY (Autor_ID) REFERENCES AUTOR(Autor_ID)
);

CREATE TABLE IF NOT EXISTS SCHUELER (
Schueler_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Vorname VARCHAR(50) NOT NULL,
Nachname VARCHAR(50) NOT NULL,
Geburtsdatum DATE NOT NULL,
Klasse VARCHAR(10),
Email VARCHAR(100),
Telefon VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS AUSLEIHE (
Ausleihe_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Buch_ID INTEGER NOT NULL,
Schueler_ID INTEGER NOT NULL,
Ausleihedatum DATE NOT NULL,
Geplante_Rueckgabe DATE NOT NULL,
Tatsaechliche_Rueckgabe DATE,
Status VARCHAR(20) NOT NULL DEFAULT 'ausgeliehen',
FOREIGN KEY (Buch_ID) REFERENCES BUCH(Buch_ID),
FOREIGN KEY (Schueler_ID) REFERENCES SCHUELER(Schueler_ID)
);

CREATE INDEX IF NOT EXISTS idx_buch_titel ON BUCH(Titel);
CREATE INDEX IF NOT EXISTS idx_schueler_nachname ON SCHUELER(Nachname);
CREATE INDEX IF NOT EXISTS idx_ausleihe_status ON AUSLEIHE(Status);

DELETE FROM AUSLEIHE;
DELETE FROM SCHUELER;
DELETE FROM BUCH;
DELETE FROM AUTOR;

INSERT INTO AUTOR (Vorname, Nachname, Geburtsjahr, Nationalitaet) VALUES
('Johann Wolfgang', 'Goethe', 1749, 'Deutsch'),
('Friedrich', 'Schiller', 1759, 'Deutsch'),
('Hermann', 'Hesse', 1877, 'Deutsch-Schweizer'),
('Franz', 'Kafka', 1883, 'Tschechisch'),
('Thomas', 'Mann', 1875, 'Deutsch'),
('Erich Maria', 'Remarque', 1898, 'Deutsch'),
('J.K.', 'Rowling', 1965, 'Britisch'),
('George', 'Orwell', 1903, 'Britisch'),
('Agatha', 'Christie', 1890, 'Britisch'),
('Ernest', 'Hemingway', 1899, 'Amerikanisch');

INSERT INTO BUCH (Titel, ISBN, Erscheinungsjahr, Seitenzahl, Kategorie, Autor_ID) VALUES
('Die Leiden des jungen Werther', '978-3-15-000067-0', 1774, 148, 'Roman', 1),
('Faust - Der Tragödie erster Teil', '978-3-15-000001-4', 1808, 176, 'Drama', 1),
('Wilhelm Tell', '978-3-15-000012-0', 1804, 144, 'Drama', 2),
('Die Räuber', '978-3-15-000015-1', 1781, 160, 'Drama', 2),
('Der Steppenwolf', '978-3-518-36675-9', 1927, 288, 'Roman', 3),
('Siddhartha', '978-3-518-36627-8', 1922, 136, 'Roman', 3),
('Die Verwandlung', '978-3-15-009900-6', 1915, 80, 'Erzählung', 4),
('Der Prozess', '978-3-596-90120-5', 1925, 312, 'Roman', 4),
('Buddenbrooks', '978-3-10-048001-5', 1901, 736, 'Roman', 5),
('Der Zauberberg', '978-3-10-048002-2', 1924, 1008, 'Roman', 5),
('Im Westen nichts Neues', '978-3-462-00001-0', 1929, 288, 'Roman', 6),
('Harry Potter und der Stein der Weisen', '978-3-551-55167-2', 1997, 336, 'Fantasy', 7),
('Harry Potter und die Kammer des Schreckens', '978-3-551-55168-9', 1998, 352, 'Fantasy', 7),
('1984', '978-3-548-23415-9', 1949, 352, 'Science-Fiction', 8),
('Farm der Tiere', '978-3-257-20118-5', 1945, 144, 'Fabel', 8),
('Mord im Orient-Express', '978-3-596-17277-7', 1934, 272, 'Krimi', 9),
('Der alte Mann und das Meer', '978-3-499-23652-4', 1952, 128, 'Roman', 10);

INSERT INTO SCHUELER (Vorname, Nachname, Geburtsdatum, Klasse, Email, Telefon) VALUES
('Max', 'Müller', '2008-03-15', '10a', 'max.mueller@schule.de', '0123-456789'),
('Anna', 'Schmidt', '2007-07-22', '11b', 'anna.schmidt@schule.de', '0123-456790'),
('Leon', 'Weber', '2009-11-08', '9c', 'leon.weber@schule.de', '0123-456791'),
('Sophie', 'Fischer', '2008-05-30', '10b', 'sophie.fischer@schule.de', '0123-456792'),
('Paul', 'Wagner', '2007-01-18', '11a', 'paul.wagner@schule.de', '0123-456793'),
('Emma', 'Becker', '2009-09-25', '9a', 'emma.becker@schule.de', '0123-456794'),
('Felix', 'Koch', '2008-12-03', '10a', 'felix.koch@schule.de', '0123-456795'),
('Lena', 'Hoffmann', '2007-04-14', '11c', 'lena.hoffmann@schule.de', '0123-456796'),
('Jonas', 'Schulz', '2009-06-21', '9b', 'jonas.schulz@schule.de', '0123-456797'),
('Mia', 'Zimmermann', '2008-08-09', '10c', 'mia.zimmermann@schule.de', '0123-456798');

INSERT INTO AUSLEIHE (Buch_ID, Schueler_ID, Ausleihedatum, Geplante_Rueckgabe, Tatsaechliche_Rueckgabe, Status) VALUES
(1, 1, '2026-01-10', '2026-01-24', '2026-01-23', 'zurueckgegeben'),
(2, 2, '2026-01-12', '2026-01-26', '2026-01-25', 'zurueckgegeben'),
(5, 3, '2026-01-15', '2026-01-29', '2026-01-28', 'zurueckgegeben'),
(7, 4, '2026-01-18', '2026-02-01', '2026-02-01', 'zurueckgegeben'),
(12, 1, '2026-01-25', '2026-02-08', NULL, 'ausgeliehen'),
(13, 2, '2026-01-28', '2026-02-11', NULL, 'ausgeliehen'),
(14, 5, '2026-02-01', '2026-02-15', NULL, 'ausgeliehen'),
(15, 6, '2026-02-02', '2026-02-16', NULL, 'ausgeliehen'),
(16, 7, '2026-02-03', '2026-02-17', NULL, 'ausgeliehen'),
(3, 3, '2026-01-05', '2026-01-19', NULL, 'ueberfaellig'),
(6, 4, '2026-01-08', '2026-01-22', NULL, 'ueberfaellig'),
(9, 8, '2026-01-10', '2026-01-24', NULL, 'ueberfaellig');

CREATE VIEW IF NOT EXISTS verfuegbare_buecher AS
SELECT b.Buch_ID, b.Titel, b.ISBN, a.Vorname AS Autor_Vorname, a.Nachname AS Autor_Nachname,
       b.Kategorie, b.Erscheinungsjahr
FROM BUCH b
JOIN AUTOR a ON b.Autor_ID = a.Autor_ID
WHERE b.Buch_ID NOT IN (
  SELECT Buch_ID FROM AUSLEIHE WHERE Status IN ('ausgeliehen','ueberfaellig')
);

CREATE VIEW IF NOT EXISTS aktuelle_ausleihen AS
SELECT au.Ausleihe_ID, b.Titel, b.ISBN, a.Nachname AS Autor,
       s.Vorname AS Schueler_Vorname, s.Nachname AS Schueler_Nachname, s.Klasse,
       au.Ausleihedatum, au.Geplante_Rueckgabe, au.Status,
       julianday('now') - julianday(au.Geplante_Rueckgabe) AS Tage_ueberfaellig
FROM AUSLEIHE au
JOIN BUCH b ON au.Buch_ID = b.Buch_ID
JOIN AUTOR a ON b.Autor_ID = a.Autor_ID
JOIN SCHUELER s ON au.Schueler_ID = s.Schueler_ID
WHERE au.Status IN ('ausgeliehen','ueberfaellig');
</code>
<run id="69831086268e2">SELECT * FROM verfuegbare_buecher;</run>
<run id="6983108626951">SELECT Kategorie, COUNT(*) as Anzahl_Buecher
FROM BUCH 
GROUP BY Kategorie 
ORDER BY Anzahl_Buecher DESC;</run>

<run id="69831086269c4">SELECT s.Nachname, b.Titel,
       ROUND((julianday('now') - julianday(au.Geplante_Rueckgabe)) * 0.50, 2) AS Mahngebuehr_EUR
FROM AUSLEIHE au
JOIN SCHUELER s ON au.Schueler_ID = s.Schueler_ID
JOIN BUCH b ON au.Buch_ID = b.Buch_ID
WHERE au.Status = 'ueberfaellig';</run>

<run id="6983108626a26">SELECT 
  s.Vorname, s.Nachname, s.Klasse,
  COUNT(au.Ausleihe_ID) as Gesamt_Ausleihen,
  SUM(CASE WHEN au.Status = 'ueberfaellig' 
           THEN CAST((julianday('now') - julianday(au.Geplante_Rueckgabe)) AS INTEGER) 
           ELSE 0 END) as Ueberfaellige_Tage
FROM SCHUELER s
LEFT JOIN AUSLEIHE au ON s.Schueler_ID = au.Schueler_ID
WHERE au.Status IN ('ausgeliehen', 'ueberfaellig') OR au.Schueler_ID IS NULL
GROUP BY s.Schueler_ID
ORDER BY Gesamt_Ausleihen DESC, Ueberfaellige_Tage DESC
LIMIT 10;</run>