Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik GK · Haupttermin · Weiterbildungskolleg 2021

Aufgabenstellung: Einkaufslisten-App

Ein Unternehmen plant die Entwicklung einer App, mit der mehrere Einkaufslisten angelegt und verwaltet werden können.

Wer die App nutzen will, muss sich mit dem eigenen Namen, einem eindeutigen Benutzernamen und einem Passwort registrieren. Dabei ist die Zustimmung erforderlich, dass die angegebenen Daten zur Erbringung aller Dienste der App gespeichert und verarbeitet werden dürfen.

Damit das Anlegen einer Einkaufsliste einfacher wird, sind im System schon zahlreiche Produkte hinterlegt, die ausgewählt werden können. Selbstverständlich dürfen auch eigene neue Produkte ergänzt werden.

Das Unternehmen speichert seine Daten in einer relationalen Datenbank. Ein Teil der Modellierung dieser Datenbank wird nachfolgend gezeigt.

Aus der Modellierung ergibt sich das nachstehende Datenbankschema. Im Anhang stehen Beispieldatensätze zu diesem Schema.

Auf den Endgeräten der Nutzenden soll eine Software laufen, die die Daten der angemeldeten Person aus einer Datenbank lädt und sie für den Einkauf im Geschäft passend anzeigt. Dafür liegt das folgende Teilmodell vor:

Anlage: Beispieldaten

Einkaufsliste

ID

Titel

Benutzername

14 · Salatliste · Linus

15 · Biomarkt · FabBen

16 · Hamstern · FabBen

17 · Einkaufen · Linus

18 · Geburtstagsfeier · FabBen

19 · Biomarkt · JaStIn

Benutzer

Benutzername

Vorname

Nachname

Passwort 1

FabBen · Ben · Lovelace · ab4bd73e2123ddef3

JaStIn · Jana · Strehl · 3e2123ddef3ab4bd7

Edelweiss · Julian · Beton · ab4bd7ab4bd7eefde

Pudelski · Laura · Pudelski · bd7ab4bd7eef00e0e

Linus · Knut · Bremhorst · ef0023ddef3ab47ea

1 Die Passwörter werden nicht im Klartext, sondern verschlüsselt in der Datenbank gespeichert.

Produkt

Bezeichnung

Kategorie

Schafskäse · Kühlprodukte

Gouda · Kühlprodukte

Hefe · Kühlprodukte

Brot · Backwaren

Baguette · Backwaren

Paprika · Obst/Gemüse

Gurke · Obst/Gemüse

Salat · Obst/Gemüse

Salamipizza · Tiefkühlprodukte

Erbsen · Tiefkühlprodukte

Schokolade · Süßspeisen

Bonbons · Süßspeisen

Limonade · Getränke

Klopapier · Hygieneartikel

Biomango · Obst/Gemüse

stehtAuf

ProduktBezeichnung

EinkaufslisteID

Menge

Schafskäse · 14 · 2

Schokolade · 15 · 3

Salat · 14 · 1

Paprika · 14 · 1

Limonade · 18 · 24

Bonbons · 18 · 3

Brot · 16 · 5

Gouda · 15 · 1

Brot · 18 · 3

Gurke · 17 · 2

Paprika · 17 · 1

Baguette · 18 · 6

Salamipizza · 17 · 3

Schafskäse · 15 · 2

Schokolade · 18 · 1

Erbsen · 18 · 1

Schokolade · 17 · 2

Hefe · 17 · 1

Hefe · 16 · 12

Klopapier · 16 · 25

Anhang: Dokumentationen der Klassen

Die Klasse EinkaufslistenApp

Ein Objekt der Klasse EinkaufslistenApp verwaltet eine aus der passenden Datenbank geladene Einkaufsliste.

Ausschnitt aus der Dokumentation der Klasse EinkaufslistenApp

EinkaufslistenApp(String pBenutzername, String pPasswort)

Ein neues Objekt der Klasse wird mit den Parametern pBenutzername und pPasswort initialisiert.

int[] ladeListenIDs()

Die Methode lädt die IDs der Einkaufslisten aus der Datenbank, die der angemeldeten Person zugeordnet sind, und gibt sie zurück.

void ladeListe(int pID)

Die Methode lädt die Einträge der mit der ID identifizierten Einkaufsliste aus der Datenbank und speichert sie. Existiert die ID nicht oder ist keine Benutzerin bzw. kein Benutzer angemeldet, so wird keine Einkaufsliste gespeichert.

int gibAktuelleID()

Die Methode liefert die ID zu der aktuell geladenen Einkaufsliste. Ist keine Einkaufsliste geladen, wird -1 zurückgegeben.

List<Eintrag> gibAktuelleEintraege()

Die Methode liefert die Einträge der aktuellen Einkaufsliste in einem Objekt der Klasse Liste<Eintrag>. Ist keine aktuelle Einkaufsliste geladen, so wird null zurückgegeben.

Die Klasse Eintrag

Ein Objekt der Klasse Eintrag verwaltet ein aus der passenden Datenbank geladenes Produkt sowie die für die zugehörige Einkaufsliste gespeicherte Menge.

Ausschnitt aus der Dokumentation der Klasse Eintrag

Eintrag(Produkt pProdukt, int pMenge)

Ein neues Objekt der Klasse wird mit den Parametern pProdukt und pMenge initialisiert.

void setzeErledigt(boolean pErledigt)

Die Methode speichert, ob der Eintrag erledigt ist.

boolean istErledigt()

Die Methode liefert, ob der Eintrag erledigt ist.

int gibMenge()

Die Methode liefert die gespeicherte Menge des Eintrags.

Produkt gibProdukt()

Die Methode liefert das Produkt zu dem Eintrag.

Die Klasse Produkt

Ein Objekt der Klasse Produkt repräsentiert ein aus der passenden Datenbank geladenes Produkt.

Ausschnitt aus der Dokumentation der Klasse Produkt

Produkt(String pBezeichnung, String pKategorie)

Ein neues Objekt der Klasse wird mit den Parametern pBezeichnung und pKategorie initialisiert.

String gibBezeichnung()

Die Methode liefert die gespeicherte Bezeichnung des Produkts.

String gibKategorie()

Die Methode liefert die Kategorie des Produkts.

Die generische Klasse List

Objekte der generischen Klasse List verwalten beliebig viele, linear angeordnete Objekte vom Typ ContentType. Auf höchstens ein Listenobjekt, aktuelles Objekt genannt, kann jeweils zugegriffen werden. Wenn eine Liste leer ist, vollständig durchlaufen wurde oder das aktuelle Objekt am Ende der Liste gelöscht wurde, gibt es kein aktuelles Objekt. Das erste oder das letzte Objekt einer Liste kann durch einen Auftrag zum aktuellen Objekt gemacht werden. Außerdem kann das dem aktuellen Objekt folgende Listenobjekt zum neuen aktuellen Objekt werden.

Das aktuelle Objekt kann gelesen, verändert oder gelöscht werden. Außerdem kann vor dem aktuellen Objekt ein Listenobjekt eingefügt werden.

Dokumentation der Klasse List<ContentType>

List()

Eine leere Liste wird erzeugt.

boolean isEmpty()

Die Anfrage liefert den Wert true, wenn die Liste keine Objekte enthält, sonst liefert sie den Wert false.

boolean hasAccess()

Die Anfrage liefert den Wert true, wenn es ein aktuelles Objekt gibt, sonst liefert sie den Wert false.

void next()

Falls die Liste nicht leer ist, es ein aktuelles Objekt gibt und dieses nicht das letzte Objekt der Liste ist, wird das dem aktuellen Objekt in der Liste folgende Objekt zum aktuellen Objekt, andernfalls gibt es nach Ausführung des Auftrags kein aktuelles Objekt, d. h., hasAccess() liefert den Wert false.

void toFirst()

Falls die Liste nicht leer ist, wird das erste Objekt der Liste aktuelles Objekt. Ist die Liste leer, geschieht nichts.

void toLast()

Falls die Liste nicht leer ist, wird das letzte Objekt der Liste aktuelles Objekt. Ist die Liste leer, geschieht nichts.

ContentType getContent()

Falls es ein aktuelles Objekt gibt (hasAccess() == true), wird das aktuelle Objekt zurückgegeben. Andernfalls (hasAccess() == false) gibt die Anfrage den Wert null zurück.

void setContent(ContentType pContent)

Falls es ein aktuelles Objekt gibt (hasAccess() == true) und pContent ungleich null ist, wird das aktuelle Objekt durch pContent ersetzt. Sonst bleibt die Liste unverändert.

void append(ContentType pContent)

Ein neues Objekt pContent wird am Ende der Liste eingefügt. Das aktuelle Objekt bleibt unverändert. Wenn die Liste leer ist, wird das Objekt pContent in die Liste eingefügt und es gibt weiterhin kein aktuelles Objekt (hasAccess() == false). Falls pContent gleich null ist, bleibt die Liste unverändert.

void insert(ContentType pContent)

Falls es ein aktuelles Objekt gibt (hasAccess() == true), wird ein neues Objekt pContent vor dem aktuellen Objekt in die Liste eingefügt. Das aktuelle Objekt bleibt unverändert. Falls die Liste leer ist und es somit kein aktuelles Objekt gibt (hasAccess() == false), wird pContent in die Liste eingefügt und es gibt weiterhin kein aktuelles Objekt. Falls es kein aktuelles Objekt gibt (hasAccess() == false) und die Liste nicht leer ist oder pContent == null ist, bleibt die Liste unverändert.

void concat(List<ContentType> pList)

Die Liste pList wird an die Liste angehängt. Anschließend wird pList eine leere Liste. Das aktuelle Objekt bleibt unverändert. Falls es sich bei der Liste und pList um dasselbe Objekt handelt, pList == null oder eine leere Liste ist, bleibt die Liste unverändert.

void remove()

Falls es ein aktuelles Objekt gibt (hasAccess() == true), wird das aktuelle Objekt gelöscht und das Objekt hinter dem gelöschten Objekt wird zum aktuellen Objekt. Wird das Objekt, das am Ende der Liste steht, gelöscht, gibt es kein aktuelles Objekt mehr (hasAccess() == false). Wenn die Liste leer ist oder es kein aktuelles Objekt gibt (hasAccess() == false), bleibt die Liste unverändert.

Die Klasse DatabaseConnector

Ein Objekt der Klasse DatabaseConnector ermöglicht die Abfrage und Manipulation einer relationalen Datenbank. Beim Erzeugen des Objekts wird eine Datenbankverbindung aufgebaut, sodass anschließend SQL-Anweisungen an diese Datenbank gerichtet werden können.

Dokumentation der Klasse DatabaseConnector

DatabaseConnector(String pIP, int pPort, String pDatabase, String pUsername, String pPassword)

Ein Objekt vom Typ DatabaseConnector wird erstellt, und eine Verbindung zur Datenbank wird aufgebaut. Mit den Parametern pIP und pPort werden die IP-Adresse und die Port-Nummer übergeben, unter denen die Datenbank mit Namen pDatabase zu erreichen ist. Mit den Parametern pUsername und pPassword werden Benutzername und Passwort für die Datenbank übergeben.

void executeStatement(String pSQLStatement)

Der Auftrag schickt den im Parameter pSQLStatement enthaltenen SQL-Befehl an die Datenbank ab. Handelt es sich bei pSQLStatement um einen SQL-Befehl, der eine Ergebnismenge liefert, so kann dieses Ergebnis anschließend mit der Methode getCurrentQueryResult abgerufen werden.

QueryResult getCurrentQueryResult()

Die Anfrage liefert das Ergebnis des letzten mit der Methode executeStatement an die Datenbank geschickten SQL-Befehls als Objekt vom Typ QueryResult zurück. Wurde bisher kein SQL-Befehl abgeschickt oder ergab der letzte Aufruf von executeStatement keine Ergebnismenge (z. B. bei einem INSERT-Befehl oder einem Syntaxfehler), so wird null geliefert.

String getErrorMessage()

Die Anfrage liefert null oder eine Fehlermeldung, die sich jeweils auf die letzte zuvor ausgeführte Datenbankoperation bezieht.

void close()

Die Datenbankverbindung wird geschlossen.

Die Klasse QueryResult

Ein Objekt der Klasse QueryResult stellt die Ergebnistabelle einer Datenbankanfrage mit Hilfe der Klasse DatabaseConnector dar. Objekte dieser Klasse werden nur von der Klasse DatabaseConnector erstellt. Die Klasse verfügt über keinen öffentlichen Konstruktor.

Dokumentation der Klasse QueryResult

String[][] getData()

Die Anfrage liefert die Einträge der Ergebnistabelle als zweidimensionales Feld vom Typ String. Der erste Index des Feldes stellt die Zeile und der zweite die Spalte dar (d. h. String[zeile][spalte]).

String[] getColumnNames()

Die Anfrage liefert die Bezeichner der Spalten der Ergebnistabelle als Feld vom Typ String zurück.

String[] getColumnTypes()

Die Anfrage liefert die Typenbezeichnung der Spalten der Ergebnistabelle als Feld vom Typ String zurück. Die Bezeichnungen entsprechen den Angaben in der Datenbank.

int getRowCount()

Die Anfrage liefert die Anzahl der Zeilen der Ergebnistabelle als int.

int getColumnCount()

Die Anfrage liefert die Anzahl der Spalten der Ergebnistabelle als int.

Teilaufgabe b)

Aus der Datenbank, die nach dem Schema in Abbildung 3 aufgebaut ist, sollen die nachfolgenden Informationen abgefragt werden.

Entwerfen Sie zu jeder der genannten Anfragen eine SQL-Anweisung.

i.

Gesucht sind die Titel sämtlicher Einkaufslisten, die der Person mit dem Benutzernamen FabBen gehören.

ii.

Gesucht sind Bezeichnung und Kategorie derjenigen Produkte, die auf der Einkaufsliste mit der ID 18 aufgeführt sind. Die Ausgabe soll zuerst nach der Kategorie absteigend und anschließend nach der Bezeichnung aufsteigend geordnet sein.

iii.

Für den Benutzer mit dem Benutzernamen Linus sollen die Kategorien bestimmt werden, aus denen Produkte auf seinen Listen stehen. Zusätzlich ist für jede dieser Kategorien die Gesamtsumme der Mengenangaben zu berechnen.

Teilaufgabe c)

Betrachtet wird die folgende SQL-Anweisung:

1 SELECT Einkaufsliste.Benutzername

2 FROM Einkaufsliste

3 WHERE Einkaufsliste.Benutzername NOT IN (

4 SELECT Einkaufsliste.Benutzername

5 FROM Einkaufsliste

6 WHERE Einkaufsliste.ID IN (

7 SELECT stehtAuf.EinkaufslisteID

8 FROM stehtAuf

9 WHERE stehtAuf.ProduktBezeichnung = 'Schokolade'

10)

11)

c.1

Analysieren Sie die Unteranfragen in Zeile 7 bis 9 sowie in Zeile 4 bis 10, indem Sie diese auf die Beispieldaten (siehe Anlage) anwenden und das jeweilige Ergebnis angeben.

c.2

Ermitteln Sie mithilfe der Beispieldaten, welches Ergebnis die vollständige Anfrage liefert.

c.3

Erläutern Sie, welche Informationen die gesamte Anfrage im Sachkontext liefert.

c.4

Beurteilen Sie das Ergebnis der SQL-Anfrage im Sachkontext hinsichtlich des Benutzers mit dem Benutzernamen Edelweiss.