MySQL – Projekt „Notenübersicht“

Wir erstellen heute eine Datenbank um für alle Schüler einer Schule Noten erfassen zu können.

Anforderung

Welche Daten wollen wir erheben?

  • Schülernamen
  • Dozentennamen
  • Fächer
  • Datum
  • Noten
  • Art der Noten (Klausur, mündlich, Ex)
  • Kurs

Struktur / Grob-Planung

tabellenzuordnung

Wir bilden also Tabellen (rot), die dann in einer Haupttabelle (grün) zusammengefaßt werden. Hinzu kommen noch weitere Spalten. Eines für das Datum der Notenerhebung (datum) und eine Spalte für Bemerkungen (bemerkung).

Umsetzung: Erstellen der Tabellen

CREATE TABLE schueler
(
  schueler_id  INT NOT NULL AUTO_INCREMENT
, vorname  VARCHAR( 50 ) NOT NULL
, nachname VARCHAR( 50 ) NOT NULL
, PRIMARY KEY( schueler_id )
)
 ENGINE = InnoDB;

CREATE TABLE dozenten
(
  dozent_id  INT NOT NULL AUTO_INCREMENT
, vorname  VARCHAR( 50 ) NOT NULL
, nachname VARCHAR( 50 ) NOT NULL
, PRIMARY KEY( dozent_id )
)
 ENGINE = InnoDB;

CREATE TABLE kurse
(
  kurs_id INT NOT NULL AUTO_INCREMENT
, kuerzel VARCHAR( 10 )
, kurs    VARCHAR( 128 ) NOT NULL
, von     DATE NOT NULL
, bis     DATE NOT NULL
, PRIMARY KEY( kurs_id )
)
 ENGINE = InnoDB;

CREATE TABLE faecher
(
  fach_id INT NOT NULL AUTO_INCREMENT
, kuerzel VARCHAR( 10 )
, fach    VARCHAR( 128 )
, PRIMARY KEY( fach_id )
)
 ENGINE = InnoDB;

CREATE TABLE pruefungstypen
(
  pruefungstyp_id INT NOT NULL AUTO_INCREMENT
, kuerzel         VARCHAR( 10 ) NOT NULL
, typ             VARCHAR( 128 ) NOT NULL
, gewichtung      INT NOT NULL
, PRIMARY KEY( pruefungstyp_id )
)
 ENGINE = InnoDB;
CREATE TABLE notenschluessel
(
  punktezahl INT NOT NULL
, note       DECIMAL( 2, 1 ) NOT NULL
, PRIMARY KEY( punktezahl )
)
 ENGINE = InnoDB;

CREATE TABLE noten
(
  note_id         INT NOT NULL AUTO_INCREMENT
, schueler_id     INT NOT NULL
, dozent_id       INT NOT NULL
, kurs_id         INT NOT NULL
, fach_id         INT NOT NULL
, pruefungstyp_id INT NOT NULL
, datum           DATE
, note            INT NOT NULL
, bemerkung       VARCHAR( 256 )
, PRIMARY KEY( note_id )
);

 

tabNotenUebersicht

Die Haupttabelle

tabNotenHaupttabelle

Tabelle notenschluessel

tabNotenNotenuebersicht

Tabelle notenschluessel befüllen wir als erstes mit Daten:
 
INSERT INTO notenschluessel 
     VALUES (1,6.0),
            (2,6.0),
            (3,5.9),
            (4,5.8),
             ...
            (100,1.0);

Der erste Wert vor dem Komma ist die Punktezahl, die zweite Zahl (bspw. 5.8) die entsprechende Schulnote. Die Dezimalzahl 5.8 muss hier also mit einem [ . ] Punk geschrieben werden.

Die Tabelle dozenten kann nun befüllt werden:

INSERT INTO dozenten( vorname, nachname )
     VALUES ( "Richard", "Wagner" )
          , ...  
          , ( "Harald", "Hienger" );

…und die Tabelle schueler:

INSERT INTO schueler( vorname, nachname )
     VALUES ( "Denise", "Aifan" )
          , ( "Rudolphus", "Blaske" )
          , ...
          , ( "Alessandro", "Volkerts" );

Wir erweitern die Tabelle notenschluessel um eine Spalte für einen 50er Schlüssel und fügen dann Daten ein. Wir ändern also die Datensätze. Dazu benötigen wir den Befehl UPDATE:

Zuerst ändern wir noch den Spaltentyp von fuenfzigerSchluessel auf dezimal:

ALTER TABLE notenschluessel
     CHANGE fuenfzigerSchluessel fuenfzigerSchluessel DECIMAL(3,1) NOT NULL;

Dann können wir damit beginnen die Daten in die Spalte einzufügen, bspw. mit:

UPDATE notenschluessel
   SET fuenfzigerSchluessel = 5
 WHERE punktezahl = 10;

oder schlauer machen wir alles auf einmal und füllen die Spalte mit den Daten einer kleinen Berechnung:

UPDATE notenschluessel
   SET fuenfzigerschluessel = ( punktezahl / 2 );

Nachdem alle Tabellen wohl gefüllt sind, können wir uns mit verschiedenen Abfragen beschäftigen.

Als erstes möchten wir alle Schüler und alle Dozenten herausfiltern, um zum Beispiel eine Einladung an alle zu senden:

SELECT vorname, nachname 
  FROM schueler
UNION
  SELECT vorname, nachname 
    FROM dozenten
ORDER BY vorname;

tabelleSchuelerDozenten

Nun einen kompletten Überblick:

SELECT *
  FROM noten
       JOIN schueler USING( schueler_id )
       JOIN dozenten USING( dozent_id )
       JOIN faecher USING( fach_id )
       JOIN notenschluessel ON noten.note = punktezahl
       JOIN pruefungstypen USING( pruefungstyp_id );

tabelleUbersicht

Die selbe Abfrage mit der Einschränkung, dass nur bestimmte Spalten angezeigt werden sollen:
 
SELECT fach
     , schueler.nachname
     , notenschluessel.note
     , dozenten.vorname
  FROM noten
       LEFT JOIN dozenten USING(dozent_id)
       LEFT JOIN faecher USING(fach_id)
       LEFT JOIN notenschluessel ON noten.note = punktezahl
       LEFT JOIN pruefungstypen USING(pruefungstyp_id)
       LEFT JOIN schueler USING(schueler_id);

Ergebnis:

tabelleAuswahl

Print Friendly, PDF & Email

Schreibe einen Kommentar