Klausur Datenbanksysteme

Prüfung Datenbanksysteme, 10. Juli 2004
S. 1
Klausur Datenbanksysteme
Name:
Matrikel-Nr.:
Studiengang:
† AI
† IK
† TM
† WI
Aufgabenblatt nicht vor Beginn der Prüfung umdrehen!
Prüfer:
Dauer:
Datum:
Hilfsmittel:
Kennzahlen:
Prof. Dr. Martin Hulin
90 Minuten gesamt
10. Juli 2004
Alle auf dem Prüfungsplan angegebenen Hilfsmittel B - K
1813, 1891, 2117, 4017
Rückseiten sind bedruckt!
Lösen Sie die Aufgaben auf den ausgegebenen Aufgabenblättern!
Ersatz-Aufgabenblätter sind auf Anfrage erhältlich.
Bewertung:
Aufg.
Punkte
1 (27) 2 (13) 3 (4)
4 (8)
5 (14) 6 (5)
7 (7)
8 (22) Σ (100)
Aufgaben
(Punkte ohne Gewähr)
Aufgabe 1: Erstellen eines Entity-Relationship-Diagramms
(27)
Für eine chemische Fabrik, soll eine Datenbank erstellt werden. Zeichnen Sie dazu Entity-Sets,
Attribute und Relationen, wie Sie der unten stehenden Beschreibung entsprechen, in ein EntityRelationship-Diagramm. Verwenden Sie dazu das ausgeteilte Extrablatt. 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.
Beschreibung der Datenbank für die chemische Fabrik
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 Datenbank einer chemischen Fabrik für sinnvoll erachten.
•
Die Fabrik benötigt verschiedene Chemikalien oder stellt sie her, z. B. Acetaldehyd oder
Brenztraubensäure. Für jede Chemikalie soll ihr Name und ihre chemische Formel (als String) in
der Datenbank abrufbar sein.
•
Die Chemikalien werden in Behältern gelagert. Folgende Abfragen sollen möglich sein
•
•
-
Nummer, Standort, Art, Herstellerfirma, Datum der Inbetriebnahme, nächster
Inspektionstermin, Kapazität eines Behälters und momentane Füllmenge.
-
Suche nach der Chemikalie, die in einem bestimmten Behälter aufbewahrt wird. Pro Behälter
wird nur eine Chemikalie gelagert, nicht mehrere zusammen.
-
Suche nach allen Behältern, in der eine bestimmte Chemikalie aufbewahrt wird.
In chemischen Reaktionen werden aus Chemikalien andere Chemikalien erzeugt, z. B. aus
Caciumphosphat, Schwefelsäure und Wasser wird Calciumhydrogenphosphat und Gips erzeugt.
1 Ca3(PO4)2 + 2 H2SO4 + 4 H2O → 1 Ca(H2PO4)2 + 2 CaSO4⋅2 H2O
Folgende Abfragen sollen möglich sein:
-
Die sogenannte Reaktionsenthalpie, das ist die aufgewendete oder entstehende Energiemenge
bei der Reaktion in kJoule/mol. Bei exothermen Reaktionen (Energie wird freigesetzt) wird
die Energiemenge negativ angegeben, bei endothermen Reaktionen (Energie wird verbraucht)
positiv.
-
Suche nach allen Chemikalien, die an der Reaktion beteiligt sind, seien es Ausgangsstoffe
oder Reaktionsprodukte
-
Für jede beteiligte Chemikalie die Art (Ausgangsstoff oder Reaktionsprodukt) und die
benötigte Menge (Anzahl Moleküle, z. B. 2 bei Schwefelsäure im Beispiel oben). Dabei kann
ein und dieselbe Chemikalie bei der einen Reaktion Ausgangsstoff sein und bei einer anderen
Reaktionsprodukt.
-
Suche nach allen Reaktoren (siehe unten), in der diese Reaktion abläuft.
-
Besondere Hinweise für die Reaktion, z. B. "Temperatur von 580° muss bereits vor
Reaktionsbeginn erreicht werden" oder "Nach Ende der Reaktion Reaktor sofort mit Wasser
ausspülen". Dabei kann es zu einer Reaktion mehrere besondere Hinweise geben.
Die chemischen Reaktionen finden in Reaktoren statt, wobei jeder Reaktor nur für eine
bestimmte Reaktion verwendet wird. Folgende Abfragen sollen möglich sein
-
Nummer, Standort, Art, Herstellerfirma, Datum der Inbetriebnahme, nächster
Inspektionstermin, maximaler Druck und maximale Temperatur eines Reaktors.
Prüfung Datenbanksysteme 10.7.2004, Name:
MatrikelNr:
S. 3
•
Bei Reaktoren und Behältern werden viele gleichartige Daten erfasst, z. B. Standort oder
Herstellerfirma, d. h. auf einem hohen Abstraktionsniveau sind beides Geräte für die chemische
Produktion. Drücken Sie dies in Ihrem Entity-Relationship-Diagramm aus.
•
Alle anderen Aspekte einer chemischen Fabrik, wie Transport von Chemikalien, Mitarbeiter,
Betriebsschichten, Kunden, Aufträge, Lieferfirmen, Laboranalysen, Sicherheitsrichtlinien usw.
brauchen in diesem Ausschnitt der Datenbank für eine chemische Fabrik nicht berücksichtigt
werden.
Aufgabe 2: Relationale Tabellenstruktur der Datenbank für die chemische Fabrik mit SQL (13)
Wandeln Sie die Entity-Sets Reaktionen und Chemikalien aus Aufgabe 1: und eventuell vorhandene
Relationen zwischen diesen beiden Entity-Sets in relationale Tabellen um. Verwenden Sie SQL zur
Definition von Tabellen, Primärschlüsseln und Fremdschlüsseln. Die anderen Entity-Sets wie z. B.
Reaktoren, Behälter oder Prozessketten und Relationen zu ihnen brauchen Sie nicht in Tabellen
umwandeln.
Schreiben Sie Ihre Lösung auf die Rückseite des Extrablattes.
CREATE TABLE Chemikalien
(Nr
INTEGER
Name
VARCHAR2 (30)
Formel
VARCHAR2 (100)
CREATE TABLE Reaktionen
(Nr
INTEGER
Enthalpie FLOAT);
PRIMARY KEY,
NOT NULL,
NOT NULL);
PRIMARY KEY,
CREATE TABLE C_beteiligt_an_R
(CNr
INTEGER
CONSTRAINT FK_Chem REFERENCES Chemikalien (Nr),
RNr
INTEGER
CONSTRAINT FK_Reak REFERENCES Reaktionen (Nr),
Art
CHAR(20),
Menge
INTEGER);
CREATE TABLE Hinweise
(Text
VARCHAR2 (256),
RNr
INTEGER
CONSTRAINT FK_H_R REFERENCES Reaktionen (Nr));
Wenn Sie die Aufgaben 1 und 2 gelöst haben, geben Sie bitte das Extrablatt mit Ihrer Lösung
ab. Sie erhalten dafür die Musterlösung, um die folgenden Aufgaben lösen zu können.
Aufgabe 3: SQL-Abfrage
(4)
Geben Sie eine SQL-Abfrage an, die die Nummern und die Standorte aller Behälter ermittelt, die vor
dem "20.08.2005" zur nächsten Inspektion müssen und die von der Art Edelstahlbehälter sind.
SELECT Nr, Standort
FROM Behälter
WHERE Inspektion < '20.08.2005' AND Art = 'Edelstahlbehälter';
Aufgabe 4: SQL-Abfrage
Geben Sie eine SQL-Abfrage an, die für jede Chemikalie die derzeit in der Fabrik vorhandene
Vorratsmenge in allen Behältern ermittelt.
SELECT ChemikalienNr, SUM(Füllmenge)
FROM Behälter
GROUP BY ChemikalienNr;
(8)
Aufgabe 5: SQL-Anweisungen
(14)
Eine neue chemische Reaktion wird in die Datenbank aufgenommen: Ethen und Brom reagieren zu
Dibrommethan (1 C2H4 + 1 Br2 → 1 C2H4Br2). Die Chemikalien sind bereits in der Datenbank
enthalten und haben die Nummern 1030, 390 bzw. 2480. Die Reaktion findet im Reaktor mit der
Nummer 172 statt, der ebenfalls schon in der Datenbank ist. Als besonderer Hinweis ist einzutragen:
"Brom in Wasser gelöst". Die Reaktionsenthalpie beträgt – 120,4 kJ/mol. Die neue Reaktion
bekommt die Nummer 2000.
INSERT INTO Reaktionen VALUES (2000, -120,4);
INSERT INTO beteiligt_an VALUES (1030, 2000, 'Ausgangsstoff', 1);
INSERT INTO beteiligt_an VALUES (390, 2000, 'Ausgangsstoff', 1);
INSERT INTO beteiligt_an VALUES (2480, 2000, 'Reaktionsprodukt', 1);
INSERT INTO Hinweise VALUES (2000, 'Brom in Wasser gelöst');
UPDATE Reaktoren SET ReaktionNr = 2000 WHERE Nr = 172;
Aufgabe 6: Ablaufsteuerung
(5)
Auf welche Weise könnte in der Datenbank eine inkonsistente Datenmenge entstehen, wenn die
Verbindung zwischen Client und Server ausfällt, während die Anweisungen von Aufgabe 5:
durchgeführt werden? Mit welchem Konzept verhindern Datenbankmanagementsysteme einen solchen
inkonsistenten Datenbestand?
Wenn während der 5 insert- und der update-Anweisung die Verbindung zur Datenbank ausfällt, so
sind einige Daten der Reaktion bereits eingetragen, andere nicht. Die Reaktion mit der Nummer 2000
wäre dann nicht vollständig in der Datenbank und damit falsch, z. B. mit einer falschen
Reaktionsgleichung.
Durch eine Transaktionssteuerung wird erreicht, dass entweder alle Daten der Reaktion 2000 in der
Datenbank eingetragen werden oder gar keine.
Aufgabe 7: B*-Bäume
(7)
Für die Namen der Chemikalien sei ein B*-Baum aufgebaut. Dazu folgende Informationen:
-
Ein interner Knoten hat einen Verteilungsgrad von p = 50, d. h. maximal 50 Sohnknoten.
-
Pro Blattknoten gibt es 50 Pointer auf Datensätze (bzw. ihre Blöcke)
Wie viele Blockzugriffe benötigt man maximal, um auf eine bestimmte Chemikalie mittels ihres
Namens zuzugreifen bei
a)
1000 Chemikalien?
1000 Datensätze
=> 1000 Pointer in den Blattknoten des B*-Baums
=> Höchstens 40 Blattknoten für die 1000 Pointer: 1000/25 = 40, wobei 25 = 50/2 der
Mindestfüllgrad ist.
=> In der nächsthöheren Ebene des Baums hat man 40 Pointer, zu jedem Blattknoten
einen. Wollte man nun nach der Rechnung 40/25 = 1,6 in dieser Ebene zwei Knoten
verwenden, müsste man die 40 Pointer auf diese zwei Knoten verteilen. Das widerspricht
aber dem Mindestfüllgrad von 25. Also ist man schon auf der obersten Ebene bei der
Wurzel angelangt. Diese hat 40 Pointer.
=> Der B*-Baum hat zwei Ebenen, dafür braucht man zwei Blockzugriffe, zusammen mit
dem Zugriff auf die Daten also drei Blockzugriffe.
b)
1000 mal so viel Chemikalien, also 1.000.000 Chemikalien?
1.000.000 Datensätze
=> 1.000.000 Pointer in den Blattknoten des B*-Baums
=> Höchstens 40.000 Blattknoten für die 1.000.000 Pointer: 1.000.000/25 = 40.000, wobei
25 = 50/2 der Mindestfüllgrad ist.
Prüfung Datenbanksysteme 10.7.2004, Name:
MatrikelNr:
S. 5
=> In der nächsthöheren Ebene des Baums hat man 40.000 Pointer. Man braucht dazu
höchstens 40.000/25 = 1.600 Knoten.
=> In der nächsthöheren Ebene des Baums braucht man höchstens 1600/25 = 64 Knoten.
=> In der nächsthöheren Ebene des Baums braucht man höchstens 64/25 = 2,56 Knoten,
also zwei Knoten, damit der Mindesfüllgrad nicht unterschritten wird.
=> Die Wurzel hat zwei Pointer.
=> Der B*-Baum hat maximal fünf Ebenen, dafür braucht man fünf Blockzugriffe,
zusammen mit dem Zugriff auf die Daten also sechs Blockzugriffe.
Kommentieren Sie (auf der nächsten Seite) das Ergebnis Ihrer Berechnung ganz kurz bezüglich der
Eignung von Datenbanken für große Datenmengen.
Obwohl 1000-mal so viele Daten vorhanden sind, wird die Suchzeit nur verdoppelt. Trotz großer
Datenmengen garantiert ein DBMS akzeptable Suchzeiten.
Aufgabe 8: Embedded SQL
(22)
Bei dieser Aufgabe soll eine C-Funktion Reaktionsformel mit Embedded SQL erstellt werden, die die
Reaktionsformel einer chemischen Reaktion ausdruckt.
void Reaktionsformel (int rnr)
Als Parameter rnr bekommt die Funktion die Nummer der Reaktion in der Datenbank übergeben. Die
Funktion ermittelt zu der Reaktion alle beteiligten Chemikalien mit ihrer Formel und mit Ihrer Menge
bei der Reaktion. Dann wird die Reaktionsformel ausgedruckt, wobei die verschiedenen Chemikalien
mit " + " getrennt werden und Ausgangsstoffe und Reaktionsprodukte durch einen Pfeil "->". Für
diese Aufgabe in der Klausur brauchen Sie nur den Teil programmieren, der die Ausgangsstoffe
druckt, d. h. den ersten Teil der Formel bis zum Pfeil. Die Zahlen für die Anzahl von Atomen eines
Elements in einem Molekül werden nicht tiefgestellt.
Beispiel: Für die Reaktion aus dem Beispiel beim Entity-Set Reaktionen wird folgende Formel
ausgedruckt: "1 Ca3(PO4)2 + 2 H2SO4 + 4 H2O". Der Rest der Formel wird (für die Aufgabe) nicht
mehr gedruckt: "-> 1 Ca(H2PO4)2 + 2 CaSO4⋅2 H2O"
a) Geben Sie eine Cursordeklaration an, die für die Reaktion mit der Reaktionsnummer :rnr die
Formel und die Menge für jede beteiligte Chemikalie ermittelt, die bei dieser Reaktion
Ausgangsstoff ist.
EXEX SQL DECLARE AStoff_Cur CURSOR FOR
SELECT b.Menge, c.Formel
FROM beteiligt_an b, Chemikalien c
WHERE b.ChemikalienNr = c.Nr AND
b.ART = 'Ausgangsstroff' AND
b.ReaktionNr = :rnr;
b) Geben Sie nun die C-Funktion Reaktionsformel an. Die Cursordeklaration von a) brauchen Sie
nicht zu wiederholen, sondern nur durch das Symbol ♦ zu kennzeichnen. Gehen Sie davon aus,
dass die Verbindung zur Datenbank bei Aufruf der Funktion bereits hergestellt ist.
void Reaktionsformel (int rnr)
{
EXEC SQL BEGIN DECLARE SECTION,
int Menge;
VARCHAR Formel[30];
EXEC SQL END DECLARE SECTION;
int i = 1;
♦
EXEC SQL OPEN AStoff_Cur;
EXEC SQL FETCH AStoff_Cur INTO :Menge, :Formel;
Formel.arr[Formel.len] = '\0';
while (sqlca.sqlcode == 0)
{ if i > 1 printf(" + ");
printf ("%i %s", Menge, Formel.arr);
EXEC SQL FETCH AStoff_Cur INTO :Menge, :Formel;
Formel.arr[Formel.len] = '\0';
i++;
}
EXEC SQL CLOSE AStoff_Cur;
return;
/* Ausdruck des Rests der Formel nach dem Reaktionspfeil
braucht in dieser Aufgabe nicht mehr programmiert werden */
}
Prüfung Datenbanksysteme 10.7.2004, Name:
MatrikelNr:
Chemikalien
Behälter
Nr
Nr
Name
Formel
ChemikalienNr
Standort
Art
Hersteller
Inbetriebnahme
Inspektion
Kapazität
Füllmenge
beteiligt_an
ChemikalienNr
ReaktionNr
Art
Menge
Hinweise
ReaktionNr
Hinweistext
Reaktoren
Nr
Reaktionen
Nr
Enthalpie
Abbildung 1: Tabellenstruktur der Datenbank für eine chemische Fabrik
ReaktionNr
Standort
Art
Hersteller
Inbetriebnahme
Inspektion
maxDruck
maxTemp
S. 7
Nr
Name
Chemikalien
Art
Menge
Nr
Gefahrstoffklassen
Formel
(1,N)
gelagert_in →
(1,1)
(1,N)
beteiligt_an ↓
(1,N)
Reaktionen
Enthalpie
Hinweise
(1,N)
läuft_ab_in ↓
(1,1)
⊃
GNr
Standort
Art
Geräte
d
Reaktoren
maxDruck
maxTemp
Hersteller
Inbetriebnahme
⊃
Behälter
Inspektionstermin
Kapazität
Füllmenge
Abbildung 2: Entity-Relationship-Diagramm der Datenbank für die chemische Fabrik