Aufgabe Vorschlag C - 2 - SQL-Anweisungen und Transaktion… · Abitur Hessen Praktische Informatik LK 2021
Thema: SQL-Anweisungen und Transaktionen · Aus der Prüfung LK (2021)
Aufgabenstellung
Entwicklung einer Datenbank für das Impfzentrum
In einer ersten Version der Datenbank möchte das Impfzentrum die Daten der zu impfenden Personen verwalten. Ebenso sollen die Daten der Impfstoffe und ihrer Lagerstandorte, der Produzenten sowie der Impfstofflieferungen verwaltet werden. Dabei gilt:
Eine Person wird zweimal geimpft, wobei zwischen den Impfterminen drei Wochen liegen sollen. Es muss sichergestellt sein, dass eine Person bei beiden Impfungen den gleichen Impfstoff erhält.
Ein Entity-Relationship-Modell (ERM) ist in Material 4 dargestellt.
Material 4: Entity-Relationship-Modell
Hinweise: Ein Impfstoff wird von einem Forschungsinstitut entwickelt (Attribut entwickler). Die Herstellung und Lieferung eines Impfstoffs erfolgt durch Produzenten. Ein Impfstoff gehört zu einer Impfstoffgruppe (Attribut gruppe), wie zum Beispiel mRNA-Impfstoff oder Vektor-Impfstoff. Ein Impfstoff muss bei einer gewissen Temperatur (z.B. -70°C) gelagert werden (Attribut lagerTemperatur). Die verschiedenen Lagerstandorte weisen durch eine entsprechende Kühlung eine jeweils konstante Temperatur (Attribut temperatur) auf.
Material 5: Tabelle Impfprotokoll (Ausschnitt)
Impfling · Risikofaktoren · Impfstoff · Impf-Termin · Impfreaktionen · Verantwortlich
Nr. 123456
Ilka Meier
geb.
12.04.1937 · Diabetes
Übergewicht · BT223a2
(Gruppe mRNA-Impfstoff) · 07.04.21 · keine · Kürzel HEIL
Dr. Silke Heiler
(Allgemeinmedizin)
Nr. 776311
Karl Huber
geb.
08.05.1943 · Bluthochdruck
Übergewicht
Rheuma · BT223a2
(Gruppe mRNA-Impfstoff) · 07.04.21 · Schwindel, Übelkeit
Muskelschmerzen
allergische Reaktion · Kürzel BADE
Kai Bader
(Allgemeinmedizin)
Nr. 270887
Vera Stark
geb.
23.11.1950 · keine · BT223a2
(Gruppe mRNA-Impfstoff) · 07.04.21 · Schmerzen an der Injektionsstelle · Kürzel WIRT
Dr. Lena Wirt
(Innere Medizin)
… · … · … · … · … · …
Nr. 620087
Ina Kraus
geb.
16.09.1948 · keine · Vec23.Cov
(Gruppe Vektor-impfstoff) · 08.04.21 · Fieber
Kopfschmerzen
Appetitlosigkeit
Gelenkschmerzen · Kürzel KLEB
Dr. Kurt Kleber
(Internist)
… · … · … · … · … · …
Nr. 221009
Lars Bauer
geb.
16.06.1954 · Diabetes · BT223a2
(Gruppe mRNA-Impfstoff) · 09.04.21 · keine · Kürzel REIC
Prof. Dr. Til Reich
(Orthopäde)
… · … · … · … · … · …
Nr. 123456
Ilka Meier
geb.
12.04.1937 · Diabetes
Übergewicht · BT223a2
(Gruppe mRNA-Impfstoff) · 29.04.21 · Kopfschmerzen
Gelenkschmerzen · Kürzel WIRT
Dr. Lena Wirt
(Innere Medizin)
Nr. 620087
Ina Kraus
geb.
16.09.1948 · keine · Vec23.Cov
(Gruppe Vektor-Impfstoff) · 29.04.21 · Müdigkeit
Kopfschmerzen · Kürzel KLEB
Dr. Kurt Kleber
(Innere Medizin)
… · … · … · … · … · …
2.2 Einige Daten der Datenbank sollen ergänzt, geändert und ausgewertet werden.
2.2.1
Formulieren Sie eine SQL-Anweisung, die alle Personen mit Personen-ID, Vor- und Nachnamen sowie dem Termin der ersten Impfung auflistet, die bisher nur einmal mit dem Impfstoff mit der Bezeichnung BT223a2 geimpft wurden.
[3 BE]
2.2.2
Implementieren Sie eine SQL-Anweisung, die alle Impfstoffgruppen mit der Anzahl der im 1. Quartal 2021 zum zweiten Mal erfolgten Impfungen, absteigend sortiert nach der Anzahl, ausgibt.
[3 BE]
2.2.3
Der kanadische Impfstoffproduzent PharmaCorn (ID 96) in Vancouver (Postleitzahl BC V6T 1Z3) liefert am 26.04.2021 erstmalig 2500 Dosen des Impfstoffs BT223a2.
Entwickeln Sie die SQL-Anweisungen, um die Datenbank zu ergänzen und die Lagermenge zu aktualisieren.
[6 BE]
Hinweise: Es existieren bereits mehrere Lagerstandorte für den Impfstoff BT223a2. Die 2500 neuen Dosen können nur eingelagert werden, wenn an einem Standort genügend Platz vorhanden ist. Sie können davon ausgehen, dass es mindestens einen Standort gibt, der die gelieferte Menge aufnehmen kann. Der Standort mit der geringsten Anzahl an BT223a2-Impfdosen ist als Lagerstandort zu wählen.
2.2.4
Für die Terminplanung soll eine nach Impfstoffen gruppierte Aufstellung mit der jeweiligen Anzahl an vorrätigen Dosen und der Anzahl der noch für die 2. Impfung benötigten Dosen in der folgenden Form ausgegeben werden:
[4 BE]
Impfstoff
Vorrätige Dosen
Benötigte Dosen
ApolloVac
668
420
BT223a2
488
620
Vec23.Cov
52
102
Entwickeln Sie eine entsprechende SQL-Anweisung.
Lösung
Nach den offiziellen Korrekturhinweisen
2.2.1 Formulieren Sie eine SQL-Anweisung, die alle Personen mit Personen-ID, Vor- und Nachnamen sowie dem Termin der ersten Impfung auflistet, die bisher nur einmal mit dem Impfstoff mit der Bezeichnung BT223a2 geimpft wurden.
Lösung: ```sql SELECT p.personID, p.vname, p.nname, i.termin1 FROM Person p, Impfung i WHERE p.personID = i.personID AND i.bezeichnung = 'BT223a2' AND i.termin1 <= NOW() AND (i.termin2 > NOW() OR i.termin2 IS null); ```
2.2.2 Implementieren Sie eine SQL-Anweisung, die alle Impfstoffgruppen mit der Anzahl der im 1. Quartal 2021 zum zweiten Mal erfolgten Impfungen, absteigend sortiert nach der Anzahl, ausgibt.
Lösung: ```sql SELECT gruppe, COUNT(*) AS Anzahl FROM Impfung JOIN Impfstoff USING (bezeichnung) WHERE termin2 BETWEEN '2021-1-1' AND '2021-3-31' GROUP BY gruppe ORDER BY Anzahl DESC; ```
2.2.3 Entwickeln Sie die SQL-Anweisungen, um die Datenbank zu ergänzen und die Lagermenge zu aktualisieren.
Lösung: ```sql INSERT INTO Produzent VALUES (96, 'PharmaCorn', 'BCV6T1Z3', 'Vancouver', 'Kanada'); INSERT INTO Lieferung VALUES (96, 'BT223a2', 2500, '2021-04-26'); UPDATE Standort SET istAnzahlImpfdosen = istAnzahlImpfdosen + 2500 WHERE ortID = (SELECT ortID FROM Standort WHERE bezeichnung = 'BT223a2' AND kapazitaetImpfdosen - istAnzahlImpfdosen >= 2500 ORDER BY istAnzahlImpfdosen ASC LIMIT 1); ```
2.2.4 Entwickeln Sie eine entsprechende SQL-Anweisung für die nach Impfstoffen gruppierte Aufstellung mit vorrätigen und benötigten Dosen.
Lösung: ```sql SELECT bezeichnung AS Impfstoff, SUM(istAnzahlImpfdosen) AS 'Vorrätige Dosen', (SELECT COUNT(*) FROM impfung AS i WHERE (i.termin2 IS null OR i.termin2 > NOW()) AND Impfstoff = i.bezeichnung GROUP BY i.bezeichnung ) AS 'Benötigte Dosen' FROM Standort GROUP BY bezeichnung; ```
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 Vorschlag B - 2 - SQL-Anweisungen und Transaktion… · LK 2021