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') ;