Bibliotheksdatenbank

Was das fertige Programm kann

• Das Skript läuft von einer leeren Datenbank bis zum Ende ohne Fehler, mit ON_ERROR_STOP, und gibt nichts außer den fünf Antworten aus.
• books (id, title, author, year, genre, copies) steht als Vorbild im Startcode: id ist der Primärschlüssel, title und author sind Pflicht, year ist positiv, copies ist mindestens 0 und standardmäßig 1.
• members (id integer, name text, email text, city text, joined date): id ist der Primärschlüssel, name und joined sind NOT NULL, und email ist UNIQUE; email und city dürfen NULL sein.
• loans (id integer, book_id integer, member_id integer, loaned_on date, returned_on date): id ist der Primärschlüssel, book_id verweist auf books und member_id auf members (beide NOT NULL), loaned_on ist NOT NULL, und ein CHECK sorgt dafür, dass returned_on nicht vor loaned_on liegt. INSERT INTO loans VALUES (6, 1, 99, '2026-09-30', NULL) muss also mit einem Fremdschlüsselfehler scheitern.
• Laden Sie die Beispieldaten aus dem Startcode: 6 Bücher, 4 Mitglieder und 5 Ausleihen, mit den angegebenen ids.
• Halten Sie fest, dass Ausleihe 2 (Kindred, ausgeliehen von Ada) am 2026-09-28 zurückgegeben wurde, mit einem UPDATE, das nur diese eine Zeile ändert.
• Q1: die Bücher, die gerade verliehen sind (returned_on ist NULL), mit den Spalten title, name und loaned_on, älteste Ausleihe zuerst.
• Q2: jedes Mitglied mit seiner Zahl an Ausleihen, Mitglieder ohne Ausleihe mit 0, in den Spalten name und loans, die meisten Ausleihen zuerst, dann nach Namen.
• Q3: die Titel der Bücher, die nie verliehen wurden, in der Spalte title, alphabetisch.
• Q4: die Genres, die mehr als einmal verliehen wurden, in den Spalten genre und loans, nach Genre sortiert.
• Q5: die Mitglieder mit unbekannter E-Mail oder unbekanntem Ort, in den Spalten name, email und city, wobei eine fehlende E-Mail als no e-mail und ein fehlender Ort als unknown erscheint, nach Namen sortiert.

Aufbau der Startdateien

main.sql

Das ganze Skript: das Schema (books ist vorgegeben), die Beispieldaten (die Zeilen für members und loans stehen auskommentiert bereit), die Änderung und die fünf Abfragen, jeweils mit TODO markiert.

Etappen

1. Etappe 1

   Schlüssel und Constraints

   Schreiben Sie CREATE TABLE members und CREATE TABLE loans nach dem Vorbild von books: PRIMARY KEY, NOT NULL, UNIQUE auf email, REFERENCES books und REFERENCES members und ein Tabellen-CHECK, der returned_on mit loaned_on vergleicht. Legen Sie members vor loans an, weil loans darauf verweist.

   Prüfungen, die nach dieser Etappe bestehen:

   • books, members und loans haben je einen Primärschlüssel
   • Jede Ausleihe verweist auf ein vorhandenes Mitglied (Fremdschlüssel auf members)
   • Jede Ausleihe verweist auf ein vorhandenes Buch (Fremdschlüssel auf books)
   • Keine E-Mail-Adresse kommt in members zweimal vor (Unique-Constraint)
   • Eine Ausleihe kann nicht vor dem Ausleihdatum zurückgegeben werden (Check auf loans)
   • Name und Beitrittsdatum eines Mitglieds sind Pflicht (NOT NULL)

2. Etappe 2

   Die Daten laden

   Entfernen Sie das -- vor den INSERT-Anweisungen für members und loans. Probieren Sie dann einmal INSERT INTO loans VALUES (6, 1, 99, '2026-09-30', NULL); aus, lesen Sie den Fremdschlüsselfehler und löschen Sie die Zeile wieder.

   Prüfungen, die nach dieser Etappe bestehen:

   • Die Daten sind geladen: 6 Bücher, 4 Mitglieder und 5 Ausleihen

3. Etappe 3

   Eine Rückgabe erfassen

   Schreiben Sie ein UPDATE mit einem WHERE auf die id der Ausleihe, sodass sich nur Ausleihe 2 ändert. Ohne WHERE gälten alle Ausleihen als zurückgegeben.

   Prüfungen, die nach dieser Etappe bestehen:

   • Ausleihe 2 wurde am 2026-09-28 zurückgegeben, und zwei Ausleihen sind noch offen

4. Etappe 4

   Die Fragen beantworten

   Schreiben Sie Q1 bis Q5 der Reihe nach, mit den Spaltennamen und dem ORDER BY aus der jeweiligen Anforderung: Joins für Q1, ein LEFT JOIN mit COUNT einer Spalte von loans für Q2, ein LEFT JOIN mit IS NULL für Q3, GROUP BY mit HAVING für Q4 und COALESCE für Q5.

   Prüfungen, die nach dieser Etappe bestehen:

   • Die fünf Antworten werden in der Reihenfolge und genau wie vorgegeben ausgegeben

Im Browser bauen

Bearbeiten Sie unten main.sql; setup.sql bleibt, wie es ist. „Code prüfen“ führt alle Abnahmetests auf einer frischen Datenbank aus, rechnen Sie also bis zum letzten Meilenstein mit Fehlschlägen.

Auf dem eigenen Rechner bauen

Legen Sie einen Ordner mit diesen Startdateien an, installieren Sie PostgreSQL 18 und arbeiten Sie die Etappen ab. Die Abnahmetests starten Sie jederzeit mit:

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

-- Library database: schema, data, one change and five answers.
-- Work through the milestones in order and run the file after each one.
-- Every run starts from an empty database.

-- 1. Schema
-- books is written for you, as a model.
CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text NOT NULL,
  year integer CHECK (year > 0),
  genre text,
  copies integer NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

-- TODO: members (id, name, email, city, joined): id is the primary key,
-- name and joined are required, and no e-mail address may appear twice.

-- TODO: loans (id, book_id, member_id, loaned_on, returned_on): id is the
-- primary key, book_id and member_id are required and must point at an
-- existing book and member, loaned_on is required, and a book cannot be
-- returned before it was loaned.

-- 2. Data
INSERT INTO books (id, title, author, year, genre, copies) VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

-- Remove the -- in front of these lines once members and loans exist.
-- INSERT INTO members (id, name, email, city, joined) VALUES
--   (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
--   (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
--   (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
--   (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

-- INSERT INTO loans (id, book_id, member_id, loaned_on, returned_on) VALUES
--   (1, 1, 1, '2026-09-01', '2026-09-10'),
--   (2, 3, 1, '2026-09-12', NULL),
--   (3, 2, 2, '2026-09-05', '2026-09-20'),
--   (4, 1, 3, '2026-09-15', NULL),
--   (5, 6, 2, '2026-09-18', NULL);

-- 3. A change
-- TODO: Ada brings Kindred back (loan 2) on 2026-09-28.

-- 4. Answers
-- TODO Q1. Which books are on loan right now, to whom, and since when?
-- TODO Q2. How many loans has each member made, including members with none?
-- TODO Q3. Which books have never been loaned?
-- TODO Q4. Which genres have been loaned more than once?
-- TODO Q5. Whose details are incomplete?

Abnahmetests

Das Projekt ist fertig, wenn jede Prüfung in test.sql besteht. Lesen Sie sie vor dem Start: Sie sind die Spezifikation, als Code geschrieben.

-- test: books, members und loans haben je einen Primärschlüssel
SELECT count(*) = 3 FROM pg_constraint
WHERE contype = 'p' AND conrelid::regclass::text IN ('books', 'members', 'loans');

-- test: Jede Ausleihe verweist auf ein vorhandenes Mitglied (Fremdschlüssel auf members)
SELECT EXISTS (SELECT 1 FROM pg_constraint
  WHERE contype = 'f' AND conrelid::regclass::text = 'loans' AND confrelid::regclass::text = 'members');

-- test: Jede Ausleihe verweist auf ein vorhandenes Buch (Fremdschlüssel auf books)
SELECT EXISTS (SELECT 1 FROM pg_constraint
  WHERE contype = 'f' AND conrelid::regclass::text = 'loans' AND confrelid::regclass::text = 'books');

-- test: Keine E-Mail-Adresse kommt in members zweimal vor (Unique-Constraint)
SELECT EXISTS (SELECT 1 FROM pg_constraint c
  JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
  WHERE c.contype = 'u' AND c.conrelid::regclass::text = 'members' AND a.attname = 'email');

-- test: Eine Ausleihe kann nicht vor dem Ausleihdatum zurückgegeben werden (Check auf loans)
SELECT EXISTS (SELECT 1 FROM pg_constraint
  WHERE contype = 'c' AND conrelid::regclass::text = 'loans'
    AND pg_get_constraintdef(oid) LIKE '%returned_on%' AND pg_get_constraintdef(oid) LIKE '%loaned_on%');

-- test: Name und Beitrittsdatum eines Mitglieds sind Pflicht (NOT NULL)
SELECT count(*) = 2 FROM pg_attribute
WHERE attrelid::regclass::text = 'members' AND attname IN ('name', 'joined') AND attnotnull;

-- test: Die Daten sind geladen: 6 Bücher, 4 Mitglieder und 5 Ausleihen
SELECT (SELECT count(*) FROM books) = 6
   AND (SELECT count(*) FROM members) = 4
   AND (SELECT count(*) FROM loans) = 5;

-- test: Ausleihe 2 wurde am 2026-09-28 zurückgegeben, und zwei Ausleihen sind noch offen
SELECT EXISTS (SELECT 1 FROM loans WHERE id = 2 AND returned_on = '2026-09-28')
   AND (SELECT count(*) FROM loans WHERE returned_on IS NULL) = 2;

-- test: Die fünf Antworten werden in der Reihenfolge und genau wie vorgegeben ausgegeben
-- output:
--     title    | name  | loaned_on
-- -------------+-------+------------
--  Dune        | Linus | 2026-09-15
--  Neuromancer | Grace | 2026-09-18
-- (2 rows)
--
--    name   | loans
-- ----------+-------
--  Ada      |     2
--  Grace    |     2
--  Linus    |     1
--  Margaret |     0
-- (4 rows)
--
--    title
-- ------------
--  Beloved
--  The Hobbit
-- (2 rows)
--
--  genre  | loans
-- --------+-------
--  sci-fi |     4
-- (1 row)
--
--    name   |        email         |  city
-- ----------+----------------------+---------
--  Linus    | no e-mail            | Berlin
--  Margaret | margaret@example.com | unknown
-- (2 rows)

Das fertige Programm starten

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Weiterentwickeln

• Ergänzen Sie eine Tabelle reservations (Mitglied, Buch, reserved_on) mit Fremdschlüsseln und eine Abfrage, die die Bücher zeigt, auf die ein Mitglied wartet.
• Ergänzen Sie eine Abfrage, die zu einem Datum Ihrer Wahl die offenen Ausleihen jedes Mitglieds zeigt, die älter als 14 Tage sind, mit der Zahl der Tage.
• Geben Sie members.id und loans.id eine Identitätsspalte (GENERATED ALWAYS AS IDENTITY) und fügen Sie neue Zeilen ein, ohne die ids zu schreiben; mit RETURNING id sehen Sie, welche id jede bekommen hat.
• Ergänzen Sie ON-DELETE-Regeln an den Fremdschlüsseln und finden Sie heraus, was mit den Ausleihen eines Mitglieds geschieht, wenn es gelöscht wird, mit RESTRICT und mit CASCADE.
• Sobald Sie Fensterfunktionen kennen (Modul I2), ordnen Sie die Bücher je Genre nach der Zahl ihrer Ausleihen.