Tutorübung zu Grundlagen Datenbanken

(1)

David Dejori

Tutorübung zu Grundlagen Datenbanken

(2)

(a) Geben Sie DDL-Anweisungen an, welche die für den Onlineshop notwendigen Relationen erstellen.

(b) Ergänzen Sie Ihre DDL-Anweisungen um die Angabe der Primär- und Fremdschlüsseln.

(c) Erweitern Sie Ihre DDL-Anweisungen so, dass die oben genannten Anforderungen erfüllt werden. Das bedeutet also, dass Kunden gelöscht werden können, ihre Bestellungen aber anonymisiert im System gespeichert bleiben.

(d) Fügen Sie Ihrer Datenbank nun mittels DML-Anweisungen die minimale Anzahl an Tupeln hinzu, sodass es einen Kunden und eine ihm zugeordnete Bestellung gibt. Dabei muss sicher gestellt sein, dass keine referentiellen Integritätsbedingungen verletzt werden.

(e) Erstellen Sie ein SQL-Skript, welches alle Tabellen in einer neuen Datenbank anlegt. Sollte die Datenbank bereits Tabellen mit gleichem Namen enthalten, so sollen diese zunächst restlos gelöscht werden.

Hausaufgabe 1

(3)

CREATE TABLE Kontinente (

KontinentenNr INT PRIMARY KEY, Name TEXT NOT NULL

);

CREATE TABLE Laender (

LaenderCode INT PRIMARY KEY, Name TEXT NOT NULL,

KontinentenNr INT REFERENCES Kontinente );

Hausaufgabe 1 (a) (b)

(4)

CREATE TABLE Lieferanten (

LieferantenNr INT PRIMARY KEY, Name TEXT,

Adresse TEXT,

LaenderCode INT REFERENCES Laender );

CREATE TABLE Kunden (

KundenNr INT PRIMARY KEY, Name TEXT,

Adresse TEXT,

LaenderCode INT REFERENCES Laender, Geburtsdatum DATE,

Geschlecht CHAR(1) );

Hausaufgabe 1 (a) (b)

(5)

CREATE TABLE liefertNach (

LieferantenNr INT REFERENCES Lieferanten, LaenderCode INT REFERENCES Laender,

PRIMARY KEY (LieferantenNr, LaenderCode) );

CREATE TABLE bereitstellen (

ArtikelNr INT REFERENCES Artikel,

LieferantenNr INT REFERENCES Lieferanten, VerfuegbareAnzahl INT,

Lieferpreis MONEY,

PRIMARY KEY (ArtikelNr, LieferantenNr) );

Hausaufgabe 1 (a) (b)

(6)

CREATE TABLE Artikel (

ArtikelNr INT PRIMARY KEY, Name TEXT,

Marke TEXT,

Typ TEXT,

UVP MONEY );

CREATE TABLE Bestellungen (

BestellNr INT PRIMARY KEY,

KundenNr INT REFERENCES Kunden, Gesamtstatus TEXT,

Gesamtpreis MONEY, Datum DATE );

Hausaufgabe 1 (a) (b)

(7)

CREATE TABLE Bestellpositionen (

BestellNr INT REFERENCES Bestellungen, ArtikelNr INT REFERENCES Artikel,

LieferantenNr INT REFERENCES Lieferanten, Position INT,

Anzahl INT,

Preis MONEY,

Steuern MONEY, Status TEXT,

PRIMARY KEY (Position, BestellNr),

FOREIGN KEY (ArtikelNr, LieferantenNr) REFERENCES bereitstellen );

Hausaufgabe 1 (a) (b)

(8)

(a) Geben Sie DDL-Anweisungen an, welche die für den Onlineshop notwendigen Relationen erstellen.

(b) Ergänzen Sie Ihre DDL-Anweisungen um die Angabe der Primär- und Fremdschlüsseln.

(c) Erweitern Sie Ihre DDL-Anweisungen so, dass die oben genannten Anforderungen erfüllt werden. Das bedeutet also, dass Kunden gelöscht werden können, ihre

Bestellungen aber anonymisiert im System gespeichert bleiben.

Hausaufgabe 1

(9)

CREATE TABLE Bestellungen (

BestellNr INT PRIMARY KEY,

KundenNr INT REFERENCES Kunden ON DELETE SET NULL, Gesamtstatus TEXT,

Gesamtpreis MONEY, Datum DATE );

Neben SET NULL gibt es noch einige weitere Klauseln.

• CASCADE propagiert das Löschen des Tupels, d. h. beim Löschen eines Kunden werden auch alle seine Bestellungen gelöscht.

• RESTRICT verhindert das Löschen. Beim Ausführen einer DELETE-Abfrage wirft das Datenbanksystem einen Fehler und bricht die Abfrage ab.

• NO ACTION sowie SET DEFAULT (sind für die Vorlesung nicht relevant)

Hausaufgabe 1 (c)

(10)

(d) Fügen Sie Ihrer Datenbank nun mittels DML-Anweisungen die minimale Anzahl an Tupeln hinzu, sodass es einen Kunden und eine ihm zugeordnete Bestellung gibt.

Dabei muss sicher gestellt sein, dass keine referentiellen Integritätsbedingungen verletzt werden.

Hausaufgabe 1

(11)

Fügen Sie Ihrer Datenbank nun mittels DML-Anweisungen die minimale Anzahl an Tupeln hinzu, sodass es einen Kunden und eine ihm zugeordnete Bestellung gibt. Dabei muss sicher gestellt sein, dass keine referentiellen Integritätsbedingungen verletzt werden. (*) Löschen Sie dann den Kunden aus der Datenbank und überprüfen Sie, ob Teilaufgabe (c) erfolgreich in der Datenbank umgesetzt ist.

INSERT INTO Kontinente VALUES (0, 'Europa');

INSERT INTO Laender VALUES (0, 'Deutschland', 0);

INSERT INTO Kunden

VALUES (0, 'Max Mustermann', 'Teststraße 1', 0, '04/10/1995', 'm');

INSERT INTO Bestellungen

VALUES (0, 0, 'versandt', 99.99, '20/11/2019');

DELETE FROM Kunden WHERE KundenNr = 0; (*)

Hausaufgabe 1 (d)

(12)

(e) Erstellen Sie ein SQL-Skript, welches alle Tabellen in einer neuen Datenbank anlegt.

Sollte die Datenbank bereits Tabellen mit gleichem Namen enthalten, so sollen diese zunächst restlos gelöscht werden.

Hausaufgabe 1

(13)

DROP TABLE IF EXISTS Bestellpositionen CASCADE;

DROP TABLE IF EXISTS Bestellungen CASCADE;

DROP TABLE IF EXISTS liefertNach CASCADE;

DROP TABLE IF EXISTS bereitstellen CASCADE;

DROP TABLE IF EXISTS Artikel CASCADE;

DROP TABLE IF EXISTS Kunden CASCADE;

DROP TABLE IF EXISTS Lieferanten CASCADE;

DROP TABLE IF EXISTS Laender CASCADE;

DROP TABLE IF EXISTS Kontinente CASCADE;

Die Reihenfolge in der die Tabellen gelöscht warden ist relevant.

Hausaufgabe 1 (e)

(14)

Anwesenheitskontrolle

(15)

Formulieren Sie eine SQL-Anfrage, um den Bekanntheitsgrad von Studenten zu ermitteln.

Gehen Sie dabei davon aus, dass Studenten sich aus gemeinsam besuchten Vorlesungen kennen. Sortieren Sie das Ergebnis absteigend nach Bekanntheitsgrad!

Hausaufgabe 2

(16)

Formulieren Sie eine SQL-Anfrage, um den Bekanntheitsgrad von Studenten zu ermitteln.

Gehen Sie dabei davon aus, dass Studenten sich aus gemeinsam besuchten Vorlesungen kennen. Sortieren Sie das Ergebnis absteigend nach Bekanntheitsgrad!

WITH bekannte AS (

SELECT DISTINCT h1.matrnr student, h2.matrnr bekannter FROM hoeren h1

JOIN hoeren h2 ON h1.vorlnr = h2.vorlnr AND h1.matrnr <> h2.matrnr )

SELECT s.matrnr, s.name, COUNT(b.*) grad FROM studenten s

LEFT JOIN bekannte b ON s.matrnr = b.student GROUP BY s.matrnr, s.name

ORDER BY grad DESC;

Hausaufgabe 2

(17)

(a) Geben Sie alle Einträge des Fahrplans aus, deren Abfahrtshaltestelle das Wort

”Garching“ enthält.

(b) Geben Sie alle Einträge des Fahrplans mit dem zusätzlichen Attribut ”Verkehrsmittel“ aus.

Alle Linien, die mit ”U“ anfangen, haben das Verkehrsmittel ”U-Bahn“, alle die mit ”S“

anfangen ”S-Bahn“, und alle restlichen ”Bus/Tram“.

(c) Finden Sie alle Abfahrten ab ”Garching, Forschungszentrum“, die Sie heute noch erreichen können. Sortieren Sie das Ergebnis aufsteigend nach Abfahrtszeit.

(d) Finden Sie alle Fahrten zwischen zwei Haltestellen (nicht-transitiv), die mindestens drei und höchstens fünf Minuten dauern.

Bonus: Berücksichtigen Sie, dass Fahrten über Mitternacht möglich sind, z. B. Abfahrt um 23:58 Uhr und Ankunft um 00:02 Uhr.

Hausaufgabe 3

(18)

Geben Sie alle Einträge des Fahrplans aus, deren Abfahrtshaltestelle das Wort ”Garching“

enthält.

SELECT *

FROM fahrplan

WHERE von LIKE '%Garching%'

Hausaufgabe 3 (a)

(19)

(a) Geben Sie alle Einträge des Fahrplans aus, deren Abfahrtshaltestelle das Wort ”Garching“

enthält.

(b) Geben Sie alle Einträge des Fahrplans mit dem zusätzlichen Attribut

”Verkehrsmittel“ aus. Alle Linien, die mit ”U“ anfangen, haben das Verkehrsmittel

”U-Bahn“, alle die mit ”S“ anfangen ”S-Bahn“, und alle restlichen ”Bus/Tram“.

Hausaufgabe 3

(20)

Geben Sie alle Einträge des Fahrplans mit dem zusätzlichen Attribut ”Verkehrsmittel“ aus. Alle Linien, die mit ”U“ anfangen, haben das Verkehrsmittel ”U-Bahn“, alle die mit ”S“ anfangen ”S- Bahn“, und alle restlichen ”Bus/Tram“.

SELECT *, CASE

WHEN linie LIKE 'U%' THEN 'U-Bahn' WHEN linie LIKE 'S%' THEN 'S-Bahn' ELSE 'Bus/Tram'

END verkehrsmittel FROM fahrplan

Hausaufgabe 3 (b)

(21)

enthält.

(c) Finden Sie alle Abfahrten ab ”Garching, Forschungszentrum“, die Sie heute noch erreichen können. Sortieren Sie das Ergebnis aufsteigend nach Abfahrtszeit.

Hausaufgabe 3

(22)

Finden Sie alle Abfahrten ab ”Garching, Forschungszentrum“, die Sie heute noch erreichen können. Sortieren Sie das Ergebnis aufsteigend nach Abfahrtszeit.

SELECT *

FROM fahrplan

WHERE abfahrt >= CURRENT_TIME AND von = 'Garching, Forschungszentrum' ORDER BY abfahrt

Hausaufgabe 3 (c)

(23)

enthält.

(d) Finden Sie alle Fahrten zwischen zwei Haltestellen (nicht-transitiv), die mindestens drei und höchstens fünf Minuten dauern.

Hausaufgabe 3

(24)

Finden Sie alle Fahrten zwischen zwei Haltestellen (nicht-transitiv), die mindestens drei und höchstens fünf Minuten dauern.

SELECT *

FROM fahrplan

WHERE EXTRACT(EPOCH FROM ankunft - abfahrt) BETWEEN 180 AND 300

Hausaufgabe 3 (d)

(25)

Finden Sie alle Fahrten zwischen zwei Haltestellen (nicht-transitiv), die mindestens drei und höchstens fünf Minuten dauern.

WITH fahrplan_dauer AS ( SELECT *,

CASE

WHEN ankunft < abfahrt

THEN EXTRACT(EPOCH FROM ankunft - abfahrt) + 86400 ELSE EXTRACT(EPOCH FROM ankunft – abfahrt)

END dauer_in_sek FROM fahrplan

)

SELECT * FROM fahrplan_dauer

WHERE dauer_in_sek BETWEEN 180 AND 300

Hausaufgabe 3 (d) (Bonus)

(26)

Ermitteln Sie für jede Vorlesung, wie viele Studenten diese vorgezogen haben.

Ein Student hat eine Vorlesung vorgezogen, wenn er in einem früheren Semester ist als der

”Modus“ der Semester der Hörer dieser Vorlesung.

Der Modus ist definiert als der Wert, der am häufigsten vorkommt – für diese Anfrage also das Semester, in dem die meisten Hörer dieser Vorlesung sind. Falls es mehrere Semester dieser Art gibt, soll nur das niedrigste zählen.

Beachten Sie, dass auch Vorlesungen ohne Hörer, sowie Vorlesungen deren Hörer alle im gleichen Semester sind, ausgegeben werden sollen.

Geben Sie für jede Vorlesung die Vorlesungsnummer, den Titel und die Anzahl der ”Vorzieher“

aus.

Hausaufgabe 4

(27)

Zunächst erstellen wir eine Anfrage, welche für jede Vorlesung aufschlüsselt, von wie vielen Stundenten sie pro Semester gehört wird.

Hausaufgabe 4

(28)

Zunächst erstellen wir eine Anfrage, welche für jede Vorlesung aufschlüsselt, von wie vielen Stundenten sie pro Semester gehört wird.

WITH vorl_sem_anz AS (

SELECT vorlnr, semester, COUNT(*) anzahl FROM hoeren

NATURAL JOIN studenten GROUP BY vorlnr, semester )

Mithilfe dieser Sicht können wir nun für jede Vorlesung den Modus der Hörersemester bestimmen. Der Modus ist jenes Semester, dem die meisten Hörer angehören.

Hausaufgabe 4

(29)

Nun können wir für jede Vorlesung den Modus der Hörersemester bestimmen. Der Modus ist das Semester, dem die meisten Hörer angehören. Wenn eine Vorlesung z. B. gleich oft von Erst- und Drittsemestern gehört wird, wollen wir sie

dem ersten Semester zuordnen. Deswegen wird die Aggregatfunktion MIN verwendet.

WITH vorl_modus AS ( SELECT vs1.vorlnr,

MIN(vs1.semester) modus FROM vorl_sem_anz vs1

WHERE vs1.anzahl = ( SELECT MAX(vs2.anzahl) FROM vorl_sem_anz vs2

WHERE vs1.vorlnr = vs2.vorlnr )

GROUP BY vs1.vorlnr )

Hausaufgabe 4

(30)

Nun müssen wir nur noch für jedes Vorlesung-Semester-Paar jene Studenten finden und auf- summieren, die die Vorlesung hören und in einem niedrigeren Semester als der Modus sind.

SELECT v.vorlnr, v.titel, COUNT(s.*) vorzieher FROM vorlesungen v

LEFT JOIN vorl_modus m ON v.vorlnr = m.vorlnr LEFT JOIN hoeren h ON v.vorlnr = h.vorlnr LEFT JOIN studenten s ON h.matrnr = s.matrnr

AND s.semester < m.modus GROUP BY v.vorlnr, v.titel

Hausaufgabe 4

(31)

WITH vorl_sem_anz AS (

SELECT vorlnr, semester, COUNT(*) anzahl FROM hoeren

NATURAL JOIN studenten GROUP BY vorlnr, semester ), vorl_modus AS (

SELECT vs1.vorlnr, MIN(vs1.semester) modus FROM vorl_sem_anz vs1

WHERE vs1.anzahl = ( SELECT MAX(vs2.anzahl) FROM vorl_sem_anz vs2

WHERE vs1.vorlnr = vs2.vorlnr )

GROUP BY vs1.vorlnr )

SELECT v.vorlnr, v.titel, COUNT(s.*) vorzieher FROM vorlesungen v

LEFT JOIN vorl_modus m ON v.vorlnr = m.vorlnr LEFT JOIN hoeren h ON v.vorlnr = h.vorlnr

LEFT JOIN studenten s ON h.matrnr = s.matrnr AND s.semester < m.modus GROUP BY v.vorlnr, v.titel