Changes between Version 2 and Version 3 of AdvancedApplicationDesign_v2
- Timestamp:
- 09/03/25 21:16:21 (6 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedApplicationDesign_v2
v2 v3 13 13 count(CASE when cc.c_status = 'Z' then 1 end) as closed_cases, 14 14 count(sc.c_id) as solved_cases 15 from Police_station as ps16 left join Crime_case as cc on ps.p_id = cc.p_id17 left join Solved_case as sc on cc.c_id = sc.c_id18 left join Sector_of_interal_affairs as sia on ps.s_id = sia.s_id15 from police_station as ps 16 left join crime_case as cc on ps.p_id = cc.p_id 17 left join solved_case as sc on cc.c_id = sc.c_id 18 left join sector_of_interal_affairs as sia on ps.s_id = sia.s_id 19 19 group by ps.p_id, ps.p_address, sia.city; 20 20 }}} … … 31 31 count(cbtc.c_id) as total_cases, 32 32 count(sc.c_id) as solved_cases, 33 34 from Type_of_crime as tc35 left join Case_belongs_to_type_of_crime as cbtc on tc.t_id = cbtc.t_id36 left join Crime_case as cc on cbtc.c_id = cc.c_id37 left join Solved_case as sc on cc.c_id = sc.c_id38 left join Accused_for as af on cc.c_id = af.c_id33 count(distinct af.accused_pe_id) as total_accused 34 from type_of_crime as tc 35 left join case_belongs_to_type_of_crime as cbtc on tc.t_id = cbtc.t_id 36 left join crime_case as cc on cbtc.c_id = cc.c_id 37 left join solved_case as sc on cc.c_id = sc.c_id 38 left join accused_for as af on cc.c_id = af.c_id 39 39 group by tc.t_id, tc.t_name; 40 40 }}} … … 55 55 ps.p_address as station_address, 56 56 count(distinct cc.c_id) as cases_managed 57 from Officer as o58 join People as p on o.pe_id = p.pe_id59 left join Police_station ps on o.pe_id = ps.pe_id60 left join Crime_case cc on ps.p_id = cc.p_id57 from officer as o 58 join people as p on o.pe_id = p.pe_id 59 left join police_station ps on o.pe_id = ps.pe_id 60 left join crime_case cc on ps.p_id = cc.p_id 61 61 group by o.pe_id, p.first_name, p.last_name, o.o_badge_no, o.o_date_of_employment, ps.p_address 62 62 … … 72 72 ps.p_address as station_address, 73 73 count(distinct s.c_id) as cases_handled 74 from Policeman as pm75 join People as p on pm.pe_id = p.pe_id76 left join Police_station as ps on pm.p_id = ps.p_id77 left join Statements as s on pm.pe_id = s.pe_id74 from policeman as pm 75 join people as p on pm.pe_id = p.pe_id 76 left join police_station as ps on pm.p_id = ps.p_id 77 left join statements as s on pm.pe_id = s.pe_id 78 78 group by pm.pe_id, p.first_name, p.last_name, pm.badge_no, pm.p_date_of_employment, ps.p_address; 79 79 }}} … … 90 90 count(case when e.is_found = true then 1 end) as found_evidence, 91 91 count(case when e.is_found = false then 1 end) as missing_evidence 92 from Evidence as e92 from evidence as e 93 93 group by e.e_type; 94 94 }}} … … 100 100 {{{#!div 101 101 {{{#!sql 102 create index idx_people_embg on People(embg);103 }}} 104 }}} 105 106 {{{#!div 107 {{{#!sql 108 create index idx_people_names on People(first_name, last_name);109 }}} 110 }}} 111 112 {{{#!div 113 {{{#!sql 114 create index idx_people_nationality on People(nationality);102 create index idx_people_embg on people(embg); 103 }}} 104 }}} 105 106 {{{#!div 107 {{{#!sql 108 create index idx_people_names on people(first_name, last_name); 109 }}} 110 }}} 111 112 {{{#!div 113 {{{#!sql 114 create index idx_people_nationality on people(nationality); 115 115 }}} 116 116 }}} … … 120 120 {{{#!div 121 121 {{{#!sql 122 create index idx_crime_case_status on Crime_case(c_status);123 }}} 124 }}} 125 126 {{{#!div 127 {{{#!sql 128 create index idx_crime_case_date on Crime_case(opening_date);122 create index idx_crime_case_status on crime_case(c_status); 123 }}} 124 }}} 125 126 {{{#!div 127 {{{#!sql 128 create index idx_crime_case_date on crime_case(opening_date); 129 129 }}} 130 130 }}} … … 134 134 {{{#!div 135 135 {{{#!sql 136 create index idx_statements_date on Statements(statement_date);137 }}} 138 }}} 139 140 {{{#!div 141 {{{#!sql 142 create index idx_statements_incident on Statements(incident_timestamp);136 create index idx_statements_date on statements(statement_date); 137 }}} 138 }}} 139 140 {{{#!div 141 {{{#!sql 142 create index idx_statements_incident on statements(incident_timestamp); 143 143 }}} 144 144 }}} … … 148 148 {{{#!div 149 149 {{{#!sql 150 create index idx_evidence_type on Evidence(e_type);151 }}} 152 }}} 153 154 {{{#!div 155 {{{#!sql 156 create index idx_evidence_found on Evidence(is_found);150 create index idx_evidence_type on evidence(e_type); 151 }}} 152 }}} 153 154 {{{#!div 155 {{{#!sql 156 create index idx_evidence_found on evidence(is_found); 157 157 }}} 158 158 }}} … … 162 162 {{{#!div 163 163 {{{#!sql 164 create index idx_policeman_rank on Policeman(rank);164 create index idx_policeman_rank on policeman(rank); 165 165 }}} 166 166 }}} … … 178 178 begin 179 179 select cc.opening_date into opening_date 180 from Crime_case as cc180 from crime_case as cc 181 181 where cc.c_id = case_id; 182 182 183 183 select sc.date_of_closing into closing_date 184 from Solved_case as sc184 from solved_case as sc 185 185 where sc.c_id = case_id; 186 186 … … 204 204 returns trigger as $$ 205 205 begin 206 update Crime_case206 update crime_case 207 207 set c_status = 'Z' 208 208 where c_id = NEW.c_id and c_status = 'A'; … … 213 213 214 214 create trigger trg_update_case_status 215 after insert on Solved_case215 after insert on solved_case 216 216 for each row 217 217 execute function update_case_status_on_solve(); … … 240 240 crime_type_id bigint; 241 241 begin 242 select coalesce(MAX(c_id), 0) + 1 into new_case_id from Crime_case;243 244 insert into Crime_case (c_id, c_name, opening_date, c_status, p_id)242 select coalesce(MAX(c_id), 0) + 1 into new_case_id from crime_case; 243 244 insert into crime_case (c_id, c_name, opening_date, c_status, p_id) 245 245 values (new_case_id, p_case_name, CURRENT_DATE, 'A', p_police_station_id); 246 246 … … 252 252 foreach crime_type_id IN ARRAY p_crime_types 253 253 loop 254 insert into Case_belongs_to_type_of_crime (c_id, t_id)254 insert into case_belongs_to_type_of_crime (c_id, t_id) 255 255 values (new_case_id, crime_type_id); 256 256 end loop; … … 273 273 v_new_officer bigint; 274 274 begin 275 -- Get current station 276 select p_id into v_old_station from Crime_case where c_id = v_case_id; 277 278 -- Get officer for new station 279 select pe_id into v_new_officer from Police_station where p_id = v_new_station; 280 281 -- Transfer case 282 update Crime_case 275 select p_id into v_old_station from crime_case where c_id = v_case_id; 276 277 select pe_id into v_new_officer from police_station where p_id = v_new_station; 278 279 update crime_case 283 280 set p_id = v_new_station 284 281 where c_id = v_case_id; 285 282 286 -- Add transfer statement 287 insert into Statements (s_id, statement_date, description, incident_timestamp, 288 incident_place, c_id, pe_id, victim_pe_id, witness_pe_id) 289 select coalesce(MAX(s_id), 0) + 1, CURRENT_DATE, 'Случајот е префрлен од станица ' || v_old_station || ' во станица ' || v_new_station, CURRENT_TIMESTAMP, 'Префрлување на случај', v_case_id, (select pe_id from Policeman where p_id = v_new_station limit 1), 2, 3 290 from Statements; 283 insert into statements (s_id, statement_date, description, incident_timestamp, incident_place, c_id, pe_id, victim_pe_id, witness_pe_id) 284 select coalesce(MAX(s_id), 0) + 1, CURRENT_DATE, 'Случајот е префрлен од станица ' || v_old_station || ' во станица ' || v_new_station, CURRENT_TIMESTAMP, 'Префрлување на случај', v_case_id, (select pe_id from policeman where p_id = v_new_station limit 1), 2, 3 285 from statements; 291 286 292 287 raise notice 'Случајот со ID % е префрлен од полициска станица % во полициска станица %', v_case_id, v_old_station, v_new_station;
