✔ Datenbankdesignfrage mit inner join über 4 Tabellen mit 3.NF

[Chefkoch]

Ich beschäftige mich erst seit kurzem mit Datenbanken und habe nun ein kleines praktisches Beispiel gemacht.

Es geht um eine Datenbank auf einer Universität mit Studenten, Professoren und Vorlesungen.


1 Professor kann mehrere Vorlesungen abhalten. Mehrere Studenten können mehrere Vorlesungen besuchen. Mehrere Vorlesungen können von mehreren Studenten besucht werden. Zwischen Vorlesung und Student haben wir also n:m

Wie die Tabellen aufgebaut sind, sieht man bei dem angehängtem Bild.

Und in der Tabelle Student_has_Vorlesung befindet sich das problem. Das ist die Transformation zwischen Student und Vorlesung, da es ja n:m ist.

Vorlesung_Professor_Pid:Integer(FK) ist nämlich der Foreign Key aus der Tabelle Vorlesung (Professor_Pid), welcher wiederum der Foreign Key aus der Tabelle Professor(pid) ist. Da ist pid der Primary key.

Das Problem ist, dass ich die 3. NF nicht so erreichen kann, da Professor_Pid von dem Primary key VID in der Tabelle Vorlesung abhängt. VID ist der Primary Key aus der Tabelle Vorlesung und müsste ja nur diesen an die neue Transformationstabelle zwischen Student und Vorlesung schicken. Ich schicke aber den Primary key und Foreign key, welcher ja nun vom Primary key abhängt, in die neue Tabelle. Somit habe ich eine transitive Abhängigkeit vom PK, da Professor_Pid als nichtschlüssel vom nichtschlüssel Vorlesung_VID abhängt. Professor_Pid ist hier redundant, doch leider brauche ich ihn, damit die folgende Abfrage funktioniert:

select a.student.Sname,a.vorlesung.Vname,a.professor.pname from 
a.student_has_vorlesung as shv inner join  a.student 
on a.student.sid=a.shv.sid 
inner join a.vorlesung on a.vorlesung.vid=a.shv.vid 
inner join a.professor on a.professor.pid=a.shv.professor_pid

hier bekomme ich studentenname, vorlesungsname und professorname. Wenn ich Professor_pid nicht als attribut habe, dann erhalte ich nur studentenname und Vorlesung, da ich ja keine Verbindung auf die Tabelle Professor habe. Wenn ich Proffesor.pid mit Vorlesung.proffessor_pid vergleichen könnte, dann würde es auch gehen. Doch wie

Die neue Tabelle hat als PK "ID".

Functional Dependency: ID->(Sid(fk),Professor_pid(fk),Vid(fk)) jedoch FD:Vid->Professor_pid und das ist eine transitive Abhängigkeit vom PK ID. Jetzt müsste ich Vid und Proffesor_pid in neue Tabelle auslagern, was ich ja aber schon gemacht habe. Das ist die Tabelle Vorlesungen.


create table a.Student_has_vorlesung(
ID int not null auto_increment primary key,
Sid int not null,
Professor_pid int not null,
Vid int not null,
Foreign key(Sid)references Student(sid),
Foreign key(Professor_pid) references Vorlesung(professor_pid),
Foreign key(Vid) references Vorlesung(vid)
)


Die Tabellenstruktur liefert das Ergebnis, welches ich möchte, doch habe ich eine transitive Abhängigkeit vom Primary Key ID in der Tabelle Student_has_Vorlesung und somit kann ich keine 3.Normalform erreichen, was ich aber muss, um nicht Modify-Anomalien irgendwann mal zu bekommen. Ich denke, dass die Lösung eigentlich die Umformulierung des Inner joins sein müsste.

 

[MPr]

auf die Gefahr hin, über die ANSI-Syntax zu stolpern, folgender Vorschlag:

select student.Sname, vl.Vname, vl.pname 
  from student_has_vorlesung as shv 
 inner join student 
    on student.sid = shv.sid 
 inner join (select professor.pname, vorlesung.vid, vorlesung.vname
               from professor 
              inner join vorlesung 
                 on vorlesung.pid = professor.pid) vl 
    on vl.vid = shv.vid;

wenn der Join nur über student_has_vorlesung erfolgt, muss die Tabelle natürlich eine pid enthalten, aber das ist im gegebenen Fall nicht erforderlich, da man zunächst Vorlesung mit Professor joinen kann, ehe man das Ergebnis an die Mapping-Tabelle student_has_vorlesung joint. InLine-Views (also Selects in der From-Clause eines anderen Selects) sind meiner Meinung nach sehr häufig eine hübsche Möglichkeit, um die Komplexität von Statements zu reduzieren.

Gruß

MP

 

[Chefkoch]

Hallo,

Danke für den Vorschlag, werde mal in diese Richtung weiterforschen. Deine Abfrage verstehe ich leider nicht ganz

Student_has_Vorlesung inner join und dann kommt plötzlich (select xy) Vorlesung. Ich vergleiche hier Student_has-Vorlesung mit Vorlesung, doch wie helfen mir die 3 Spalten, die ich durch das select erhalte? muss gestehen, dass ich nicht so viel mit Subselects gearbeitet habe.

 

[MPr]

wenn ich die Fragestellung richtig verstanden habe, geht es doch darum, dass die (redundante) professor_pid in student_has_vorlesung benötigt wird, um den inner join zur Tabelle professor herzustellen. Diese Notwendigkeit entfällt, wenn zunächst professor mit vorlesung gejoint wird, und die Ergebnisse dann mit student_has_vorlesung verbunden werden.

Gruß

MP

 

[Chefkoch]

Die Abfrage funktioniert leider nicht, der Professor name wird nicht ausgegeben.


edit: es ist so, dass ich es gelöst habe, und zwar mit einem Where in der letzten Zeile, aber ich würde noch gerne eine andere lösung sehen. Also, kommt leute!

werde es dann auch hier reinposten

 

[MPr]

was für ein RDBMS ist das denn? Unter Oracle funktioniert die InLine-Variante - nach ein paar kleineren Benamungskorrekturen (und da das ANSI-SQL ist, würde ich mich wundern, wenn andere RDBMS sich da anders verhalten):

SQL> select * from professor;

       PID PNAME
---------- --------------------
         1 Hegel
         2 Kant
         3 Schopenhauer
         4 Nietzsche

SQL> select * from student;

       SID SNAME
---------- --------------------
       100 Müller
       101 Maier
       102 Schmidt
       103 Gantenbein

SQL> select * from vorlesung;

       VID        PID VNAME
---------- ---------- ----------------------------------------------------------------
        10          1 Phänomenologie des Geistes
        11          2 Kritik der reinen Vernunft
        12          2 Kritik der Urteilskraft
        13          3 Welt als Wille und Vorstellung
        14          4 Geburt der Tragödie aus dem Geiste der Musik

SQL> select * from student_has_vorlesung;

        ID STUDENT_SID VORLESUNG_VID
---------- ----------- -------------
         1         100            10
         2         100            11
         3         101            11
         4         101            13
         5         103            12
         6         103            14
         7         102            14

7 Zeilen ausgewählt.

SQL> SELECT student.Sname, vl.Vname, vl.pname
  2    FROM student_has_vorlesung shv
  3   INNER JOIN student
  4      ON student.sid = shv.student_sid
  5   INNER JOIN (SELECT professor.pname, vorlesung.vid, vorlesung.vname
  6                 FROM professor
  7                INNER JOIN vorlesung
  8                   ON vorlesung.pid = professor.pid) vl
  9      ON vl.vid = shv.vorlesung_vid;

SNAME                VNAME                                                            PNAME
-------------------- ---------------------------------------------------------------- --------------------
Müller               Phänomenologie des Geistes                                       Hegel
Maier                Kritik der reinen Vernunft                                       Kant
Müller               Kritik der reinen Vernunft                                       Kant
Gantenbein           Kritik der Urteilskraft                                          Kant
Maier                Welt als Wille und Vorstellung                                   Schopenhauer
Schmidt              Geburt der Tragödie aus dem Geiste der Musik                     Nietzsche
Gantenbein           Geburt der Tragödie aus dem Geiste der Musik                     Nietzsche

7 Zeilen ausgewählt.

Es gäbe noch ein paar andere Möglichkeiten, das zu formulieren, aber jedenfalls sollte es überflüssig sein, die pid in die Mapping-Tabelle student_has_vorlesung aufzunehmen.

Gruß

MP

 

[Chefkoch]

Hallo,
Danke für die Mühe.

Es ist eine mysql datenbank und ich greife zur zeit mit oracle sql developer zu. Ging bis jetzt ohne Probleme.

1) Ich verstehe nicht wo sich die Spalte vl.pname befindet. Es gibt nur die Tabelle Professor mit Spalte Pname.

#SQL> SELECT student.Sname, vl.Vname, vl.pname

Weiters verstehe ich nicht, warum du vl statt vorlesung benutzen kannst. Programm kann ja nicht erkennen, dass vl die Abkürzung für vorlesung ist.

2) Verstehe ich nicht, wie sich das 2. SELECT sofort nach dem INNER JOIN eingliedert.

Es heißt ja im Grunde "student_has_vorlesung shv INNER JOIN vorlesung". Doch vor vorlesung erfolgt ja ein SELECT. Was wird mit den Daten gemacht, welches diese SELECT liefert? Was wird da kombiniert, damit diese Daten irgendwie in die INNER JOINS einfließen?

du fragst auch: SELECT professor.pname, vorlesung.vid, vorlesung.vname; was passiert mit der Ausgabe von vorlesung.vid und vorlesung.vname?

select * from a.professor

Pid         Pname                Pjahre      
----------- -------------------- ----------- 
1           Hegel                10          
2           Kant                 10          
3           Schoppenhauer        10          
4           Nietzsche            10 


select * from a.student

Sid         Sname                
----------- -------------------- 
1           Müller               
2           Maier                
3           Schmidt              
4           Gantenbein 

select * from a.vorlesung

Vid         Vname                Professor_Pid 
----------- -------------------- ------------- 
1           Phänomenologie       1             
2           Kritik der reinen V  2             
3           Kritik der Urteil    2             
4           Welt                 3             
5           Geburt der Tragödie  4  

select * from a.student_has_vorlesung2

ID          Sid         Vid         
----------- ----------- ----------- 
1           1           1           
2           1           2           
3           2           2           
4           2           4           
5           4           3           
6           4           5           
7           3           5           

7 rows selected

select a.student.Sname, a.vorlesung.Vname, a.professor.pname from 
a.professor, a.student_has_vorlesung2 as shv 
inner join  a.student on a.student.sid=a.shv.sid 
inner join a.vorlesung on a.vorlesung.vid=a.shv.vid 
where  a.professor.pid=a.vorlesung.professor_pid

Sname                Vname                pname                
-------------------- -------------------- -------------------- 
Müller               Phänomenologie       Hegel                
Müller               Kritik der reinen V  Kant                 
Maier                Kritik der reinen V  Kant                 
Maier                Welt                 Schoppenhauer        
Gantenbein           Kritik der Urteil    Kant                 
Gantenbein           Geburt der Tragödie  Nietzsche            
Schmidt              Geburt der Tragödie  Nietzsche            

7 rows selected

es kommt das gleiche heraus, wobei das markierte a.professor erst das WHERE möglich macht. Mir erscheint das irgendwie nicht so "sauber" programmiert.

 

[MPr]

eine nette (manchmal aber auch problematische) Eigenschaft von SQL ist, dass man immer eine ganze Reihe unterschiedlicher Möglichkeiten hat, um eine Ergebnismenge zu definieren. Und neben diesen inhaltlichen gibt es dann auch noch syntaktische Varianten, die einander inhaltlich völlig entsprechen.

zu 2) offenbar kann man auch in mysql ANSI-Syntax und die traditionellen Schreibweisen für einen Join kombinieren. Das abschließende

where  a.professor.pid=a.vorlesung.professor_pid

ist daher einfach eine syntaktische Variante für einen inner join der Tabellen Vorlesung und Professor über die pid. Umgekehrt kann man auch komplett auf die inner join Syntax verwenden und alle joins über Where-Bedingungen abbilden:

SQL> r
  1  select student.Sname, vorlesung.Vname, professor.pname
  2    from professor, student_has_vorlesung shv, student, vorlesung
  3   where student.sid = shv.student_sid
  4     and vorlesung.vid = shv.vorlesung_vid
  5*    and professor.pid = vorlesung.pid

SNAME                VNAME                                                            PNAME
-------------------- ---------------------------------------------------------------- --------------------
Müller               Kritik der reinen Vernunft                                       Kant
Müller               Phänomenologie des Geistes                                       Hegel
Maier                Welt als Wille und Vorstellung                                   Schopenhauer
Maier                Kritik der reinen Vernunft                                       Kant
Gantenbein           Geburt der Tragödie aus dem Geiste der Musik                     Nietzsche
Gantenbein           Kritik der Urteilskraft                                          Kant

6 Zeilen ausgewählt.

Ich habe mir diese traditionelle Schreibweise angewöhnt, aber der Vorteil der ANSI-Variante ist natürlich, dass man nicht so leicht eine Join-Bedingung vergisst. Letztlich ist es aber nur eine Geschmacksfrage.

zu 1) das vl ist in diesem Fall einfach der Name, der der InLine-View zugeweisen wird. Außerhalb des Kunstrukts sind die zugehörigen Spalten dann nur noch über diesen Alias verfügbar. InLine-Views kann man als logische Klammern verwenden, um komplexe Statements (für den, der sie schreibt) zu gliedern - intern macht der Optimizer der DB daraus wahrscheinlich ohnehin wieder etwas ganz anderes.

Gruß

MP