Aufgabe 5.2 (40 Punkte)

Für jeden Übungsteilnehmer sind auf dem Datenbankserver bereits zwei leere Datenbanken angelegt: user_[loginname]_Uni und user_[loginname]_WM2006. Wechseln Sie in die Datenbank user_[loginname]_Uni. Passen Sie das SQL-Skript uni-db.sql  an, um in Ihrer Datenbank die entsprechenden Tabellen anzulegen und zu füllen. Erläutern Sie, wie man die Daten einspielen kann.

Formulieren Sie anschließend die folgenden acht Abfragen in SQL und überprüfen Sie sie anhand Ihrer Datenbank:

1. Wie lauten die Namen der Assistenten, die nach 1965 geboren wurden?

2. Wie lauten die Namen der Studierenden, die keine Vorlesung besuchen?

3. Welche Vorlesungen mit Professoren setzen die Vorlesung 'Grundzüge' voraus?

4. Welche Professoren haben eine Lehrbelastung von mehr als 5 SWS?

5. In welcher Etage haben wie viele Professoren ihr Büro? (Raumnr. 0-99 entspricht der 0. Etage, Raumnr. 100-199 entspricht der 1. Etage, ...)

6. Finden Sie die Professoren, deren sämtliche Vorlesungen nur auf selbst gelesenen (direkten) Vorgängern aufbauen.

7. Welche Studenten haben Vorlesungen gehört, für die ihnen die direkten Voraussetzungen fehlen?

8. Welche Vorlesungen setzen alle Vorlesungen voraus, die mit dem Anfangsbuchstaben 'E' beginnen?

Musterlösung vom 02.06.2009:

Eine Datenbank lässt sich mit dem Kommandozeilenaufruf mysql -u [loginname] -p user_[loginname]_Uni < uni-db.sql mit den Tabellen aus der Uni-Datenbank füllen, da die Daten in der Datei bereits in SQL-Syntax vorliegen und nur noch mit dem MySQL-Kommandozeilentool ausgeführt zu werden müssen.

Alternativ lässt sich dieser Befehl auch über phpMyAdmin ausführen, indem man die leere Datenbank auswählt und und die SQL-Datei unter 'Importieren' ausführt.

Es ergeben sich folgende Abfragen:
1.

select Name
  from Assistenten
 where Year(GebDatum) > 1965

2.

select Name
  from Studenten
 where MatrNr not in (select MatrNr from hoeren)

3.

select p.Name, v1.Titel
  from Professoren p, Vorlesungen v1, Vorlesungen v2, voraussetzen vs 
 where p.PersNr = v1.gelesenVon 
   and vs.Nachfolger = v1.VorlNr 
   and vs.Vorgaenger = v2.VorlNr 
   and v2.Titel = 'Grundzuege'

4.

  select p.Name, SUM(v.SWS) Stunden
    from Professoren p, Vorlesungen v
   where p.PersNr = v.gelesenVon
group by p.Name
  having Stunden > 5

5.

  select floor(raum/100) as Etage, COUNT(*) as Anzahl
    from Professoren
group by floor(raum/100)

6.

select p.PersNr, p.Name
  from Professoren p
 where not exists (select *
                     from Vorlesungen v1, voraussetzen vor, Vorlesungen v2
                    where p.PersNr = v1.gelesenVon
                      and v1.VorlNr = vor.Vorgaenger
                      and vor.Nachfolger = v2.VorlNr
                      and v2.gelesenVon != p.PersNr)

7. Bei der Relation 'Voraussetzen' handelt es sich um eine N:M-Beziehung. Daher kann eine Vorlesung mehrere Vorlesungen voraussetzen. Bei der folgenden SQL-Anfrage müsste die Abfrage korrekterweise lauten: Welche Studenten haben Vorlesungen gehört, für die ihnen alle direkten Voraussetzungen fehlen?.

select distinct s.MatrNr, s.Name
  from Studenten s, hoeren h, voraussetzen v
 where s.MatrNr = h.MatrNr
   and h.VorlNr = v.Nachfolger
   and not exists (select *
                     from hoeren h2
                    where h2.VorlNr = v.Vorgaenger
                      and h2.MatrNr = s.MatrNr)

8.

select v1.*
  from Vorlesungen v1
 where not exists (select *
                     from Vorlesungen v2
                    where v2.Titel like 'E%'
                      and not exists (select *
                                        from voraussetzen vor
                                       where v2.VorlNr = vor.Vorgaenger
                                         and vor.Nachfolger = v1.VorlNr))