Geben Sie diese gegebenenfalls an

Fachbereich 12 – Institut für Informatik Dr. Karsten Tolle

Datenbanken und Informationssysteme (DBIS)

Grundlagen der Programmierung 2 Aufgabenblatt Nr. 11

Abgabe: Mittwoch 04. Juli vor! der Vorlesung

Bem.: Lösungen zu Aufgabe 1b) und 2 a-c) sind zusätzlich per E-Mail an den Tutor zu schicken!

Aufgabe 1 (30 - Punkte)

a) Prüfen Sie die folgenden SQL-Statements auf Fehler. Geben Sie diese gegebenenfalls an. Liegt kein Fehler vor, geben Sie die Ergebnisse an (inklusive Relationsschema), welche Sie bezogen auf die dargestellten Tabellen erwarten würden:

 select count(*) from berg where name like 'Mount';

 select count(name) from berg, bestiegen, bergsteiger where id_bergsteiger = 3;

 select id, sum(prominenz) from berg, bestiegen where prominenz between 6000 and 6500 and id_bergsteiger = 3;

b) Erstellen Sie für folgende Anfragen entsprechende SQL-Befehle:

 Welche Berge haben eine größere Höhe über NN als deren Prominenz? Geben Sie deren ID, Name und die entsprechende Differenz aus. (Beispiel: Beim Berg 3 Denali ist dies der Fall und die Differenz beträgt 50.).

 Welche Berge (id und name) hat Gerlinde Kaltenbrunner wann (jahr) bestiegen?

 Welche Berge (id und name) wurden mehr als einmal bestiegen? Sortieren Sie das Ergebnis absteigend nach der Höhe über NN der Berge.

Aufgabe 2 (50 Punkte – 5, 5, 32, 8)

In Anlehnung an Aufgabenblatt 8! Basierend auf

http://www.offenedaten.frankfurt.de/dataset/gesundheit/resource/f6a97c6c-06e3-489f-8a47- 59c434c36fe0 wurde eine vereinfachte Version als CSV-Datei erstellt. Nutzen Sie als

Ausgangspunkt für diese Aufgabe den SQL-Dump und die CSV-Datei zu dieser Aufgabe von der PRG2-Seite. Erstellen Sie für folgende Schritte entsprechende SQL-Statements:

a) Erweitern der Datenbank um eine Tabelle, welche die CSV-Daten aufnehmen kann.

b) Einfügen der Daten aus der CSV-Datei in die Tabelle.

c) Erstellen folgender Views (Ziel ist es eventuell auftretende Probleme über die View zu lösen und nicht an den Ursprungstabellen!):

i. Eine View mit dem Namen: revier_telefonliste – welche nur den Namen und die Telefonnummer der Polizeireviere auflistet.

ii. Eine View mit dem Namen: strassen_polizei – welche für alle 628 Einträge aus dem Straßenverzeichnis, diese um die zugehörigen Daten der Polizeireviere erweitert. (Man beachte, dass das Polizeirevier (PR) 15 im PR 14 aufgegangen ist. Dort sollen entsprechend die Daten vom PR 14 eingetragen werden, siehe:

http://www.fnp.de/lokales/frankfurt/Ich-sehe-nur-Vorteile;art675,1308328) … gleiches gilt für PR 9 welches jetzt zum PR 8 gehört

(http://www.fnp.de/lokales/frankfurt/Kassiert-die-Polizei-ein-Revier- ein;art675,600095). Beispielausgabe:

…

iii. Eine View mit dem Namen: strassen_aerzte – welche die Einträge aus dem Straßenverzeichnis, um die Ärzte-Daten des zugehörigen Stadtteils erweitert, sofern Daten für den Stadtteil vorliegen. Man beachte, dass Flughafen nicht in der CSV-Datei für Ärzte enthalten ist. Das Ergebnis sollte daher 623 Einträge enthalten.

d) Erstellen Sie folgende Anfragen möglichst unter Verwendung der erzeugten Views:

i. Wie lauten die Telefonnummern der zugehörigen Polizeireviere für die Straße mit dem Namen „Berger Straße“? Hierbei gleiche Telefonnummern nicht doppelt ausgeben!

ii. Welche Straßen liegen in (oder auch teilweise in) Stadtteilen mit der höchsten Dichte an allgemeinen Kassenärzten in 2012? (Lösungshinweis: Zum Identifizieren eines Maximums ist eine Subquery sinnvoll, siehe auch https://dev.mysql.com/doc/refman/8.0/en/subqueries.html.)

Aufgabe 3 (20 Punkte)

Gegeben sei das Relationenschema R = (A, B, C, D, E). Geben Sie für die jeweils gegebenen funktionalen Abhängigkeiten an, welche Schlüssel für die Relation existieren.

a) F = {A  C, B  D, C C}

b) F = {A  E, E  A, C  AD}

c) F = {D  ABCE, AB  DE, CE  AB}

d) F = {AB  CE, AD  ABD, E  D}

e) F = {ABCDE  ACE, AD  BC, BC  E, E  D}