SQLize | PHPize | SQLtest

CREATE TABLE besitzer ( besitzer_id integer PRIMARY KEY, name varchar(20), vorname varchar(20), strasse varchar(50), hausnr smallint, plz integer ); CREATE TABLE hunde ( hunde_id integer PRIMARY KEY, hunde_name varchar(20), rasse varchar(40), geschlecht char, besitzer_id integer REFERENCES besitzer(besitzer_id), CHECK (geschlecht = 'w' OR geschlecht = 'm') ); CREATE TABLE trainer ( trainer_id integer PRIMARY KEY, name varchar(20), vorname varchar(20) ); CREATE TABLE zertifikat ( zertifikat_id integer PRIMARY KEY, bezeichnung varchar(100) ); CREATE TABLE kurs ( kurs_nr integer PRIMARY KEY, kursname varchar(100), trainer_id integer REFERENCES trainer(trainer_id), zertifikat_id integer REFERENCES zertifikat(zertifikat_id) ); CREATE TABLE termin ( termin_nr integer PRIMARY KEY, datum date null, uhr time, kurs_nr integer REFERENCES kurs(kurs_nr), hunde_id integer REFERENCES hunde(hunde_id) ); CREATE TABLE hunde_zertifikat ( hunde_id integer REFERENCES hunde(hunde_id), zertifikat_id integer REFERENCES zertifikat(zertifikat_id), note numeric(8,1), PRIMARY KEY (hunde_id, zertifikat_id) ); CREATE TABLE rudel ( rudel_nr integer PRIMARY KEY, rudelname varchar(40) ); CREATE TABLE rudel_mitglied ( rudel_nr integer REFERENCES rudel(rudel_nr), mitglied_id integer REFERENCES hunde(hunde_id) ); CREATE TABLE hunderennen ( zeitraum varchar(20) PRIMARY KEY, rennname varchar(40) ); CREATE TABLE rennerergebnis ( zeitraum varchar(20) REFERENCES hunderennen(zeitraum), rudel_nr integer REFERENCES rudel(rudel_nr), endzeit time, PRIMARY KEY (zeitraum, rudel_nr) ); INSERT INTO besitzer VALUES (1, 'Holland', 'Tom', 'Alicenstr', 9, 35392); INSERT INTO besitzer VALUES (2, 'Cena', 'John', 'Breitenstr', 17, 35398); INSERT INTO besitzer VALUES (3, 'Downey', 'Robert', 'Schillerstr', 11, 35390); INSERT INTO hunde VALUES (1, 'Molly', 'Husky', 'w', 1); INSERT INTO hunde VALUES (2, 'Arthur', 'Shiba Inu', 'm', 2); INSERT INTO hunde VALUES (3, 'Petra', 'Dogge', 'm', 2); INSERT INTO hunde VALUES (4, 'Bonty', 'Dogge', 'w', 1); INSERT INTO hunde VALUES (5, 'Winter', 'Husky', 'm', 3); INSERT INTO hunde VALUES (6, 'Summer', 'Dogge', 'w', 3); INSERT INTO hunde VALUES (7, 'Autumn', 'Shiba Inu', 'm', 1); INSERT INTO hunde VALUES (8, 'Star', 'Dogge', 'w', 3); INSERT INTO hunde VALUES (9, 'Lily', 'Shiba Inu', 'w', 2); INSERT INTO trainer VALUES (1, 'Ackerman', 'Levi'); INSERT INTO trainer VALUES (2, 'D. Luffy', 'Monkey'); INSERT INTO trainer VALUES (3, 'Jaeger', 'Eren'); INSERT INTO trainer VALUES (4, 'Arlert', 'Armin'); INSERT INTO zertifikat VALUES (1, 'Bachelor of Blanket'); INSERT INTO zertifikat VALUES (2, 'Bachelor of Clicks'); INSERT INTO zertifikat VALUES (3, 'Master of Poison'); INSERT INTO zertifikat VALUES (4, 'Master of Tricks'); INSERT INTO zertifikat VALUES (5, 'Bachelor of Fetch'); INSERT INTO kurs VALUES (1, 'Grundkurs Deckentraining', 1, 1); INSERT INTO kurs VALUES (2, 'Clickertraining', 1, 2); INSERT INTO kurs VALUES (3, 'Anti-Giftkoeder-Training', 2, 3); INSERT INTO kurs VALUES (4, 'Hundetricks', 3, 4); INSERT INTO kurs VALUES (5, 'Apportieren lernen', 2, 5); INSERT INTO termin VALUES (1, '2024-01-31', now(), 1, 1); INSERT INTO termin VALUES (2, '2023-01-06', now(), 3, 4); INSERT INTO termin VALUES (3, '2023-08-24', now(), 5, 5); INSERT INTO termin VALUES (4, '2023-07-01', now(), 2, 3); INSERT INTO termin VALUES (5, '2023-07-03', now(), 2, 6); INSERT INTO hunde_zertifikat VALUES (1, 2, 3.5); INSERT INTO hunde_zertifikat VALUES (2, 3, 1.2); INSERT INTO hunde_zertifikat VALUES (2, 4, 2.0); INSERT INTO hunde_zertifikat VALUES (4, 1, 4.3); INSERT INTO hunde_zertifikat VALUES (2, 5, 3.3); INSERT INTO hunde_zertifikat VALUES (4, 5, 1.7); INSERT INTO rudel VALUES (1, 'At Least We Tried'); INSERT INTO rudel VALUES (2, 'Born Pro'); INSERT INTO rudel VALUES (3, 'Legends Never Die'); INSERT INTO rudel_mitglied VALUES (1, 1); INSERT INTO rudel_mitglied VALUES (2, 2); INSERT INTO rudel_mitglied VALUES (1, 9); INSERT INTO rudel_mitglied VALUES (3, 5); INSERT INTO rudel_mitglied VALUES (3, 4); INSERT INTO rudel_mitglied VALUES (2, 6); INSERT INTO rudel_mitglied VALUES (1, 7); INSERT INTO rudel_mitglied VALUES (2, 3); INSERT INTO rudel_mitglied VALUES (3, 8); INSERT INTO hunderennen VALUES ('20/21', 'Winter Solstice Race'); INSERT INTO hunderennen VALUES ('21/22', 'Winter Wonderland Competition'); INSERT INTO hunderennen VALUES ('22/23', 'Blizzard Snow Race'); INSERT INTO rennerergebnis VALUES ('20/21', 1, '00:05:30'); INSERT INTO rennerergebnis VALUES ('20/21', 2, '00:04:25'); INSERT INTO rennerergebnis VALUES ('20/21', 3, '00:03:55'); INSERT INTO rennerergebnis VALUES ('21/22', 1, '00:07:01'); INSERT INTO rennerergebnis VALUES ('21/22', 2, '00:06:40'); INSERT INTO rennerergebnis VALUES ('21/22', 3, '00:08:11'); INSERT INTO rennerergebnis VALUES ('22/23', 1, '00:08:09'); INSERT INTO rennerergebnis VALUES ('22/23', 2, '00:08:50'); INSERT INTO rennerergebnis VALUES ('22/23', 3, '00:09:06'); SELECT * FROM besitzer; SELECT * FROM hunde; SELECT * FROM trainer; SELECT * FROM zertifikat; SELECT * FROM kurs; SELECT * FROM termin; SELECT * FROM hunde_zertifikat; SELECT * FROM rudel; SELECT * FROM rudel_mitglied; SELECT * FROM hunderennen; SELECT * FROM rennerergebnis; /* Trigger - create a trigger that checks if a specific dog has Bachelor certificate before registering Master course */ /* needs fix */ CREATE OR REPLACE FUNCTION check_zertifikat() RETURNS trigger AS $$ DECLARE erforderlich integer; BEGIN SELECT hz.zertifikat_id INTO erforderlich FROM hunde_zertifikat hz WHERE hz.hunde_id = NEW.hunde_id; IF (NEW.kurs_nr = 3 OR NEW.kurs_nr = 4) AND erforderlich IS NULL THEN RAISE EXCEPTION 'Erforderliches Zertifikat fehlt --> 1, 2 oder 5' USING HINT = 'Bitte überprüfen Sie das Zertifikat Ihres Hundes'; END IF; RETURN NULL; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE TRIGGER tg_zertifikat BEFORE INSERT OR UPDATE ON termin FOR EACH ROW EXECUTE FUNCTION check_zertifikat(); /* Zum Testen */ INSERT INTO termin VALUES (6, '2023-07-02', now(), 3, 4); SELECT * FROM termin;