Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik LK · Nachtermin 2022

Thema: SQL-Abfragen · Aus der Prüfung LK · Nachtermin (2022)

Aufgabenstellung

Aufgabenstellung:

Die Schach-AG einer Schule plant, gemeinsam mit den Schach-AGs weiterer Schulen eine Schach-Liga zu gründen, in der dauerhaft Wettkämpfe stattfinden. Dabei sollen die Schulen wiederholt einzelne Partien oder ganze Turniere gegeneinander austragen. Hierfür sollen die Schülerinnen und Schüler eines Informatikkurses eine Datenbank erstellen. Diese soll die folgenden Daten verwalten:

Für die teilnehmenden Schulen sollen der Name, eine eindeutige Nummer (die sogenannte Schulnummer) und die Adresse verwaltet werden. Damit die Lehrenden der verschiedenen Schulen untereinander Kontakt aufnehmen können, sollen außerdem folgende Daten von der Lehrerin oder dem Lehrer der Schach-AG gespeichert werden: der Name, das Geburtsdatum, die Telefonnummer und die E-Mail-Adresse.

Von den Spielerinnen und Spielern werden der Name und die zugehörige Schule verwaltet. Um feststellen zu können, welche Spielerinnen und Spieler im gleichen Jahr geboren sind, wird außerdem das Geburtsdatum gespeichert. Dadurch können die Spielerinnen und Spieler in Altersgruppen eingeteilt werden.

Bei einer Partie spielen zwei Spielerinnen bzw. Spieler gegeneinander. Beide können aus der gleichen Schule oder von verschiedenen Schulen stammen. Dabei werden der Zeitpunkt des Beginns der Partie und das Ergebnis gespeichert. Außerdem wird gespeichert, wer die Farbe Weiß und wer die Farbe Schwarz hat.

Als Entwurf für die Datenbank hat der Informatikkurs zunächst das folgende Entity-Relationship-Diagramm erstellt:

Aus dem Entity-Relationship-Diagramm wurde anschließend das folgende Datenbankschema abgeleitet (siehe Abbildung 2).

Die Ergebnisse der Partien werden wie folgt gespeichert: Hat Weiß gewonnen, so wird der Wert 1 gespeichert. Hat Schwarz gewonnen, so wird der Wert -1 gespeichert. Geht die Partie unentschieden aus (ein sogenanntes Patt), so wird der Wert 0 gespeichert.

Anlage:

Lehrer EMailAdresse · Name · Geburtsdatum · Telefonnummer

allen@info.nrw · Frances E. Allen · 1971-08-04 · 0123-4567

blanch@info.nrw · Gertrude Blanch · 1972-02-02 · 01233-5678

lovelace@info.nrw · Ada Lovelace · 1973-12-10 · 01232-68104

chen@info.nrw · Peter Chen · 1977-01-03 · 01131-5316161

bayer@info.nrw · Rudolf Bayer · 1977-03-03 · 01121-6695314

dahl@info.nrw · Ole-Johann Dahl · 1977-10-12 · 01911-718212

Schule Schulnummer · Name · Adresse · LehrerEMailAdresse

123 · Gamma-Gesamtschule · Baumallee 13, 10007 Krähwinkel · bayer@info.nrw

170 · Huffman-Hauptschule · Holzweg 5, 10006 Jammertal · blanch@info.nrw

552 · Gödel-Gymnasium · An de Dyk 13, 1005 Nergenshuizen · dahl@info.nrw

662 · Knuth-Kolleg · Hintergasse 3, 10001 Vordertupfingen · chen@info.nrw

732 · Shannon-Schule · Heimweg 3, 10004 Posemuckel · allen@info.nrw

840 · Knuth-Kolleg · Vorderer Weg 1, 10000 Hintertupfingen · lovelace@info.nrw

Spieler ID · Name · Geburtsdatum

1 · Armin Gips · 2010-03-14

2 · Lee Coeur · 2011-02-26

3 · Heinz Ellmann · 2009-08-05

4 · Karl Ender · 2006-06-30

5 · Kai Mauer · 2008-05-23

6 · Klaus Uhr · 2011-04-27

7 · Nico Laus · 2009-12-15

8 · Lisa Bonn · 2009-11-13

9 · Johannes Brodt · 2007-02-01

gehoertZu SpielerID · Schulnummer

1 · 123

2 · 123

3 · 552

4 · 840

5 · 732

6 · 840

7 · 123

8 · 662

9 · 170

Partie Startzeitpunkt · spieltWeissID · spieltSchwarzID · Ergebnis

2022-04-07 12:00 · 1 · 2 · -1

2022-04-07 12:45 · 2 · 1 · -1

2022-04-07 13:30 · 1 · 2 · -1

2022-04-07 12:00 · 3 · 5 · 0

2022-04-08 08:10 · 3 · 7 · 1

2022-04-26 09:55 · 7 · 3 · 1

2022-04-27 11:40 · 9 · 6 · 1

2022-04-28 13:50 · 7 · 6 · -1

2022-04-29 08:10 · 3 · 2 · 1

Hinweis: Ist das Ergebnis 1, so hat Weiß gewonnen. Ist das Ergebnis -1, so hat Schwarz gewonnen. Ist das Ergebnis 0, so gab es ein Patt (ein Unentschieden).

Teilaufgabe b)

Mithilfe der Datenbank sollen die folgenden Informationen abgefragt werden:

(i)

Ermittelt werden sollen alle Personen, die eine Partie mit den weißen Figuren verloren haben. Auszugeben sind der Name der Person sowie der Name ihrer Schule.

(ii)

Ermittelt werden sollen die Schulnummer und der Name jeder Schule sowie die Anzahl der Spielerinnen und Spieler, die für die jeweilige Schule antreten. Die Ausgabe soll nach dieser Anzahl absteigend sortiert sein.

Teilaufgabe c)

Gesucht werden alle Spielerinnen und Spieler, die bisher noch an keiner Partie beteiligt waren. Zusätzlich sollen zu jeder Person die zugehörige Schule samt deren Kontaktdaten bestimmt werden. Dazu wird die folgende Anfrage an das Datenbanksystem gerichtet:

1 SELECT Spieler.Name, Schule.Name

2 FROM Spieler

3 INNER JOIN gehoertZu

4 ON Spieler.ID = gehoertZu.SpielerID

5 INNER JOIN Schule

6 ON gehoertZu.Schulnummer = Schule.Schulnummer

7 WHERE Spieler.ID NOT IN (

8 SELECT Partie.spieltWeissID

9 FROM Partie

10)

11 ORDER BY Schule.Name ASC, Spieler.Name ASC

1.

Analysieren Sie die Anfrage, indem Sie diese auf die Beispieldaten in der Anlage anwenden, das entstehende Ergebnis darstellen und die Wirkungsweise der Anfrage erläutern.

2.

Begründen Sie, weshalb die SQL-Anweisung die Anforderungen nicht erfüllt.

3.

Erläutern Sie, auf welche Weise die Anfrage ergänzt werden kann, damit die gewünschte Information geliefert wird.

Aufgabenstellung: Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik LK · Nachtermin 2022

Lösung

Nach den offiziellen Korrekturhinweisen

b) (i) Ermittelt werden sollen alle Personen, die eine Partie mit den weißen Figuren verloren haben. Auszugeben sind der Name der Person sowie der Name ihrer Schule. Entwerfen Sie eine SQL-Anweisung.

Lösung: ```sql SELECT Spieler.Name, gehoertZu.Schulnummer FROM Spieler INNER JOIN Partie ON Partie.spieltWeissID = Spieler.ID INNER JOIN gehoertZu ON gehoertZu.SpielerID = Spieler.ID WHERE Partie.Ergebnis = -1 ```

b) (ii) Ermittelt werden sollen die Schulnummer und der Name jeder Schule sowie die Anzahl der Spielerinnen und Spieler, die für die jeweilige Schule antreten. Die Ausgabe soll nach dieser Anzahl absteigend sortiert sein. Entwerfen Sie eine SQL-Anweisung.

Lösung: ```sql SELECT Schule.Schulnummer, Schule.Name, COUNT(Spieler.ID) AS Anzahl FROM Schule INNER JOIN gehoertZu ON Schule.Schulnummer = gehoertZu.Schulnummer INNER JOIN Spieler ON gehoertZu.SpielerID = Spieler.ID GROUP BY Schule.Schulnummer ORDER BY Anzahl DESC ```

c) 1. Analysieren Sie die Anfrage, indem Sie diese auf die Beispieldaten in der Anlage anwenden, das entstehende Ergebnis darstellen und die Wirkungsweise der Anfrage erläutern.

Lösung: | Spieler.Name | Schule.Name | |---|---| | Karl Ender | Knuth-Kolleg | | Klaus Uhr | Knuth-Kolleg | | Lisa Bonn | Knuth-Kolleg | | Kai Mauer | Shannon-Schule |

c) 2. Begründen Sie, weshalb die SQL-Anweisung die Anforderungen nicht erfüllt.

Lösung: 1. Da die Kontaktdaten der Schule fehlen und mehrere Schulen den gleichen Namen haben können, ist die Zuordnung der Schule zu den Spielerinnen und Spielern nicht eindeutig. 2. Es werden nicht nur die Spielerinnen und Spieler ermittelt, die noch keine Partie gespielt haben, sondern die Spielerinnen und Spieler, die noch keine Partie mit der Farbe Weiß gespielt haben. Damit werden mehr Personen ermittelt, als eigentlich gefordert ist.

c) 3. Erläutern Sie, auf welche Weise die Anfrage ergänzt werden kann, damit die gewünschte Information geliefert wird.

Lösung: Eine erweiterte Projektion ist der erste Schritt für die Verbesserung der ermittelten Daten: Hier sollten entweder die Schulnummer oder die Anschrift der Schule sowie die Kontakt-E-Mail-Adresse oder die Kontakt-Telefonnummer ergänzt werden. Um die Anfrage weiter zu verbessern, sollte die Unterabfrage angepasst werden: Hier müssen die Spielerinnen und Spieler ausgewählt werden, die mindestens ein Spiel mit der Farbe Weiß oder ein Spiel mit der Farbe Schwarz gespielt haben. Dies kann man durch zwei Abfragen erreichen, die mit UNION verknüpft werden.

Die ausführliche Erklärung Schritt für Schritt (mit KI) gibt es mit Premium. Premium

Löse sie direkt hier: automatische Korrektur, Hinweise und Lösung Schritt für Schritt.

‹ Aufgabe 3 - SQL-Abfragen · LK · Haupttermin 2022 · Aufgabe 3 - SQL-Abfragen · GK · Haupttermin 2021 ›

Mehr Aufgaben zu SQL-Abfragen