Index_creation.sql

-- All indexes uses to speed up queries from milestone 3 (sorted by table):

CREATE INDEX PARTIES_IDX_PARTY_AGE ON PARTIES(PARTY_AGE);
CREATE INDEX PARTIES_IDX_AT_FAULT_PARTY_AGE ON PARTIES(AT_FAULT, PARTY_AGE);
CREATE INDEX PARTIES_IDX_COLLISION_CASE_ID_STATEWIDE_VEHICLE_TYPE_ID ON PARTIES(COLLISION_CASE_ID, STATEWIDE_VEHICLE_TYPE_ID);
CREATE INDEX PARTIES_IDX_ID_VEHICLE_MAKE ON PARTIES(ID, VEHICLE_MAKE);
CREATE INDEX PARTIES_IDX_COLLISION_CASE_ID_ID ON PARTIES(COLLISION_CASE_ID, ID);
CREATE INDEX PARTIES_IDX_STATEWIDE_VEHICLE_TYPE_ID_VEHICLE_MAKE_VEHICLE_YEAR ON PARTIES(STATEWIDE_VEHICLE_TYPE_ID, VEHICLE_MAKE, VEHICLE_YEAR);

CREATE INDEX VICTIMS_IDX_PARTY_ID_VICTIM_DEGREE_OF_INJURY_ID ON VICTIMS(PARTY_ID, VICTIM_DEGREE_OF_INJURY_ID);
CREATE INDEX VICTIM_DEGREE_OF_INJURY_IDX_DEFINITION_ID ON VICTIM_DEGREE_OF_INJURY(DEFINITION, ID);
CREATE INDEX VICTIMS_IDX_PARTY_ID_VICTIM_AGE ON VICTIMS(PARTY_ID, VICTIM_AGE);

CREATE INDEX COLLISIONS_IDX_CASE_ID_COUNTY_CITY_LOCATION ON COLLISIONS(CASE_ID, COUNTY_CITY_LOCATION);
CREATE INDEX COLLISIONS_IDX_CASE_ID_TYPE_OF_COLLISION_ID ON COLLISIONS(CASE_ID, TYPE_OF_COLLISION_ID);

CREATE INDEX ROAD_CONDITION_IDX_DEFINITION_ID ON ROAD_CONDITION(DEFINITION, ID);

CREATE INDEX TYPE_OF_COLLISION_IDX_DEFINITION_ID ON TYPE_OF_COLLISION(DEFINITION, ID);

CREATE INDEX STATEWIDE_VEHICLE_TYPE_IDX_DEFINITION_ID ON STATEWIDE_VEHICLE_TYPE(DEFINITION, ID);

DROP INDEX PARTIES_IDX_PARTY_AGE;
DROP INDEX PARTIES_IDX_AT_FAULT_PARTY_AGE;
DROP INDEX PARTIES_IDX_COLLISION_CASE_ID_STATEWIDE_VEHICLE_TYPE_ID;
DROP INDEX PARTIES_IDX_ID_VEHICLE_MAKE;
DROP INDEX PARTIES_IDX_COLLISION_CASE_ID_ID;
DROP INDEX PARTIES_IDX_STATEWIDE_VEHICLE_TYPE_ID_VEHICLE_MAKE_VEHICLE_YEAR;

DROP INDEX VICTIMS_IDX_PARTY_ID_VICTIM_DEGREE_OF_INJURY_ID;
DROP INDEX VICTIM_DEGREE_OF_INJURY_IDX_DEFINITION_ID;
DROP INDEX VICTIMS_IDX_PARTY_ID_VICTIM_AGE;

DROP INDEX COLLISIONS_IDX_CASE_ID_COUNTY_CITY_LOCATION;
DROP INDEX COLLISIONS_IDX_CASE_ID_TYPE_OF_COLLISION_ID;

DROP INDEX ROAD_CONDITION_IDX_DEFINITION_ID;

DROP INDEX TYPE_OF_COLLISION_IDX_DEFINITION_ID;

DROP INDEX STATEWIDE_VEHICLE_TYPE_IDX_DEFINITION_ID;

-- Index by queries from milestone 3:

-- Q1: 
CREATE INDEX PARTIES_IDX_PARTY_AGE ON PARTIES(PARTY_AGE);
CREATE INDEX PARTIES_IDX_AT_FAULT_PARTY_AGE ON PARTIES(AT_FAULT, PARTY_AGE);

-- Q2:
CREATE INDEX PARTIES_IDX_COLLISION_CASE_ID_STATEWIDE_VEHICLE_TYPE_ID ON PARTIES(COLLISION_CASE_ID, STATEWIDE_VEHICLE_TYPE_ID);
CREATE INDEX ROAD_CONDITION_IDX_DEFINITION_ID ON ROAD_CONDITION(DEFINITION, ID);
CREATE INDEX STATEWIDE_VEHICLE_TYPE_IDX_DEFINITION_ID ON STATEWIDE_VEHICLE_TYPE(DEFINITION, ID);

-- Q3:
CREATE INDEX PARTIES_IDX_ID_VEHICLE_MAKE ON PARTIES(ID, VEHICLE_MAKE);
CREATE INDEX VICTIMS_IDX_PARTY_ID_VICTIM_DEGREE_OF_INJURY_ID ON VICTIMS(PARTY_ID, VICTIM_DEGREE_OF_INJURY_ID);
CREATE INDEX VICTIM_DEGREE_OF_INJURY_IDX_DEFINITION_ID ON VICTIM_DEGREE_OF_INJURY(DEFINITION, ID);

-- Q5: 
CREATE INDEX PARTIES_IDX_COLLISION_CASE_ID_STATEWIDE_VEHICLE_TYPE_ID ON PARTIES(COLLISION_CASE_ID, STATEWIDE_VEHICLE_TYPE_ID);
CREATE INDEX COLLISIONS_IDX_CASE_ID_COUNTY_CITY_LOCATION ON COLLISIONS(CASE_ID, COUNTY_CITY_LOCATION);

-- Q7:
CREATE INDEX VICTIMS_IDX_PARTY_ID_VICTIM_AGE ON VICTIMS(PARTY_ID, VICTIM_AGE);
CREATE INDEX PARTIES_IDX_COLLISION_CASE_ID_ID ON PARTIES(COLLISION_CASE_ID, ID);
CREATE INDEX COLLISIONS_IDX_CASE_ID_TYPE_OF_COLLISION_ID ON COLLISIONS(CASE_ID, TYPE_OF_COLLISION_ID);
CREATE INDEX TYPE_OF_COLLISION_IDX_DEFINITION_ID ON TYPE_OF_COLLISION(DEFINITION, ID);

-- Q8:
CREATE INDEX STATEWIDE_VEHICLE_TYPE_IDX_DEFINITION_ID ON STATEWIDE_VEHICLE_TYPE(DEFINITION, ID);
CREATE INDEX PARTIES_IDX_STATEWIDE_VEHICLE_TYPE_ID_VEHICLE_MAKE_VEHICLE_YEAR ON PARTIES(STATEWIDE_VEHICLE_TYPE_ID, VEHICLE_MAKE, VEHICLE_YEAR);


--Q1
DROP INDEX PARTIES_IDX_PARTY_AGE;
DROP INDEX PARTIES_IDX_AT_FAULT_PARTY_AGE;

--Q2
DROP INDEX PARTIES_IDX_COLLISION_CASE_ID_STATEWIDE_VEHICLE_TYPE_ID;
DROP INDEX ROAD_CONDITION_IDX_DEFINITION_ID;
DROP INDEX STATEWIDE_VEHICLE_TYPE_IDX_DEFINITION_ID;

--Q3
DROP INDEX PARTIES_IDX_ID_VEHICLE_MAKE;
DROP INDEX VICTIMS_IDX_PARTY_ID_VICTIM_DEGREE_OF_INJURY_ID;
DROP INDEX VICTIM_DEGREE_OF_INJURY_IDX_DEFINITION_ID;

--Q5
DROP INDEX PARTIES_IDX_COLLISION_CASE_ID_STATEWIDE_VEHICLE_TYPE_ID;
DROP INDEX COLLISIONS_IDX_CASE_ID_COUNTY_CITY_LOCATION;

--Q7
DROP INDEX VICTIMS_IDX_PARTY_ID_VICTIM_AGE;
DROP INDEX PARTIES_IDX_COLLISION_CASE_ID_ID;
DROP INDEX COLLISIONS_IDX_CASE_ID_TYPE_OF_COLLISION_ID;
DROP INDEX TYPE_OF_COLLISION_IDX_DEFINITION_ID;

--Q8
DROP INDEX STATEWIDE_VEHICLE_TYPE_IDX_DEFINITION_ID;
DROP INDEX PARTIES_IDX_STATEWIDE_VEHICLE_TYPE_ID_VEHICLE_MAKE_VEHICLE_YEAR;