Aufgabe 1: ER-Diagramme (ca. 15% der Klausurpunkte)

Erstellen Sie ein ER-Diagramm für den Minigolfbetreiber "Bahn frei". Dieser möchte zur Auswertung und Analyse der Bahnergebnisse die Statistiken über von den Gästen gesammelten Daten in einer Datenbank erfassen. Verwenden Sie die Chen-Notation.

Jeder Gast erhält eine Mitgliedskarte, auf welcher eine Gastnummer, der Name und das Geburtsdatum steht. Die Gastnummer ist eine automatisch generierte laufende Nummer und sets eindeutig.

Es gibt 18 Bahnen, sie sind von 1 bis 18 durchnummeriert.

Mehrere Gäste können zusammen eine Partie Minigolf spielen. Zu einem solchen Spiel soll das Datum und eine eindeutige Spiel-ID gespeichert werden. Des Weiteren ist zu speichern, wie viele Schläge ein Gast in einem bestimmten Spiel benötigt hat.

Es gibt eine spezielle Form von Spielen, nämlich Wettkampf-Spiele. Diese sind Teil eines Wettkampfs. Ein Wettkampf hat ein Datum und eine Bezeichnung. Es ist möglich, dass an ein und dem selben Tag mehrere Wettkämpfe stattfinden, diese haben dann aber jeweils eine andere Bezeichnung, z. B. "Ü60". Zu einem Wettkampf speichern wir noch die Startgebühr, die von jedem Spieler zu zahlen ist, sowie eine Menge von Preisen, welche jeweils aus einer Platzierung und einem Gewinn bestehen. Beispielsweise kann es für den ersten Platz 100 EUR geben, für den zweiten Platz 50 EUR, etc.

Aufgabe 2: Normalisierung (10%)

1. Bringen Sie die folgende Tabelle "Buch" in die erste Normalform und stellen Sie das Ergebnis als Tabelle mit den hier gezeigten Daten dar.

   ISBN	Titel	Autoren
   123456789X	Das Fahrrad	Karin Biker, Bob Biker

   
   
2. Überführen Sie die folgende Tabelle "Arzttermine" zunächst in die zweite, dann in die dritte Normalform:

   Arzttermine(Datum, Uhrzeit, Arzt, Patient, Raum, Geburtsdatum)

   Es gelten folgende funktionale Abhängigkeiten:

   • Arzt → Raum
   • Patient → Geburtsdatum

Aufgabe 3: Transformation ER → Relationenschema (15%)

1. Überführen Sie das folgende ER-Diagramm in ein Relationenschema. Kennzeichnen Sie Primärschlüssel und Fremdschlüssel.
   [[ { _e: "LKW", pos: [150, 100], attributes: [ { _a:"Nummernschild", options:["primary"], pos: [89, 13] }, { _a:"Fahrer", pos: [245, 13] } ] }, { _e: "Produkt", pos: [520, 100], attributes: [ { _a:"Produktnummer", pos: [465, 25], options:["primary"] }, { _a:"Bezeichnung", pos: [580, 25]} ] }, { _e: "Filiale", pos: [520, 277], attributes: [ { _a:"Ort", pos: [465, 200], options:["primary"] }, { _a:"Strasse", pos: [580, 200]} ] }, { _e: "Lieferung", pos: [150, 277], options:["weak"], attributes: [ { _a:"Abfahrt", pos: [89, 350], options:["extending_primary"] } ] } ], [ { _r: "fährt", options:["weak"], _e: ["LKW","Lieferung"], card: ["1", "N"], }, { _r: "beliefert", _e: ["Lieferung","Filiale"], card: ["M", "N"], }, { _r: "beinhaltet", _e: ["Lieferung", "Produkt"], card: ["N", "M"], attributes: [{_a:"Menge", pos: [360 , 110]}] } ]]
2. Zeichnen Sie "LKW", "fährt" und "Lieferung" erneut und verwenden Sie nun die Krähenfuß-Notation. Beachten Sie dabei, dass es auch LKWs geben darf, die noch nie eine Lieferung gefahren haben.
3. Schreiben Sie ein CREATE TABLE-Kommando, um Ihre Relation "Lieferung" zu erstellen. Gehen Sie davon aus, dass die anderen Tabellen bereits existieren. Das Attribut "Abfahrt" soll das Datum und die Uhrzeit der Abfahrt speichern. Wählen Sie sinnvolle Datentypen, definieren Sie Primär- und Fremdschlüssel.

Aufgabe 4: Relationale Algebra (8%)

Gegeben ist folgendes Schema, formulieren Sie unten stehende Anfragen mithilfe von Ausdrücken der relationalen Algebra:

Kunde (Kundennr, Name, EMail)
Konzert (Konzertnr, Datum, Location, Band, Eintrittspreis)
Buchung (Kundennr, Konzertnr, Anzahl_Tickets, Buchungsdatum)

1. Am welchem Datum spielt die Band "Bongo Group"?
2. Wie heißen die Kunden, die das Konzert mit der Konzertnummer 517 gebucht haben?
3. Welche Kunden haben schon einmal eine Buchung für ein Konzert in letzter Minute - also am Tag des Konzerts - aufgegeben?

Aufgabe 5: SQL (20%)

Gegeben ist folgendes Schema, formulieren Sie unten stehende Anfragen mithilfe von SQL:

Kunde (Kundennr, Name, EMail)
Konzert (Konzertnr, Datum, Location, Band, Eintrittspreis)
Buchung (Kundennr, Konzertnr, Anzahl_Tickets, Buchungsdatum)

1. Führen Sie folgende Buchung aus: Kunde 111 möchte heute zwei Tickets für Konzert 517 buchen.
2. Ändern Sie die E-Mail-Adresse von Kunde 111 in "julia@example.com"
3. Löschen Sie alle Buchungen, bei denen die Anzahl 0 angegeben wurde.
4. Welche Kunden (alle Spalten anzeigen) haben eine E-Mail-Adresse, die auf @example.com endet?
5. Für welche Bands hat der Kunde mit der Kundennummer 111 Tickets gebucht? Geben Sie keine Band doppelt aus.
6. Geben Sie für jedes Konzert die Konzertnr und die Anzahl der gebuchten Tickets aus. Achten Sie darauf, dass auch Konzerte angezeigt werden, für die noch nie eine Buchung aufgegeben wurden.
7. Wie viel Geld hat der Kunde mit der Kundennummer 111 insgesamt für Tickets ausgegeben?
8. Welche Kunden (Kundennr) haben noch nie eine Buchung aufgegeben?
9. Welche Bands (jede nur einmal ausgeben) spielen an ein und dem selben Tag zwei oder mehr Konzerte?