/************************************************************************************* * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick *************************************************************************************/ /* Alte Views, Tabellen und Domains (in der richtigen Reihenfolge) löschen. */ DROP VIEW IF EXISTS haendler_liefert_ware CASCADE; DROP VIEW IF EXISTS liefert CASCADE; DROP VIEW IF EXISTS ware CASCADE; DROP VIEW IF EXISTS haendler CASCADE; DROP VIEW IF EXISTS typ CASCADE; DROP TABLE IF EXISTS r_liefert CASCADE; DROP TABLE IF EXISTS e_ware CASCADE; DROP TABLE IF EXISTS e_haendler CASCADE; DROP TABLE IF EXISTS enum_typ CASCADE; DROP DOMAIN IF EXISTS D_LIEFERZEIT CASCADE; /* Neue Domains erstellen. */ -- CREATE DOMAIN IF NOT EXISTS D_UNTAINTED -- AS -- VARCHAR CHECK (value !~ '[<>"'';]|--|/\*'); DO $$ BEGIN CREATE DOMAIN D_UNTAINTED AS VARCHAR CHECK (value !~ '[<>"'';]|--|/\*'); EXCEPTION WHEN duplicate_object THEN null; END $$; CREATE DOMAIN D_LIEFERZEIT AS SMALLINT CHECK (value > 0); /* Neue Tabellen erstellen. */ CREATE TABLE enum_typ (t_id INTEGER NOT NULL, t_name D_UNTAINTED NOT NULL, t_supertyp_id INTEGER, CONSTRAINT pk_enum_typ PRIMARY KEY (t_id), CONSTRAINT fk_enum_typ_enum_typ FOREIGN KEY (t_supertyp_id) REFERENCES enum_typ (t_id), CONSTRAINT unique_enum_typ_tname UNIQUE (t_name) ); CREATE TABLE e_haendler (h_id INTEGER NOT NULL, h_name D_UNTAINTED NOT NULL, h_ortschaft D_UNTAINTED, CONSTRAINT pk_haendler PRIMARY KEY (h_id), CONSTRAINT unique_haendler_name_ortschaft UNIQUE (h_name, h_ortschaft) ); CREATE TABLE e_ware (w_id INTEGER NOT NULL, t_id INTEGER NOT NULL DEFAULT 0, w_bezeichnung D_UNTAINTED NOT NULL, CONSTRAINT pk_ware PRIMARY KEY (w_id), CONSTRAINT fk_ware_typ FOREIGN KEY (t_id) REFERENCES enum_typ (t_id), CONSTRAINT unique_ware_t_id_bezeichnung UNIQUE (t_id, w_bezeichnung) ); CREATE TABLE r_liefert (h_id INTEGER NOT NULL, w_id INTEGER NOT NULL, l_preis NUMERIC(8,2) NOT NULL, l_lieferzeit D_LIEFERZEIT, CONSTRAINT pk_liefert PRIMARY KEY (h_id,w_id,l_preis), CONSTRAINT fk_liefert_haendler FOREIGN KEY (h_id) REFERENCES e_haendler (h_id), CONSTRAINT fk_liefert_ware FOREIGN KEY (w_id) REFERENCES e_ware (w_id) ); /* Neue Updatable Views erstellen.*/ CREATE VIEW typ (t_id, t_name, t_supertyp_id) AS SELECT t_id, t_name, t_supertyp_id FROM enum_typ ; CREATE VIEW ware (w_id, w_typ, w_bezeichnung) AS SELECT w_id, t_name, w_bezeichnung FROM e_ware JOIN enum_typ USING (t_id) ; CREATE VIEW haendler (h_id, h_name, h_ortschaft) AS SELECT h_id, h_name, h_ortschaft FROM e_haendler ; CREATE VIEW liefert (h_id, w_id, l_preis, l_lieferzeit) AS SELECT h_id, w_id, l_preis, l_lieferzeit FROM r_liefert ; CREATE VIEW haendler_liefert_ware (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ; DROP RULE IF EXISTS insert_into_ware ON ware CASCADE; DROP RULE IF EXISTS delete_from_ware ON ware CASCADE; DROP RULE IF EXISTS update_ware ON ware CASCADE; CREATE RULE insert_into_ware AS ON INSERT TO ware DO INSTEAD INSERT INTO e_ware (w_id, t_id, w_bezeichnung) VALUES (NEW.w_id, (SELECT t_id FROM enum_typ WHERE t_name = NEW.w_typ), NEW.w_bezeichnung ) ; CREATE RULE delete_from_ware AS ON DELETE TO ware DO INSTEAD DELETE FROM e_ware WHERE w_id = OLD.w_id ; CREATE RULE update_ware AS ON UPDATE TO ware DO INSTEAD UPDATE e_ware SET w_id = NEW.w_id, w_id = (SELECT t_id FROM enum_typ WHERE t_name = NEW.w_typ), w_bezeichnung = NEW.w_bezeichnung WHERE w_id = OLD.w_id ; DROP RULE IF EXISTS insert_into_haendler ON haendler CASCADE; DROP RULE IF EXISTS delete_from_haendler ON haendler CASCADE; DROP RULE IF EXISTS update_haendler ON haendler CASCADE; CREATE RULE insert_into_haendler AS ON INSERT TO haendler DO INSTEAD INSERT INTO e_haendler (h_id, h_name, h_ortschaft) VALUES (NEW.h_id, NEW.h_name, NEW.h_ortschaft) ; CREATE RULE delete_from_haendler AS ON DELETE TO haendler DO INSTEAD DELETE FROM e_haendler WHERE h_id = OLD.h_id ; CREATE RULE update_haendler AS ON UPDATE TO haendler DO INSTEAD UPDATE e_haendler SET h_id = NEW.h_id, h_name = NEW.h_name, h_ortschaft = NEW.h_ortschaft WHERE h_id = OLD.h_id ; DROP RULE IF EXISTS insert_into_liefert ON liefert CASCADE; DROP RULE IF EXISTS delete_from_liefert ON liefert CASCADE; DROP RULE IF EXISTS update_liefert ON liefert CASCADE; CREATE RULE insert_into_liefert AS ON INSERT TO liefert DO INSTEAD INSERT INTO r_liefert (h_id, w_id, l_preis, l_lieferzeit) VALUES (NEW.h_id, NEW.w_id, NEW.l_preis, NEW.l_lieferzeit) ; CREATE RULE delete_from_liefert AS ON DELETE TO liefert DO INSTEAD DELETE FROM r_liefert WHERE h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis ; CREATE RULE update_liefert AS ON UPDATE TO liefert DO INSTEAD UPDATE r_liefert SET h_id = NEW.h_id, w_id = NEW.w_id, l_preis = NEW.l_preis, l_lieferzeit = NEW.l_lieferzeit WHERE h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis ; DROP RULE IF EXISTS delete_from_haendler_liefert_ware ON haendler_liefert_ware CASCADE; CREATE RULE delete_from_haendler_liefert_ware AS ON DELETE TO haendler_liefert_ware DO INSTEAD DELETE FROM r_liefert WHERE h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis ; /* Daten einfügen */ INSERT INTO typ(t_id, t_name, t_supertyp_id) VALUES (0, 'Sonstiges', NULL), (1, 'Computerzubehör', NULL), (2, 'CPU', 1), (3, 'RAM', 1) ; INSERT INTO haendler(h_id, h_name, h_ortschaft) VALUES (1, 'Maier', 'Königsbrunn'), (2, 'Müller', 'Königsbrunn'), (3, 'Maier', 'Augsburg'), (4, 'Huber', NULL), (5, 'Schmidt', 'Hamburg') ; INSERT INTO ware(w_id, w_typ, w_bezeichnung) VALUES (1, 'CPU', 'Pentium IV 3,8'), (2, 'CPU', 'Celeron 2,6'), (3, 'CPU', 'Athlon XP 3000+'), (4, 'RAM', 'SDRAM 1GB'), (5, 'Sonstiges', 'Eieruhr') ; /* Fehler! 'CPUx' gibt es nicht als Typ! INSERT INTO ware(w_id, w_typ, w_bezeichnung) VALUES (10, 'CPUx', 'Pentium IV 3,8') ; */ INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit) VALUES (1, 1, 200.00, 1), (1, 1, 194.00, 6), (1, 2, 100.00, NULL), (1, 3, 150.00, 7), (1, 4, 10.00, 1), (1, 5, 5.00, 1), (2, 1, 160.00, NULL), (2, 1, 190.00, 1), (2, 2, 180.00, NULL), (2, 3, 170.00, 4), (2, 5, 5.00, 1), (3, 1, 195.00, 2), (3, 2, 190.00, 1), (4, 1, 150.00, 3), (4, 3, 180.00, 5), (4, 3, 199.99, 1) ;