SQL Abfragen

Dieser Aufgabenteil ist von allen Fachrichtungen zu bearbeiten.

Alle Abfragen beziehen sich auf das so genannte "FH Abbild". Der Abschnitt "Anleitungen" in der linken Seitenleiste enthält Hinweise, wie sie das FH Abbild auf einem eigenen PC installieren oder von den FH Rechenzentren darauf zugreifen können. Verwenden Sie zur Orientierung die ER Übersicht der Datenbank.

Bei ALLEN Aufgaben gilt, dass in der Ausgabe sinnvolle Spaltenbezeichner und eine sinnvolle Auswahl an Spalten getroffen werden muss. Eine Spalte mit einer Funktion als Bezeichner ist nicht aussagekräftig!

Soweit keine bestimmten Datenbankspalten explizit gefordert werden, sind im Rahmen der SELECT-Klausel selbstständig aussagekräftige Spalten zu wählen.

Aufgabe 1.1 [68 Zeilen]
Zeigen sie alle Länderkürzel mit mehr als zwei Stellen.

Aufgabe 1.2 [19 Zeilen]
Zeigen sie alle Buchveröffentlichungen auf die eines der folgenden Kriterien zutrifft: Veröffentlicht zwischen 2003 und 1989, Titel enthält den Begriff "geheim" und "Tagebuch", Titel enthält den Begriff "Faszination".

Aufgabe 1.3 [30 Zeilen]
Die Tabelle "gehaltsstufe" enthält Informationen über monatliche Vergütung sowie das einmal pro Jahr gezahlte Weihnachtsgeld. Berechnen sie aus diesen Informationen für jede Gehaltsstufe den jährlichen Verdienst.

Aufgabe 1.4 [8 Zeilen]
Nennen sie alle Prüfungstypen, bei denen die Anzahl an erlaubten Wiederholungen nicht zwei ist.

Aufgabe 2.1 [20 Zeilen]
Nennt die Prüfungen (Bezeichnung) in denen ein Student (vollständiger Name) aus Pinneberg (PLZ 25421) schon einmal durchgefallen ist (angetreten und Note >= 5).

Aufgabe 2.2 [31 Zeilen]
Gebt bitte alle Assistenten und sonstigen Mitarbeiter mit den folgenden Informationen aus: PIN, Name, Vorname, Aufgaben ID, Art der Beschäftigung (Sonstiger Mitarbeiter oder Assistent).

Aufgabe 2.3 [2 Zeilen]
Gebt bitte den Aufgabenbezeichner, Vorname und Nachname der Personen aus, welche das Medien Rechenzentrum betreuen und für die Buchhaltung zuständig sind.

Aufgabe 2.4 [71 Zeilen]
Gebt bitte Nachname, Vorname, Kürzel, Jahr der Befristung und Veranstaltungsbezeichner der Assistenten aus die Veranstaltungen betreuen.

Aufgabe 2.5 [41 oder 40 Zeilen]
Gebt bitte alle Veranstaltungen (Bezeichnung) aus, die in einem Labor stattfinden. Sofern eine Veranstaltung mehrfach im Stundenplan vorhanden ist, soll sie nur einmalig angezeigt werden.

Aufgabe 2.6 [? Zeilen]
Welche Gehälter werden an der Hochschule pro Jahr durchschnittlich (2 Nachkommastellen), maximal, minimal und aufsummiert gezahlt? Beachten sie auch die Zahlung des Weihnachtsgeldes.

Aufgabe 2.7 [? Zeilen]
Wie viele Studenten bekommen mehr BAföG als das durchschnittliche BAföG? Das durchschnittliche BAföG ergibt sich dabei aus den Beträgen die an Studenten und Schüler der FH bzw. PTL Wedel gezahlt werden.

Aufgabe 3.1 [18 Zeilen]
Gebt bitte alle Veranstaltungen (Bezeichnung) aus, deren Termine in unterschiedlichen Arten von Räumen stattfinden. Es kommen also nur Veranstaltungen in Frage, zu denen mehr als ein Termin im Stundenplan existiert.

Aufgabe 3.2 [13 Zeilen]
Es gibt Gebäude in denen anscheinend mehrere Studenten der FH Wedel wohnen (identische Straße, PLZ und Ort). Welche Gebäude sind das und wieviele Studenten wohnen dort jeweils?

Aufgabe 3.3 [? Zeilen]
Gebt bitte die Wochentage mit der Anzahl der jeweils stattfindenden Veranstaltungstermine aus. Die Ausgabe soll absteigend nach der Anzahl sortiert sein.
Hinweis: Räume können mehrfach belegt sein. Berücksichtigt daher bitte jede Kombination aus Raum und Zeitslot nur einfach.
Hinweis: Schauen sie auf ihre Gruppierung, auch 0 ist eine zu zählende Anzahl.

Aufgabe 3.4 [1533 Zeilen]
Gebt das alter aller Studenten (PIN, Vorname, Nachname) in Tagen aus.
Hinweis: Informieren sie sich über die MySQL Funktionen DATEDIFF und CURDATE. Erstere Funktion wird von der MySQL Workbench leider nicht hervorgehoben, der Datenbankserver versteht sie aber trotzdem.

Aufgabe 3.5 [? Zeilen]
Nenne den oder die ältesten Studenten (Vorname, Nachname, Geburtsdatum).
Hinweis: Die Zeilenzahl dieser Aufgabe ergibt sich unmittelbar aus der vorigen Aufgabe.

Aufgabe 3.6 [? Zeilen]
Gebt bitte die Durchschnittsnote aller Prüfungen, gruppiert nach Fachrichtungen aus. Rundet dabei auf eine Nachkommastelle.
Hinweis: Schauen sie auf ihre Gruppierung.

Aufgabe 3.7 [? Zeilen]
Gebt die Anzahl aller Studenten im ersten Verwaltungssemester aus.

Aufgabe 3.8 [11 Zeilen]
Gebt die Anzahl der immatrikulierten Studierenden pro Fachrichtung aus und deren Prozentanteil bezogen auf die Gesamtanzahl von Studenten und Schülern im ersten Verwaltungssemester.
Hinweis: Magic Numbers vermeiden sie mit der vorigen Aufgabe.

Aufgabe 3.9a [515 Zeilen]
Assistenten betreuen eine oder mehrere Veranstaltungen, teilweise auch gemeinsam. Nennt alle Veranstaltungen und sortiert diese nach der Anzahl der betreuenden Assistenten.

Aufgabe 3.9b [48 Zeilen]
Assistenten betreuen eine oder mehrere Veranstaltungen, teilweise auch gemeinsam. Gebt bitte alle Veranstaltungen aus, die von mehr als einem Assistenten betreut werden. Sortiert die Veranstaltungen nach der Anzahl der betreuenden Assistenten und zeigt dabei keine Veranstaltungen an, die von keinem Assistenten betreut werden.

Aufgabe 3.10 [1 Zeile]
Welche Aufgaben (Bezeichnung) sind nicht vergeben? (Die Lösung soll mittels EXISTS formuliert werden.)

Manuelle SQL Abfragen

Demonstrieren sie die Korrektheit ihrer Lösungsvorschläge anhand der beiliegenden Beispieldaten. Diesen Daten fehlen möglicherweise einige Spalten im Vergleich zum FH Abbild, deren Daten sind für die Lösung jedoch nicht relevant.

Geben sie für jeden Ihrer Zwischenschritte die sich ergebende Relation an. Im einfachsten Fall können sie das Ergebnis der Abfrage, beginnend mit der FROM Anweisung, einfach zeilenweise (bezogen auf ihre SQL Anweisungen) berechnen. Bitte machen sie in ihren Berechnungen mindestens die folgenden Schritte deutlich:

  • Jeden JOIN
  • Filterungen mit WHERE
  • Gruppierungen mit GROUP BY
  • Projektionen im SELECT

Aufgabe 1.3 und 2.6

Tabelle: gehaltsstufe
+---------------+----------+------+--------------+
|gehaltsstufe_id|gs_kuerzel|gehalt|weihnachtsgeld|
+---------------+--------- +------+--------------+
| 1             | gs1      | 1200 | 0            |
| 2             | gs1+     | 1200 | 1200         |
| 3             | gs2      | 3600 | 800          |
+---------------+----------+------+--------------+

Tabelle: mitarbeiter
+---+---------------+-------+
|pin|gehaltsstufe_id|kuerzel|
+---+---------------+-------+
| 1 | 3             | mri   |
| 2 | 3             | uh    |
| 3 | 1             | eh    |
| 4 | 2             | gh    | 
+---+---------------+-------+

Aufgabe 3.9a und 3.9b

Tabelle: assistent
+---+
|pin|
+---+
| 1 |
| 3 | 
+---+

Tabelle: mitarbeiter
+---+---------------+-------+
|pin|gehaltsstufe_id|kuerzel|
+---+---------------+-------+
| 1 | 3             | mri   |
| 2 | 3             | uh    |
| 3 | 1             | eh    |
| 4 | 2             | gh    | 
+---+---------------+-------+

Tabelle: veranstaltung
+----------------+-----------------+
|veranstaltung_id|veranstaltung_bez|
+----------------+-----------------+
| 1              | Übg. Datenbanken|
| 2              | Datenbanken     |
| 3              | ABWL            |
+----------------+-----------------+

Tabelle: betreut
+---+----------------+
|pin|veranstaltung_id|
+---+----------------+
| 1 | 1              |
| 2 | 2              |
| 4 | 3              |
+---+----------------+

Modellierung

Diese Aufgabenteile müssen nur von Studiengängen mit 2 oder 3 ECTS für die Datenbankübung bearbeitet werden!

Diese Aufgabe umfasst die Erstellung eines ER-Datenmodells für ein Meinungsforschungsinstitut, welches die Inhalte des nachfolgend beschriebenen Fachkonzepts abbildet.

Ausgangsbeschreibung

  • Wir betrachten ein Meinungsforschungsinstitut mit etwa 150 hauptamtlichen und einer stark schwankenden Anzahl an nebenberuflichen Mitarbeitern. Sitz des Unternehmens ist Hamburg, Geschäftszweck ist die Durchführung von Meinungsumfragen aller Art.

  • Grundsätzlich können die Meinungsumfragen auf vielen verschiedenen Wegen erhoben werden. Üblicherweise, also in mehr als 80% der Fälle, werden Telefoninterviews oder Straßenumfragen durchgeführt. Grundlage für jede Befragung ist ein standarisierter Fragebogen.

  • Jede Umfrage gliedert sich in vier Phasen, die von verschiedenen Mitarbeitern betreut werden. Neben den Verantwortlichen für die individuellen Phasen wird jede Umfrage von einem Mitarbeiter geleitet. Ausschließlich dieser leitende Mitarbeiter pflegt den Kontakt zum Kunden!

  • In der ersten Phase wird der Fragebogen konzipiert. Grundsätzlich kann zwischen den folgenden Arten von zu erfassenden Antworten unterschieden werden:

    • Beliebiger Text
    • Single Choice, also die Auswahl einer Möglichkeit aus mehreren Vorgaben
    • Multiple Choice, also die Auswahl mehrerer Möglichkeiten aus mehreren Vorgaben.

  • Die entsprechenden Mitarbeiter möchten Single und Multiple Choice Fragestellungen naheliegenderweise nicht mit Freitexten erfassen müssen. Für diese Fragestellungen müssen die möglichen Antworten aus dem Fragebogen hervorgehen. Diese Antwortmöglichkeiten müssen nach einer festgelegten Reihenfolge sortiert werden können.

  • In der zweiten Phase werden die Daten erhoben. Je nach Art der Umfrage muss sich der Verantwortliche für die Erhebungsphase um eine stark varierende Anzahl an nebenberuflichen Mitarbeitern kümmern. Diese nebenberuflichen Mitarbeiter holen dann die Antworten ein. Die eigentliche Durchführung ist vom verantwortlichen Mitarbeiter in geeigneter Weise zu dokumentieren und nicht Teil der Datenbank. Wichtig ist nur, dass die erhobenen Antworten für die Aufbereitung in der dritten Phase zugänglich ist. Dabei kann z.B. mit ausgefüllten Papierfragebögen, Tonträgern, Videos oder Excel Dateien gearbeitet werden.

  • Die Befragten Personen werden sich grundsätzlich auch bei Multiple oder Single Choice Fragestellungen nicht immer auf die vorgegebenen Antwortmöglichkeiten beschränken. Daher müssen auch in diesem Fall Freitextantworten erfasst werden können. Außerdem werden Antworten gerne verweigert (mit Begründung oder auch ohne), auch dieser Umstand muss sich abbilden lassen.

  • Die Erhebung der Antworten geschieht über eine der folgenden Methoden:

    • Befragung durch einen Mitarbeiter. In diesem Fall muss sich jeder erhobene Fragebogen dem interviewenden Mitarbeiter zuordnen lassen.
    • Versand und Empfang von Fragebögen, welche von den Probanden eigenständig ausgefüllt werden.
    • Online Fragebögen mit und ohne Zugangsbeschränkung.

  • In der dritten Phase werden die Daten aufbereitet. Die von den Mitarbeitern erfassten Dokumente werden digitalisiert und in einer Datenbank gespeichert. Da es Vorkommen kann, dass einzelne Merkmalsausprägungen nicht eindeutig zuordbar sind, werden zu jeder konkreten Antwort im Rahmen der Auswertung einer, mehrere oder auch kein Kommentar erfasst. So können Mehrdeutigkeiten durch ein z.B. schwer lesbares Schriftbild oder undeutlich gesprochene Antworten markiert werden.

  • Zu jedem Kommentar ist ein Zeitstempel sowie der kommentierende Mitarbeiter zu erfassen. Ausserdem fallen Kommentare in eine von drei Kategorien, welche die jeweilige Antwort im ersten und zweiten Fall für die Analyse als unbrauchbar markieren.

    1. Unverständlich
    2. Mehrdeutig
    3. Anmerkung

  • In der vierten Phase werden die Daten gemeinsam mit dem Kunden interpretiert. Dabei werden alle Antworten die mit mindestens einem Kommentar der Kategorie "Unverständlich" oder "Mehrdeutig" versehen sind in der Regel nicht berücksichtigt.

  • Für jede Umfrage ist zu erfassen, welchen Zeitraum die einzelnen Phasen eingenommen haben. Dabei dürfen sich die zweite und dritte Phase überlappen. In allen anderen Fällen muss die vorige Phase vollkommen abgeschlossen sein.

  • Kunden des Institutes sind ausschließlich Firmen. Für jede Firma sind der Name und alle für diese Firmen durchgeführten Befragungen zu erfassen.

  • Nicht jeder Mitarbeiter kommt für die Leitung von Umfrageprojekten oder einzelnen Phasen in Frage. Einen Nachweis der entsprechenden Qualifikation kann durch eine interne Schulung erbracht werden. Neben den Qualifikationen "Planung", "Erhebung", "Aufbereitung", "Analyse" und "Projektleitung" sollen künftig noch weitere Qualifikationen erfasst werden können.

  • Sofern datenschutzrechtlich zulässig, sollen sich Antworten optional der Befragen Person zuordnen lassen. Dafür werden die befragten Personen mit einer eindeutigen ID versehen, welche beim Bundesamt für zentrale Zuordnung oder der NSA zu erfragen ist. Neben der eindeutigen ID soll zu den Personen das Geschlecht, Name und Vorname sowie Familienstand und Alter erfasst werden.

ER-Diagramm

Erstellen sie ein ER-Diagramm in Chen-Notation entsprechend der Vorlesung. Es darf dabei keinen Entitytypen ohne Primärschlüssel geben. Das entstehende Diagramm soll zusammenhängend sein, also nicht aus mehreren unabhängigen Teilen bestehen. Sämtliche Beziehungen müssen mit einem sprechenden Namen sowie den Kardinalitäten versehen werden. Ein handgezeichnetes Diagramm ist ausreichend, sofern es sauber gezeichnet wurde.

Achtung: Die MySQL Workbench kann keine Diagramme im hier geforderten Format erstellen.

ER-Modell und relationales Schema

Aufbauend auf dem ER-Diagramm der vorigen Aufgabe sind für eine Teilmenge der dort vorkommenden Entitytypen ein ER-Modell und das sich daraus ergebende relationale Schema abzuleiten. Überführen sie bitte die folgenden Sachverhalte in die jeweilige textuelle Darstellung:

  • Mitarbeiter sowie deren Qualifikationen
  • Umfragen sowie der hauptverantwortliche Mitarbeiter
  • Die Phasen der Umfragen sowie die jeweils verantwortlichen Mitarbeiter.

Datenbankschema

Diese Aufgabenteile müssen nur von Studiengängen mit 3 ECTS für die Datenbankübung bearbeitet werden!

Aufbauend auf dem ER-Diagramm ist mit der MySQL Workbench ein Datenmodell anzulegen und daraus ein Skript zu erzeugen, welches die entsprechender Tabellen erstellt. Hauptsächlicher Zweck dieser Aufgabe ist es, die bereitgestellte Funktionalität des Tools kennenzulernen und entsprechend einzusetzen. Macht euch dementsprechend mit den Modellierungsfunktionen der Workbench vertraut und erläutert im Abnahmegespräch die von euch verwendeten Funktionen.

Testdaten

Erzeugt SQL-Statements, um für alle Entitäten sinnvolle Testdaten zu erzeugen. Erstellt und dokumentiert mindestens vier Sachverhalte, die sich aus der Aufgabenstellung ergeben und über die Datenbank abgebildet werden können.