Lösungen Klausur Datenbanksysteme

Prüfung Datenbanksysteme, 17. Juli 2003
S. 1
Lösungen Klausur Datenbanksysteme
Aufgaben
(Punkte ohne Gewähr)
Für eine Spedition/Logistikunternehmen soll eine Datenbank erstellt werden. Sie sehen auf dem letzten Blatt dazu Abbildung 2: Ausschnitt aus einem Entity-Relationship-Diagramm der SpeditionsDatenbank.
Erklärung des Ausschnitts:
Firmen sind die Kunden der Spedition. Privatkunden werden nicht bedient. Die Adressen einer Firma
werden nicht direkt gespeichert, sondern als Relation Firmenadresse zum Entity-Set Ort.
Die Firmen vergeben Transport-Aufträge an die Spedition. Die Relationen von bzw. nach geben dabei die Absender- bzw. Emfängeradresse an. Die Waren sollen am Starttermin abgeholt werden und
am Endtermin geliefert werden. Status kann die Werte: 'Auftrag erteilt', 'Transport geplant', 'Transport
begonnen', 'Transport erfolgreich abgeschlossen', 'Transport fehlerhaft abgeschlossen', 'Rechnung gestellt', 'Rechnung bezahlt' bekommen.
Jeder Auftrag umfasst ein oder mehrere Auftragspositionen, das sind die Waren, die tranportiert werden sollen. Da die gleichen Waren immer wieder transportiert werden müssen, z. B. immer wieder
Kisten mit Äpfeln, werden die Daten der Waren extra in der Entity Warenart gespeichert. Z. B. könnte eine Auftragsposition die Ware mit der ID 500 sein, Menge ist 200 Stück. Dabei handelt es sich um
die Warenart 10, das sind 'Kisten mit Äpfeln', Untertyp ist 'Jona Gold' und das Gewicht einer Kiste
beträgt 22 kg.
Aufgabe 1: Ergänzung des Entity-Relationship-Diagramms
(35)
Das ER-Diagramm ist noch nicht vollständig. Ergänzen Sie das ER-Diagramm in Abbildung 2 um
weitere Entities und Relationen. Achten sie darauf, ...
•
dass Sie ein ER-Diagramm erstellen sollen und nicht ein Diagramm einer relationalen Tabellenstruktur, d.h. M:N-Relationen sind noch nicht in Verbindungstabellen umgewandelt, es gibt
mehrwertige Attribute, und eine Strukturierung in Unterentities ist möglich.
•
dass Sie Anomalien und Redundanzen vermeiden.
•
dass Sie die Art der Relationen in der ausführlichen (min, max)-Schreibweise kennzeichnen.
Unten stehende Informationen sollen in der Datenbank enthalten sein und effizient abgefragt werden
können. Informationen, die hier nicht genannt sind, brauchen auch nicht ins ER-Diagramm, selbst
wenn Sie sie für eine Speditions-Datenbank für sinnvoll erachten.
•
•
Die Spedition transportiert nur Container. Alle Waren werden in Container verladen. Waren, die
nicht in Container verladen werden können, wie z. B. Erdöl, werden von unserer Spedition nicht
transportiert. Jeder Container hat eine Seriennummer, eine Normgröße (möglich sind die Werte
'einfach' und 'doppelt') und einen Typ (Typen sind z. B. 'normal' oder 'Kühlcontainer'). Folgende
Abfragen sollen möglich sein:
-
Suche nach allen Waren, die in einem bestimmten Container verpackt sind.
-
Suche nach allen Containern, in die eine bestimmte Ware verpackt ist. Dies können auch mehrere Container sein, wenn die Ware nicht in einen hinein passt.
Die Spedition hat zwei verschiedene Transportmittel: LKW und Schiffe. Für jeden LKW soll aus
der Datenbank erfragt werden können: das Kennzeichen, das Eigengewicht, das zulässige Gesamtgewicht , die Maße (Länge, Breite und Höhe), die Motorleistung, das Datum der Erstzulassung,
die Ladekapazität an Containern der Normgröße 'einfach' und außerdem die zulässige Höchstgeschwindigkeit und die Zahl der Achsen.
Für jedes Schiff soll aus der Datenbank erfragt werden können: das Kennzeichen, das Eigenge-
Prüfung Datenbanksysteme, 17. Juli 2003
S. 2
wicht, das zulässige Gesamtgewicht , die Maße (Länge, Breite und Höhe), die Motorleistung, das
Datum der Erstzulassung, die Ladekapazität an Containern der Normgröße 'einfach' und außerdem
der Tiefgang und der Typ (möglich sind die Werte 'Selbstfahrer' mit eigenem Motor und 'Schleppkahn').
•
Die Spedition plant die Transporte, um die Aufträge abwickeln zu können. Nach der Zuordnung
der Waren zu Containern (siehe oben) werden die Transporte durch eine Kette von Transportabschnitten realisiert. Folgende Abfragen sollen möglich sein:
-
Suche nach dem Startort eines Transportabschnitts. Dies ist ein Ort aus der Entity Ort.
-
Suche nach dem Zielort eines Transportabschnitts.
-
Suche nach dem Transportmittel für den Transportabschnitt.
-
Suche nach allen Containern, die mit dem Transportabschnitt transportiert werden.
-
Abfrage von Status, Beginn, Dauer und Verspätung eines Transportabschnitts.
•
Mittels eines GPS-Systems werden für alle Transportmittel alle Minuten die augenblicklichen
Koordinaten zusammen mit der Zeit erfasst und abgespeichert. Tragen Sie eine Weak-Entity
Fahrtdaten ein, die diese Werte speichert.
•
Warum ist Fahrtdaten eine Weak-Entity? (Bitte ankreuzen)
Weil die Daten automatisch vom GPS-System ohne menschliches Zutun gespeichert werden.
Weil Fahrtdaten nur zu einem anderen Entity-Set in Relation stehen und nicht zu mehreren.
Eine Weak-Entity kombiniert Zeit- mit Ortsdaten. Genau das ist hier der Fall.
⌧ Weil ein Fahrtdaten-Datensatz nur mit dem zugehörigen Transportmittel eine sinnvolle
Information darstellt, allein macht er keinen Sinn.
Weil die Tabelle unweigerlich überläuft, wenn niemand die alten Fahrtdaten löscht.
•
Nicht alle Container sind für alle Warenarten geeignet. Folgende Abfragen sollen möglich sein:
-
Suche nach allen Containern, die für eine bestimmte Warenart geeignet sind.
-
Suche nach allen Warenarten, die in einen bestimmten Container verladen werden können.
-
Suche nach der Kapazität eines bestimmten Containers für eine bestimmte Warenart.
Aufgabe 2: Ergänzung der Tabellenstruktur der Gremien-Datenbank
(10)
In Aufgabe 1: haben Sie unter anderem Zusatzinformation bezüglich Container und Transportabschnitten in das Entity-Relationship-Diagramm eingefügt. Wandeln Sie nur die zu Container und
Transportabschnitte eingetragenen Entitie-Sets und Relationen in Tabellen eines relationalen Datenbanksystems um und tragen diese Tabellen in Abbildung 1: Tabellenstruktur der SpeditionsDatenbank ein. Die anderen Entity-Sets wie z. B. Transportmittel brauchen Sie nicht umwandeln und
eintragen. Kennzeichnen Sie Primär- und Fremdschlüssel analog zu den bereits eingetragenen Tabellen.
Hinweis: Die Aufgaben 3 bis 10 können allein durch die Information in Abbildung 1 und Abbildung 2
gelöst werden, Ihre Zusätze aus Aufgabe 1: und Aufgabe 2: sind dazu nicht notwendig.
Aufgabe 3: SQL-Abfrage
Geben Sie eine SQL-Abfrage an, die den Start- und Endtermin von jedem Auftrag ermittelt, dessen
Status 'Auftrag erteilt' ist.
SELECT Starttermin, Endtermin •
FROM Auftrag •
WHERE Status = 'Auftrag erteilt'; •
(3)
Prüfung Datenbanksysteme, 17. Juli 2003, Name: «Name»
Matr.-Nr.: «MatrikelNr»
Aufgabe 4: SQL-Abfrage
S. 3
(10)
Geben Sie eine SQL-Abfrage an, die die Bezeichnungen, Untertypen und Mengen aller Waren ermittelt, die die Firma mit Namen 'Marvelli GmbH' in Auftrag gegeben haben und die zu einem Auftrag
gehören, dessen Status 'Transport begonnen' ist.
SELECT Warenart.Bezeichnung, Warenart.Untertyp, Ware.Menge •
FROM Firma •, Auftrag •, Ware •, Warenart •
WHERE Firma.Name = 'Marvelli GmbH' • AND
Status = 'Transport begonnen' • AND
Auftrag.Auftraggeber_ID = Firma.ID • AND
Ware.Auftrag_ID = Auftrag.ID • AND
Ware.Warenart_ID = Warenart.ID •;
Aufgabe 5: SQL-Abfrage
(8)
Geben Sie eine SQL-Abfrage an, die die IDs aller Aufträge ermittelt, die mehr als eine Auftragsposition (Ware) umfassen.
SELECT ID •
FROM Auftrag •
WHERE (SELECT COUNT(Ware.ID) ••
FROM Ware •
WHERE Ware.Auftrag_ID = Auftrag.ID •)• > 1 •;
Aufgabe 6: SQL-Abfrage
(8)
Geben Sie eine SQL-Abfrage an, die für jeden Auftrag (gekennzeichnet durch seine ID) das Gesamtgewicht der Waren ermittelt, die er umfasst.
SELECT Ware.Auftrag_ID, •
SUM(Warenart.Gewicht * Ware.Menge) •• Gesamtgewicht
FROM Warenart •, Ware •
WHERE Ware.Warenart_ID = Warenart.ID •
GROUP By Ware.Auftrags_ID ••;
Aufgabe 7: SQL-Anweisungen
(16)
Die Firma 'Marvelli GmbH' (sie hat die Firmen-ID 122) gibt am '18.7.2003, 10:00' einen neuen Transportauftrag. Er bekommt die ID 1428, soll vom Ort mit der ID 100 zum Ort mit der ID 200 gehen, am
'22.7.2003, 6:00' beginnen und am '22.7.2003, 15:00' am Ziel sein. Der Auftrag umfasst 1000 Kisten
Äpfel der Sorte 'Jona Gold' (Warenart-ID 10) und 5000 Kanister Apfelsaft 'Goldene Sonne' (WarenartID 50). Für den Transport wird ein Rechnungsbetrag von 370 Euro vereinbart. In der Tabelle Waren
wird durch einen Trigger und eine Sequence bei Insert automatisch eine neue ID erzeugt; die ID
braucht also von Ihnen nicht angegeben zu werden. Geben Sie SQL-Anweisungen an, mit denen diese
Informationen (nur die in dieser Aufgabe genannten Informationen) in die Datenbank eingetragen
werden. Beachten Sie dabei, dass die Warenarten, die Orte und die Firma bereits in der Datenbank
vorhanden sind.
INSERT • INTO Auftrag • VALUES • (1428 •, '18.7.2003, 10:00' •,
'22.7.2003, 6:00', '22.7.2003, 15:00' , 'Auftrag erteilt' •, 370 •,
122 •, 100, 200 •);
INSERT INTO Waren• (Menge, Auftrags_ID, Warenart_ID)• VALUES
(1000 •, 1428 •, 10 •);
INSERT INTO Waren • (Menge, Auftrags_ID, Warenart_ID) VALUES
(5000, 1428, 50 •);
Prüfung Datenbanksysteme, 17. Juli 2003
Aufgabe 8: Transaktionen
S. 4
(6)
In Aufgabe 7: wurde ein neuer Auftrag in die Datenbank geschrieben. Dabei werden mehrere SQLAnweisungen für verschiedene Tabellen ausgeführt. Beschreiben Sie, was bei einem Datenbankmangementsystem mit Transaktionssteuerung und einem Datenbankmanagementsystem ohne Transaktionen passiert, wenn nach der ersten SQL-Anweisung die Verbindung zum Client abreißt und die weiteren SQL-Anweisungen nicht mehr durchgeführt werden können.
Ohne Transaktionssteuerung:
Ohne Transaktionssteuerung werden die einzelnen Insert-Anweisungen unabhängig voneinander verarbeitet. Die erste Insert-Anweisung wird also ausgeführt die anderen nicht mehr. Damit ist in der
Datenbank ein Auftrag ohne Waren, d. h. falsch (inkonsistent) gespeichert.
Mit Transaktionssteuerung:
Mit Transaktionssteuerung werden die 3 Insert-Anweisungen zu einer Transaktion zusammengefasst.
Da nach der 1. Insert-Anweisung die beiden anderen nicht mehr kommen und insbesondere kein
Commit gegeben wird, werden alle Teilaktionen mit Rollback wieder rückgängig gemacht. Es wird
kein unvollständiger Auftrag in die Datenbank eingefügt, die Daten bleiben konsistent.
Aufgabe 9: Embedded SQL
(18)
Die Spedition will eine Statistik über den Anteil kurzfristiger Aufträge aufstellen. Dazu wird für jede
Kundenfirma der Anteil der Aufträge ermittelt, bei denen das Auftragsdatum nur 2 Tage oder weniger
vor dem Starttermin liegt. Unten sehen Sie eine C-Funktion mit Embedded SQL, die diese Aufgabe
ausführt. Analysieren Sie das Programm und versehen Sie es mit den richtigen Kommentaren an den
richtigen Stellen. Dazu ist eine Liste mit möglichen Kommentaren angegeben. Tragen Sie die passenden Kommentarnummern in die gekennzeichneten Felder [ ] ein.
void anteil_kurzfrist (void)
{EXEC SQL BEGIN DECLARE SECTION;
float Zeitdifferenz;
char Firmenname [50];
int Firmenid;
EXEC SQL END DECLARE SECTION;
[4]
[2]
int kurzfrist_auftr, anzahl_auftr; /* Zählvariable */
float anteil_kurzfrist; /* Variable zur Berechnung des Ergebnis */
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
EXEC SQL DECLARE firmen_cur CURSOR FOR
SELECT ID, Name FROM Firma;
EXEC SQL DECLARE auftrags_cur CURSOR FOR
SELECT Starttermin – Auftragsdatum
FROM Auftrag
WHERE AuftraggeberID = :Firmenid;
EXEC SQL OPEN firmen_cur;
EXEC SQL FETCH firmen_cur INTO :Firmenid, :Firmenname;
while (sqlca.sqlcode == 0)
{ anzahl_auftr = 0;
kurzfrist_auftr = 0;
EXEC SQL OPEN auftrags_cur;
EXEC SQL FETCH auftrags_cur INTO :Zeitdifferenz;
[5]
[8]
[10]
[11]
[16]
[23]
[26]
[13]
[19]
Prüfung Datenbanksysteme, 17. Juli 2003, Name: «Name»
Matr.-Nr.: «MatrikelNr»
while (sqlca.sqlcode == 0)
{ anzahl_auftr = anzahl_auftr + 1;
if (Zeitdifferenz <= 2)
kurzfrist_auftr = kurzfrist_auftr + 1;
EXEC SQL FETCH auftrags_cur INTO :Zeitdifferenz;
}
S. 5
[25]
[27]
[22]
if (anzahl_auftr == 0)
[28]
printf("Firma %i, %s hat keine Aufträge erteilt",
Firmenid, Firmenname);
else
{anteil_kurzfrist = 100 * kurzfrist_auftr / anzahl_auftr;
printf ("Firma %i, %s hat %f Prozent kurzfristige Aufträge.\n",
Firmenid, Firmenname, anteil_kurzfrist);
}
[31]
EXEC SQL CLOSE auftrags_cur;
EXEC SQL FETCH firmen_cur INTO :Firmenid, :Firmenname;
}
EXEC SQL CLOSE firmen_cur;
return;
[18]
[32]
sqlerror:
printf("\n\n FEHLER: %s \n\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
return;
}
Kommentarliste:
Nummer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Kommentar
User-Work-Area zur Aufnahme eines Datensatzes des Cursors für die Waren
User-Work-Area zur Aufnahme eines Datensatzes des Cursors für die Firmen
User-Work-Area zur Aufnahme der gesamten Tabelle "Auftrag"
User-Work-Area zur Aufnahme eines Datensatzes des Cursors für die Aufträge
Wenn der Server bei einem beliebigen SQL-Kommando einen Laufzeitfehler meldet,
springe zur angegebenen Marke für die Fehlerbehandlung.
Deklariere einen Cursor, der alle Waren liefert
Deklariere einen Cursor, der alle Waren für den gerade betrachteten Auftrag liefert
Deklariere einen Cursor, der alle Firmen liefert
Deklariere einen Cursor, der alle Aufträge liefert
Deklariere einen Cursor, der alle Aufträge für die gerade betrachtete Firma liefert
Führe die Abfrage nach allen Firmen durch
Führe die Abfrage nach allen Aufträgen durch
Führe die Abfrage nach allen Aufträgen für die gerade betrachtete Firma durch
Führe die Abfrage nach der Firma für den gerade betrachteten Auftrag durch
Führe die Abfrage nach allen Waren für den gerade betrachteten Auftrag durch
Hole den ersten Firmendatensatz in die User-Work-Area
Hole alle Firmendatensätze in die User-Work-Area
Hole den nächsten Firmendatensatz in die User-Work-Area
Hole den ersten Datensatz des Cursors auftrags_cur in die User-Work-Area
Hole alle Datensätze des Cursors auftrags_cur in die User-Work-Area
Hole alle Auftrags-Datensätze für die gerade betrachtete Firma in die User-Work-Area
Hole den nächsten Datensatz des Cursors auftrags_cur in die User-Work-Area
In einer Schleife führe für alle Firmen durch:
In einer Schleife führe für alle Aufträge in der Datenbank durch:
Prüfung Datenbanksysteme, 17. Juli 2003
25
26
27
28
29
30
31
32
33
S. 6
In einer Schleife führe für alle Aufträge der gerade betrachteten Firma durch:
Setze für die gerade betrachtete Firma die Zählvariablen zurück
Wenn die Zeitspanne zwischen Auftragsdatum und Starttermin weniger als 2 Tage beträgt,
so handelt es sich um einen kurzfristigen Auftrag
Fallunterscheidung ist notwendig, um eine Division durch 0 zu vermeiden
Fallunterscheidung ist sinnvoll, da für eine Firma ohne Aufträge der Anteil der kurzfristigen Aufträge irreführend wäre
Schließe den Cursor auftrags_cur; dies soll man immer durchführen, wenn ein Cursor
nicht mehr gebraucht wird
Schließe den Cursor auftrags_cur; dies ist notwendig, da er für die nächste Firma wieder
neu geöffnet werden muss.
Schließe den Cursor firmen_cur; dies soll man immer durchführen, wenn ein Cursor nicht
mehr gebraucht wird.
Schließe den Cursor firmen_cur; dies ist notwendig, da er für die nächste Firma wieder
neu geöffnet werden muss.
Aufgabe 10: Trigger
(8)
Erstellen Sie einen Datenbank-Trigger, der beim Eintrag eines neuen Auftrags überprüft, ob der Starttermin vor dem Endtermin liegt. Falls dies nicht der Fall ist, soll der Auftrag nicht in die Datenbank
eingetragen werden, sondern ein Fehler generiert werden.
Hinweis: Datumsfelder können mit den Operatoren <, >, = usw. verglichen werden.
CREATE OR REPLACE TRIGGER Terminkontrolle
BEFORE •
AFTER //nichtzutreffendes streichen
OR UPDATE OF
//nichtzutreffendes streichen
INSERT • OR DELETE
ON Auftrag • //Tabellenname ergänzen
FOR EACH ROW •
FOR EACH STATEMENT
//nichtzutreffendes streichen
BEGIN
if :new•.Endtermin <• :new.Starttermin
then raise_application_error• (-20010, 'Endtermin vor Starttermin'•);
end if;
END;
/
Prüfung Datenbanksysteme, 17. Juli 2003, Name: «Name»
Firma
Matr.-Nr.: «MatrikelNr»
Firmenadresse
Ort
ID
Firmen_ID
ID
Name
Name2
Branche
Ort_ID
Strasse_HNr
PLZ
Ortsname
Telefon
geogr_Länge
geogr_Breite
Auftrag
ID
Auftragsdatum
Starttermin
Endtermin
Status
Rechnungsbetrag
Auftraggeber_ID
von_ID
nach_ID
Container
SerienNr
Ware
verpackt
ID
Ware_ID
Menge
Container_ID
Normgröße
Typ
Auftrag_ID
Warenart_ID
geeignet_für
Transportabschnitt
Warenart_ID
Warenart
ID
Bezeichnung
Untertyp
Gewicht
ID
Container_ID
Kapazität
wird_transportiert
_in
Container_ID
Transportabschnitt_ID
Abbildung 1: Tabellenstruktur der Speditions-Datenbank
Status
Beginn
Dauer
Verspätung
Transportmittel_ID
Startort_ID
Zielort_ID
S. 7
Prüfung Datenbanksysteme, 17. Juli 2003
S. 8
Länge
Kennzeichen Eigengewicht Motorleistung
zul_Ges_Gew
Erstzulassung
Ladekapazität
Maße
Breite
Höhe
Tiefgang
Transportmittel
(0,N)
Koordinaten
mit ↑
(0,N)
← transportiert
ID
(1,N)
(1,1)
(1,1)
Typ
Normgröße
ist_geeignet ↓
(1,N)
Container
ID
ist_in ↑
nach →
(1,1)
(0,N)
ID
Strasse_HNr
PLZ
Ortsname
Telefon
(0,N)
nach ↑
(1,1)
Koordinaten
(1,N)
(1,1)
Firmenadresse
Status
Ware
Achszahl
Ort
von ↑
Dauer
Kapazität
Menge
von →
(0,N)
(0,N)
Beginn
(0,N)
ID
zul_Vmax
Transportabschnitt
(0,N)
(1,N)
Typ
LKW
(0,N)
(1,1)
Fahrtdaten
Schiff
⊃
hat ↓
Zeit
⊃
d
Firma
(1,N)
Verspätung
(1,1)
(1,1)
← umfasst
hat ↓
(1,N)
(1,1)
Auftrag
(0,N)
← gibt
Name
(0,N)
Name2
Warenart
ID
Bezeichnung
Untertyp
ID
ID
Auftragsdatum
Gewicht
Status
Starttermin
Rechnungsbetrag
Branche
Endtermin
ID
Strasse_HNr
PLZ
Ort
Ortsname
Telefon
(0,N)
(0,N)
von ↑
nach ↑
(1,1)
Koordinaten
(1,N)
(1,1)
Firmenadresse
ID
Menge
Ware
Firma
(1,N)
(1,1)
(1,1)
ist_von ↓
← umfasst
(1,N)
Auftrag
(1,1)
← gibt
(0,N)
(0,N)
Name2
Warenart
ID
Bezeichnung
Untertyp
ID
Name
ID
Gewicht
Auftragsdatum
Status
Starttermin
Rechnungsbetrag
Branche
Endtermin
Abbildung 2: Ausschnitt aus einem Entity-Relationship-Diagramm der Speditions-Datenbank
Prüfung Datenbanksysteme, 17. Juli 2003
S. 9