DROP TABLE IF EXISTS prueft      CASCADE;
DROP TABLE IF EXISTS student     CASCADE;
DROP TABLE IF EXISTS professor   CASCADE;
DROP TABLE IF EXISTS studiengang CASCADE;

CREATE TABLE studiengang
(
 stg_id    INTEGER    NOT NULL PRIMARY KEY,
 name      VARCHAR    NOT NULL,
 kuerzel   VARCHAR(3) NOT NULL UNIQUE,
 abschluss VARCHAR(3) NOT NULL
);

CREATE TABLE professor
(
 p_id  INTEGER NOT NULL PRIMARY KEY,
 name  VARCHAR NOT NULL,
 url   VARCHAR NOT NULL,
 email VARCHAR          UNIQUE
);

CREATE TABLE student
(
 s_id    INTEGER      NOT NULL PRIMARY KEY,
 stg_id  INTEGER      NOT NULL,
 name    VARCHAR(100) NOT NULL,
 wohnort VARCHAR(100) NOT NULL,

 CONSTRAINT fk_student_studiengang 
   FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id)
);

CREATE TABLE prueft
(
 p_id   INTEGER      NOT NULL,
 s_id   INTEGER      NOT NULL,
 fach   VARCHAR(100) NOT NULL,
 raum   VARCHAR(6),
 beginn TIMESTAMP,
 dauer  INTERVAL,

 CONSTRAINT pk_pruefung
   PRIMARY KEY (p_id, s_id, fach),
   
 CONSTRAINT fk_pruefung_professor 
   FOREIGN KEY (p_id) REFERENCES professor (p_id),
 CONSTRAINT fk_pruefung_student 
   FOREIGN KEY (s_id) REFERENCES student   (s_id)
);

/***********************************************************************
 * Daten
 ***********************************************************************/

INSERT INTO studiengang(stg_id, name, kuerzel, abschluss) VALUES
  (1, 'Interaktive Medien',        'IAM', 'BA')
, (2, 'Interaktive Mediensysteme', 'IMS', 'MA')
, (3, 'Informatik',                'IN',  'BSc')
, (4, 'Informatik',                'MIN', 'MSc')
;
 
INSERT INTO professor (p_id, name, url, email) VALUES
  (1, 'Kowarschick', 'https://kowa.hs-augsburg.de/',                                               'kowa@hs-augsburg.de')
, (2, 'Klever',      'https://klever.hs-augsburg.de/',                                             'klever@hs-augsburg.de')
, (3, 'Rist',        'https://www.hs-augsburg.de/Informatik/Thomas-Rist.html',                     'tr@rz.hs-augsburg.de')
, (4, 'Kipp',        'https://www.michaelkipp.de',                                                 'michael.kipp@hs-augsburg.de')
, (5, 'Teynor',      'https://www.hs-augsburg.de/Informatik/Prof.-Dr.-Ing.-alexandra-teynor.html', 'alexandra.teynor@hs-augsburg.de')
, (6, 'Mayer',       'https://www.mayer.de/',                                                      null)
, (7, 'Mayer',       'https://www.mayerzwo.de/',                                                   'mayerzwo@hs-augsburg.de')
;


INSERT INTO student (s_id, stg_id, name, wohnort) VALUES
  (1, 1, 'Anton',  'Augsburg')
, (2, 1, 'Berta',  'Affing')
, (3, 1, 'Cäsar',  'Augsburg')
, (4, 1, 'Dora',   'Augsburg')
, (5, 2, 'Emil',   'München')
, (6, 3, 'Frieda', 'Bobingen')
, (7, 2, 'Gerda',  'München')
, (8, 3, 'Hans',   'Königsbrunn')
;

INSERT INTO prueft(p_id, s_id, fach, raum, beginn, dauer) VALUES
  (1, 1, 'MMDB',  'J2.10', TIMESTAMP '2016-01-29 10:00', INTERVAL '20 minutes')
, (1, 2, 'MMDB',  'J2.10', TIMESTAMP '2016-01-29 10:20', INTERVAL '20 minutes')
, (1, 3, 'MMDB',  'J2.10', TIMESTAMP '2016-01-29 10:40', INTERVAL '40 minutes')
, (1, 4, 'MMDB',  'J2.10', TIMESTAMP '2016-01-29 10:40', INTERVAL '40 minutes')
, (1, 5, 'MMDB',  'J2.10', TIMESTAMP '2016-01-29 10:40', INTERVAL '40 minutes')
, (2, 2, 'Netz2', NULL,    TIMESTAMP '2016-01-29 10:45', INTERVAL '15 minutes')
, (5, 2, 'SEng',  'M2.03', TIMESTAMP '2016-01-29 10:45', INTERVAL '15 minutes')
, (2, 3, 'Netz2', NULL,    TIMESTAMP '2016-01-29 11:00', INTERVAL '15 minutes')
, (5, 3, 'SEng',  'M2.03', TIMESTAMP '2016-02-01 12:00', INTERVAL '15 minutes')
, (5, 8, 'SEng',  'J2.13', TIMESTAMP '2016-01-29 11:00', INTERVAL '15 minutes')
, (2, 8, 'Netz2', 'J2.13', TIMESTAMP '2016-02-01 12:00', INTERVAL '15 minutes')
;