Klausur Datenbanksysteme, Lösungen

Fachhochschule Ravensburg-Weingarten, Fachbereich Elektrotechnik und Informatik
Klausur Datenbanksysteme, Lösungen
Prüfer:
Dauer:
Datum:
Hilfsmittel:
Kennzahl:
Prof. Dr. Martin Hulin
90 Minuten gesamt
9. Februar 2001
Bücher, Scripte, Manuals, Taschenrechner ohne Datenbankmanagementsystem
1891
Art
Funktion
Größe
Kapazität
ID
(0,N)
Lager
Maschine

(1,1)
führt aus 

y
Koordinaten
(1,1)
(1,N)
Anzahl
(0,N)
Ort
 ist am
benötigt 
(0,M)
CNC-Programm
Produktionsauftrag
Anzahl
d
x
Status
ID
erzeugt 
(0,N)
Bezeichnung
(0,N)
Gruppe
Status
Teil/Produkt
ID
Anzahl
Status
(0,N)
Startort
ID
(0,N)
(0,N)
(1,1)
(1,1)
Transportauftrag
für 
(0,N)
braucht
Zielort
(0,N)
(1,1)
(1,1)
Anzahl
Anzahl
führt aus
(0,N)
ID
Funktion
Transportroboter
Kapazität
Tabelle Transportauftrag
Attribut
Primär- oder Fremdschlüssel (mit Bezug)
ID 
Primärschlüssel 
Status 
Teil_ID 
Fremdschlüssel, Bezug auf Teil/Produkt (ID) 
Anzahl_Teile 
Startort_ID 
Fremdschlüssel, Bezug auf Ort (ID) 
Zielort_ID 
Fremdschlüssel, Bezug auf Ort (ID) 
Transportroboter_ID  Fremdschlüssel, Bezug auf Transportroboter (ID) 
Tabelle 1: Struktur der relationalen Tabelle Transportauftrag (zu Aufgabe 1: g))
Aufgaben mit Lösungen
Aufgabe 1: Entity-Relationship-Diagramm
(Punkte )
(50)
Eine Möbelschreinerei fertigt keine Konfektionsmöbel sondern mit modernen Maschinen Möbel nach
Maß. Daher gibt es keine Fließbandproduktion, sondern Transportroboter transportieren die
Ausgangsteile, Zwischenprodukte und Endprodukte zwischen den Maschinen und dem Lager. Ein
Programm steuert Maschinen und Transportroboter. Alle Daten über Maschinen, Produkte,
Produktionsaufträge usw. werden in einer Datenbank gespeichert. Die Struktur der Datenbank ist aus
obigem noch unvollständigen Entity-Relationship-Diagramm zu entnehmen. Im Folgenden sind die
einzelnen Entities und Relationen mit Beispieldatensätzen kurz beschrieben.
Prüfung Datenbanksysteme, 9.2.2001
S. 2
Teil/Produkt ist die Entity für Ausgangsteile, Zwischenprodukte und Endprodukte. Der Status gibt an,
zu welcher der drei Möglichkeiten das Teil/Produkt gehört.
(T775, Tischlerplatte 2m*4m, Holz, Ausgangsteil, 50)
(T8760, Bettgestell, Betten, Zwischenprodukt, 1)
(T8761, Bett, Betten, Endprodukt, 2)
Die Entities Lager und Maschine sind zu einer Oberentity Ort generalisiert, da sowohl Maschinen als
auch die verschiedenen Lagerplätze an einen Ort gebunden sind, der durch seine Koordinaten
gekennzeichnet ist.
(M3, (5; 19), Fräsmaschine, 3 m/Minute)
(L7, (3; 8), Regal für Holzstangen, 5m*0,5m*2m)
In der M:N-Relation „ist am“ wird eingetragen, wo die Teile sich gerade befinden. Die gerade
transportierten Teile sind entweder noch am Ausgangsort oder schon am Zielort eingetragen. Damit
ist das Attribut Anzahl bei Teil/Produkt eigentlich redundant und könnte weggelassen werden.
Beispiel:
Von Teil T775 sind 30 Stück am Lagerort L4, 19 am Lagerort L5 und 1 Stück bei Maschine M3.
Transportroboter
(R5, Langholztransporter 3m, 10 Stangen)
Für die Transportroboter trägt das Steuerprogramm Transportaufträge ein, die dann von den Robotern
der Reihe nach abgearbeitet werden, z. B.
R5 soll 3 Stangen T15 vom Lagerort L7 zur Maschine M4 bringen; Status: noch nicht begonnen.
Für die Maschinen trägt das Steuerprogramm Bearbeitungsaufträge ein, die dann von den Maschinen
der Reihe nach abgearbeitet werden, z. B.
M6 soll bei Teil T876 (Tischplatte) 4 Teile T655 (Tischbeine) anleimen; damit entsteht das Teil
T9455; die Steuerung der Maschine beim Kleben übernimmt das CNC-Programm; Status: in
Bearbeitung.
a) Kreuzen Sie für die Relation Transportauftrag für Teil/Produkt auf Grund der Information im ERDiagramm die richtigen Antworten an:    
Ja Nein
Ein einzelner Transportauftrag kann mehrere unterschiedliche Teile/Produkte umfassen.
Bei einem Transportauftrag wird mindestens ein Teil/Produkt transportiert.
Jedes Teil/Produkt kommt in mindestens einem Transportauftrag vor.
Ein Teil/Produkt kann in mehreren Transportaufträgen vorkommen.
b) Bei einem Produktionsauftrag werden Teile/Produkte benötigt, z. B. für einen Tisch eine
Tischplatte und vier Tischbeine. Zeichnen Sie die entsprechende Relation und die Art der
Relation (detaillierte Angabe (min, max)) in das obige ER-Diagramm ein.
c) Bei einem Produktionsauftrag wird genau ein Teil/Produkt erzeugt, z. B. ein Tisch. Zeichnen Sie
die entsprechende Relation und die Art der Relation in das obige ER-Diagramm ein.
d) Ein Transportauftrag hat einen Startort, von wo aus ein Teil transportiert wird, z. B. Lager L3.
Zeichnen Sie die entsprechende Relation und die Art der Relation in das obige ER-Diagramm ein.
e) Ein Transportauftrag hat einen Zielort, wohin ein Teil transportiert wird, z. B. Maschine M3.
Zeichnen Sie die entsprechende Relation und die Art der Relation in das obige ER-Diagramm ein.
f) Bestimmte Teile/Produkte (Zwischen- und Endprodukte) brauchen andere Teile/Produkte um
gefertigt zu werden, z. B. Tisch T9455 die Tischplatte T876 und 4 Tischbeine T655. Tisch 9456
braucht die gleichen Tischbeine T655 (4 Stück), aber die Tischplatte T877 und die Auszugsplatte
T888. Diese Informationen sollen nicht erst beim Produktionsauftrag (siehe b)) in die Datenbank
eingetragen werden, sondern schon vorab. Tragen Sie die Relation Teil/Produkt braucht
Teil/Produkt zusammen mit der Art der Relation in das obige ER-Diagramm ein.
g) Wandeln Sie die Entity Transportauftrag in eine relationale Tabelle um und tragen die Struktur in
Tabelle 1 auf Seite 1 ein. Soweit sinnvoll berücksichtigen Sie auch alle Relationen von
Prüfung Datenbanksysteme, 9.2.2001
S. 3
Transportauftrag. Tragen Sie in die letzte Spalte ein, ob ein Attribut Primär- oder Fremdschlüssel
ist, bei Fremdschlüsseln auch den Bezug.
h) Die Koordinaten bei Ort sind ein zusammengesetztes Attribut. Tragen Sie dies im ER-Diagramm
ein.
Abgabe ER-Diagramm
Geben Sie das Blatt mit dem Entity-Relationship-Diagramm ab. Sie erhalten ein Blatt mit der
Tabellenstruktur einer relationalen Datenbank als Grundlage für die weiteren Aufgaben. Mehrdeutige
Relationen sind bereits in Verbindungstabellen umgewandelt (grau unterlegt), Primärschlüssel sind
unterstrichen und der Bezug durch Fremdschlüssel mit Pfeilen dargestellt. Die Spaltennamen sind
selbst erklärend.
Aufgabe 2: SQL-Abfrage
(6)
Geben Sie eine SQL-Abfrage an, mit der Bezeichnung und Anzahl aller Endprodukte sortiert nach
Gruppe aufgelistet werden.
SELECT  Bezeichnung , Anzahl 
FROM Teil/Produkt 
WHERE Status = 'Endprodukt' 
ORDER BY Gruppe ;
Aufgabe 3: SQL-Abfrage
(9)
Geben Sie eine SQL-Abfrage an, mit der die IDs aller Transportaufträge gesucht werden, die ein
Teil/Produkt der Gruppe „Bretter“ zu einer Maschine mit der Funktion „Oberfräse“ bringen
SELECT  Transportauftrag.ID 
FROM Teil/Produkt , Transportauftrag , Ort_Lager_Maschine 
WHERE Teil/Produkt.ID = Transportauftrag.Teile_ID 
AND Transportauftrag.Zielort = Ort_Lager_Maschine.ID 
AND Teil/Produkt.Gruppe = 'Bretter' 
AND Ort_Lager_Maschine.Funktion/Art = 'Oberfräse' ;
Aufgabe 4: SQL-Abfrage
(7)
Geben Sie eine SQL-Abfrage an, die für das Produkt T777 ID und Status aller Teile/Produkte ausgibt,
die zur Produktion von T777 benötigt werden. Ihre Abfrage soll auch funktionieren, wenn es gerade
keinen Produktionsauftrag für T777 gibt.
SELECT  Bestandteil_ID , Status 
FROM Teil/Produkt , Teil_braucht_Teil 
WHERE Bestandteil_ID = Teil/Produkt.ID 
AND Ganzes_ID = 'T777' ;
Aufgabe 5: SQL-Anweisungen
Bei einem Produktionsauftrag erzeugt die Maschine M6 das Produkt T896. Geben Sie zwei SQLAnweisungen an, mit der die Anzahl dieses Produkts um 1 erhöht wird bzw. ein neuer Datensatz in
die Tabelle ist_am_Ort eingetragen wird, der besagt, dass 1 Stück T896 am Ort M6 vorhanden ist.
UPDATE  Teil/Produkt  SET Anzahl = Anzahl + 1  WHERE ID = 'T896' ;
INSERT INTO  ist_am_Ort  VALUES (1 , 'M6' , 'T896' );
(9)
Prüfung Datenbanksysteme, 9.2.2001
S. 4
Aufgabe 6: Schlüssel
(7)
In einer Tabelle ist folgender Datenbestand gegeben. Kreuzen Sie unten die wahren Aussagen an.
Ja
A
B
C
D
E
1
2
1
2
3
3
1
1
2
2
1
2
1
1
1
1
1
2
1
2
1
2
2
3
5
3
5
3
1
1
Nein
{A, B} ist Minimalschlüssel (candidate key)
{A, B, C} ist Minimalschlüssel (candidate key)
{A, B, C} ist Superschlüssel (super key)
{A} ist Minimalschlüssel (candidate key)
{A, B, C, D, E} ist Superschlüssel (super key)
{B, C, D} ist Superschlüssel (super key)
{C, D, E} ist Superschlüssel (super key)
Aufgabe 7: C-Funktion mit ODBC
(28)
Erstellen Sie eine rekursive C-Funktion void Grundteile (char * Teile_ID)
mit ODBC. Die Funktion soll alle Teile/Produkte ermitteln, die zur Produktion des Teils/Produkts mit
der ID Teile_ID notwendig sind. Wenn ein solches benötigtes Teil den Status ‘Ausgangsteil‘ hat,
soll seine ID ausgedruckt werden, sonst soll die Funktion Grundteile rekursiv mit dem benötigten
Teil als Teile_ID aufgerufen werden.
Hinweise:
 Die Verbindung zur Datenbank sei bereits aufgebaut, der globale Verbindungs-Handle hdbc und
der Environment-Handle henv belegt. Variablen für die Verarbeitung eines SQL-Statements,
z. B. ein Statement-Handle, sind dagegen weder deklariert noch belegt.

Sie können die SQL-Anweisung von Aufgabe 4: benutzen, müssen aber die konkrete ID ‘T777‘
durch den Parameter Teile_ID ersetzen. Für die Zusammensetzung von Strings dürfen Sie den
Operator ‘+‘ verwenden.

Falls Sie die genaue C-, SQL- oder ODBC-Syntax einer Funktion oder eines Konstrukts nicht
kennen, beschreiben Sie die Operation, die Sie durchführen wollen, in einem Kommentar.

Eine Fehlerbehandlung braucht nicht eingebaut werden.

Beispielablauf der rekursiven Funktion Grundteile:
Aufruf von Grundteile("T1")
Benötigt werden die Teile T2 und T3
T2 ist Ausgangsteil, wird ausgedruckt
T3 ist kein Ausgangsteil, Aufruf von Grundteile("T3")
Benötigt werden die Teile T4 und T5
Beides sind Ausgangsteile, sie werden ausgedruckt.
Prüfung Datenbanksysteme, 9.2.2001
S. 5
T6
T1
T2
T3
T7
T4
T5
Abbildung 1: Beispielgraf für die Relation Teil_braucht_Teil
void Grundteile (char * Teile_ID)
{HSTMT hstmt; 
char SQL_Abfrage[100]; 
RETCODE rc; 
SDWORD Datenlaenge1, Datenlaenge2;
//C-Variablen für Datenbankinhalte:
char Bestandteil_ID[6]; 
char Status[20]; 
//SQL-Abfrage zusammensetzen
sprintf (SQL_Abfrage, "%s'%s'",
"SELECT Bestandteil_ID, Status
FROM Teil/Produkt , Teil_braucht_Teil
WHERE Bestandteil_ID = Teil/Produkt.ID
AND Ganzes_ID = ", Teile_ID);  
SQLAllocStmt (hdbc, &hstmt); 
SQLBindCol (hstmt, 1 ,SQL_C_CHAR, Bestandteil_ID ,
sizeof(Bestandteil_ID), &Datenlaenge1);
SQLBindCol (hstmt, 2 ,SQL_C_CHAR, Status ,
sizeof(Status), &Datenlaenge2);
SQLExecDirect (hstmt , (unsigned char *)SQL_Abfrage , SQL_NTS);
for (rc = SQLFetch(hstmt) ; rc == SQL_SUCCESS ;
rc = SQLFetch(hstmt) )
{if (!strcmp(Status, "Ausgangsteil") 
printf("%s\n", Bestandteil_ID); 
else Grundteile  (Bestandteil_ID ); /* rekursiver Aufruf */
}
SQLFreeStmt(hstmt, SQL_DROP); 
}
Prüfung Datenbanksysteme, 9.2.2001
Ort_Lager_
Maschine
S. 6
Produktionsauftrag
ID
ID
x-Koordinate
y-Koordinate
Funktion/Art
Kapazität/Größe
Status
CNC-Programm
erzeugt
Maschine_ID
ist_am_Ort
benötigt
Anzahl
Anzahl
Ort_ID
Teil_ID
Auftrag_ID
Teil_ID
Teil/Produkt
ID
Bezeichnung
Gruppe
Status
Anzahl
Teil_braucht_Teil
Ganzes_ID
Bestandteil_ID
Anzahl
Transportauftrag
ID
Status
Anzahl
Transportrob
ID
Funktion
Kapazität
Teile_ID
Startort
Zielort
Roboter_ID
Abbildung 2: Tabellenstruktur der Datenbank für die Möbelproduktion