Class 3.2 – Indices and foreign keys

create unique index on cases (casenumber);
alter table cases add column location geometry;
update cases set location = st_transform(st_setsrid(st_makepoint(xcoord, ycoord), 2264), 4326);
create index on cases using gist(location) ;

alter table inspections add foreign key (casenumber) references cases (casenumber) on delete cascade;
alter table violations add foreign key (casenumber) references cases (casenumber) on delete cascade;
alter table case_history add foreign key (casenumber) references cases (casenumber) on delete cascade;

Now delete a random case from cases and see how it disappears from the other tables.

Left join cases to violations, inspections, and case_history.

Attempt:

select 
(select count(*) from case_history where case_history.casenumber = cases.casenumber) as cnt,
* 
from cases
order by cnt desc
limit 100

Do an explain on the query, save the output. Then create an index and re-do the explain.

--create index on case_history (casenumber);
create index on inspections (casenumber);
create index on violations (casenumber);