Материјализирани погледи

Статистика на случаи по полициска станица

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

Анализа на типови на криминал

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

Анализа на перформансот на секој службеник

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

Сумирање на типови на докази

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

Индексирање

Индекси за People табелата

create index idx_people_embg on people(embg)

create index idx_people_names on people(first_name, last_name)

create index idx_people_nationality on people(nationality)

Индекси за Crime_case табелата

create index idx_crime_case_status on crime_case(c_status)

create index idx_crime_case_date on crime_case(opening_date)

Индекси за Statements табелата

create index idx_statements_date on statements(statement_date)

create index idx_statements_incident on statements(incident_timestamp)

Индекси за Evidence табелата

create index idx_evidence_type on evidence(e_type)

create index idx_evidence_found on evidence(is_found)

Индекси за Policeman табелата

create index idx_policeman_rank on policeman(rank)

Функции и тригери

Траење на случај во денови

create or replace function calculate_case_duration(case_id bigint)
returns integer as $$
declare
    opening_date date;
    closing_date date;
    duration integer;
begin
    select cc.opening_date into opening_date
    from crime_case as cc
    where cc.c_id = case_id;
    
    select sc.date_of_closing into closing_date
    from solved_case as sc
    where sc.c_id = case_id;
    
    if closing_date is not null then
        duration := closing_date - opening_date;
    else
        duration := CURRENT_DATE - opening_date;
    end if;
    
    return duration;
end;
$$ language plpgsql;

Промена на статус на случајот кога ќе биде завршен

create or replace function update_case_status_on_solve()
returns trigger as $$
begin
    update crime_case
    set c_status = 'Z'
    where c_id = NEW.c_id and c_status = 'A';
    
    return new;
end;
$$ language plpgsql;

create trigger trg_update_case_status
    after insert on solved_case
    for each row
    execute function update_case_status_on_solve();

Процедури и трансакции

Креирање на нов случај со иницјална изјава

create or replace procedure create_crime_case(
    in p_case_name varchar(255),
    in p_police_station_id bigint,
    in p_policeman_id bigint,
    in p_victim_id bigint,
    in p_witness_id bigint,
    in p_statement_description varchar(10000),
    in p_incident_timestamp timestamp,
    in p_incident_place varchar(4000),
    in p_crime_types bigint[]
)
language plpgsql as $$
declare
    new_case_id bigint;
    new_statement_id bigint;
    crime_type_id bigint;
begin
    select coalesce(MAX(c_id), 0) + 1 into new_case_id from crime_case;
    
    insert into crime_case (c_id, c_name, opening_date, c_status, p_id)
    values (new_case_id, p_case_name, CURRENT_DATE, 'A', p_police_station_id);
    
    select coalesce(MAX(s_id), 0) + 1 INTO new_statement_id FROM Statements;
    
    insert into Statements (s_id, statement_date, description, incident_timestamp, incident_place, c_id, pe_id, victim_pe_id, witness_pe_id)
    values (new_statement_id, CURRENT_DATE, p_statement_description, p_incident_timestamp, p_incident_place, new_case_id, p_policeman_id, p_victim_id, p_witness_id);
    
    foreach crime_type_id IN ARRAY p_crime_types
    loop
        insert into case_belongs_to_type_of_crime (c_id, t_id)
        values (new_case_id, crime_type_id);
    end loop;
    
    raise notice 'Случајот % е успешно креиран со изјава %', new_case_id, new_statement_id;
end;
$$;

Трансфер на случај помеѓу полициски станици

do $$
declare
    v_case_id bigint := 1;
    v_old_station bigint;
    v_new_station bigint := 2;
    v_new_officer bigint;
begin
    select p_id into v_old_station from crime_case where c_id = v_case_id;
    
    select pe_id into v_new_officer from police_station where p_id = v_new_station;
    
    update crime_case 
    set p_id = v_new_station 
    where c_id = v_case_id;
    
    insert into statements (s_id, statement_date, description, incident_timestamp, incident_place, c_id, pe_id, victim_pe_id, witness_pe_id)
    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
    from statements;
    
    raise notice 'Случајот со ID % е префрлен од полициска станица % во полициска станица %', v_case_id, v_old_station, v_new_station;    

exception
    when others then
        raise exception 'Transaction failed: %', SQLERRM;
end;
$$;

Релациона алгебра

Излистување на сите докази поврзани со предметот „Подморница“

π(e_name, e_type, is_found)(
  σ(c_name = 'Подморница')(
    Evidence ⋈(e_id) Mentions_evidence ⋈(s_id) Statements ⋈(c_id) Crime_case
  )
)

Излистување на сите луѓе инволвирани во случаи во Скопје

π(first_name, last_name, role)(
  ρ(role ← 'Accused')(
    σ(city = 'Скопје')(
      People ⋈(pe_id) Accused ⋈(pe_id=accused_pe_id) Accused_for ⋈(c_id) Crime_case ⋈(p_id) Police_station ⋈(s_id) Sector_of_interal_affairs
    )
  )
  ∪
  ρ(role ← 'Victim')(
    σ(city = 'Скопје')(
      People ⋈(pe_id) Victim ⋈(pe_id=victim_pe_id) Statements ⋈(c_id) Crime_case ⋈(p_id) Police_station ⋈(s_id) Sector_of_interal_affairs
    )
  )
)