DABA Probeklausur

(mögliche) Probeklausur Datenbanken
by Igor
(nach Überlieferungen)
Themen in letzter Prüfung : Autoteile und Ärzte
Allgemeine Hinweise:
Die Klausur ist in 3 große Aufgaben, jeweils mit mehreren Teilaufgaben,
unterteilt.
Bei der Bearbeitung ist zu beachten:
 Hilfsmittel sind nicht zugelassen.
 Es stehen insgesamt 90 Minuten zur Verfügung.
 Alle 3 Aufgaben sind zu bearbeiten, so dass für jede Aufgabe ungefähr
30 Minuten veranschlagt werden sollten.
 Die Aufgaben sind auf dem dafür vorgesehenen freien Platz nach den
Fragen bzw. direkt in den Bildern zu beantworten (ggf. sind noch
Rückseiten der Blätter oder spezielle Zusatzbögen zu benutzen).
 Kurze Antworten mit den wesentlichen Stichworten genügen!
Beispiel: „alles gewusst gute Note“ ist besser als „Da ich alles
wusste, konnte der Professor nicht umhin, mir eine gute Note zu
geben“!
 Geben Sie auf Ihrem Exemplar der Klausuraufgaben sowie ggf. auf
Konzeptbögen unbedingt Name und Matr.Nr. an!
Die Bewertung geschieht wie folgt:
 Insgesamt sind 90 Punkte zu erreichen.
 Pro Aufgabe sind 30 Punkte zu erreichen.
 Etwaige Zusatzbögen sind mit abzugeben!
1
Aufgabenbereich 1: Modellierung und das rel.
Datenbankmodell (30 Punkte)
a) Erstellen Sie zu dem folgenden Sachverhalt ein logisches Modell (Tabellenschema bzw. ERDiagramm) (15 Punkte)
Für eine neue Couchsurfing-Website soll eine Datenbank entwickelt werden.
In dieser sollen alle Teilnehmer mit ihrer E-Mail Adresse gespeichert werden, die
auch zur Identifikation dient. Außerdem sollen der Name, die Adresse und das
Geburtsdatum jedes Teilnehmers erfasst werden. Ein Eintrag BietetCouch sagt
aus, ob der jeweilige Teilnehmer eine Couch zum Übernachten anbietet oder nur
Übernachtungsmöglichkeiten sucht.
Es übernachtet immer ein Teilnehmer bei einem anderen. Mehrere Personen sind
nicht vorgesehen. Natürlich kann auch ein Teilnehmer, der eine Couch anbietet, bei
jemand anderem übernachten.
Zu jeder Übernachtung wird erfasst, wann diese stattfindet und für wie viele
Nächte. Außerdem kann eine Übernachtung verschiedene Status haben, je
nachdem ob die Übernachtung angefragt, abgelehnt, gebucht, abgeschlossen oder
abgesagt ist Zur leichteren Auswertung wird jedem Status eine eindeutige Nummer
zugeordnet.
Anschließend ist es möglich, Bewertungen abzugeben. Der Übernachtende
bewertet denjenigen, der die Couch angeboten hat und andersherum. Durch die
Bewertungen erlangt jeder Teilnehmer einen bestimmten Ruf, der auch in der
Datenbank vermerkt wird.
Lösung:
b) Stellen Sie die Tabelle Teilnehmer in mathematische Formulierung mit einer möglichen Relation
dar. (Typbeschreibung + Relation) und stellen sie die mögl. Wertebereich von BieteCouch und Ruf,
sowie zuletzt das Kreuzprodukt dar. (7 Punkte)

Typbeschreibung: Teilnehmer(E-Mail, Pseudo, Name, Adresse, Gebdat, BeiteCouch, Ruf)

Relation: R = { ([email protected], MM, Mad Max, Hauptstr. 1 89077 Ulm, 01.02.1990, Böse)}

Wertebereiche:
2
Dom[BieteCouch] = {ja, nein}

Dom[Ruf] = {gut, mittel, schlecht}
Kreuzprodukt: Dom[BieteCouch]x Dom[Ruf] = { (ja,gut),(ja,mittel),(ja,schlecht),(nein,gut),
(nein,mittel), (nein,schlecht)}
c) Integritätsbedingungen (8 Punkte)
Beantworten Sie folgende Fragen:
1. Welche Integritätsbedingungen sind in dem aus TabA und TabB bestehenden
Schema verletzt? Woran erkennt man die Verletzung?
2. Was müsste in Tabelle TabB geschehen, wenn in Tabelle TabA der Datensatz mit
idA = 1 gelöscht würde? Welche Möglichkeiten gibt es damit umzugehen?
Lösung:
1.


TabA: Verletzung der Entitätsintegrität. Dies erkennt man daran, dass das
Primärschlüsselattribut "idA" den Wert NULL hat.
TabB: Verletzung der Referentiellen Integrität. Dies erkennt man daran, dass der Wert "3"
des Fremdschlüsselattributs "idA" in der referenzierten Tabelle TabA nicht existiert
2.
Hier können drei verschiedene Möglichkeiten definiert werden:
 Restrict: Der Datensatz kann in TabA gar nicht gelöscht werden, solange dieser noch von
anderen Tabellen, hier TabB, referenziert wird.
 Cascade: Die referenzierende Zeile aus TabB (d.h. die zweite Zeile) wird auch gelöscht.
 Set NULL: Das Fremdschlüsselattribut "idA" der referenzierenden Zeile aus TabB wird auf
NULL gesetzt.
3
Aufgabenbereich 2: Transformationsregeln und
Normalisierung (30 Punkte)
a) Bitte geben Sie zu jeder der folgenden funktionalen Abhängigkeiten an, ob diese in der obigen Tabelle
tatsachlich bestehen oder nicht. Falls nein, geben Sie jeweils mindestens zwei Datensätze an, welche eine
entsprechende funktionale Abhängigkeit widerlegen. (10 Punkte)
Lösung (Datensätze angeben!):
b)
Überführen Sie das nachfolgende Datenblatt in ein Relationales Datenbankmodell (tabellarisch) in der 3NF.
Bitte achten Sie auch darauf, dass in Ihrem Relationalen Datenbankmodell keine komplexen (bzw.
zusammengesetzten) Attribute, wie z. B. "Name", mehr vorliegen. (20 Punkte)
4
Lösung:
1. NF
Mehrwertige / Zusammengesetzte Attribute?
 Attribut "Teilnehmer" (MatNr bis Klausur) → mehrwertig!
 Name, Adresse → zusammengesetzt!
2. NF
Partielle Abhängigkeiten?
 Fach/Dozent partiell abhängig von VorlNr => auslagern in eigene Tabelle „Vorlesung“
 Vorname, Nachname, ..., StGLeiter partiell abhängig von MatNr => auslagern in eigene Tabelle „Student“
 Note voll abhängig (eine Note je Vorlesung und Student) => Umbenennen der Tabelle in „Klausur“
5
3. NF
Transitive Abhängigkeiten?
 StGName und StGLeiter sind von der StGNr funktional abhängig und damit transitiv von MatNr die 3. NF ist
dadurch verletzt

Die beiden Attribute werden in eine eigene Tabelle „Studiengang“ ausgelagert und mit der StGNr als
Schlüssel versehen.
6
Aufgabenbereich 3: SQL (30 Punkte)
Teil 1) DRL (20 Punkte)
a) Zeigen Sie alle Artikel mit Hersteller, Bezeichnung und
Listenpreis. Sortieren Sie das Ergebnis nach dem Hersteller. Bei
gleichem Hersteller soll absteigend nach dem Listenpreis sortiert
werden, ansonsten ist die Bezeichnung ausschlaggebend.
Lösung:
SELECT hersteller, bezeichnung, listenpreis
FROM Artikel
ORDER BY hersteller, listenpreis DESC, bezeichnung;
b) Wie viele verschiedene Orte gibt es?
SELECT count(DISTINCT ort) AS AnzahlOrte
FROM Kunde;
c) Wie viele Artikel wurden bisher insgesamt geliefert?
SELECT SUM(liefermenge) AS Gesamtliefermenge
FROM bestellung_artikel;
d) Was ist der durchschnittliche Listen- und Einkaufspreis für
Sony-Produkte?
SELECT AVG(listenpreis) AS AvgListenpreis,
AVG(einkaufspreis) AS AvgEinkaufspreis
7
FROM Artikel
WHERE hersteller = 'Sony';
e) Welche Kunden wurden von jemandem geworben?
SELECT kunden_nr, vorname, nachname
FROM Kunde
WHERE geworben_von IS NOT NULL;
f) Welche Blue-Ray Player gibt es und wer stellt sie her?
Zeigen Sie für jeden Hersteller die teuersten Produkte zuerst.
SELECT *
FROM Artikel
WHERE bezeichnung LIKE '%Blue-Ray%'
ORDER BY hersteller, listenpreis DESC;
g) An welchem Tag wurde am meisten bestellt?
Bezogen auf die Anzahl der Bestellungen:
SELECT bestelldatum, COUNT(bestelldatum) AS AnzahlBestellungen
FROM Bestellung
GROUP BY bestelldatum
ORDER BY AnzahlBestellungen DESC;
h) Von welchen Artikeln wurde am meisten bestellt? Listen Sie nur
die Artikel auf, bei denen die Gesamtbestellmenge größer oder
gleich 5 ist.
SELECT artikel_nr, SUM(bestellmenge) AS Gesamtmenge
FROM bestellung_artikel
GROUP BY artikel_nr
HAVING SUM(bestellmenge) >= 5
ORDER BY Gesamtmenge DESC;
i) Gibt es einen Tag im Jahr 2011 an dem mehr als eine Bestellung
einging?
SELECT bestelldatum, COUNT(bestell_nr) AS AnzBestellungen
FROM bestellung
WHERE bestelldatum BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY bestelldatum
HAVING COUNT(bestell_nr) > 1
ORDER BY 2 DESC;
j) Welche Bestellungen wurden von Kunden
aus Ulm oder Stuttgart aufgegeben?
SELECT bestell_nr, ort
FROM Bestellung AS b, Kunde AS k
WHERE b.kunden_nr = k.kunden_nr
AND (ort = 'Stuttgart' OR ort = 'Ulm');
n)
k) Welche Bestellungen enthalten Produkte von HTC?
Wieviele wurden davon jeweils geliefert?
SELECT bestell_nr, SUM(liefermenge) AS HTCMenge
FROM bestellung_artikel ba
INNER JOIN Artikel a
ON a.artikel_nr = ba.artikel_nr
8
WHERE hersteller = 'HTC'
GROUP BY bestell_nr;
l) Listen Sie nun diese Bezirke auf und zeigen Sie,
wie viel jeweils dorthin geliefert wurde.
SELECT PLZ, SUM(liefermenge) AS Gesamtliefermenge
FROM Kunde AS k
INNER JOIN Bestellung AS b ON k.kunden_nr = b.kunden_nr
INNER JOIN bestellung_artikel AS ba ON b.bestell_nr =
ba.bestell_nr
WHERE lieferdatum BETWEEN '2011-07-01' AND '2011-12-31'
AND liefermenge > 0
GROUP BY PLZ;
m) Wie viele TV-Geräte wurden bisher insgesamt bestellt?
(Lösen Sie die Aufgabe ohne Joins)
SELECT SUM(bestellmenge) AS BestellteTVGeraete
FROM bestellung_artikel
WHERE artikel_nr IN
(
SELECT artikel_nr
FROM Artikel
WHERE bezeichnung LIKE '%TV%'
);
Teil 2) DML (10 Punkte)
a)
Legen Sie eine Tabelle Mitarbeiter an, die folgende Spalten enthält:
mitarbeiter_nr, nachname, vorname, geboren_am, aktiv.
Vergeben Sie geeignete Datentypen und legen Sie den Primärschlüssel fest. Eintragungen in diese Tabelle
müssen immer vollständig sein. Die Spalte aktiv gibt an, ob ein Mitarbeiter aktiv ist (1) oder nicht (0). Neue
Mitarbeiter sind immer aktiv.
CREATE TABLE Mitarbeiter
(
mitarbeiter_nr INTEGER PRIMARY KEY AUTO_INCREMENT, nachname VARCHAR(50) NOT NULL,
vorname VARCHAR(50) NOT NULL,
geboren_am DATE NOT NULL, aktiv BIT NOT NULL DEFAULT 1
);
b)
(Mehrere SQL Anweisungen)
Legen Sie eine Tabelle Sprache an, die verschiedene Sprachen mit einem dazugehörigen Kürzel enthält. Jede
Sprache soll durch ihr Kürzel identifiziert werden und ist in ihrer Bezeichnung eindeutig.
Vergeben Sie geeignete Datentypen und legen Sie den Primärschlüssel fest. Legen Sie außerdem folgende
Datensätze an:
de Deutsch
en Englisch
fr Französisch
9
CREATE TABLE Sprache
(
kuerzel VARCHAR(5) PRIMARY KEY,
bezeichnung VARCHAR(50) UNIQUE
);
INSERT INTO Sprache VALUES('de', 'Deutsch');
INSERT INTO Sprache VALUES('en', 'Englisch');
INSERT INTO Sprache VALUES('fr', 'Französisch');
c)
Ändern Sie den Eintrag „Englisch“ in der Tabelle Sprache in „UK-Englisch“.
UPDATE Sprache
SET bezeichnung = ‘UK-Englisch‘
WHERE kuerzel = ‘en‘;
d)
Löschen Sie alle Einträge aus der Tabelle
DELETE FROM Sprache;
10