Tutorübung zu Grundlagen Datenbanken

(1)

David Dejori

Tutorübung zu Grundlagen Datenbanken

(2)

• Für SQL-Aufgaben am besten ein lokales Datenbanksystem aufsetzen

• Lehrstuhl empfiehlt PostgreSQL (postgresql.org)

(the world’s most advanced open source relational database)

• Installation und Einrichtung: siehe Zusatzaufgabe 5

• Bei Schwierigkeiten bzw. Problemen à Tutorsprechstunde

(jeweils mittwochs von 13:00 bis 16:00 in 02.09.011B)

• Alternative zu lokalem Datenbanksystem: HyPer (hyper-db.de)

(Datenbanksystem des Lehrstuhls, Unischema bereits importiert)

• Folien, SQL-Skript zum Unischema und aktueller Punktestand: daviddejori.com/gdb

2 David Dejori | Tutorübung zu Grundlagen Datenbanken | Wintersemester 2019/20

Organisatorisches – Lokales Datenbanksystem

(3)

psql <datenbank> öffnet eine Datenbank in der PostgreSQL-Konsole Innerhalb der PostgreSQL-Konsole gibt es u. a. diese zwei wichtigen Befehle:

(display tables)

• \dt listet alle Tabellen auf

(display)

• \d <tabelle> zeigt das Schema einer Tabelle sowie deren Schlüssel, Constraints, Indizes, Triggers und Beziehungen mit anderen Tabellen an

(\d+ zeigt einige weitere Informationen)

Die wichtigsten PostgreSQL-Befehle

(4)

(a) Übertragen Sie das ER-Modell des Onlineshops aus Blatt 4 in ein relationales Schema.

(b) Verfeinern Sie das relationale Schema soweit möglich durch Eliminierung von Relationen.

Hinweis: 1:1- und 1:N-Beziehungen können immer aufgelöst werden.

(c) Finden Sie für die Attribute sinvolle und von Postgres unterstützte Datentypen.

Hausaufgabe 1

(5)

Anwesenheitskontrolle

(6)

Formulieren Sie folgende Anfragen auf dem bekannten Universitätsschema in SQL. Geben Sie alle Ergebnisse duplikatfrei aus.

(a) Finden Sie die Studenten, die den Professor Sokrates aus Vorlesung(en) kennen.

(b) Finden Sie die Studenten, die Vorlesungen hören, die auch Fichte hört.

(c) Finden Sie die Assistenten von Professoren, die den Studenten Carnap unterrichtet haben – z.B. als potentielle Betreuer seiner Bachelorarbeit.

(d) Geben Sie die Namen der Professoren an, die Theophrastos aus Vorlesungen kennt.

(e) Welche Vorlesungen werden von Studenten im Bachelorstudium (1. – 6. Semester) gehört? Geben Sie die Titel dieser Vorlesungen an.

(f) Bestimmen Sie für jede Vorlesung wie viele Studenten diese hören. Geben Sie auch Vorle- sungen ohne Hörer aus. Sortieren Sie das Ergebnis absteigend nach Anzahl der Hörer.

Hausaufgabe 2

(7)

Finden Sie die Studenten, die den Professor Sokrates aus Vorlesung(en) kennen.

SELECT DISTINCT s.*

FROM studenten s NATURAL JOIN hoeren

NATURAL JOIN vorlesungen v

JOIN professoren p ON v.gelesenvon = p.persnr WHERE p.name = 'Sokrates';

oder

SELECT DISTINCT s.*

FROM studenten s, hoeren h, vorlesungen v, professoren p WHERE h.matrnr = s.matrnr

AND v.vorlnr = h.vorlnr

AND p.persnr = v.gelesenvon AND p.name = 'Sokrates';

Hausaufgabe 2 (a)

(8)

Hausaufgabe 2

(9)

Finden Sie die Studenten, die Vorlesungen hören, die auch Fichte hört.

SELECT DISTINCT s1.*

FROM studenten s1

NATURAL JOIN hoeren h1

JOIN hoeren h2 ON h1.vorlnr = h2.vorlnr JOIN studenten s2 ON h2.matrnr = s2.matrnr AND s1.matrnr <> s2.matrnr WHERE s2.name = 'Fichte';

oder SELECT DISTINCT s1.*

FROM studenten s1, studenten s2, hoeren h1, hoeren h2 WHERE s1.matrnr = h1.matrnr

AND h1.vorlnr = h2.vorlnr AND h2.matrnr = s2.matrnr

AND s1.matrnr <> s2.matrnr AND s2.name = 'Fichte';

Hausaufgabe 2 (b)

(10)

Hausaufgabe 2

(11)

Finden Sie die Assistenten von Professoren, die den Studenten Carnap unterrichtet haben.

SELECT DISTINCT a.*

FROM assistenten a

JOIN professoren p ON a.boss = v.gelesenvon JOIN hoeren h ON v.vorlnr = h.vorlnr

JOIN studenten s ON h.matrnr = s.matrnr WHERE s.name = 'Carnap';

oder SELECT DISTINCT a.*

FROM assistenten a, vorlesungen v, hoeren h, studenten s WHERE a.boss = v.gelesenvon

AND v.vorlnr = h.vorlnr AND h.matrnr = s.matrnr AND s.name = 'Carnap';

Hausaufgabe 2 (c)

(12)

Hausaufgabe 2

(13)

Geben Sie die Namen der Professoren an, die Theophrastos aus Vorlesungen kennt.

SELECT DISTINCT p.*

FROM professoren p

JOIN vorlesungen v ON p.persnr = v.gelesenvon JOIN hoeren h ON v.vorlnr = h.vorlnr

JOIN studenten s ON h.matrnr = s.matrnr WHERE s.name = 'Theophrastos';

oder

SELECT DISTINCT p.*

FROM professoren p, vorlesungen v, hoeren h, studenten s WHERE p.persnr = v.gelesenvon

AND v.vorlnr = h.vorlnr AND h.matrnr = s.matrnr

AND s.name = 'Theophrastos';

Hausaufgabe 2 (d)

(14)

Hausaufgabe 2

(15)

Welche Vorlesungen werden von Studenten im Bachelorstudium (1. – 6. Semester) gehört?

Geben Sie die Titel dieser Vorlesungen an.

SELECT DISTINCT titel FROM vorlesungen

NATURAL JOIN hoeren NATURAL JOIN studenten

WHERE semester BETWEEN 1 AND 6;

oder

SELECT DISTINCT v.titel

FROM vorlesungen v, hoeren h, studenten s WHERE v.vorlnr = h.vorlnr

AND h.matrnr = s.matrnr

AND s.semester BETWEEN 1 AND 6;

Hausaufgabe 2 (e)

(16)

Hausaufgabe 2

(17)

Bestimmen Sie für jede Vorlesung wie viele Studenten diese hören. Geben Sie auch

Vorlesungen ohne Hörer aus. Sortieren Sie das Ergebnis absteigend nach Anzahl der Hörer.

SELECT v.vorlnr, v.titel, COUNT(h.matrnr) -- oder COUNT(h.*) FROM vorlesungen v

LEFT JOIN hoeren h ON v.vorlnr = h.vorlnr GROUP BY v.vorlnr, v.titel

ORDER BY count DESC;

Wird durch eine Aggregatfunktion eine neue Spalte erzeugt, so erhält diese den Namen der Aggregatfunktion in Kleinbuschtaben. Als Alternative kann man mit

COUNT(h.matrnr) hoerer oder COUNT(h.matrnr) AS hoerer

der Spalte einen neuen Namen geben und über diesen auf die Spalte der Aggregatfunktion zugreifen, z. B.

ORDER BY hoerer DESC.

Hausaufgabe 2 (f)

(18)

Bestimmen Sie für alle Studenten eine gewichtete Durchschnittsnote ihrer Prüfungen. Die Gewichtung der einzelnen Prüfungen erfolgt gemäß dem Vorlesungsumfang (SWS). Dies entspricht dem Verfahren der Durchschnittsnotenberechnung für Ihr Bachelor-Zeugnis.

Benutzen Sie die Relation pruefenxl statt pruefen.

Durchschnittsnote = , wobei über alle geschriebenen Prüfungen summiert wird.

WITH pruefenxl AS ( SELECT *

FROM pruefen UNION ALL VALUES

(25403, 5049, 2126, 1), (26120, 5001, 2137, 1), (26120, 5043, 2126, 3), (26120, 5052, 2126, 4), (26120, 4630, 2137, 1)

)

SELECT ...

Hausaufgabe 3

(19)

Bestimmen Sie für alle Studenten eine gewichtete Durchschnittsnote ihrer Prüfungen.

WITH pruefenxl AS ( SELECT *

FROM pruefen UNION

ALL VALUES

(25403, 5049, 2126, 1), (26120, 5001, 2137, 1), (26120, 5043, 2126, 3), (26120, 5052, 2126, 4), (26120, 4630, 2137, 1)

)

SELECT matrnr, name, SUM(note * sws) / SUM(sws) FROM studenten

NATURAL JOIN pruefenxl NATURAL JOIN vorlesungen GROUP BY matrnr, name

Hausaufgabe 3

(20)

Folgender Ausdruck im Tupelkalkül gibt alle Studenten aus, die alle von ihnen gehörten Vorlesungen bestanden haben.

{ s | s ∈ Studenten ∧

∀ h ∈ hoeren (h.MatrNr = s.MatrNr ⇒

∃ p ∈ pruefen (p.MatrNr = s.MatrNr ∧ p.VorlNr = h.VorlNr ∧ p.Note ≤ 4)) } Übersetzen Sie die Abfrage nun in SQL. Da SQL keine Allquantoren und Implikationen unterstützt, müssen Sie diese zunächst umformen.

(a) Formen Sie den Ausdruck in einen äquivalenten um, der keine Implikationen oder Allquantoren verwendet.

(b) Übersetzen Sie den so erlangten Ausdruck in SQL.

Hausaufgabe 4

(21)

{ s | s ∈ Studenten ∧ ∀ h ∈ hoeren (h.MatrNr = s.MatrNr ⇒

∃ p ∈ pruefen (p.MatrNr = s.MatrNr ∧ p.VorlNr = h.VorlNr ∧ p.Note ≤ 4)) }

1. Implikation umformen A ⇒ B ⇔ ¬A ∨ B

{ s | s ∈ Studenten ∧ ∀ h ∈ hoeren (h.MatrNr ≠ s.MatrNr ∨

∃ p ∈ pruefen (p.MatrNr = s.MatrNr ∧ p.VorlNr = h.VorlNr ∧ p.Note ≤ 4)) } 2. Allquantor durch negierten Existenzquantor ersetzen ∀x P(x) ⇔ ¬∃x ¬P(x) { s | s ∈ Studenten ∧ ¬∃ h ∈ hoeren (¬(h.MatrNr ≠ s.MatrNr ∨

∃ p ∈ pruefen (p.MatrNr = s.MatrNr ∧ p.VorlNr = h.VorlNr ∧ p.Note ≤ 4))) } 3. De Morgansches Gesetz anwenden ¬(A ∨ B) ⇔ ¬A ∧ ¬B { s | s ∈ Studenten ∧ ¬∃ h ∈ hoeren (h.MatrNr = s.MatrNr ∧

¬∃ p ∈ pruefen (p.MatrNr = s.MatrNr ∧ p.VorlNr = h.VorlNr ∧ p.Note ≤ 4)) }

Hausaufgabe 4 (a)

(22)

Übersetzten Sie den folgenden Ausdruck vom Tupelkalkül in eine SQL-Abfrage.

{ s | s ∈ Studenten ∧¬∃ h ∈ hoeren (h.MatrNr = s.MatrNr ∧

¬∃ p ∈ pruefen (p.MatrNr = s.MatrNr

SELECT * ∧ p.VorlNr = h.VorlNr ∧ p.Note ≤ 4)) } FROM studenten s

WHERE NOT EXISTS ( SELECT *

FROM hoeren h

WHERE h.matrnr = s.matrnr AND NOT EXISTS ( SELECT *

FROM pruefen p

WHERE p.matrnr = s.matrnr AND p.vorlnr = h.vorlnr AND p.note <= 4

) )

Tutorübung zu Grundlagen Datenbanken