WihYmhXn

1drop database if exists Zbrodnie;
2
3CREATE DATABASE Zbrodnie ;
4
5DROP TABLE dochodzenie, inspektorowie, narzedzie_zbrodni, notowani, podejrzani,więzienie, wyrok, zbrodnia CASCADE;
6
7CREATE TABLE Narzedzie_zbrodni(Nazwa VARCHAR(45) NOT NULl, rodzaj TEXT NOT NULL, legalne BOOLEAN NOT NULL, IleRazyUzyty INT, id_narzedzia_zbrodni INT PRIMARY KEY NOT NULL);
8
9ALTER TABLE Narzedzie_zbrodni ALTER COLUMN IleRazyUzyty SET DEFAULT 0;
10
11ALTER TABLE Narzedzie_zbrodni ADD CHECK ( rodzaj IN ('biala','palna'));
12
13
14CREATE TABLE Zbrodnia (Miasto varchar(45)  NOT NULL, Stan varchar(45)  NOT NULL, Czas date   NOT NULL, liczba_ofiar int   NOT NULL, id_narzedzia_zbrodni int   NOT NULL, id_zbrodni int  primary key  NOT NULL, foreign key(id_narzedzia_zbrodni) references Narzedzie_zbrodni(id_narzedzia_zbrodni));
15
16CREATE TABLE Inspektorowie ( Imie varchar(45)  NOT NULL, Nazwisko varchar(45)  NOT NULL, wiek int   NOT NULL, liczba_spraw int, id_inspektora int primary key  NOT NULL);
17
18ALTER TABLE Inspektorowie ALTER COLUMN liczba_spraw SET DEFAULT 0;
19
20CREATE TABLE Dochodzenie ( id_dochodzenia int primary key NOT NULL, id_inspektora int  NOT NULL, status TEXT  NOT NULL, czas_trwania int   NOT NULL, id_zbrodni int NOT NULL, foreign key(id_zbrodni) references Zbrodnia(id_zbrodni), foreign key(id_inspektora) references Inspektorowie(id_inspektora));
21
22
23ALTER TABLE Dochodzenie ADD CHECK ( status IN ('Zakończone', 'W_trakcie', 'Przerwane'));
24
25CREATE TABLE Podejrzani( Imie varchar(45)  NOT NULL, Nazwisko varchar(45)  NOT NULL, wiek int   NOT NULL, notowany boolean   NOT NULL, id_notowanego int  ,id_podejrzanego int primary key  NOT NULL, id_dochodzenia int NOT NULL);
26
27CREATE TABLE Więzienie ( Nazwa varchar(45)  NOT NULL, Adres varchar(45)  NOT NULL, Stan varchar(45)   NOT NULL, liczba_więzniów int   NOT NULL, id_więzienia int primary key  NOT NULL);
28
29CREATE TABLE Wyrok (Nazwa_sądu varchar(45)  NOT NULL, data date  NOT NULL, il_sędziów int   NOT NULL, wyrok int   NOT NULL, id_więzienia int, id_dochodzenia int   NOT NULL, id_oskarżonego int   NOT NULL, id_wyrok int primary key  NOT NULL, foreign key(id_dochodzenia) references Dochodzenie(id_dochodzenia), foreign key(id_oskarżonego) references Podejrzani(id_podejrzanego));
30
31
32CREATE TABLE Notowani ( Imie varchar(45)  NOT NULL, Nazwisko varchar(45)  NOT NULL, wiek int   NOT NULL, liczba_morderstw int   NOT NULL, NaWolności boolean  NOT NULL, id_wyrok int   NOT NULL, id_więzienia int   NOT NULL , id_notowanego int primary key  NOT NULL , foreign key(id_wyrok) references Wyrok(id_wyrok), foreign key(id_więzienia) references Więzienie(id_więzienia));
33
34 CREATE OR REPLACE FUNCTION NowyWiezien() RETURNS TRIGGER AS $$ BEGIN update Więzienie set liczba_więzniów=Więzienie.liczba_więzniów+1 where Więzienie.id_więzienia=New.id_więzienia;return null; END; $$ LANGUAGE 'plpgsql';
35CREATE TRIGGER NowyWiezien_trigger AFTER INSERT ON Wyrok FOR EACH ROW EXECUTE PROCEDURE NowyWiezien();
36
37CREATE OR REPLACE FUNCTION LiczbaUzycNarzedzia() RETURNS TRIGGER AS $$ BEGIN update Narzedzie_zbrodni set IleRazyUzyty=Narzedzie_zbrodni.IleRazyUzyty+1 WHERE Narzedzie_zbrodni.id_narzedzia_zbrodni=New.id_narzedzia_zbrodni; return null; END; $$ LANGUAGE 'plpgsql';
38
39CREATE TRIGGER LiczbaUzycNarzedzia133_trigger AFTER INSERT ON Zbrodnia FOR EACH ROW EXECUTE PROCEDURE LiczbaUzycNarzedzia();
40
41
42---NIE---Dziala---
43
44CREATE OR REPLACE FUNCTION dochodzenieDod() RETURNS TRIGGER AS $$ BEGIN insert into Dochodzenie(id_inspektora,id_dochodzenia,status,czas_trwania,id_zbrodni) values ((select id_inspektora from Inspektorowie order by random() limit 1),new.idDochodzenia,('W_trakcie',DATE_PART('day',New.Czas- now()),id_zbrodni);return null; END; $$ LANGUAGE 'plpgsql';
45
46CREATE TRIGGER dochodzeniedod_trigger AFTER INSERT ON Zbrodnia FOR EACH ROW EXECUTE PROCEDURE dochodzenieDOD);
47
48--------NIE--DZIALA---
49
50
51CREATE OR REPLACE FUNCTION RozwiazSprawe(numerDochodzenia INT) RETURNS TABLE ( NazwaSądu varchar(45), dataRozp date, LiczbaSędziów int, WyrokSądu int, WięzienieId int, dochodzenieId int,oskarżonyId int ,numerWyroku int)   AS $$ BEGIN RETURN QUERY select Nazwa_sądu, data,il_sędziów, wyrok,  id_więzienia, id_dochodzenia, id_oskarżonego, id_wyrok FROM Wyrok WHERE id_dochodzenia = numerDochodzenia; END; $$ LANGUAGE ‘plpgsql’;
52
53
54insert into Narzedzie_zbrodni(Nazwa,Rodzaj,legalne,IleRazyUzyty,id_narzedzia_zbrodni) values ('Nóż','biala',TRUE,1,101);
55insert into Narzedzie_zbrodni(Nazwa,Rodzaj,legalne,IleRazyUzyty,id_narzedzia_zbrodni) values ('Kij bejsbolowy','biala',TRUE,1,106);
56
57
58insert into Więzienie(Nazwa,Adres,Stan,liczba_więzniów,id_więzienia) values ('The United States Penitentiary','Beaumont','Texas',1460,666);
59insert into Więzienie(Nazwa,Adres,Stan,liczba_więzniów,id_więzienia) values ('Federal Correctional Complex','Butner','Indiana',1460,777);
60
61insert into Podejrzani(Imie,Nazwisko,wiek,notowany,id_dochodzenia,id_notowanego,id_podejrzanego) values ('Jack','Black',21,TRUE,68,1,123);
62insert into Podejrzani(Imie,Nazwisko,wiek,notowany,id_dochodzenia,id_notowanego,id_podejrzanego) values ('Stefan','Was',30,TRUE,90,3,543);
63
64
65insert into Inspektorowie(Imie,Nazwisko,wiek,liczba_spraw, id_inspektora) values ('Tom','Hardy',34,3,01);
66insert into Inspektorowie(Imie,Nazwisko,wiek,liczba_spraw, id_inspektora) values ('Hank','Rhodes',44,10,002);
67
68
69insert into Zbrodnia(Miasto,Stan,Czas,liczba_ofiar,id_narzedzia_zbrodni,id_zbrodni) values ('Chicago','Illinois','2017-06-04',1,101,2);
70insert into Zbrodnia(Miasto,Stan,Czas,liczba_ofiar,id_narzedzia_zbrodni,id_zbrodni) values ('Gdansk','Pomorskie','2019-01-18',1,106,3);
71
72insert into Dochodzenie(id_dochodzenia,id_inspektora,status,czas_trwania,id_zbrodni) values (2,01,'W_trakcie',31,2);
73insert into Dochodzenie(id_dochodzenia,id_inspektora,status,czas_trwania,id_zbrodni) values (4,002,'W_trakcie',12,3);
74
75insert into Wyrok(Nazwa_sądu,data,il_sędziów,wyrok,id_więzienia,id_dochodzenia,id_oskarżonego,id_wyrok) values ('Wielki','2017-08-03',3,25,666,2,123,14);
76insert into Wyrok(Nazwa_sądu,data,il_sędziów,wyrok,id_więzienia,id_dochodzenia,id_oskarżonego,id_wyrok) values ('Okregowy','2016-03-01',2,12,777,4,543,11);
77
78insert into Notowani(Imie, Nazwisko, wiek, liczba_morderstw, NaWolności, id_wyrok, id_więzienia, id_notowanego) values ('Slawomir','Piatek',23,5,TRUE, 10,666,999);
79insert into Notowani(Imie, Nazwisko, wiek, liczba_morderstw, NaWolności, id_wyrok, id_więzienia, id_notowanego) values ('Kazimierz','Wielki',30,1,FALSE, 11,777,388);
80
81Select RozwiazSprawe(4)