Aufgabe Vorschlag A - 2 - SQL-Anweisungen und Transaktion… · Abitur Hessen Praktische Informatik LK 2021
Thema: SQL-Anweisungen und Transaktionen · Aus der Prüfung LK (2021)
Aufgabenstellung
2 Entwicklung einer Datenbank für den Lieferservice
Ein Entity-Relationship-Modell (ERM) der Datenbank ist in Material 7 zu finden.
Material 7
Entity-Relationship-Modell
Hinweise:
Ein Fahrer kann tageweise für unterschiedliche Filialen des Restaurants tätig sein.
Die Attribute start, ausgeliefert und ende des Entitätstyps Lieferauftrag haben den Typ DATETIME.
Ein E-Motorroller hat, wie ein Moped, ein jährlich zu erneuerndes Versicherungskennzeichen. Ein Kleinwagen hat ein amtliches Kennzeichen.
2.2 Die Datenbank soll erweitert und die Daten sollen ausgewertet und ergänzt werden.
2.2.1
Formulieren Sie eine SQL-Anweisung, die die Bestellnummern und die Lieferdauer (Zeit zwischen Start und Auslieferung) aller Lieferaufträge ermittelt, deren Lieferdauer mehr als 45 Minuten beträgt. [3 BE]
Hinweise: Die SQL-Funktion TIMEDIFF(time1, time2) liefert die Differenz time1 - time2 als Wert des Typs TIME. Die SQL-Funktion MINUTE(time) ermittelt die Minuten eines TIME-Wertes, die Funktion HOUR(time) die Stunden.
2.2.2
Das Kennzeichen des Kleinwagens sowie Vor- und Nachname des Fahrers für die Auslieferung der Bestellung mit der Nummer 4711 sollen ermittelt werden.
Geben Sie die entsprechende SQL-Anweisung an. [3 BE]
2.2.3
Implementieren Sie eine SQL-Anweisung, welche die Vor- und Nachnamen aller Fahrer mit der Anzahl ihrer durchgeführten Lieferaufträge auflistet. [3 BE]
Hinweis: Alle Fahrer sollen in der Liste erscheinen, auch solche, die noch keinen Lieferauftrag durchgeführt haben. Die Fahrer-Liste ist nach der Anzahl der Lieferaufträge absteigend zu sortieren.
2.2.4
Eine Tabelle Geschäftsführer soll in der Datenbank nach folgendem Modell ergänzt werden:
Der Fremdschlüssel zur Implementierung der Beziehung soll in der bestehenden Tabelle Filiale ergänzt werden. Frau Mira Bellenbaum ist Geschäftsführerin der Filiale Nr. 3 in der Bahnhofstr. 3 in 37213 Witzenhausen.
Entwickeln Sie die SQL-Anweisungen, um die Tabelle Geschäftsführer zu erstellen und die Tabelle Filiale, um den Fremdschlüssel personalnr zu erweitern sowie die Filiale in Witzenhausen und die Geschäftsführerin in die jeweiligen Tabellen einzufügen. [8 BE]
Hinweis: Die Personalnummer der Geschäftsführerin wird vom System automatisch vergeben und muss beim Einfügen der Filiale ermittelt werden.
2.3 Transaktionskonzept und ACID-Eigenschaften
Datenbankmanagementsysteme erlauben den gleichzeitigen Zugriff mehrerer Benutzer. Bei lesendem Zugriff auf die Daten ist das kein Problem. Wenn jedoch mehrere Benutzer zur gleichen Zeit die gleichen Daten ändern wollen, muss das Datenbankmanagementsystem für die Konsistenz der Daten sorgen.
2.3.1
Erläutern Sie in diesem Zusammenhang das Konzept einer Transaktion. [2 BE]
2.3.2
Ein Datenbankmanagementsystem garantiert bei der Ausführung einer Transaktion die Einhaltung der vier grundlegenden Eigenschaften atomicity, consistency, isolation und durability, die auch als ACID-Eigenschaften bezeichnet werden.
Beschreiben Sie diese vier grundlegenden Eigenschaften. [4 BE]
Lösung
Nach den offiziellen Korrekturhinweisen
2.2.1 Formulieren Sie eine SQL-Anweisung, die die Bestellnummern und die Lieferdauer (Zeit zwischen Start und Auslieferung) aller Lieferaufträge ermittelt, deren Lieferdauer mehr als 45 Minuten beträgt.
Lösung: ```sql SELECT bestellnr, TIMEDIFF(ausgeliefert, start) AS Lieferdauer FROM Lieferauftrag WHERE HOUR(TIMEDIFF(ausgeliefert, start)) >= 1 OR MINUTE(TIMEDIFF(ausgeliefert, start)) > 45; ```
2.2.2 Das Kennzeichen des Kleinwagens sowie Vor- und Nachname des Fahrers für die Auslieferung der Bestellung mit der Nummer 4711 sollen ermittelt werden. Geben Sie die entsprechende SQL-Anweisung an.
Lösung: ```sql SELECT kennzeichen, vorname, nachname FROM Fahrzeug INNER JOIN Lieferauftrag USING (fahrzeugnr) INNER JOIN Fahrer USING (fahrernr) WHERE bestellnr = 4711; ```
2.2.3 Implementieren Sie eine SQL-Anweisung, welche die Vor- und Nachnamen aller Fahrer mit der Anzahl ihrer durchgeführten Lieferaufträge auflistet.
Lösung: ```sql SELECT vorname, nachname, COUNT(bestellnr) AS Anzahl FROM Fahrer LEFT JOIN Lieferauftrag USING (fahrernr) GROUP BY fahrernr ORDER BY Anzahl DESC; ```
2.2.4 Entwickeln Sie die SQL-Anweisungen, um die Tabelle Geschäftsführer zu erstellen und die Tabelle Filiale, um den Fremdschlüssel personalnr zu erweitern sowie die Filiale in Witzenhausen und die Geschäftsführerin in die jeweiligen Tabellen einzufügen.
Lösung: ```sql CREATE TABLE Geschäftsführer ( personalnr INTEGER AUTO_INCREMENT, vorname VARCHAR(64), nachname VARCHAR(64), PRIMARY KEY (personalnr) ); ALTER TABLE Filiale ADD gf_personalnr INTEGER; ALTER TABLE Filiale ADD FOREIGN KEY (gf_personalnr) REFERENCES Geschäftsführer(personalnr); INSERT INTO Geschäftsführer (vorname, nachname) VALUES ('Mira', 'Bellenbaum'); INSERT INTO Filiale (filialnr, strassehausnr, plz, ort, gf_personalnr) VALUES (3, 'Bahnhofstr.3', '37213', 'Witzenhausen', (SELECT personalnr FROM Geschäftsführer WHERE vorname = 'Mira' AND nachname = 'Bellenbaum')); ```
2.3.1 Erläutern Sie in diesem Zusammenhang das Konzept einer Transaktion.
Lösung: Eine Transaktion ist eine Gruppe von logisch zusammenhängenden SQL-Anweisungen, die nur gemeinsam ausgeführt werden sollen. Kann aufgrund eines Fehlers oder einer Zugriffsverletzung eine der Anweisungen nicht ausgeführt werden, wird keine der Anweisungen in der Transaktion ausgeführt und der Datenbestand wird in den Ausgangszustand der Transaktion zurückversetzt.
2.3.2 Beschreiben Sie diese vier grundlegenden Eigenschaften (ACID).
Lösung: Atomicity (Atomarität) – Alles oder nichts: Die Operationen einer Transaktion werden vollständig oder gar nicht ausgeführt. Tritt ein Fehler auf, werden die Operationen per Rollback rückgängig gemacht. Consistency (Konsistenz) – DB bleibt in konsistentem Zustand: Datenintegrität (Wertebereiche, Fremdschlüssel etc.) wird gewahrt. Bei Verletzung werden Änderungen verworfen. Isolation (Isolation) – Transaktionen laufen isoliert voneinander ab; gleichzeitig ablaufende Transaktionen stören sich nicht (z. B. durch Datensperren). Durability (Dauerhaftigkeit) – Daten sind nach erfolgreichem Abschluss (Commit) dauerhaft gespeichert; auch bei Systemausfällen geht nichts verloren (z. B. durch Logging).
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 C - 2.3 · LK 2022 · Aufgabe Vorschlag B - 2 - SQL-Anweisungen und Transaktion… · LK 2021 ›