David DejoriCREATE TABLE Kontinente(KontinentenNrINT PRIMARYKEY, Name TEXTNOTNULL); CREATE TABLE Laender (LaenderCodeINT PRIMARYKEY, Name ... Wintersemester 2019/20 7 Hausaufgabe 1

David Dejori

Tutorübung zu Grundlagen Datenbanken

(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ülltwerden. 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ächstrestlos gelöscht werden.

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

Hausaufgabe 1

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)

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)

);



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)

);



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

);



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

);





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




Hausaufgabe 1

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 werdenauch 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)9David Dejori | Tutorübung zu Grundlagen Datenbanken | Wintersemester 2019/20

Hausaufgabe 1 (c)




(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ätsbedingungenverletzt werden.



Hausaufgabe 1

Fügen Sie Ihrer Datenbank nun mittels DML-Anweisungen die minimale Anzahl an Tupelnhinzu, sodass es einen Kunden und eine ihm zugeordnete Bestellung gibt. Dabei muss sichergestellt sein, dass keine referentiellen Integritätsbedingungen verletzt werden. (*) LöschenSie 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 KundenVALUES (0, 'Max Mustermann', 'Teststraße 1', 0, '04/10/1995', 'm');

INSERT INTO BestellungenVALUES (0, 0, 'versandt', 99.99, '20/11/2019');

DELETE FROM Kunden WHERE KundenNr = 0; (*)11David Dejori | Tutorübung zu Grundlagen Datenbanken | Wintersemester 2019/20

Hausaufgabe 1 (d)





(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 diesezunächst restlos gelöscht werden.


Hausaufgabe 1

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)

Anwesenheitskontrolle

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

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

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 bekannterFROM hoeren h1JOIN hoeren h2 ON h1.vorlnr = h2.vorlnr

AND h1.matrnr <> h2.matrnr) SELECT s.matrnr, s.name, COUNT(b.*) gradFROM studenten sLEFT JOIN bekannte b ON s.matrnr = b.studentGROUP BY s.matrnr, s.nameORDER BY grad DESC;


Hausaufgabe 2

(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

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

SELECT *FROM fahrplanWHERE von LIKE '%Garching%'


Hausaufgabe 3 (a)







Hausaufgabe 3

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

SELECT *,CASEWHEN linie LIKE 'U%' THEN 'U-Bahn'WHEN linie LIKE 'S%' THEN 'S-Bahn'ELSE 'Bus/Tram'

END verkehrsmittelFROM fahrplan


Hausaufgabe 3 (b)







Hausaufgabe 3

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

SELECT *FROM fahrplanWHERE abfahrt >= CURRENT_TIME AND von = 'Garching, Forschungszentrum' ORDER BY abfahrt


Hausaufgabe 3 (c)




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



Hausaufgabe 3

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


SELECT *FROM fahrplanWHERE EXTRACT(EPOCH FROM ankunft - abfahrt) BETWEEN 180 AND 300


Hausaufgabe 3 (d)

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


WITH fahrplan_dauer AS (SELECT *,CASEWHEN ankunft < abfahrtTHEN EXTRACT(EPOCH FROM ankunft - abfahrt) + 86400ELSE EXTRACT(EPOCH FROM ankunft – abfahrt)

END dauer_in_sekFROM fahrplan

)SELECT * FROM fahrplan_dauerWHERE dauer_in_sek BETWEEN 180 AND 300


Hausaufgabe 3 (d) (Bonus)

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

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

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(*) anzahlFROM hoerenNATURAL JOIN studentenGROUP BY vorlnr, semester

)

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


Hausaufgabe 4

Nun können wir für jede Vorlesung den Modus der Hörersemester bestimmen. Der Modus istdas Semester, dem die meisten Hörer angehören. Wenn eine Vorlesung z. B. gleich oft von Erst- und Drittsemestern gehört wird, wollen wir siedem ersten Semester zuordnen. Deswegen wirddie Aggregatfunktion MIN verwendet.

WITH vorl_modus AS (SELECT vs1.vorlnr,

MIN(vs1.semester) modusFROM vorl_sem_anz vs1WHERE vs1.anzahl = ( SELECT MAX(vs2.anzahl)FROM vorl_sem_anz vs2WHERE vs1.vorlnr = vs2.vorlnr

) GROUP BY vs1.vorlnr

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

Hausaufgabe 4

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.*) vorzieherFROM vorlesungen vLEFT JOIN vorl_modus m ON v.vorlnr = m.vorlnrLEFT JOIN hoeren h ON v.vorlnr = h.vorlnrLEFT JOIN studenten s ON h.matrnr = s.matrnr

AND s.semester < m.modusGROUP BY v.vorlnr, v.titel


Hausaufgabe 4

WITH vorl_sem_anz AS (SELECT vorlnr, semester, COUNT(*) anzahlFROM hoerenNATURAL JOIN studentenGROUP BY vorlnr, semester

), vorl_modus AS (SELECT vs1.vorlnr, MIN(vs1.semester) modusFROM vorl_sem_anz vs1WHERE vs1.anzahl = ( SELECT MAX(vs2.anzahl)FROM vorl_sem_anz vs2WHERE vs1.vorlnr = vs2.vorlnr

) GROUP BY vs1.vorlnr

)SELECT v.vorlnr, v.titel, COUNT(s.*) vorzieherFROM vorlesungen vLEFT JOIN vorl_modus m ON v.vorlnr = m.vorlnrLEFT JOIN hoeren h ON v.vorlnr = h.vorlnrLEFT JOIN studenten s ON h.matrnr = s.matrnr AND s.semester < m.modusGROUP BY v.vorlnr, v.titel


Hausaufgabe 4

Documents

David DejoriCREATE TABLE Kontinente(KontinentenNrINT PRIMARYKEY, Name TEXTNOTNULL); CREATE TABLE Laender (LaenderCodeINT PRIMARYKEY, Name ... Wintersemester 2019/20 7 Hausaufgabe 1