Changes between Initial Version and Version 1 of AdvancedApplicationDesign_v2

Timestamp:

09/03/25 19:49:46 (8 weeks ago)

Author:

212012

Comment:

--

AdvancedApplicationDesign_v2

@@ +1 @@
+= Материјализирани погледи =
+=== Статистика на случаи по полициска станица ===
+
+{{{#!div
+{{{#!sql
+create materialized view case_statistics_by_station as
+select 
+    ps.p_id,
+    ps.p_address as station_address,
+    sia.city,
+    count(cc.c_id) as total_cases,
+    count(case when cc.c_status = 'A' then 1 end) as active_cases,
+    count(CASE when cc.c_status = 'Z' then 1 end) as closed_cases,
+    count(sc.c_id) as solved_cases
+from Police_station as ps
+left join Crime_case as cc on ps.p_id = cc.p_id
+left join Solved_case as sc on cc.c_id = sc.c_id
+left join Sector_of_interal_affairs as sia on ps.s_id = sia.s_id
+group by ps.p_id, ps.p_address, sia.city;
+}}}
+}}}
+
+=== Анализа на типови на криминал ===
+
+{{{#!div
+{{{#!sql
+create materialized view crime_type_analysis as
+select
+    tc.t_id,
+    tc.t_name,
+    count(cbtc.c_id) as total_cases,
+    count(sc.c_id) as solved_cases,
+        count(distinct af.accused_pe_id) as total_accused
+from Type_of_crime as tc
+left join Case_belongs_to_type_of_crime as cbtc on tc.t_id = cbtc.t_id
+left join Crime_case as cc on cbtc.c_id = cc.c_id
+left join Solved_case as sc on cc.c_id = sc.c_id
+left join Accused_for as af on cc.c_id = af.c_id
+group by tc.t_id, tc.t_name;
+}}}
+}}}
+
+=== Анализа на перформансот на секој службеник ===
+
+{{{#!div
+{{{#!sql
+create materialized view police_performance as
+select 
+    'Officer' as role_type,
+    o.pe_id,
+    p.first_name,
+    p.last_name,
+    o.o_badge_no as badge_number,
+    o.o_date_of_employment as date_of_employment,
+    ps.p_address as station_address,
+    count(distinct cc.c_id) as cases_managed
+from Officer as o
+join People as p on o.pe_id = p.pe_id
+left join Police_station ps on o.pe_id = ps.pe_id
+left join Crime_case cc on ps.p_id = cc.p_id
+group by o.pe_id, p.first_name, p.last_name, o.o_badge_no, o.o_date_of_employment, ps.p_address
+
+union all
+
+select 
+    'Policeman' as role_type,
+    pm.pe_id,
+    p.first_name,
+    p.last_name,
+    pm.badge_no as badge_number,
+    pm.p_date_of_employment as date_of_employment,
+    ps.p_address as station_address,
+    count(distinct s.c_id) as cases_handled
+from Policeman as pm
+join People as p on pm.pe_id = p.pe_id
+left join Police_station as ps on pm.p_id = ps.p_id
+left join Statements as s on pm.pe_id = s.pe_id
+group by pm.pe_id, p.first_name, p.last_name, pm.badge_no, pm.p_date_of_employment, ps.p_address;
+}}}
+}}}
+
+=== Сумирање на типови на докази ===
+
+{{{#!div
+{{{#!sql
+create materialized view evidence_summary as
+select 
+    e.e_type,
+    count(*) as total_evidence,
+    count(case when e.is_found = true then 1 end) as found_evidence,
+    count(case when e.is_found = false then 1 end) as missing_evidence
+from Evidence as e
+group by e.e_type;
+}}}
+}}}
+
+{{{#!div
+{{{#!sql
+}}}
+}}}