DROP TABLE IF EXISTS stg_zugeordnet_lv CASCADE; DROP TABLE IF EXISTS lp_haelt_lv CASCADE; DROP TABLE IF EXISTS lp_zugeordnet_stg CASCADE; DROP TABLE IF EXISTS fk_beteiligt_stg CASCADE; DROP TABLE IF EXISTS amt_stg CASCADE; DROP TABLE IF EXISTS amt_fk CASCADE; DROP TABLE IF EXISTS amtsbezeichnung CASCADE; DROP TABLE IF EXISTS lehrveranstaltung CASCADE; DROP TABLE IF EXISTS professor CASCADE; DROP TABLE IF EXISTS lehrperson CASCADE; DROP TABLE IF EXISTS studiengang CASCADE; DROP TABLE IF EXISTS fakultaet CASCADE; DROP DOMAIN IF EXISTS D_SPRECHSTUNDE CASCADE; DROP DOMAIN IF EXISTS D_DIENSTZIMMER CASCADE; DROP DOMAIN IF EXISTS D_TELEFON CASCADE; DROP DOMAIN IF EXISTS D_EMAIL CASCADE; DROP DOMAIN IF EXISTS D_NAME CASCADE; DROP DOMAIN IF EXISTS D_TITEL CASCADE; DROP DOMAIN IF EXISTS D_KUERZEL CASCADE; DROP DOMAIN IF EXISTS D_STATUS_LEHRPERSON CASCADE; DROP DOMAIN IF EXISTS D_ABSCHLUSS CASCADE; DROP DOMAIN IF EXISTS D_ANREDE CASCADE; CREATE DOMAIN D_ANREDE AS CHAR(4) CHECK (VALUE IN ('Herr', 'Frau')) ; CREATE DOMAIN D_ABSCHLUSS AS VARCHAR(8) CHECK (VALUE IN ('Bachelor', 'Master')) ; CREATE DOMAIN D_STATUS_LEHRPERSON AS VARCHAR(16) CHECK (VALUE IN ('Professor', 'Lehrbeauftragter', 'Lehrkraft')) ; CREATE DOMAIN D_KUERZEL AS VARCHAR(10) CHECK (VALUE ~ '\A[\wäöüß_.\-$]+\Z') ; CREATE DOMAIN D_TITEL AS VARCHAR(20) CHECK (VALUE ~ '\A[\wäöüß_.@$+\-/()\s]+\Z') ; CREATE DOMAIN D_NAME AS VARCHAR(100) CHECK (VALUE ~ '\A[\wäöüß_.@$+\-/()\s]+\Z') ; CREATE DOMAIN D_EMAIL AS VARCHAR(100) -- W3C: https://www.regextester.com/97767 CHECK (VALUE ~* '\A[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-z0-9-]+(?:\.[a-z0-9-]+)*\Z') ; CREATE DOMAIN D_TELEFON AS VARCHAR(100) CHECK (VALUE ~ '\A[0-9][0-9\-/()\s]*\Z') ; CREATE DOMAIN D_DIENSTZIMMER AS VARCHAR(6) CHECK (VALUE ~ '\A[A-Z][0-9][.][0-9][0-9][a-z]?\Z') ; CREATE DOMAIN D_SPRECHSTUNDE AS VARCHAR(50) CHECK (VALUE ~ '\A[\wäöüß_.!:,;+\-/()\s]+\Z') ; CREATE TABLE fakultaet (fk_id INTEGER NOT NULL, fk_kuerzel D_KUERZEL NOT NULL, fk_name D_NAME NOT NULL, fk_email D_EMAIL, fk_tel D_TELEFON NOT NULL, CONSTRAINT pk_fk PRIMARY KEY (fk_id), CONSTRAINT unique_fk_kuerzel UNIQUE (fk_kuerzel) ); CREATE TABLE studiengang (stg_id INTEGER NOT NULL, stg_kuerzel D_KUERZEL NOT NULL, stg_name D_NAME NOT NULL, stg_abschluss D_ABSCHLUSS NOT NULL, stg_titel D_NAME NOT NULL, stg_zugeordnet_zu INTEGER NOT NULL, CONSTRAINT pk_studiengang PRIMARY KEY (stg_id), CONSTRAINT unique_studiengang_kuerzel UNIQUE (stg_kuerzel), CONSTRAINT fk_stg_zugeordnet_zu_fk FOREIGN KEY (stg_zugeordnet_zu) REFERENCES fakultaet (fk_id) ); CREATE TABLE lehrperson (lp_id INTEGER NOT NULL, lp_kuerzel D_KUERZEL NOT NULL, lp_anrede D_ANREDE NOT NULL, lp_titel D_TITEL, lp_name D_NAME NOT NULL, lp_email D_EMAIL, lp_zugeordnet_zu INTEGER NOT NULL, lp_status D_STATUS_LEHRPERSON NOT NULL DEFAULT 'Lehrbeauftragter', CONSTRAINT pk_lehrperson PRIMARY KEY (lp_id), CONSTRAINT unique_lp_kuerzel UNIQUE (lp_kuerzel), CONSTRAINT fk_lp_zugeordnet_zu_fk FOREIGN KEY (lp_zugeordnet_zu) REFERENCES fakultaet (fk_id) ); CREATE TABLE professor (lp_id INTEGER NOT NULL, p_dienstzimmer D_DIENSTZIMMER, p_sprechstunde D_SPRECHSTUNDE NOT NULL, CONSTRAINT pk_professor PRIMARY KEY (lp_id), CONSTRAINT fk_professor_is_a_lehrperson FOREIGN KEY (lp_id) REFERENCES lehrperson (lp_id) ); CREATE TABLE lehrveranstaltung (lv_id INTEGER NOT NULL, lv_kuerzel D_KUERZEL NOT NULL, lv_titel D_NAME NOT NULL, lv_zugeordnet_zu INTEGER NOT NULL, CONSTRAINT pk_lehrveranstaltung PRIMARY KEY (lv_id), CONSTRAINT fk_lv_zugeordnet_zu FOREIGN KEY (lv_zugeordnet_zu) REFERENCES fakultaet (fk_id) ); CREATE TABLE amtsbezeichnung (ab_id INTEGER NOT NULL, ab_weiblich VARCHAR(50) NOT NULL, ab_maennlich VARCHAR(50) NOT NULL, ab_plural VARCHAR(50) NOT NULL, CONSTRAINT pk_amtsbezeichnung PRIMARY KEY (ab_id) ); CREATE TABLE amt_fk (fk_id INTEGER NOT NULL, lp_id INTEGER NOT NULL, ab_id INTEGER NOT NULL, CONSTRAINT pk_amt_fk PRIMARY KEY (lp_id, ab_id), CONSTRAINT fk_amt_fk_fk FOREIGN KEY (fk_id) REFERENCES fakultaet (fk_id), CONSTRAINT fk_amt_fk_prof FOREIGN KEY (lp_id) REFERENCES professor (lp_id), CONSTRAINT fk_amt_fk_ab FOREIGN KEY (ab_id) REFERENCES amtsbezeichnung (ab_id) ); CREATE TABLE amt_stg (stg_id INTEGER NOT NULL, lp_id INTEGER NOT NULL, ab_id INTEGER NOT NULL, CONSTRAINT pk_amt_stg PRIMARY KEY (stg_id, lp_id, ab_id), CONSTRAINT fk_amt_stg FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id), CONSTRAINT fk_amt_stg_prof FOREIGN KEY (lp_id) REFERENCES professor (lp_id), CONSTRAINT fk_amt_stg_ab FOREIGN KEY (ab_id) REFERENCES amtsbezeichnung (ab_id) ); CREATE TABLE fk_beteiligt_stg (fk_id INTEGER NOT NULL, stg_id INTEGER NOT NULL, CONSTRAINT pk_fk_beteiligt_stg PRIMARY KEY (fk_id, stg_id), CONSTRAINT fk_fk_beteiligt_stg_fk FOREIGN KEY (fk_id) REFERENCES fakultaet (fk_id), CONSTRAINT fk_fk_beteiligt_stg_stg FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id) ); CREATE TABLE lp_zugeordnet_stg (lp_id INTEGER NOT NULL, stg_id INTEGER NOT NULL, CONSTRAINT pk_lp_zugeordnet_stg PRIMARY KEY (lp_id, stg_id), CONSTRAINT fk_lp_zugeordnet_stg_lp FOREIGN KEY (lp_id) REFERENCES lehrperson (lp_id), CONSTRAINT fk_lp_zugeordnet_stg_stg FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id) ); CREATE TABLE lp_haelt_lv (lp_id INTEGER NOT NULL, lv_id INTEGER NOT NULL, CONSTRAINT pk_lp_haelt_lv PRIMARY KEY (lp_id, lv_id), CONSTRAINT fk_lp_haelt_lv_lp FOREIGN KEY (lp_id) REFERENCES lehrperson (lp_id), CONSTRAINT fk_lp_haelt_lv_lv FOREIGN KEY (lv_id) REFERENCES lehrveranstaltung (lv_id) ); CREATE TABLE stg_zugeordnet_lv (stg_id INTEGER NOT NULL, lv_id INTEGER NOT NULL, CONSTRAINT pk_stg_zugeordnet_lv PRIMARY KEY (stg_id, lv_id), CONSTRAINT fk_stg_zugeordnet_lv_stg FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id), CONSTRAINT fk_stg_zugeordnet_lv_lv FOREIGN KEY (lv_id) REFERENCES lehrveranstaltung (lv_id) );