/******************** drop all triggeres and indexes ********************/
DROP TRIGGER update_crime_record;
DROP TRIGGER change_associate;

DROP INDEX officer_ix;
DROP INDEX report_ix;
DROP INDEX offender_ix;
DROP INDEX penalty_ix;

/******************** drop all tables ********************/
DROP TABLE crime_offender;
DROP TABLE crime_report;
DROP TABLE police_officer;
DROP TABLE crime;
DROP TABLE person;
DROP TABLE associate;
DROP TABLE offender;

/******************** drop all cluster ********************/
DROP CLUSTER clus_crime_report;
DROP CLUSTER clus_police_officer;

/******************** create cluster ********************/
CREATE CLUSTER clus_police_officer (officer_id char(4));
CREATE CLUSTER clus_crime_report (incident_id char(4));

/******************** create tables ********************/
create table police_officer (
     officer_id       char (4)  CONSTRAINT pk_officer PRIMARY KEY,
     officer_surname  char (30) CONSTRAINT notNull_o_surname NOT NULL,
     officer_firstname char (20),
     date_employed    date,
     station_name     char (20),
     station_address  char (20),
     station_postcode char (4),
     station_phone    char (15),
   CONSTRAINT chk_offcr_name CHECK (officer_surname != officer_firstname));

create table person (
      person_id        char (4) CONSTRAINT pk_person PRIMARY KEY,
      person_surname   char (30) CONSTRAINT notNull_p_surname NOT NULL,
      person_firstname char (20),
      gender           char (6) CONSTRAINT chk_f_m_psn CHECK (UPPER(gender) IN ('MALE','FEMALE')),
      person_address   char (20),
      person_postcode  char (4),
      person_phone     char (15),
    CONSTRAINT chk_psn_name CHECK (person_surname != person_firstname));

create table crime (
    crime_id         char (4) CONSTRAINT pk_crime PRIMARY KEY,
    crime_description char (30));

create table crime_report (
      incident_id	char (4) CONSTRAINT pk_incident PRIMARY KEY,
      officer_id	char (4) CONSTRAINT fk_police_officer REFERENCES police_officer(officer_id),
      person_id 	char(4) CONSTRAINT fk_person REFERENCES person(person_id),
      crime_id  	char(4) CONSTRAINT fk_crime REFERENCES crime(crime_id),
      crime_locality   char(15),
      crime_date	date,
      report_date      date,
      cost_of_crime    number(9,2))
    CLUSTER clus_police_officer (officer_id);

create table offender (
      offender_id        char (4) CONSTRAINT pk_offender PRIMARY KEY,
      offender_surname   char (30),
      offender_firstname char (20),
      gender           char (6) CONSTRAINT chk_f_m_offnd CHECK (UPPER(gender) IN ('MALE','FEMALE')),
      offender_address   char (20),
      offender_postcode  char (4),
      offender_phone     char (15),
      associate_no		integer default 0,
    CONSTRAINT chk_offnd_name CHECK (offender_surname != offender_firstname));

create table crime_offender (
      offender_id        char (4) CONSTRAINT fk_offender REFERENCES offender(offender_id),
      incident_id        char (4) CONSTRAINT fk_incident REFERENCES crime_report(incident_id),
      penalty            char (1) CONSTRAINT chk_penalty CHECK (UPPER(penalty) IN ('Y','N')),
    CONSTRAINT pk_offender_incident PRIMARY KEY (offender_id,incident_id))
    CLUSTER clus_crime_report (incident_id);
    
create table associate (
      associate_id        char (4) CONSTRAINT pk_associate PRIMARY KEY,
      offender_id        char (4) CONSTRAINT fk_ass_offender REFERENCES offender(offender_id),
      associate_surname   char (30),
      associate_firstname char (20),
      gender           char (6) CONSTRAINT chk_f_m_asst CHECK (UPPER(gender) IN ('MALE','FEMALE')),
      associate_address   char (20),
      associate_postcode  char (4),
      associate_phone     char (15),
    CONSTRAINT chk_associate_name CHECK (associate_surname != associate_firstname));

/******************** create index ********************/
CREATE INDEX officer_ix ON CLUSTER clus_police_officer;
CREATE INDEX report_ix ON CLUSTER clus_crime_report;
CREATE INDEX offender_ix ON offender(offender_surname);
CREATE BITMAP INDEX penalty_ix ON  crime_offender (penalty);

/******************** Create trigger for check reporting date********************/
CREATE OR REPLACE TRIGGER update_crime_record
  BEFORE 
  INSERT OR UPDATE ON crime_report
  FOR EACH ROW
  DECLARE 
	valid_period CONSTANT NUMBER :=7;
  BEGIN
    IF :old.report_date + valid_period <= sysdate
    THEN
 	  Raise_application_error(-20001,
 		'May not update crime record that occured 1 week ago!!');
    END IF;
  END;
/
/********** test trigger (allow update)*********/
UPDATE crime_report
	SET cost_of_crime = '1300'
	WHERE incident_id = 'I011';
/********** test trigger (not allow update)*********/
UPDATE crime_report
	SET cost_of_crime = '1300'
	WHERE incident_id = 'I012';
/******************** create trigger to change number of associate in offender table */
CREATE OR REPLACE TRIGGER change_associate
  AFTER
  INSERT OR UPDATE OR DELETE ON associate
  FOR EACH ROW
  BEGIN
   IF DELETING THEN
     UPDATE offender
	  SET associate_no= associate_no - 1
	  WHERE offender_id=:old.offender_id; 
   END IF;
   IF UPDATING THEN
     UPDATE offender
	  SET associate_no= associate_no - 1
	  WHERE offender_id=:old.offender_id;
     UPDATE offender
	  SET associate_no= associate_no + 1
	  WHERE offender_id=:new.offender_id ;
   END IF;
   IF INSERTING THEN
     UPDATE offender
	  SET associate_no= associate_no + 1
	  WHERE offender_id=:new.offender_id ;
   END IF;
  END;
/

/******************** load sample data into police_officer table ********************/
INSERT INTO police_officer(
	officer_id,officer_surname,officer_firstname,date_employed,
	station_name,station_address,station_postcode,station_phone)
VALUES ('O001','Lim','Top','01-Mar-2000','Clayton','Clayton Road','3800','03 9999 9999');

INSERT INTO police_officer(
	officer_id,officer_surname,officer_firstname,date_employed,
	station_name,station_address,station_postcode,station_phone)
VALUES ('O002','Lee','Hodson','01-Mar-2001','Melbourne','Collin Road','3000','03 8888 8888');

INSERT INTO police_officer(
	officer_id,officer_surname,officer_firstname,date_employed,
	station_name,station_address,station_postcode,station_phone)
VALUES ('O003','Kao','Nite','01-Mar-2002','Glen Waverley','Collin Road','3150','03 8888 8888');

INSERT INTO police_officer(
	officer_id,officer_surname,officer_firstname,date_employed,
	station_name,station_address,station_postcode,station_phone)
VALUES ('O004','Cheng','Hodson','01-Mar-2000','Box Hill','Whitehouse Road','3128','03 8888 8888');

INSERT INTO police_officer(
    officer_id,officer_surname,officer_firstname,date_employed,
	station_name,station_address,station_postcode,station_phone)
VALUES ('O005','Liao','Can','01-Mar-2002','Caulfield','Caulfield Road','3130','03 8888 8888');

/******************** load sample data into person table ********************/
INSERT INTO person(
person_id,person_surname,person_firstname,gender,person_address,person_postcode,person_phone)
VALUES ('P001','Lee','A-Ha','Female','Clayton Street','3800','0411133333');

INSERT INTO person(
person_id,person_surname,person_firstname,gender,person_address,person_postcode,person_phone)
VALUES ('P002','Lim','Ham','Male','Mel Street','3000','0422234333');

INSERT INTO person(
person_id,person_surname,person_firstname,gender,person_address,person_postcode,person_phone)
VALUES ('P003','An','Apple','Female','High Street','3150','03 9802 0000');

INSERT INTO person(
person_id,person_surname,person_firstname,gender,person_address,person_postcode,person_phone)
VALUES ('P004','Chan','Jackie','Male','Will Street','3800','03 9876 5432');

INSERT INTO person(
person_id,person_surname,person_firstname,gender,person_address,person_postcode,person_phone)
VALUES ('P005','Chen','Beauti','Female','Rich Street','3800','03 9888 8888');

/******************** load sample data into crime table ********************/
INSERT INTO Crime(
crime_id,crime_description)
VALUES ('C001','theft');

INSERT INTO Crime(
crime_id,crime_description)
VALUES ('C002','Robbery');

INSERT INTO Crime(
crime_id,crime_description)
VALUES ('C003','Fire');

INSERT INTO Crime(
crime_id,crime_description)
VALUES ('C004','Gun Shoot');

INSERT INTO Crime(
crime_id,crime_description)
VALUES ('C005','Assault');
/******************** load sample data into crime_report table ********************/
INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I001','O001','P001','C001','Clayton','01-Mar-2000','01-Mar-2000','1000');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I002','O002','P002','C002','Melbourne','01-Apr-2001','01-Apr-2001','500');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I003','O003','P003','C003','Glen Waverley','01-Jan-2002','01-Jan-2002','15000');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I004','O004','P004','C004','Box Hill','01-Dec-2000','31-Dec-2000','2000');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I005','O005','P005','C005','Caulfield','23-Nov-2002','23-Nov-2002','3000');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I006','O001','P001','C002','Clayton','21-July-2001','21-July-2001','5000');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I007','O002','P002','C002','Melbourne','01-Oct-2000','01-Oct-2000','100');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I008','O003','P003','C001','Glen Iris','11-Sep-2002','11-Sep-2002','420');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I009','O003','P004','C002','Mount Waverley','11-Oct-2002','11-Oct-2002','13000');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I010','O003','P005','C004','Knox City','11-Jan-2002','11-Jan-2002','400');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I011','O003','P003','C005','Toorak','11-Feb-2002','11-Feb-2002','1400');

INSERT INTO crime_report(
incident_id,officer_id,person_id,crime_id,crime_locality,crime_date,report_date,cost_of_crime)
VALUES ('I012','O003','P003','C005','Toorak',SYSDATE,SYSDATE,'2400');
/******************** load sample data into offender table ********************/
INSERT INTO offender (
      offender_id,
      offender_surname,
      offender_firstname,
      gender,
      offender_address,
      offender_postcode,
      offender_phone)
VALUES ('F001','LEE','LA','Male','WILL STREET','3100','03 9989 9901');

INSERT INTO offender (
      offender_id,
      offender_surname,
      offender_firstname,
      gender,
      offender_address,
      offender_postcode,
      offender_phone)
VALUES ('F002','TEE','TA','Male','BEN STREET','3180','03 9989 9900');

INSERT INTO offender (
      offender_id,
      offender_surname,
      offender_firstname,
      gender,
      offender_address,
      offender_postcode,
      offender_phone)
VALUES ('F003','WEE','WA','Female','CHI STREET','3200','03 9900 9900');

INSERT INTO offender (
      offender_id,
      offender_surname,
      offender_firstname,
      gender,
      offender_address,
      offender_postcode,
      offender_phone)
VALUES ('F004','EEI','WO','Female','BAD STREET','3100','03 9900 9900');


/******************** load sample data into crime_offender table ********************/
INSERT INTO crime_offender (
      offender_id,
      incident_id,
      penalty)
VALUES ('F001','I001','Y');

INSERT INTO crime_offender (
       offender_id,
       incident_id,
       penalty)
VALUES ('F002','I001','N');

INSERT INTO crime_offender (
       offender_id,
       incident_id,
       penalty)
VALUES ('F003','I001','N');
/******************** load sample data into associate table ********************/
INSERT INTO associate (
      associate_id,
      offender_id,
      associate_surname,
      associate_firstname,
      gender,
      associate_address,
      associate_postcode,
      associate_phone)
VALUES('AS01','F003','BI','PHI','Female','Hi Street','3100','9899 9000');

INSERT INTO associate (
      associate_id,
      offender_id,
      associate_surname,
      associate_firstname,
      gender,
      associate_address,
      associate_postcode,
      associate_phone)
VALUES('AS02','F003','BI','PHI','Female','Hi Street','3100','9899 9000');

INSERT INTO associate (
      associate_id,
      offender_id,
      associate_surname,
      associate_firstname,
      gender,
      associate_address,
      associate_postcode,
      associate_phone)
VALUES('AS03','F001','BI','PHI','Female','Hi Street','3100','9899 9000');

INSERT INTO associate (
      associate_id,
      offender_id,
      associate_surname,
      associate_firstname,
      gender,
      associate_address,
      associate_postcode,
      associate_phone)
VALUES('AS04','F001','BI','PHI','Female','Hi Street','3100','9899 9000');


---------------------------------------------------------------------------------------
/**Requirement #1 Include detail of offenders in the system*/
SELECT * FROM 	offender;
---------------------------------------------------------------------------------------
/**Requirement #2 Details of the categories of crime which an offender have engaged in are stored.*/
SELECT crime.crime_id,crime.crime_description,offender.offender_id,offender_surname,offender_firstname
FROM crime,crime_report,crime_offender,offender
WHERE crime.crime_id=crime_report.crime_id
AND crime_offender.incident_id=crime_report.incident_id
AND crime_offender.offender_id=offender.offender_id; 
---------------------------------------------------------------------------------------
/**Requirement #3 Record whether charges are laid against offenders*/
SELECT offender.offender_id,offender_surname,offender_firstname,penalty
FROM offender,crime_offender
WHERE offender.offender_id=crime_offender.offender_id;
---------------------------------------------------------------------------------------
/**Requirement #4 Record the officer responsible for charges laid against offenders*/
SELECT police_officer.officer_id,police_officer.officer_surname,
       offender.offender_id,offender_surname,offender_firstname,penalty
FROM offender,crime_offender,crime_report,police_officer
WHERE offender.offender_id=crime_offender.offender_id
AND police_officer.officer_id=police_officer.officer_id
AND crime_offender.incident_id=crime_report.incident_id;
---------------------------------------------------------------------------------------
/**Requirement #10 Record details of known associates of offenders.*/
SELECT offender.offender_id,offender_surname,offender_firstname,associate_surname
FROM offender,associate
WHERE offender.offender_id=associate.offender_id
ORDER BY associate.offender_id;

/**********test trigger for insert_associate **********/
SELECT offender_id,associate_no FROM offender --> before add associate into a offender
	WHERE offender_id='F003';
	
INSERT INTO associate ( --> insert an associated person of the offender
      associate_id,
      offender_id,
      associate_surname,
      associate_firstname,
      gender,
      associate_address,
      associate_postcode,
      associate_phone)
VALUES('AS05','F003','QI','YI','Male','Hi Street','3100','9899 9000');

SELECT offender_id,associate_no FROM offender --> after add associate into the offender record
	WHERE offender_id='F003';
	
/**********test trigger for delete_associate**********/
SELECT offender_id,associate_no FROM offender --> before delete associate into a offender
 WHERE offender_id='F003';
	
DELETE FROM associate --> delete an associated person of the offender
 WHERE associate_id='AS05';

SELECT offender_id,associate_no FROM offender--> after delete an associate from the offender record
 WHERE offender_id='F003';
/**********test trigger for update_associate**********/
SELECT offender_id,associate_no FROM offender --> before delete associate into a offender
 WHERE offender_id IN ('F003','F001');
	
UPDATE associate --> delete an associated person of the offender
 SET offender_id='F001'
 WHERE associate_id='AS01';

SELECT offender_id,associate_no FROM offender--> after delete an associate from the offender record
 WHERE offender_id IN ('F003','F001');

/******************** Q6. calcuate size 

6.1 The crime_report table: assume the blocks are 2048 bytes and are 80% full.

	Row length = 4+4+4+4+5+6+6+6=49 bytes

	2048/49=41 allow for 80 % full so 41 * 0.8 = 33 rows per block	

	there are 1000 records so require 

	1000/33 = 30 blocks
 

6.2 An index based on the incident_id on the crime_report table
 
	Index entry row length 4 bytes for key and 6 bytes for pointer = 4+6 = 10

	2048/10=204 allow for 80 % full so 204 * 0.8 = 163 rows per block

	there are 2000 students so require 

	1000/163 = 6 blocks 

********************/