Datenbanken: Komplexaufgaben

Realwelt-Szenarien modellieren

Diese Aufgaben bieten die Möglichkeit, reale Situationen durch die Erstellung von Datenbank-Schemas, Entity-Relationship-Diagrammen und SQL-Abfragen zu modellieren. Die Schülerinnen und Schüler sollen dabei die theoretischen Konzepte praktisch anwenden.

Die Aufgabenstruktur

Für jede Aufgabe sollen die folgenden Schritte durchlaufen werden:

1. Datenbank-Schema erstellen: Definiere die notwendigen Tabellen, Spalten (mit Datentypen), Primärschlüssel (PK) und Fremdschlüssel (FK) basierend auf dem Szenario. (Beispieldaten musst du selbstständig erstellen und einfügen.)
2. ER-Diagramm entwerfen: Erstelle ein Entity-Relationship-Diagramm, das die Entitäten, deren Attribute (nur die wichtigsten) und die Beziehungen zwischen ihnen mit den korrekten Kardinalitäten darstellt.
3. SQL-Abfragen erstellen (mindestens 5): Schreibe mindestens 5 SQL-Abfragen (SELECT, INSERT, UPDATE, DELETE), um typische Operationen für dieses Szenario durchzuführen.

Aufgabenübersicht

Bibliothekssystem

Szenario: Ein kleines Bibliothekssystem zur Verwaltung von Büchern, Mitgliedern und Ausleihen.

Schwierigkeitsgrad: 7/10

Schritte:

1. Schema: Tabellen: `Buecher` (BuchID PK, Titel, Autor, ISBN, Erscheinungsjahr), `Mitglieder` (MitgliedID PK, Name, Adresse), `Ausleihen` (AusleihID PK, BuchID FK, MitgliedID FK, Ausleihdatum, Rückgabedatum).
2. ERD: Entitäten: Buch, Mitglied, Ausleihe. Beziehungen: Mitglied-leiht-aus-Buch (1:n), Buch-ist-Teil-von-Ausleihe (1:n). Kardinalitäten beachten.
3. SQL-Abfragen: Alle Bücher eines bestimmten Autors anzeigen; Alle ausgeliehenen Bücher eines bestimmten Mitglieds anzeigen; Ein neues Buch hinzufügen; Ein Ausleihdatum aktualisieren; Alle Bücher anzeigen, die überfällig sind.

Online-Buchhandlung

Szenario: Ein einfaches System für eine Online-Buchhandlung zur Verwaltung von Produkten, Kunden und Bestellungen.

Schwierigkeitsgrad: 8/10

Schritte:

1. Schema: Tabellen: `Produkte` (ProduktID PK, Titel, Autor, Preis, Genre), `Kunden` (KundenID PK, Name, Email), `Bestellungen` (BestellID PK, KundenID FK, Bestelldatum, Gesamtpreis), `Bestellpositionen` (BestellPosID PK, BestellID FK, ProduktID FK, Menge, PreisProEinheit).
2. ERD: Entitäten: Produkt, Kunde, Bestellung, Bestellposition. Beziehungen: Kunde-hat-Bestellung (1:n), Bestellung-enthält-Produkt (über Bestellposition, m:n).
3. SQL-Abfragen: Alle Bücher eines bestimmten Genres anzeigen; Namen des Kunden und Bestelldatum seiner letzten Bestellung anzeigen; Neue Bestellung mit mehreren Positionen hinzufügen; Gesamtpreis einer Bestellung aktualisieren; Kunden anzeigen, die noch keine Bestellung getätigt haben.

Universitätskursverwaltung

Szenario: Verwaltung von Studenten, Kursen und deren Einschreibungen an einer Universität.

Schwierigkeitsgrad: 7/10

Schritte:

1. Schema: Tabellen: `Studenten` (StudentenID PK, Vorname, Nachname), `Kurse` (KursID PK, Kursname, Semester), `Einschreibungen` (EinschreibungsID PK, StudentenID FK, KursID FK, Note).
2. ERD: Entitäten: Student, Kurs, Einschreibung. Beziehungen: Student-belegt-Kurs (m:n, über Einschreibung), Einschreibung-bezieht-sich-auf-Student (1:n), Einschreibung-bezieht-sich-auf-Kurs (1:n).
3. SQL-Abfragen: Alle Kurse anzeigen, die ein bestimmter Student belegt; Durchschnittliche Note aller Studenten in einem bestimmten Kurs berechnen; Neuen Studenten einschreiben; Note eines Studenten für einen Kurs aktualisieren; Studenten auflisten, die in mindestens zwei Kursen eingeschrieben sind.

Projektmanagement-Tool

Szenario: Ein einfaches Tool zur Verwaltung von Projekten, Aufgaben und zugewiesenen Mitarbeitern.

Schwierigkeitsgrad: 8/10

Schritte:

1. Schema: Tabellen: `Projekte` (ProjektID PK, Projektname, Startdatum, Enddatum), `Aufgaben` (AufgabenID PK, ProjektID FK, Aufgabenname, Status, Fälligkeitsdatum), `Mitarbeiter` (MitarbeiterID PK, Name, Abteilung), `Zuweisungen` (ZuweisungsID PK, AufgabenID FK, MitarbeiterID FK).
2. ERD: Entitäten: Projekt, Aufgabe, Mitarbeiter, Zuweisung. Beziehungen: Projekt-hat-Aufgabe (1:n), Aufgabe-hat-Status (1:1), Mitarbeiter-wird-zugewiesen-zu-Aufgabe (m:n, über Zuweisung).
3. SQL-Abfragen: Alle Aufgaben eines bestimmten Projekts anzeigen, die noch nicht abgeschlossen sind; Alle Mitarbeiter anzeigen, die einem bestimmten Projekt zugewiesen sind; Neue Aufgabe für ein Projekt erstellen und sie einem Mitarbeiter zuweisen; Status einer Aufgabe ändern; Projekte anzeigen, die in den nächsten 30 Tagen fällig sind.

Event-Ticketing-System

Szenario: Verwaltung von Events, Veranstaltungsorten, Tickettypen und Kundenbuchungen.

Schwierigkeitsgrad: 7/10

Schritte:

1. Schema: Tabellen: `Veranstaltungsorte` (VeranstaltungsortID PK, Name, Adresse, Kapazität), `Events` (EventID PK, VeranstaltungsortID FK, Eventname, Datum, Uhrzeit), `TicketTypen` (TicketTypID PK, EventID FK, TypBezeichnung, Preis), `Kunden` (KundenID PK, Name, Email), `Buchungen` (BuchungsID PK, KundenID FK, TicketTypID FK, AnzahlTickets, Buchungsdatum).
2. ERD: Entitäten: Veranstaltungsort, Event, TicketTyp, Kunde, Buchung. Beziehungen: Veranstaltungsort-beherbergt-Event (1:n), Event-hat-TicketTyp (1:n), Kunde-erstellt-Buchung (1:n), Buchung-bezieht-sich-auf-TicketTyp (1:n).
3. SQL-Abfragen: Alle Events anzeigen, die an einem bestimmten Veranstaltungsort stattfinden; Anzahl verfügbarer Tickets eines Typs anzeigen; Neue Buchung erstellen; Anzahl gebuchter Tickets für ein Event aktualisieren; Kunden anzeigen, die Tickets für Events nach einem bestimmten Datum gekauft haben.

Restaurant-Management

Szenario: Verwaltung von Menüpunkten, Bestellungen, Tischen und Kunden in einem Restaurant.

Schwierigkeitsgrad: 6/10

Schritte:

1. Schema: Tabellen: `Tische` (TischID PK, TischNummer, Kapazität), `Kunden` (KundenID PK, Name, Telefon), `Menue` (MenueID PK, ArtikelName, Beschreibung, Preis, Kategorie), `Bestellungen` (BestellID PK, TischID FK, KundenID FK, Bestellzeitpunkt, Gesamtpreis), `Bestellpositionen` (BestellPosID PK, BestellID FK, MenueID FK, Anzahl, PreisProEinheit).
2. ERD: Entitäten: Tisch, Kunde, Menü, Bestellung, Bestellposition. Beziehungen: Tisch-hat-Bestellung (1:n), Kunde-platziert-Bestellung (1:n), Bestellung-enthält-Menü (m:n, über Bestellposition).
3. SQL-Abfragen: Verfügbare Menüpunkte einer Kategorie anzeigen; Alle Bestellungen für einen bestimmten Tisch anzeigen; Neue Bestellung aufnehmen; Gesamtpreis einer Bestellung berechnen; Kunden anzeigen, die mehr als 3 Artikel auf ihrer Bestellung hatten.

Waren- und Inventarverwaltung

Szenario: Verwaltung von Produkten, Lieferanten und Lagerbeständen in einem Geschäft.

Schwierigkeitsgrad: 7/10

Schritte:

1. Schema: Tabellen: `Produkte` (ProduktID PK, Name, Beschreibung, Verkaufspreis), `Lieferanten` (LieferantenID PK, Name, Kontaktperson), `Lagerbestand` (LagerID PK, ProduktID FK, LieferantenID FK, Menge, LetzteBestellung), `Einkaufsbestellungen` (BestellID PK, LieferantenID FK, BestellDatum, Gesamtbetrag).
2. ERD: Entitäten: Produkt, Lieferant, Lagerbestand, Einkaufsbestellung. Beziehungen: Produkt-ist-gelagert-in-Lagerbestand (1:n), Lieferant-liefert-Produkt (über Lagerbestand, m:n), Lieferant-sendet-Einkaufsbestellung (1:n).
3. SQL-Abfragen: Produkte anzeigen, deren Lagerbestand unter einem Schwellenwert liegt; Name und Kontaktdaten des Lieferanten eines Produkts anzeigen; Neue Einkaufsbestellung aufgeben; Lagerbestand aktualisieren; Lieferanten anzeigen, die mehr als 5 Produkte liefern.

Autovermietungssystem

Szenario: Verwaltung von Autos, Kunden und Mietvorgängen.

Schwierigkeitsgrad: 6/10

Schritte:

1. Schema: Tabellen: `Autos` (AutoID PK, Marke, Modell, Kennzeichen, Verfügbarkeit), `Kunden` (KundenID PK, Name, Führerscheinnummer), `Mietverträge` (MietvertragID PK, AutoID FK, KundenID FK, MieteStart, MieteEnde, KilometerstandStart, KilometerstandEnde).
2. ERD: Entitäten: Auto, Kunde, Mietvertrag. Beziehungen: Kunde-mietet-Auto (m:n, über Mietvertrag), Auto-ist-Teil-von-Mietvertrag (1:n), Kunde-ist-Teil-von-Mietvertrag (1:n).
3. SQL-Abfragen: Verfügbare Autos anzeigen; Mietverträge eines Kunden anzeigen; Neuen Mietvertrag erstellen und Auto als 'nicht verfügbar' markieren; Kilometerstand und Enddatum eines Mietvertrags aktualisieren; Autos anzeigen, die im letzten Monat vermietet waren.

Blog-Plattform

Szenario: Verwaltung von Benutzern, Blog-Posts, Kommentaren und Kategorien.

Schwierigkeitsgrad: 6/10

Schritte:

1. Schema: Tabellen: `Benutzer` (UserID PK, Benutzername, Email), `Posts` (PostID PK, UserID FK, Titel, Inhalt, Erstellungsdatum), `Kommentare` (KommentarID PK, PostID FK, UserID FK, KommentarText, KommentarDatum), `Kategorien` (KategorieID PK, KategorieName), `PostKategorien` (PostID FK, KategorieID FK).
2. ERD: Entitäten: Benutzer, Post, Kommentar, Kategorie, PostKategorie. Beziehungen: Benutzer-schreibt-Post (1:n), Benutzer-schreibt-Kommentar (1:n), Post-hat-Kommentar (1:n), Post-gehört-zu-Kategorie (m:n, über PostKategorien).
3. SQL-Abfragen: Alle Posts eines Benutzers anzeigen; Anzahl der Kommentare pro Post anzeigen; Neuen Blog-Post erstellen und einer Kategorie zuweisen; Neuen Kommentar zu einem Post hinzufügen; Posts anzeigen, die zu einer bestimmten Kategorie gehören.

Fitness-Tracker

Szenario: Verwaltung von Benutzern, Workouts, Übungen und deren Details wie Sätze und Wiederholungen.

Schwierigkeitsgrad: 7/10

Schritte:

1. Schema: Tabellen: `Benutzer` (UserID PK, Name, Geburtsdatum), `Workouts` (WorkoutID PK, UserID FK, WorkoutDatum, DauerInMinuten), `Uebungen` (UebungID PK, Name, Beschreibung), `WorkoutDetails` (DetailID PK, WorkoutID FK, UebungID FK, Saetze, Wiederholungen, Gewicht).
2. ERD: Entitäten: Benutzer, Workout, Übung, WorkoutDetail. Beziehungen: Benutzer-führt-aus-Workout (1:n), Workout-enthält-Übung (m:n, über WorkoutDetail), WorkoutDetail-bezieht-sich-auf-Workout (1:n), WorkoutDetail-bezieht-sich-auf-Übung (1:n).
3. SQL-Abfragen: Alle Workouts eines Benutzers anzeigen; Durchschnittliche Sätze für eine Übung berechnen; Neues Workout mit Übungen hinzufügen; Wiederholungen für eine Übung aktualisieren; Benutzer anzeigen, die mehr als 3 verschiedene Übungen durchgeführt haben.

Abschließende Übungsaufgabe: Eigenes Szenario

Aufgabenstellung: Denke dir ein einfaches Szenario aus deinem täglichen Leben oder einem Hobby aus (z.B. eine persönliche Musiksammlung, Verwaltung von Büchern, die du gelesen hast, eine Einkaufsliste, Verwaltung von Sportgeräten, Aufzeichnung von Reiserouten).

1. Beschreibe Ihr gewähltes Szenario kurz (ähnlich wie die obigen Beispiele).
2. Erstelle ein Datenbank-Schema (Tabellen, Spalten, PKs, FKs) für Ihr Szenario.
3. Entwirf ein ER-Diagramm für Ihr Schema.
4. Schreibe mindestens 5 SQL-Abfragen, die typische Operationen für deine Datenbank abbilden. Denk dir die Beispieldaten selbst aus und wie du diese in die Tabellen einfügen würden.

Hinweise zur Lösung: Beginne mit den Kernentitäten und leite daraus die Beziehungen und Attribute ab. Denke bei den Abfragen an verschiedene Anwendungsfälle, die du mit deiner Datenbank abdecken möchten.

Schwierigkeitsgrad: mittel | Bearbeitungszeit: 60 Minuten