Changes between Version 3 and Version 4 of AdvancedApplicationDesign_v2
- Timestamp:
- 09/03/25 21:50:08 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedApplicationDesign_v2
v3 v4 17 17 left join solved_case as sc on cc.c_id = sc.c_id 18 18 left join sector_of_interal_affairs as sia on ps.s_id = sia.s_id 19 group by ps.p_id, ps.p_address, sia.city ;19 group by ps.p_id, ps.p_address, sia.city 20 20 }}} 21 21 }}} … … 37 37 left join solved_case as sc on cc.c_id = sc.c_id 38 38 left join accused_for as af on cc.c_id = af.c_id 39 group by tc.t_id, tc.t_name ;39 group by tc.t_id, tc.t_name 40 40 }}} 41 41 }}} … … 76 76 left join police_station as ps on pm.p_id = ps.p_id 77 77 left join statements as s on pm.pe_id = s.pe_id 78 group by pm.pe_id, p.first_name, p.last_name, pm.badge_no, pm.p_date_of_employment, ps.p_address ;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 }}} 80 80 }}} … … 91 91 count(case when e.is_found = false then 1 end) as missing_evidence 92 92 from evidence as e 93 group by e.e_type ;93 group by e.e_type 94 94 }}} 95 95 }}} … … 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 }}} … … 294 294 }}} 295 295 }}} 296 297 = Релациона алгебра = 298 === Излистување на сите докази поврзани со предметот „Подморница“ === 299 {{{#!div 300 {{{#!sql 301 π(e_name, e_type, is_found)( 302 σ(c_name = 'Подморница')( 303 Evidence ⋈(e_id) Mentions_evidence ⋈(s_id) Statements ⋈(c_id) Crime_case 304 ) 305 ) 306 }}} 307 }}} 308 309 === Излистување на сите луѓе инволвирани во случаи во Скопје === 310 {{{#!div 311 {{{#!sql 312 π(first_name, last_name, role)( 313 ρ(role ← 'Accused')( 314 σ(city = 'Скопје')( 315 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 316 ) 317 ) 318 ∪ 319 ρ(role ← 'Victim')( 320 σ(city = 'Скопје')( 321 People ⋈(pe_id) Victim ⋈(pe_id=victim_pe_id) Statements ⋈(c_id) Crime_case ⋈(p_id) Police_station ⋈(s_id) Sector_of_interal_affairs 322 ) 323 ) 324 ) 325 }}} 326 }}}
