***********************************************************
***********************************************************
Andreas Stern: "Keine Angst vor Microsoft Access!"
Sämtliche CREATE TABLE Befehle für die Stundenplan-Datenbank
(Übungen aus dem SQL-Kapitel siehe weiter unten)
Datum: 14.2.2013
Kontakt: www.andreasstern.de
***********************************************************
***********************************************************


***********************************************************
Mit den folgenden Befehlen werden zwar die Fremdschlüsselspalten
schon angelegt - zunächst werden aber noch KEINE Beziehungen
zwischen den Tabellen hergestellt!!
***********************************************************

CREATE TABLE tblVeranstaltung
(
    ver_id             Counter         NOT NULL,
    ver_bezeichnung    Text(30)        NOT NULL,
    ver_bemerkung      Memo            NULL,
    CONSTRAINT pk_ver PRIMARY KEY (ver_id)
);

CREATE TABLE tblDozent
(
    doz_id             Counter               NOT NULL,
    doz_anrede         Text(10)              NOT NULL,
    doz_name           Text(30)              NOT NULL,
    doz_vorname        Text(30)              NULL,
    doz_geburtstag     DateTime              NULL,
    doz_anschrift      Text(255)             NULL,
    doz_telefon        Text(30)              NULL,
    doz_email          Text(255)             NULL,
    doz_bemerkung      Memo                  NULL,
    CONSTRAINT pk_doz PRIMARY KEY (doz_id)
);

CREATE TABLE tblGebaeude
(
    geb_id             Counter               NOT NULL,
    geb_bezeichnung    Text(30)              NOT NULL,
    geb_bemerkung      Memo                  NULL,
    CONSTRAINT pk_geb PRIMARY KEY (geb_id)
);

CREATE TABLE tblAusstattung
(
    aus_id             Counter               NOT NULL,
    aus_bezeichnung    Text(30)              NOT NULL,
    aus_bemerkung      Memo                  NULL,
    CONSTRAINT pk_aus PRIMARY KEY (aus_id)
);

CREATE TABLE tblRaum
(
    raum_id            Counter               NOT NULL,
    geb_id_f           Long                  NOT NULL,
    raum_nummer        Text(30)              NOT NULL,
    raum_bezeichnung   Text(30)              NULL,
    raum_bemerkung     Memo                  NULL,
    CONSTRAINT pk_raum PRIMARY KEY (raum_id)
);

CREATE TABLE tblPlanstunde
(
    plan_id            Counter               NOT NULL,
    raum_id_f          Long                  NULL,
    doz_id_f           Long                  NULL,
    ver_id_f           Long                  NULL,
    plan_jahr          Long                  NOT NULL,
    plan_monat         Long                  NOT NULL,
    plan_tag           Text(20)              NOT NULL,
    plan_von           Text(30)              NOT NULL,
    plan_bis           Text(30)              NOT NULL,
    plan_bemerkung     Memo                  NULL,
    CONSTRAINT pk_plan PRIMARY KEY (plan_id)
);

CREATE TABLE tblAus_Raum
(
    ausraum_id         Counter               NOT NULL,
    raum_id_f          Long                  NOT NULL,
    aus_id_f           Long                  NOT NULL,
    ausraum_anzahl     Single                NULL,
    ausraum_bemerkung  Memo                  NULL,
    CONSTRAINT pk_ausraum PRIMARY KEY (ausraum_id)    
);

CREATE TABLE tblDoz_Ver
(
    dozver_id          Counter               NOT NULL,
    doz_id_f           Long                  NOT NULL,
    ver_id_f           Long                  NOT NULL,
    dozver_von         DateTime              NULL,
    dozver_bis         DateTime              NULL,
    dozver_bemerkung   Memo                  NULL,
    CONSTRAINT pk_dozver PRIMARY KEY (dozver_id)    
);

CREATE TABLE tblRaum_Ver
(
    raumver_id         Counter               NOT NULL,
    raum_id_f          Long                  NOT NULL,
    ver_id_f           Long                  NOT NULL,
    raumver_von        DateTime              NULL,
    raumver_bis        DateTime              NULL,
    raumver_bemerkung  Memo                  NULL,
    CONSTRAINT pk_raumver PRIMARY KEY (raumver_id)    
);

***********************************************************
JETZT werden die Beziehungen hergestellt, d.h. jetzt wird
festgelegt, welcher Fremdschlüssel sich auf welchen
Primärschlüssel bezieht!
Das Erstellen der Beziehungen Schritt für Schritt zu beobachten,
kann sehr lehrreich sein. Darum empfehle ich Ihnen, jedes mal, 
wenn Sie einen der folgenden SQL-Befehle ausgeführt haben, 
in Access DATENBANKTOOLS / Beziehungen aufzurufen!
***********************************************************

ALTER TABLE tblRaum
    ADD CONSTRAINT tblGebaeudetblRaum FOREIGN KEY (geb_id_f)
    REFERENCES tblGebaeude (geb_id);

ALTER TABLE tblPlanstunde
    ADD CONSTRAINT tblRaumtblPlanstunde FOREIGN KEY (raum_id_f)
    REFERENCES tblRaum (raum_id);

ALTER TABLE tblPlanstunde
    ADD CONSTRAINT tblDozenttblPlanstunde FOREIGN KEY (doz_id_f)
    REFERENCES tblDozent (doz_id);

ALTER TABLE tblPlanstunde
    ADD CONSTRAINT tblVeranstaltungtblPlanstunde FOREIGN KEY (ver_id_f)
    REFERENCES tblVeranstaltung (ver_id);

ALTER TABLE tblAus_Raum
    ADD CONSTRAINT tblRaumtblAus_Raum FOREIGN KEY (raum_id_f)
    REFERENCES tblRaum (raum_id);

ALTER TABLE tblAus_Raum
    ADD CONSTRAINT tblAusstattungtblAus_Raum FOREIGN KEY (aus_id_f)
    REFERENCES tblAusstattung (aus_id);

ALTER TABLE tblDoz_Ver
    ADD CONSTRAINT tblDozenttblDoz_Ver FOREIGN KEY (doz_id_f)
    REFERENCES tblDozent (doz_id);

ALTER TABLE tblDoz_Ver
    ADD CONSTRAINT fk_dozver_ver FOREIGN KEY (ver_id_f)
    REFERENCES tblVeranstaltung (ver_id);

ALTER TABLE tblRaum_Ver
    ADD CONSTRAINT fk_raumver_raum FOREIGN KEY (raum_id_f)
    REFERENCES tblRaum (raum_id);

ALTER TABLE tblRaum_Ver
    ADD CONSTRAINT fk_raumver_ver FOREIGN KEY (ver_id_f)
    REFERENCES tblVeranstaltung (ver_id);


***********************************************************
***********************************************************
Übungen
***********************************************************
***********************************************************

CREATE TABLE tblVeranstaltung (
    ver_id		counter	NOT NULL  CONSTRAINT CVER1 PRIMARY KEY,
    ver_bezeichnung	text(30)	NOT NULL,
    ver_bemerkung	memo        
)

CREATE TABLE tblRaum (
    raum_id		counter	NOT NULL CONSTRAINT CtblRaum1 PRIMARY KEY,
    geb_id		long,
    raum_nummer		text(30)	NOT NULL,
    raum_bezeichnung	text(30),
    raum_bemerkung	text(255),
    CONSTRAINT CtblRaum2 FOREIGN KEY (geb_id) REFERENCES tblGebaeude
)


INSERT INTO tblRaum (geb_id_f, raum_nummer)
VALUES (2, "212")

UPDATE  tblRaum
SET     raum_bezeichnung = "Mehrzweckraum"
WHERE   raum_id = 5

DELETE FROM tblDozent WHERE doz_id = 2

***********************************************************
***********************************************************
SELECT
***********************************************************
***********************************************************

SELECT raum_nummer 
FROM tblRaum
WHERE raum_bezeichnung = "Chemieraum"

SELECT doz_name, doz_vorname, doz_telefon 
FROM tblDozent
WHERE doz_anschrift like "*Bremen*"

SELECT doz_name, doz_vorname 
FROM tblDozent
WHERE doz_geburtstag = #7/28/1960#

SELECT geb_bezeichnung, raum_nummer, raum_bezeichnung
FROM tblGebaeude, tblRaum
WHERE tblGebaeude.geb_id = tblRaum.geb_id_f

SELECT doz_name, doz_vorname, ver_bezeichnung
FROM tblDozent, tblDoz_Ver, tblVeranstaltung
WHERE tblDozent.doz_id = tblDoz_Ver.doz_id_f
AND tblDoz_Ver.ver_id_f = tblVeranstaltung.ver_id

SELECT ver_bezeichnung, raum_nummer, ausraum_anzahl, aus_bezeichnung
FROM tblVeranstaltung, tblRaum_Ver, tblRaum, tblAus_Raum, tblAusstattung
WHERE tblVeranstaltung.ver_id = tblRaum_Ver.ver_id_f
AND tblRaum_Ver.raum_id_f = tblRaum.raum_id
AND tblRaum.raum_id = tblAus_Raum.raum_id_f
AND tblAus_Raum.aus_id_f = tblAusstattung.aus_id

SELECT tblGebaeude.geb_bezeichnung, tblRaum.raum_nummer, tblRaum.raum_bezeichnung
FROM tblGebaeude INNER JOIN tblRaum 
ON tblGebaeude.geb_id = tblRaum.geb_id_f

SELECT tblDozent.doz_name, tblDozent.doz_vorname, tblVeranstaltung.ver_bezeichnung
FROM tblVeranstaltung INNER JOIN 
(tblDozent INNER JOIN tblDoz_Ver 
ON tblDozent.doz_id = tblDoz_Ver.doz_id_f) 
ON tblVeranstaltung.ver_id = tblDoz_Ver.ver_id_f

SELECT tblVeranstaltung.ver_bezeichnung, tblRaum.raum_nummer, tblAus_Raum.ausraum_anzahl, tblAusstattung.aus_bezeichnung
FROM tblVeranstaltung INNER JOIN ((tblRaum INNER JOIN (tblAusstattung INNER JOIN tblAus_Raum ON tblAusstattung.asu_id =
tblAus_Raum.aus_id_f) ON tblRaum.raum_id = tblAus_Raum.raum_id_f) INNER JOIN tblRaum_Ver ON tblRaum.raum_id =
tblRaum_Ver.raum_id_f) ON tblVeranstaltung.ver_id = tblRaum_Ver.ver_id_f

SELECT ver_bezeichnung, raum_nummer, ausraum_anzahl, aus_bezeichnung
FROM tblVeranstaltung, tblRaum_VER, tblRaum, tblAus_Raum, tblAusstattung
WHERE tblVeranstaltung.ver_id = tblRaum_Ver.ver_id_f
AND tblRaum_Ver.raum_id_f = tblRaum.raum_id
AND tblRaum.raum_id = tblAus_Raum.raum_id_f
AND tblAus_Raum.aus_id_f = tblAusstattung.aus_id

SELECT tblRaum.raum_id, tblRaum.raum_nummer, tblPlanstunde.plan_id, tblPlanstunde.plan_tag, tblPlanstunde.plan_von
FROM tblRaum INNER JOIN tblPlanstunde ON tblRaum.raum_id = tblPlanstunde.raum_id_f
ORDER BY tblRaum.raum_id

SELECT tblPlanstunde.plan_id, tblPlanstunde.plan_tag, tblPlanstunde.plan_von, tblRaum.raum_id, tblRaum.raum_nummer
FROM tblRaum RIGHT JOIN tblPlanstunde ON tblRaum.raum_id = tblPlanstunde.raum_id_f
ORDER BY tblPlanstunde.plan_id

SELECT tblRaum.raum_id, tblRaum.raum_nummer, tblPlanstunde.plan_id, tblPlanstunde.plan_tag, tblPlanstunde.plan_von
FROM tblRaum LEFT JOIN tblPlanstunde ON tblRaum.raum_id = tblPlanstunde.raum_id_f
ORDER BY tblRaum.raum_id



