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

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

Aufgabenstellung

Aufgabenstellung: Datenbank der Schach-Liga

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 treten die Schulen wiederholt in einzelnen Partien oder auch in ganzen Turnieren gegeneinander an. Die Schülerinnen und Schüler eines Informatikkurses sollen hierfür eine Datenbank entwickeln, die die folgenden Daten verwalten soll:

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.

Der Informatikkurs hat für die Datenbank einen Entwurf erstellt, der aus dem folgenden Entity-Relationship-Diagramm besteht:

Aus dem Entity-Relationship-Diagramm entstand das folgende Datenbankschema (siehe Abbildung 2):

Für das Ergebnis einer Partie gilt folgende Speicherung: Bei einem Sieg von Weiß steht der Wert 1, bei einem Sieg von Schwarz der Wert -1. Endet die Partie unentschieden (ein sogenanntes Patt), wird der Wert 0 abgelegt.

Anlage: Beispieldaten der Datenbank

Tabelle: 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

Tabelle: 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

Tabelle: Spieler

ID · Name · Geburtsdatum · gehoertZu

1 · Armin Gips · 2010-03-14 · 123

2 · Lee Coeur · 2011-02-26 · 123

3 · Heinz Ellmann · 2009-08-05 · 552

4 · Karl Ender · 2006-06-30 · 840

5 · Kai Mauer · 2008-05-23 · 732

6 · Klaus Uhr · 2011-04-27 · 840

7 · Nico Laus · 2009-12-15 · 123

8 · Lisa Bonn · 2009-11-13 · 662

9 · Johannes Brodt · 2007-02-01 · 170

Tabelle: 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)

Aus der Datenbank sollen die folgenden Informationen abgefragt werden:

i.

Ermittelt werden sollen die Namen sämtlicher Spielerinnen und Spieler mit Geburtsjahr 2008 oder 2009.

ii.

Gesucht werden alle Partien, die Weiß verloren hat. Zu jeder dieser Partien sollen der Startzeitpunkt, der Name der Person, die Weiß spielt, sowie der Name von deren Schule ausgegeben werden.

iii.

Ausgegeben werden sollen die Schulnummer und der Name jeder Schule zusammen mit der Anzahl der Spielerinnen und Spieler, die für die jeweilige Schule antreten. Die Ausgabe soll nach dieser Anzahl absteigend geordnet sein.

Teilaufgabe c)

Gesucht sind alle Spielerinnen und Spieler, die bisher noch keine Partie gespielt haben. Zusätzlich sollen zu jeder Person ihre Schule sowie deren Kontaktdaten bestimmt werden. Dafür wird folgende Anfrage an das Datenbanksystem gerichtet:

1 SELECT Spieler.Name, Schule.Name

2 FROM Spieler

3 INNER JOIN Schule

4 ON Spieler.Schulnummer = Schule.Schulnummer

5 WHERE Spieler.ID NOT IN (

6 SELECT Partie.spieltWeissID

7 FROM Partie

8)

9 ORDER BY Schule.Name ASC

c.1

Analysieren Sie die Anfrage, indem Sie sie auf die Beispieldaten in der Anlage anwenden, das Resultat darstellen und die Anfrage erläutern.

c.2

Begründen Sie, warum die SQL-Anweisung die Vorgaben nicht erfüllt.

c.3

Erläutern Sie, auf welche Weise sich die Anfrage ergänzen lässt, damit sie die gewünschte Information liefert.

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

Lösung

Nach den offiziellen Korrekturhinweisen

b) i. Ermittelt werden sollen die Namen sämtlicher Spielerinnen und Spieler mit Geburtsjahr 2008 oder 2009. Entwerfen Sie eine SQL-Anweisung.

Lösung: ```sql SELECT Name FROM Spieler WHERE Geburtsdatum >= '2008-01-01' AND Geburtsdatum <= '2009-12-31' ```

b) ii. Gesucht werden alle Partien, die Weiß verloren hat. Zu jeder dieser Partien sollen der Startzeitpunkt, der Name der Person, die Weiß spielt, sowie der Name von deren Schule ausgegeben werden. Entwerfen Sie eine SQL-Anweisung.

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

b) iii. Ausgegeben werden sollen die Schulnummer und der Name jeder Schule zusammen mit der Anzahl der Spielerinnen und Spieler, die für die jeweilige Schule antreten. Die Ausgabe soll nach dieser Anzahl absteigend geordnet sein. Entwerfen Sie eine SQL-Anweisung.

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

c) 1. Analysieren Sie die Anfrage, indem Sie sie auf die Beispieldaten in der Anlage anwenden, das Resultat darstellen und die 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 | Die Unteranfrage von Zeile 6 bis 7 liefert die IDs aller Spielerinnen und Spieler, die eine Partie mit der Farbe Weiß gespielt haben. Die gesamte Anfrage verknüpft die Relationen Spieler und Schule jeweils mit einem INNER JOIN über ihre jeweiligen Primärschlüssel. Dabei werden nur die Datensätze selektiert, bei denen die ID der Spielerinnen und Spieler in der Unterabfrage nicht enthalten sind. Projiziert wird das Ergebnis auf die Namen der Spielerinnen und Spieler und den Namen der Schule. Das Ergebnis wird aufsteigend nach Namen der Schule sortiert.

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

Lösung: Die gewonnenen Informationen sind aus folgenden Gründen nicht ausreichend: 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 sich die Anfrage ergänzen lässt, damit sie die gewünschte Information liefert.

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 · GK · Haupttermin 2022 · Aufgabe 3 - SQL-Abfragen · LK · Haupttermin 2022 ›

Mehr Aufgaben zu SQL-Abfragen