Probeklausur_DBI_mit.. - Praktische Informatik

Probeklausur zur Vorlesung Datenbanksysteme I
05.02.2008
Fachbereich 12 – Elektrotechnik und Informatik
Fachgruppe Praktische Informatik
Universität Siegen
Name, Vorname:_____________________________________________________________
Aufgabe 1: SQL (25 P.)
a.) Ermitteln Sie die Nachnamen aller Kunden, die eine Bonitaet von 3 haben. (1P.)
LÖSUNG:
SELECT nachname
FROM person NATURAL JOIN kunde
WHERE bonitaet = 3;
b.) Wieviele verschiedene Orte sind in der Tabelle ort enthalten? (2P.)
LÖSUNG:
SELECT count(*) FROM ort;
Annahme: alle Tupel in Tabelle ort stehen für verschiedene Orte.
c.) Ermitteln Sie die Vor- und Nachnamen aller Kunden, die im Dortmund wohnen. (3P.)
LÖSUNG:
SELECT vorname, nachname
FROM kunde NATURAL JOIN person NATURAL JOIN ort
WHERE name = 'Dortmund';
Hinweis: Obwohl keine Attribute von Kunde gebracht werden, muss der Verbund zur Kunde
gebildet werden, weil somit auch Personen, die keine Kunden (Mitarbeitern) sind, ausgegeben
werden würden.
d.) Ermitteln Sie die Personen-Nummern der Mitarbeiter mit dem Vornamen Sven. (3P.)
LÖSUNG:
SELECT person.pers_nr
FROM person, mitarbeiter
WHERE vorname = 'Sven'
AND person.pers_nr = mitarbeiter.pers_nr;
e.) Ermitteln Sie die Nachnamen aller Kunden, die von einem Mitarbeiter, der in Dortmund
wohnt, betreut werden. (4P.)
LÖSUNG:
SELECT K.nachname
FROM kunde, person K, mitarbeiter, person M, ort
WHERE kunde.pers_nr = K.pers_nr
AND mitarbeiter.pers_nr = M.pers_nr
AND kunde.betreuer = M.pers_nr
AND M.plz = ort.plz
AND ort.name = 'Dortmund';
Hinweis: In der Bedingung spielen 2 verschiedene Personen eine Rolle: der Kunde und ein
Mitarbeiter. Für jede diese Rollen muss eine eigene Tupel variable deklariert werden (der
Relation Person nur 1x in den Verbund aufzunehmen reicht nicht aus)
f.) Finden Sie die Anzahl aller Aktienverkäufe am 5.2.2008 heraus. (2P.)
LÖSUNG:
SELECT count (*)
FROM verkauf
WHERE datum='5.5.2005';
g.) Ermitteln Sie den durchschnittlichen Kontostand aller Depotinhaber pro Ort, absteigend
nach Durchschnitt sortiert. (3P.)
LÖSUNG:
SELECT AVG(saldo) AS durchschnitt, ort.plz
FROM person, konto, ort
WHERE person.plz = ort.plz
AND person.pers_nr = konto.pers_nr
GROUP BY ort.name
ORDER BY durchschnitt desc;
Hinweis: Es reicht nicht aus, nur nach PLZ zu gruppieren (und der Verbund mit ort
wegzulassen), weil ein Or5t mehrer PLZ-Bereiche haben kann.
h.) Finden Sie das Konto mit dem höchsten Saldo (bei Gleichheit: alle Konten mit dem
höchsten Saldo); geben Sie den Saldo und den Nachnamen des Inhabers aus. (3P.)
LÖSUNG:
SELECT person.nachname, konto.saldo
FROM konto NATURAL JOIN person
WHERE saldo >= all (
SELECT saldo
from konto );
i.) Bestimmen Sie die Nummern aller Depots, die zwei oder mehr Aktienpositionen mit
jeweils mehr als 50 Stück einer Aktie erhalten. (4P.)
LÖSUNG:
SELECT depot_nr
FROM aktien_position NATURAL JOIN depot
WHERE anzahl > 50
GROUP BY depot_nr
HAVING count(*) > 1;
Aufgabe 2: Indexe und B-Bäume (4 P.)
a.) Was ist der Unterschied zwischen einem Primär- und Sekundärindex?
Skizzieren Sie kurz und prägnant, woraus ein Sekundärindex besteht.
Welche Art von Index liegt bei einem B- bzw. B*-Baum vor? (3 P.)
LÖSUNG:
Primär = eingebettet in PrimärDaten, Sekundärindex = davon getrennt und später löschbar
oder hinzufügbar.
Sekundärindex enthält für jeden auftretenden Sekundärschlüsselwert eine Liste von
Referenzen auf die Tupel, in denen dieser Sekundärschlüsselwert auftritt.
B- bzw. B*-Baum = Primärindex
b.) Wieviele Verweise auf Unterbäume enthält ein Knoten in einem B-Baum der Ordnung
m minimal bzw. maximal? (1 P.)
LÖSUNG:
Wurzel: minimal 0, maximal 2*m + 1;
Nichtwurzel: minimal m + 1, maximal 2*m + 1;
Aufgabe 3: Funktionale Abhängigkeiten, Normalisierung (10 P.)
Gegeben sei das Relationenschema R = ( Q, W, E, R, T, Z ) mit der Menge der funktionalen
Abhängigkeiten
F = {Q -> Z; R -> ET; QZ -> E; W -> E; T -> E; Q -> W}
a.) Zeichnen Sie die graphische Darstellung der Abhängigkeiten. (1 P.)
b.) Ist dieses Schema in dritter Normalform? Erfüllt es das Boyce-Codd-Kriterium? Falls
nicht, geben Sie eine funktionale Abhängigkeit an, die das Kriterium verletzt. (2 P.)
LÖSUNG:
nicht in 3NF, u.a. wegen T -> E
nicht in 3NF -> nicht in BCNF
c.) Ermitteln Sie eine kanonische Überdeckung Fc für F. (4 P.)
LÖSUNG:
Q -> ZW; R -> T; T -> E; W -> E
d.) Bestimmen Sie einen Identifizierungsschlüssel für R. (1 P.)
LÖSUNG:
QR
e.) Geben Sie eine 3NF-Zerlegung für R an. (2 P.)
Aufgabe 4: Grundkonzepte von JDBC (7P.)
a.) Beschreiben Sie, welche Objekte erzeugt werden müssen und welche Operationen
aufgerufen werden müssen (ohne Fehlerbehandlung), bevor Sie in einem Java-Programm
mittels JDBC eine Abfrage auf einer Datenbank ausführen können. (3P.)
LÖSUNG:
Class.forName('jdbc-Driver'); conn = java.sql.DriverManager.getConnection; Statement stmt
= conn.createStatement (); ResultSet r = stmt.executeQuery (' text der Query ')
b.) Mit welcher Operation können Sie mittels JDBC eine Abfrage ausführen? Welche Typen
haben die Argumente und der Rückgabewert dieser Operation? (1P.)
LÖSUNG:
ResultSet r = stmt.executeQuery (' text der Query ')
c.) Das Ergebnis einer Abfrage ist stets eine Tabelle. Kann der Compiler den Relationentyp
des Ergebnisses stets zur Compile-Zeit voraussagen? (1P.)
LÖSUNG:
Nein, denn die Abfrage kann Projektionen enthalten, die wird als Text übergeben, und der
Text kann dynamisch erstellt werden und sogar von Benutzereingaben abhängen.
d.) Beschreiben Sie entweder verbal (oder durch ein sinnvoll kommentiertes
Beispielprogramm), wie man die einzelnen Attributwerte der einzelnen Tupel im Ergebnis
einer Abfrage in ein Java-Programm übernimmt (ohne Fehlerbehandlung). (2P.)
LÖSUNG:
(a) iterieren über alle Tupel im resultset
(b) jedes einzelne Attribut anhand des Typs mit einer getXX ( 'attributname' ), XX der
Typ, holen.
Tabellen-Schemata für Aufgabe 1
CREATE TABLE ort (
plz
int,
name
varchar(20) not null,
bu_land
varchar(25) not null,
primary key(plz) );
CREATE TABLE person (
pers_nr
int,
plz
int,
anrede
varchar(5),
vorname
varchar(30),
nachname
varchar(30) not null,
strasse
varchar(30) not null,
hausnummer int not null,
primary key(pers_nr) );
CREATE TABLE mitarbeiter (
pers_nr
int,
arbeitszeit int default 100,
status
varchar(10),
foreign key (pers_nr)
references person (pers_nr) );
CREATE TABLE kunde (
pers_nr
int,
bonitaet
int default 2,
betreuer
int
references mitarbeiter(pers_nr),
foreign key(pers_nr)
references person(pers_nr) );
CREATE TABLE konto (
pers_nr
int,
kto_nr
int,
saldo
real,
kredit_limit real,
primary key (pers_nr, kto_nr),
foreign key (pers_nr)
references person (pers_nr) );
CREATE TABLE einzahlung (
pers_nr
int,
kto_nr
int,
betrag
real not null,
datum
date default current_date,
foreign key (pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE auszahlung (
pers_nr
int,
kto_nr
int,
betrag
real not null,
datum
date default current_date,
foreign key (pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE ueberweisung (
von_pers_nr int,
von_kto_nr int,
an_pers_nr int,
an_kto_nr
int,
verwendungszweck varchar(27),
betrag
real not null,
datum
date default current_date,
foreign key (von_pers_nr, von_kto_nr)
references konto (pers_nr, kto_nr),
foreign key (an_pers_nr, an_kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE depot (
pers_nr
int,
depot_nr
int,
primary key (pers_nr, depot_nr),
foreign key (pers_nr)
references person (pers_nr) );
CREATE TABLE aktie (
name
varchar(50) primary key,
kurs
real );
CREATE TABLE aktien_position (
pos_nr
int,
depot_nr
int,
aktien_name varchar(50),
anzahl
int default 0,
primary key (pos_nr),
foreign key (aktien_name)
references aktie (name),
foreign key (depot_nr)
references depot (depot_nr) );
CREATE TABLE kauf (
depot_pers_nr int,
depot_nr
int,
pos_nummer int,
kto_pers_nr int,
kto_nr
int,
anzahl
int NOT NULL,
datum
date default current_date,
foreign key (depot_pers_nr, depot_nr,
pos_nummer) references
aktien_position (pers_nr, depot_nr, nummer),
foreign key (kto_pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE verkauf (
depot_pers_nr int,
depot_nr
int,
pos_nummer int,
kto_pers_nr int,
kto_nr
int,
kurs
real,
anzahl
int not null,
datum
date default current_date,
foreign key (depot_pers_nr, depot_nr,
pos_nummer) references
aktien_position (pers_nr, depot_nr, nummer),
foreign key (kto_pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );