Aplikacja

Link do Muzeum

Diagram ERD

Skrypt tworzący bazę danych w PostgreSQL

DROP TABLE IF EXISTS Historia; DROP TABLE IF EXISTS Galeria; DROP TABLE IF EXISTS Eksponat; DROP TABLE IF EXISTS Artysta; DROP TABLE IF EXISTS Instytucja; CREATE TABLE Galeria ( galeria_id VARCHAR(6) PRIMARY KEY, nazwa VARCHAR(300) NOT NULL UNIQUE, adres VARCHAR(300) NOT NULL ); CREATE TABLE Artysta ( artysta_id VARCHAR(6) PRIMARY KEY, imie VARCHAR(100) NOT NULL, nazwisko VARCHAR(100) NOT NULL, rok_urodzenia INTEGER NOT NULL CHECK (rok_urodzenia > 0 AND rok_urodzenia <= EXTRACT(YEAR FROM CURRENT_DATE)), rok_smierci INTEGER CHECK (rok_smierci IS NULL OR rok_smierci > rok_urodzenia) ); CREATE TABLE Eksponat ( eksponat_id VARCHAR(6) PRIMARY KEY, tytul VARCHAR(300) NOT NULL, artysta_id VARCHAR(6) NULL REFERENCES Artysta (artysta_id), typ VARCHAR(100) NOT NULL, czy_wypozyczalny BOOLEAN NOT NULL, wysokosc_cm DECIMAL(10, 2) NOT NULL CHECK (wysokosc_cm > 0), szerokosc_cm DECIMAL(10, 2) NOT NULL CHECK (szerokosc_cm > 0), waga_kg DECIMAL(10, 3) NOT NULL CHECK (waga_kg > 0) ); CREATE TABLE Instytucja ( instytucja_id VARCHAR(6) PRIMARY KEY, nazwa VARCHAR(300) NOT NULL UNIQUE, miasto VARCHAR(100) NOT NULL ); CREATE TABLE Historia ( historia_id VARCHAR(10) PRIMARY KEY, eksponat_id VARCHAR(6) NOT NULL REFERENCES Eksponat (eksponat_id) ON DELETE CASCADE, galeria_id VARCHAR(6) NULL REFERENCES Galeria (galeria_id), sala_id VARCHAR(10) NULL, instytucja_id VARCHAR(6) NULL REFERENCES Instytucja (instytucja_id), data_rozpoczecia DATE NOT NULL, data_zakonczenia DATE NOT NULL CHECK (data_rozpoczecia <= data_zakonczenia), CONSTRAINT check_lokalizacja CHECK ( ((galeria_id IS NOT NULL AND sala_id IS NOT NULL AND instytucja_id IS NULL) OR (galeria_id IS NULL AND sala_id IS NULL AND instytucja_id IS NOT NULL)) ) ); -- Wyzwalacz, który sprawdza, czy nowe wypożyczenie (lub edycja istniejącego) nie narusza zasady 30 dni. CREATE OR REPLACE FUNCTION sprawdz_liczbe_dni_wypozyczenia() RETURNS TRIGGER AS $$ DECLARE suma_dni INTEGER; rok INTEGER; dni_w_roku INTEGER; dni_stare INTEGER; BEGIN IF NEW.instytucja_id IS NOT NULL THEN -- Iteracja po latach, które obejmuje wypożyczenie FOR rok IN EXTRACT(YEAR FROM NEW.data_rozpoczecia)..COALESCE(EXTRACT(YEAR FROM NEW.data_zakonczenia), EXTRACT(YEAR FROM NEW.data_rozpoczecia)) LOOP dni_w_roku := LEAST(COALESCE(NEW.data_zakonczenia, CURRENT_DATE), TO_DATE(rok || '-12-31', 'YYYY-MM-DD')) - GREATEST(NEW.data_rozpoczecia, TO_DATE(rok || '-01-01', 'YYYY-MM-DD')); -- Jeśli to jest UPDATE, uwzględniamy dni z istniejącego rekordu IF TG_OP = 'UPDATE' THEN IF EXTRACT(YEAR FROM OLD.data_rozpoczecia) <= rok AND EXTRACT(YEAR FROM COALESCE(OLD.data_zakonczenia, CURRENT_DATE)) >= rok THEN dni_stare := LEAST(COALESCE(OLD.data_zakonczenia, CURRENT_DATE), TO_DATE(rok || '-12-31', 'YYYY-MM-DD')) - GREATEST(OLD.data_rozpoczecia, TO_DATE(rok || '-01-01', 'YYYY-MM-DD')); ELSE dni_stare := 0; END IF; ELSE dni_stare := 0; END IF; -- Obliczanie sumy dni wypożyczenia w danym roku (z uwzględnieniem zmian) SELECT COALESCE(SUM( LEAST(COALESCE(h.data_zakonczenia, CURRENT_DATE), TO_DATE(rok || '-12-31', 'YYYY-MM-DD')) - GREATEST(h.data_rozpoczecia, TO_DATE(rok || '-01-01', 'YYYY-MM-DD')) ), 0) INTO suma_dni FROM Historia h WHERE h.eksponat_id = NEW.eksponat_id AND h.instytucja_id IS NOT NULL AND EXTRACT(YEAR FROM h.data_rozpoczecia) <= rok AND (h.data_zakonczenia IS NULL OR EXTRACT(YEAR FROM h.data_zakonczenia) >= rok); suma_dni := suma_dni - dni_stare + dni_w_roku; IF suma_dni > 30 THEN RAISE EXCEPTION ' Nie można wypożyczyć eksponatu % na więcej niż 30 dni w roku %!', NEW.eksponat_id, rok; END IF; END LOOP; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE TRIGGER sprawdz_liczbe_dni_wypozyczenia_trigger BEFORE INSERT OR UPDATE ON Historia FOR EACH ROW EXECUTE FUNCTION sprawdz_liczbe_dni_wypozyczenia(); -- Wyzwalacz, który sprawdza, czy jest co najmniej 1 eksponat artysty w muzeum. CREATE OR REPLACE FUNCTION sprawdz_czy_jest_eksponat_artysty() RETURNS TRIGGER AS $$ DECLARE artysta_id_eksponatu VARCHAR(6); wszystkie_eksponaty INT; eksponaty_na_wypozyczeniu INT; dzien record; BEGIN -- Jeśli jest to galeria to nie musimy sprawdzać IF NEW.galeria_id is NOT NULL THEN RETURN NEW; END IF; -- Pobieramy artystę powiązanego z nowym eksponatem SELECT artysta_id INTO artysta_id_eksponatu FROM Eksponat WHERE eksponat_id = NEW.eksponat_id; -- Jeśli nie ma artysty to nie sprawdzamy IF artysta_id_eksponatu IS NULL THEN RETURN NEW; END IF; SELECT COUNT(*) INTO wszystkie_eksponaty FROM Eksponat e WHERE e.artysta_id = artysta_id_eksponatu; FOR dzien IN SELECT gs AS dzien FROM GENERATE_SERIES(NEW.data_rozpoczecia, NEW.data_zakonczenia, '1 day'::interval) gs LOOP SELECT COUNT(DISTINCT e.eksponat_id) INTO eksponaty_na_wypozyczeniu FROM Eksponat e JOIN Historia h on h.eksponat_id = e.eksponat_id WHERE e.artysta_id = artysta_id_eksponatu AND h.instytucja_id IS NOT NULL AND h.data_rozpoczecia <= dzien.dzien AND dzien.dzien <= h.data_zakonczenia; IF eksponaty_na_wypozyczeniu + 1 = wszystkie_eksponaty THEN RAISE EXCEPTION 'Co najmniej jeden eksponat artysty musi być w galerii w danym czasie'; END IF; END LOOP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE TRIGGER sprawdz_czy_jest_eksponat_artysty_trigger BEFORE INSERT OR UPDATE ON Historia FOR EACH ROW EXECUTE FUNCTION sprawdz_czy_jest_eksponat_artysty(); -- Wyzwalacz, który sprawdza czy daty wypożyczenia się nie nakładają CREATE OR REPLACE FUNCTION sprawdz_nakladanie_dat() RETURNS TRIGGER AS $$ DECLARE overlapping_count INT; BEGIN SELECT COUNT(*) INTO overlapping_count FROM Historia h WHERE h.eksponat_id = NEW.eksponat_id AND h.historia_id != COALESCE(NEW.historia_id, 'NULL') AND h.data_rozpoczecia <= NEW.data_zakonczenia AND NEW.data_rozpoczecia <= h.data_zakonczenia; -- Jeśli pokrywa sie z inną datą z historii to wyrzuca błąd IF overlapping_count > 0 THEN RAISE EXCEPTION 'Daty pokrywaja sie z istniejacymi datami w historii %', NEW.historia_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE TRIGGER sprawdz_nakladanie_dat_trigger BEFORE INSERT OR UPDATE ON Historia FOR EACH ROW EXECUTE FUNCTION sprawdz_nakladanie_dat(); -- Wyzwalacze, które będą automatycznie generowały id tak, że jeśli to jest id galerii -- to rozpocznie się od litery G, a id instytucji rozpocznie się od litery I etc. CREATE SEQUENCE IF NOT EXISTS seq_galeria_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE IF NOT EXISTS seq_artysta_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE IF NOT EXISTS seq_eksponat_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE IF NOT EXISTS seq_instytucja_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE IF NOT EXISTS seq_historia_id START WITH 1 INCREMENT BY 1; -- Dla galerii CREATE OR REPLACE FUNCTION generuj_galeria_id() RETURNS TRIGGER AS $$ BEGIN NEW.galeria_id := 'G' || LPAD(NEXTVAL('seq_galeria_id')::TEXT, 5, '0'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_galeria_id BEFORE INSERT ON Galeria FOR EACH ROW EXECUTE FUNCTION generuj_galeria_id(); -- Dla Artysty CREATE OR REPLACE FUNCTION generuj_artysta_id() RETURNS TRIGGER AS $$ BEGIN NEW.artysta_id := 'A' || LPAD(NEXTVAL('seq_artysta_id')::TEXT, 5, '0'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_artysta_id BEFORE INSERT ON Artysta FOR EACH ROW EXECUTE FUNCTION generuj_artysta_id(); -- Dla eksponatu CREATE OR REPLACE FUNCTION generuj_eksponat_id() RETURNS TRIGGER AS $$ BEGIN NEW.eksponat_id := 'E' || LPAD(NEXTVAL('seq_eksponat_id')::TEXT, 5, '0'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_eksponat_id BEFORE INSERT ON Eksponat FOR EACH ROW EXECUTE FUNCTION generuj_eksponat_id(); -- Dla instytucji CREATE OR REPLACE FUNCTION generuj_instytucja_id() RETURNS TRIGGER AS $$ BEGIN NEW.instytucja_id := 'I' || LPAD(NEXTVAL('seq_instytucja_id')::TEXT, 5, '0'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_instytucja_id BEFORE INSERT ON Instytucja FOR EACH ROW EXECUTE FUNCTION generuj_instytucja_id(); -- Dla Historii CREATE OR REPLACE FUNCTION generuj_historia_id() RETURNS TRIGGER AS $$ BEGIN NEW.historia_id := 'H' || LPAD(NEXTVAL('seq_historia_id')::TEXT, 9, '0'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_historia_id BEFORE INSERT ON Historia FOR EACH ROW EXECUTE FUNCTION generuj_historia_id();