Probeklausur 6

7.2.2008
Hochschule Karlsruhe –Technik und Wirtschaft-
WS 2007
Fakultät für Informatik und Wirtschaftsinformatik
Prof. Schmidt/Prof. Senger
Klausur zur Vorlesung Datenbanken II
Aufgabe 1 Aufgabe 2 Aufgabe 3 Aufgabe 4
Summe
Bearbeitungszeit: 120 min.
Aufgabe 1 (Erstellung ER-Modell, Ableitung relationales Schema, PL/SQL)
[18]
a) Das Schema aus Anhang A ist unvollständig. Ergänze die fehlenden NOT NULL, PRIMARY und FOREIGN
KEY Constraints.
b) Zeichne ein ERD des Schemas in <min,max> Notation.
c) Schreibe eine PL/SQL-Prozedur die überprüft, ob es für den Kunden, der einen Flug mit der
Buchungsnummer x gebucht hat, n Tage später eine Rückflugmöglichkeit gibt. Die Buchungsnummer x und
die Anzahl der Tage n sollen als Parameter eingegeben werden. Das Ergebnis der Prüfung kann als Text über
DBMS_OUTPUT.PUT_LINE ausgegeben werden. Es genügt die Anzeige, ob überhaupt ein Rückflug
möglich ist; die Flugdaten müssen nicht ausgegeben werden.
Hinweise: Die Funktion substr(s,a,b) liefert den Teilstring a bis b des Strings s (ist a negativ, so wird vom
Stringende her gezählt, erstes Zeichen hat den Index 1).
In Anhang H sind einige Funktionen zum Umgang mit Datumsangaben vorgestellt, die hilfreich sein
können.
In Anhang B sind weiterhin einige Beispieldatensätze aus der Tabelle FLUGPLAN abgebildet.
Aufgabe 2 (Oracle Typsystem)
Szenario: Flugplan
[18]
a) Erstelle auf Grundlage des relationalen Schema aus Aufgabe 1 ein objekrelationales Typsystem mit folgenden
Eigenschaften :
[4]
- Der Typ flughafen_t besitzt die selben Attribute wie die Tabelle flughafen
- Der Typ flugplan_t besitzt neben den Attributen id, abflug, ankunft und dauer (die den
Attributen der Tabelle flugplan entsprechen) zwei Referenzen (von, bis), welche Verweise auf die
zugehörigen Start- bzw. Zielflughäfen darstellen. Weiterhin enthält der Typ ein mengenwertiges Attribut
(varray(7) of number(1)), das speichert ob an einem bestimmten Tag ein Flug duchgeführt wird.
Beispiel: flugtage_t(0,1,0,1,0,1,0) => Der Flug findet Montag, Mittwoch und Freitag statt.
b) Erstelle für diese Typen entsprechende typisierte Tabellen. Definiere dabei verschiedene in Frage kommende
Typen von Constraints. Übertrage anschließend mit zwei entsprechenden SQL-Statements die Datensätze aus
den relationalen Tabellen in die typisierten Tabellen.
[6]
c) Formuliere auf den typisierten Tabellen die folgende Anfrage: Wie kommt man mit 1 mal umsteigen von
Peking (PEK) nach Frankfurt (FRA) (die Wartezeit beim Umsteigen spielt dabei keine Rolle) ? [2]
d) Erweitere das zuvor definierte Typsystem um folgende Methoden (nur Definition, keine Implementierung):
[2]
- Eine Methode fliegt_heute(), welche für eine flugplan_t Instanz überprüft, ob dieser am
aktuellen Tag stattfindet. Wenn ja, soll 1 zurückgegeben werden, andernfalls 0.
- Eine Methode naechster_flug_zum_ziel(ziel flughafen_t), welcher für eine Flughafen den
nächsten Flug (Instanz vom Typ flugplan_t) zum als Parameter angegebenen Ziel zurückgibt.
1
7.2.2007
Hochschule Karlsruhe –Technik und Wirtschaft-
WS 2007
Fakultät für Informatik und Wirtschaftsinformatik
Prof. Schmidt/Prof. Senger
e) Implementere die Methode naechster_flug_zum_ziel(...)1. Zur Vereinfachung brauchst du nur
Flüge betrachten, die noch am selben Tag abfliegen. Der Flug sollte aber frühestens eine Stunde nach Anfrage
starten (Zeit zum einchecken, richtiges Gate finden, noch mal auf die Toilette, ...). Du darfst alle unter d)
definierten Methoden benutzen, wenn sie dir helfen (das tun sie!). In Anhang H sind ein Paar Funktionen zum
Umgang mit Zeit-/Datumsangaben aufgelistet, die hilfreich sein können.
[4]
Aufgabe 3 (PHP)
[14 + 2]
Grundlage ist der relationale Datenbestand aus Anhang A sowie das Fragment einer OR-Schicht in Anhang C.
Bei der Implementierung kannst du entweder die Methoden aus der PEAR/DB Klasse nutzen, oder du benutzt
die Methoden der DB_Util Klasse aus Anhang D.
a) Erstelle eine Klasse Flughafen, die von der Klasse CRUD_Flughafen abgeleitet ist und realisiere darin eine
Methode anzahl_ziele(), welche die Anzahl der direkt angeflogenen Ziele für einen Flughafen
zurückgibt
[4]
b) Erstelle in der Klasse Flugplan, die von der Klasse CRUD_Flugplan abgeleitet ist,eine Methode
zielflughafen(), welche für einen Flug die Instanz des Zielflughafens zurückliefert.
[3]
c) In Anhang C ist weiterhin das Skelett einer CRUD-Klasse Reise dargestellt, deren Instanzen mehrere
Flugabschnitte enthalten sollen. Hierbei ist auf die Reihenfolge der Flugabschnitte zu achten. [7]
- Wie muss die relationale Datenbank dafür erweitert werden? – Erstelle dazu das/die DDL-Statements.
- Erstelle die Methode flugabschnitt_hinzufuegen($flugabschnitt_instanz), welche für
eine Reise einen Abschnitt ans Ende der bisherige Reise hinzufügt.
d) Bonusaufgabe: Erweitere Teil c), indem du die Plausibilität der Flugabschnitte prüfst, d.h. der
einzufügende Flugabschnitt muss vom selben Flughafen aus starten, an dem der vorherige Flugabschnitt
endet. Ist dies nicht der Fall, beende das Programm mit einem die(…) Statement.
[2]
Aufgabe 4 (XML)
[11 + 5]
a) Erstelle ein SQLX Statement auf Basis des relationalen Schema aus Aufgabe 1, das der DTD aus Anhang E
genügt (Rootelement: flughaefen).
[3]
b) Im XML-Repository befindet sich eine Datei /DB2/KLAUSUR/flughaefen.xml, welche der DTD aus
Anhang E entspricht und Informationen über die Flughäfen enthält. Erstelle eine XQuery Anfrage, welche
die Namen aller Flughäfen in Shanghai zurückgibt.
[3]
Ausgabeformat: <airport>(Pu Dong)</airport>
<airport>(Hongqiao)</airport>
c)
Trage das XML-Dokument aus Anhang G in eine typisierte Tabelle (Typ XMLType) ein. Füge dann
anschließend für den 13.12.2007 noch den Flugabschnitt SK777 zu diesem Dokument hinzu.
Wie könnte man automatisch überprüfen, ob der Flugabschnitt passt (d.h. ob er vom Zielflughafen des
vorherigen Flugabschnittes startet) ?
[5]
d) Bonusaufgabe: Erstelle auf Basis des relationalen Datenbestandes (Anh. A) einen typisierten View, dessen
Datensätze wie in Anhang F aussehen (Hinweis: für die Formatierung der Abflug-/Ankunftszeit hilft die
Funktion substr(). Siehe dazu Kurzbeschreibung in Aufgabe 1 sowie Beispieldatensätze in Anh. B).
1
ohne umschließendes create or replace type body as … end; - Rahmengerüst
2
7.2.2008
Hochschule Karlsruhe –Technik und Wirtschaft-
WS 2007
Fakultät für Informatik und Wirtschaftsinformatik
Prof. Schmidt/Prof. Senger
Anhang A:
- DDL-Statements:
create table flughafen (
ID
char(3)
primary key,
NAME varchar2(40)
not null unique,
ORT varchar2(80)
);
create table flugplan (
ID
char(6),
VON
char(3) references flughafen(ID) on delete cascade,
BIS
char(3) references flughafen(ID) on delete cascade,
ABFLUG varchar2(5),
ANKUNFT varchar2(5),
DAUER
varchar2(5),
SO
number(1),
MO
number(1),
DI
number(1),
MI
number(1),
DO
number(1),
FR
number(1),
SA
number(1)
);
create table kunde (
ID
number(5),
NACHNAME
varchar2(30),
VORNAME
varchar2(25),
WOHNORT
varchar2(25)
)
create table flugbuchung (
BUCHUNGSNUMMER integer /* identifiziert eine Buchung */,
KUNDENNUMMER integer,
FLUGID
char(6),
FLUGDATUM
date
);
Anhang B: (Inhalt der Tabelle flugplan):
3
7.2.2007
Hochschule Karlsruhe –Technik und Wirtschaft-
WS 2007
Fakultät für Informatik und Wirtschaftsinformatik
Prof. Schmidt/Prof. Senger
Anhang C:
class CRUD_Flughafen {
private $id;
private $name;
private $stadt;
// Konstruktor
function __construct($id, $name, $stadt) { ... }
// Legt Datensatz in der Datenbank an und liefert Instanz zurück
function anlegen($id, $name, $stadt) { ... }
// liefert Datensatz mit der entsprechenden ID zurück
static function get($id) { ... }
}
class CRUD_Flugplan {
private $id;
private $von;
private $bis;
private $abflug;
private $ankunft;
private $dauer;
private $flug_tage = array();
// Konstruktor
function __construct($id, $von, $bis, $abflug, $ankunft, $dauer, $flug_tage) { ... }
// Legt Datensatz in der Datenbank an und liefert Instanz vom Typ Flugplan zurück
function anlegen($id, $von, $bis, $abflug, $ankunft,
$dauer, $flug_tage=array(1,1,1,1,1,1,1)) { ... }
// liefert Instanz des Ausgangsflughafens (Typ Flughafen)
function ausgangsflughafen() { ... }
// hier wirst du aktiv
function zielflughafen() { ... }
// liefert Datensatz mit der entsprechenden ID zurück (Typ Flugplan)
static function get($id) { ... }
}
class CRUD_Reise {
private $id;
// Konstruktor
function __construct($id) { ... }
// Legt Datensatz in der Datenbank an und liefert Instanz zurück
static function anlegen() { ... }
4
7.2.2008
Hochschule Karlsruhe –Technik und Wirtschaft-
WS 2007
Fakultät für Informatik und Wirtschaftsinformatik
Prof. Schmidt/Prof. Senger
// liefert die Flugabschnitte sortiert nach Reihenfolge in einem Array zurück
// (als Instanzen vom Typ flugplan)
function flugabschnitte() { ... }
// hier kommst du zum Zug
function flugabschnitt_hinzufuegen($flug_instanz) { ... }
// löscht eine Reise
function delete() { ... }
}
Anhang D:
class DB_Util
Methode
Beschreibung
Static function connect($dsn)
Öffnet eine Datenbankverbindung zu $dsn
static function get_connection()
liefert das offene Datenbankhandle zurück
static function
query($sql, $data=array()):
Führt das angegebene SQL-Statement aus.
Handelt es sich um ein SELECT-Statement,
so wird ein Array aus Dictionary-Objekten
zurückgeliefert. Ein Dictionary-Objekt
entspricht einem Datensatz. Die Keys des
Dictionaries sind durch die Attribute des
SELECT-Statements vorgegeben (in
Großbuchstaben).
static function
object_query($sql,
$class,
$data=array()):
Analog zu query(...), allerdings werden
Instanzen des betreffenden Typs (Parameter
$class) zurückgeliefert.
static function
create_id($name='DEFAULT_SEQUENCE')
Erzeugt eine neue eindeutige ID für die
Sequenz $name
static function close()
Schließt die Verbindung zur Datenbank
static function commit()
Führt ein COMMIT aus
static function rollback()
Führt ein ROLLBACK aus
Anhang E (DTD):
<!ELEMENT flughaefen (flughafen+)>
<!ELEMENT flughafen (name, stadt)>
<!ATTLIST flughafen
ID ID #REQUIRED
>
<!ELEMENT name (#PCDATA)>
<!ELEMENT stadt (#PCDATA)>
5
7.2.2007
Hochschule Karlsruhe –Technik und Wirtschaft-
WS 2007
Fakultät für Informatik und Wirtschaftsinformatik
Prof. Schmidt/Prof. Senger
Anhang F (XML-Dokument):
<?xml version="1.0" encoding="UTF-8"?>
<flug ID="SK1010">
<von abflug="07:45">BGO</von>
<nach ankunft="11:40">BRU</nach>
<tage>
<SO>1</SO>
<MO>1</MO>
...
<SA>0</SA>
</tage>
</flug>
Anhang G (XML-Dokument):
<reise id="r353252">
<name>Schulz</name>
<vorname>Stefan</vorname>
<flugabschnitte>
<flugabschnitt id="SK933" datum="12.12.2007"/>
<flugabschnitt id="SK1265" datum="12.12.2007"/>
<flugabschnitt id="SK166" datum="13.12.2007"/>
</flugabschnitte>
</reise>
Anhang H (Oracle Funktionen):
Funktion: to_char(date, format)
Format:
D: Tag der Woche (1-7).
DAY:
Name des Tages ('MONTAG
', 'DIENSTAG ', 'MITTWOCH ', ...).
DD:
Tag im Monat (1-31).
DDD:
Tag im Jahr (1-366).
DY:
Abgekürzter Name des Tages ('MO', 'DI', 'MI', ...)
Beispiel:
select to_char(sysdate, 'DAY') as Wochentag
from dual;
WOCHENTAG
--------Mittwoch
Rechnen mit Datum:
select sysdate + 1 from dual;
-> morgen um die gleiche Zeit
select sysdate + 1/2 from dual; -> in 12 Stunden
Funktion substr:
select substr('ABCDEF', 2,3) von_vorne,
substr('ABCDEF', -3,2) von_hinten
from dual;
VON_VORNE VON_HINTEN
-------------------BCD
DE
6