sfff

Tables (29)

crime

  • crime_id — NUMBER(5)
  • crime_code — VARCHAR2(10) — NOT NULL
  • crime_type — VARCHAR2(50) — NOT NULL
  • crime_date — DATE — NOT NULL
  • description — VARCHAR2(500)
  • status — VARCHAR2(10)
  • date_solved — DATE
  • severity_level — VARCHAR2(10)
  • location_id — NUMBER(5) — NOT NULL
  • recording_officer_id — NUMBER(5) — NOT NULL
  • investigating_officer_id — NUMBER(5)
  • supervising_officer_id — NUMBER(5)

crime_evidence

  • crime_id — NUMBER(5)
  • evidence_id — NUMBER(5)
  • relevance_description — VARCHAR2(200)
  • date_linked — DATE

crime_offender

  • crime_id — NUMBER(5)
  • person_id — NUMBER(5)
  • role_in_crime — VARCHAR2(50)
  • charges_filed — VARCHAR2(200)
  • conviction_status — VARCHAR2(20)

crime_prevention

  • project_id — NUMBER(5)
  • crime_id — NUMBER(5)
  • prevention_method — VARCHAR2(100)
  • effectiveness_rating — NUMBER(1)
  • date_implemented — DATE

crime_victim

  • crime_id — NUMBER(5)
  • person_id — NUMBER(5)
  • injury_severity — VARCHAR2(20)
  • medical_attention_required — VARCHAR2(3)

crime_witness

  • crime_id — NUMBER(5)
  • person_id — NUMBER(5)
  • witness_type — VARCHAR2(20)
  • cooperation_level — VARCHAR2(20)

equipment

  • equipment_id — NUMBER(5)
  • equipment_name — VARCHAR2(100) — NOT NULL
  • equipment_type — VARCHAR2(50)
  • description — VARCHAR2(200)
  • quantity_available — NUMBER(4)
  • storage_location — VARCHAR2(100)

event

  • event_id — NUMBER(5)
  • event_name — VARCHAR2(100) — NOT NULL
  • event_type — VARCHAR2(50)
  • event_date — DATE — NOT NULL
  • location — VARCHAR2(100)
  • description — VARCHAR2(300)
  • expected_attendees — NUMBER(4)

evidence

  • evidence_id — NUMBER(5)
  • evidence_type — VARCHAR2(50) — NOT NULL
  • description — VARCHAR2(300)
  • date_collected — DATE — NOT NULL
  • collected_by — VARCHAR2(100)
  • storage_location — VARCHAR2(100)
  • status — VARCHAR2(20)

funding

  • funding_id — NUMBER(5)
  • project_id — NUMBER(5) — NOT NULL
  • funding_source — VARCHAR2(100) — NOT NULL
  • amount — NUMBER(8,2) — NOT NULL
  • funding_date — DATE
  • purpose — VARCHAR2(200)
  • status — VARCHAR2(20)

location

  • location_id — NUMBER(5)
  • address — VARCHAR2(100) — NOT NULL
  • city — VARCHAR2(50) — NOT NULL
  • postcode — VARCHAR2(10)
  • region — VARCHAR2(50) — NOT NULL
  • country — VARCHAR2(50)
  • location_type — VARCHAR2(20) — NOT NULL

offender

  • person_id — NUMBER(5)
  • criminal_record — CLOB
  • sentence_details — VARCHAR2(300)
  • sentence_start_date — DATE
  • sentence_end_date — DATE
  • probation_officer — VARCHAR2(100)

officer

  • officer_id — NUMBER(5)
  • badge_number — VARCHAR2(10) — NOT NULL
  • first_name — VARCHAR2(50) — NOT NULL
  • last_name — VARCHAR2(50) — NOT NULL
  • rank — VARCHAR2(30) — NOT NULL
  • department — VARCHAR2(50) — NOT NULL
  • phone — VARCHAR2(20)
  • email — VARCHAR2(100)
  • date_joined — DATE

person

  • person_id — NUMBER(5)
  • first_name — VARCHAR2(50) — NOT NULL
  • last_name — VARCHAR2(50) — NOT NULL
  • date_of_birth — DATE
  • address — VARCHAR2(100)
  • phone — VARCHAR2(20)
  • email — VARCHAR2(100)
  • person_type — VARCHAR2(20) — NOT NULL

project

  • project_id — NUMBER(5)
  • project_name — VARCHAR2(100) — NOT NULL
  • project_type — VARCHAR2(50) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE
  • description — VARCHAR2(500)
  • tasks — VARCHAR2(500)
  • duration_days — NUMBER(4)
  • is_abroad — VARCHAR2(3)
  • travel_details — VARCHAR2(300)
  • location_id — NUMBER(5) — NOT NULL
  • project_leader_id — NUMBER(5) — NOT NULL

project_equipment

  • project_id — NUMBER(5)
  • equipment_id — NUMBER(5)
  • quantity_used — NUMBER(4)
  • date_issued — DATE
  • date_returned — DATE
  • condition_on_return — VARCHAR2(100)

project_skill

  • project_id — NUMBER(5)
  • skill_id — NUMBER(5)
  • skill_level_required — VARCHAR2(20)

related_crimes

  • primary_crime_id — NUMBER(5)
  • related_crime_id — NUMBER(5)
  • relationship_type — VARCHAR2(50) — NOT NULL
  • description — VARCHAR2(200)
  • CHECK — (primary_crime_id != related_crime_id)

skill

  • skill_id — NUMBER(5)
  • skill_name — VARCHAR2(50) — NOT NULL
  • skill_category — VARCHAR2(50)
  • description — VARCHAR2(200)

staff_volunteer

  • volunteer_id — NUMBER(5)
  • staff_id — VARCHAR2(10) — NOT NULL
  • department — VARCHAR2(50) — NOT NULL
  • position — VARCHAR2(50) — NOT NULL
  • employment_start_date — DATE

student_volunteer

  • volunteer_id — NUMBER(5)
  • student_id — VARCHAR2(10) — NOT NULL
  • course — VARCHAR2(100) — NOT NULL
  • year_of_study — NUMBER(1)
  • expected_graduation — DATE

training

  • training_id — NUMBER(5)
  • training_name — VARCHAR2(100) — NOT NULL
  • training_type — VARCHAR2(50)
  • duration_hours — NUMBER(3)
  • provider — VARCHAR2(100)
  • description — VARCHAR2(300)

victim

  • person_id — NUMBER(5)
  • victim_statement — CLOB
  • injuries — VARCHAR2(200)
  • compensation_claimed — VARCHAR2(3)
  • compensation_amount — NUMBER(8,2)

volunteer

  • volunteer_id — NUMBER(5)
  • first_name — VARCHAR2(50) — NOT NULL
  • last_name — VARCHAR2(50) — NOT NULL
  • date_of_birth — DATE
  • address — VARCHAR2(100)
  • phone — VARCHAR2(20)
  • email — VARCHAR2(100)
  • emergency_contact_name — VARCHAR2(100)
  • emergency_contact_phone — VARCHAR2(20)
  • volunteer_type — VARCHAR2(20) — NOT NULL
  • insurance_details — VARCHAR2(200)
  • date_registered — DATE

volunteer_event

  • volunteer_id — NUMBER(5)
  • event_id — NUMBER(5)
  • role — VARCHAR2(50)
  • hours_contributed — NUMBER(4,2)
  • feedback — VARCHAR2(300)

volunteer_project

  • volunteer_id — NUMBER(5)
  • project_id — NUMBER(5)
  • hours_worked — NUMBER(5,2)
  • start_date — DATE
  • end_date — DATE
  • role — VARCHAR2(50)
  • performance_rating — NUMBER(1)

volunteer_skill

  • volunteer_id — NUMBER(5)
  • skill_id — NUMBER(5)
  • proficiency_level — VARCHAR2(20)
  • years_experience — NUMBER(2)
  • date_acquired — DATE

volunteer_training

  • volunteer_id — NUMBER(5)
  • training_id — NUMBER(5)
  • completion_date — DATE — NOT NULL
  • trainer_name — VARCHAR2(100)
  • score — NUMBER(3)
  • certificate_issued — VARCHAR2(3)

witness

  • person_id — NUMBER(5)
  • witness_statement — CLOB
  • testimony_date — DATE
  • reliability_rating — NUMBER(1)
  • court_attendance_required — VARCHAR2(3)