Database Systems

Part A

For the police custody system, the identifiable spatial and non-spatial entities include;

Crime, Reporter, Witness, Offender, Victim, Police, Detective, Location (city and district are derived entities from location) and Address

Assumptions

  • That the reporter of the crime is not necessarily the witness
  • That a district is a larger division than a city
  • That a crime is recorded by a police and investigated by a detective

The table below summarizes the entities and their relationship

Entity name Description Relationship with other entities
Crime This is the offense Belongs to a type, affects a victim,
Reporter A citizen who reports the crime Has address,
Offender The person who commits crime Has address, commits a crime, offends
Police That who records the crime Records crime notes
Victim That who is affected by the crime Has address
Witness That who witnesses a crime Witnesses a crime, has address
Detective The police who investigates a crime Investigates
Address Address Has a district
District A region Has a city
City A city
Type

N/B: the attributes names have been shortened for easier labeling of the E-R diagram.

For example crime_id is shown as c_id, district is shown as dist and offender_id is shown as o_id. Foreign key, which creates the relationship of entities, are shown on the relationship. Primary key attributes are underlined.

ER1

ER2
Part B

The relational schema is shown below. Normalization has been shown

1NF

Crime (crime_id, district,city,date,object,crime_notes,reporter,city,district, address,victim_id,reporter_id,police_id,detective_id.witness_id,offender_id)

Reporter (reporter_id, NRIC_No,names,phone, address,postal_code,city,district)

Offender (offender_id, NRIC_No,names,phone,age,gender, address,postal_codecity,district)

Police (police_id,police_registratin_number,name)

Victim (victim_id, NRIC_No,names,phone,age,gender, address,postal_codecity,district)

Witness (witness_id, NRIC_No,names,phone, address,postal_codecity,district)

Detective (d_id,reg_number,name)

2NF

Crime (crime_id, district,city, date,object,crime_notes,reporter,,victim_id,reporter_id,police_id,detective_id,witness_id,offender_id)

Reporter (reporter_id, NRIC_No, f_name,l_name,phone, address)

Offender (offender_id, NRIC_No, f_name,l_name,phone,age,gender, address)

Police (police_id,police_registratin_number, , f_name,l_name)

Victim (victim_id, NRIC_No,f_name,l_name,phone,age,gender, address)

Witness (witness_id, NRIC_No,f_name,l_name,phone, address)

Detective (d_id,reg_number, f_name,l_name)

Address (address_id,address,postal_code,city,district)

3NF

Crime (crime_id, date,object, crime_notes,reporter,victim_id,reporter_id,police_id,detective_id,witness_id,offender_id,city_id)

Reporter (reporter_id, NRIC_No, f_name,l_name,phone, address_id)

Offender (offender_id, NRIC_No, f_name,l_name,phone,age,gender, address_id)

Police (police_id, NRIC_No,police_registratin_number, , f_name,l_name)

Victim (victim_id, NRIC_No, f_name,l_name,phone,age,gender, address_id)

Witness (witness_id, NRIC_No,f_name,l_name,phone, address_id)

Detective (d_id,reg_number, f_name,l_name)

Address (address_id,address,postal_code,city_id)

City(city_id,name,district_id)

District (district_id,name)

The relations have been elaborated in the table below

Crime (crime_id, date,object, crime_notes,reporter, victim_id,reporter_id, police_id, detective_id, witness_id,offender_id,city_id,date_resolved,status)

PK/FK Field name Null/Not NULL Data type
pk Crime_id NOT NULL integer
Date NOT NULL DATE
object NULL VARCHAR2(64)
Crime_notes NOT NULL Integer
fk Reporter_id NOT NULL Integer
Fk Police_id NOT NULL Integer
fk victim_id NOT NULL Integer
Fk Detective_id NOT NULL Integer
Fk Witness_id NOT NULL Integer
Fk Offender_id NOT NULL Integer
Fk City_id NOT NULL integer
Date_resolved NULL DATE
status NOT NULL VARCHAR2 (6)

Reporter (reporter_id, f_name,l_name,phone, address_id)

PK/FK Field name Null/Not NULL Data type
Pk Reporter_id NOT NULL Integer
Pk NRIC_No NOT NULL Integer
F_name NOT NULL Varchar2(32)
L_name NOT NULL Varchar2(32)
Phone NOT NULL Varchar2(15)
Fk Address_id NOT NULL Integer

Offender (offender_id, f_name,l_name,phone,age,gender, address_id)

PK/FK Field name Null/Not NULL Data type
Pk Offender_id Not NULL Integer
Pk NRIC_No NOT NULL Integer
F_name Not NULL Varchar2(32)
L_name Not NULL Varchar2(32)
Phone Not NULL Varchar2(15)
Age Not NULL Integer
Gender Not NULL Varchar2(6)
Fk Address_id Not NULL Integer

Police (police_id,police_registratin_number,name)

PK/FK Field name Null/Not NULL Data type
pk Police_id Not NULL Integer
pk Police_reg_number Not NULL Varcher2(10)
F_name Not NULL Varchar2(32)
L_name Not NULL Varchar2(32)

Victim (victim_id, f_name,l_name,phone,age,gender, address_id)

PK/FK Field name Null/Not NULL Data type
Pk NRIC_No NOT NULL Integer
Fk Victim_id Not NULL Integer
F_name Not NULL Varchar2(32)
L_name Not NULL Varchar2(32)
Phone Not NULL Varchar2(15)
Age Not NULL Integer
Gender Not NULL Varchar2(6)
Fk Address_id Not NULL Integer

Witness (witness_id,f_name,l_name,phone, address_id)

PK/FK Field name Null/Not NULL Data type
Pk Witness_id NOT NULL Integer
Pk NRIC_No NOT NULL Integer
F_name NOT NULL Varchar2(32)
L_name NOT NULL Varchar2(32)
phone NOT NULL Varchar2(15)
fk Address_id NOT NULL Integer

Detective (detective_id,reg_number, f_name,l_name)

PK/FK Field name Null/Not NULL Data type
pk Detective_id Not NULL Integer
pk Detective_reg_number Not NULL Varchar2(10)
F_name Not NULL Varchar2(32)
L_name Not NULL Varchar2(32)

Address (address_id,address,postal_code,cit y_id)

PK/FK Field name Null/Not NULL Data type
Pk Address_id Not NULL Integer
Address Not NULL Varchar2(32)
Postal_code Not NULL Integer
Fk City_id Not NULL Integer

City(city_id,name,district_id)

PK/FK Field name Null/Not NULL Data type
Pk City_id Not NULL Integer
Name Not NULL Varchar2(32)
Fk District_id Not NULL Integer

District (district_id,name)

PK/FK Field name Null/Not NULL Data type
pk District_id NOT NULL Integer
Name NOT NULL Varchar232

The oracle sql schema is shown below

create table district (

district_id integer not null,

name varchar2(32) not null,

CONSTRAINT district_id_pk PRIMARY KEY (district_id)

);

create table city (

city_id integer not null,

name varchar2(32) not null,

district_id integer not null,

PRIMARY KEY (city_id),

CONSTRAINT fk_district_id FOREIGN KEY (district_id) REFERENCES district(district_id)

);

create table address (

address_id integer not null,

address varchar2(32) not null,

postal_code integer not null,

city_id integer not null,

PRIMARY KEY(address_id),

CONSTRAINT fk_city_id FOREIGN KEY (city_id) REFERENCES city(city_id)

);

create table detective(

detective_id integer not null,

detective_reg_numer varchar2(10) not null,

f_name varchar2(32) not null,

l_name varchar2(32) not null,

primary key(detective_id),

CONSTRAINT detective_uni UNIQUE (detective_reg_numer)

);

create table witness(

witness_id integer not null,

nric_no integer not null,

f_name varchar2(32) not null,

l_name varchar2(32) not null,

phone varchar2(15) not null,

address_id integer not null,

primary key(witness_id),

CONSTRAINT witness_uni UNIQUE (nric_no),

CONSTRAINT fk_address_id_w FOREIGN KEY (address_id) REFERENCES address(address_id)

);

create table victim(

victim_id integer not null,

nric_no integer not null,

f_name varchar2(32) not null,

l_name varchar2(32) not null,

phone varchar2(15) not null,

gender varchar2(6) not null,

age integer not null,

address_id integer not null,

primary key(victim_id),

CONSTRAINT victim_uni UNIQUE (nric_no),

CONSTRAINT fk_address_id_v FOREIGN KEY (address_id) REFERENCES address(address_id)

);

create table police(

police_id integer not null,

police_reg_no varchar2(10) not null,

f_name varchar2(32) not null,

l_name varchar2(32) not null,

CONSTRAINT police_uni UNIQUE (police_reg_no),

primary key(police_id)

);

create table offender(

offender_id integer not null,

nric_no integer not null,

f_name varchar2(32) not null,

l_name varchar2(32) not null,

phone varchar2(15) not null,

gender varchar2(6) not null,

age integer not null,

address_id integer not null,

primary key(offender_id),

CONSTRAINT offender_uni UNIQUE (nric_no),

CONSTRAINT fk_address_id_o FOREIGN KEY (address_id) REFERENCES address(address_id)

);

create table reporter(

reporter_id integer not null,

nric_no integer not null,

f_name varchar2(32) not null,

l_name varchar2(32) not null,

phone varchar2(15) not null,

address_id integer not null,

primary key(reporter_id),

CONSTRAINT reporter_uni UNIQUE (nric_no),

CONSTRAINT fk_address_id_r FOREIGN KEY (address_id) REFERENCES address(address_id)

);

create table crime(

crime_id integer not null,

o_date DATE not null,

object_used varchar2(26) not null,

crime_notes varchar2(256) not null,

reporter_id integer not null,

police_id integer not null,

detective_id integer not null,

witness_id integer not null,

offender_id integer not null,

city_id integer not null,

victim_id integer not null,

date_resolved DATE,

status varchar2(6) not null,

type_of_crime varchar2(10) not null,

primary key(crime_id),

CONSTRAINT fk_city_id_c FOREIGN KEY (city_id) REFERENCES city(city_id),

CONSTRAINT fk_offender_id FOREIGN KEY (offender_id) REFERENCES offender(offender_id),

CONSTRAINT fk_witness_id FOREIGN KEY (witness_id) REFERENCES witness(witness_id),

CONSTRAINT fk_detective_id FOREIGN KEY (detective_id) REFERENCES detective(detective_id),

CONSTRAINT fk_police_id FOREIGN KEY (police_id) REFERENCES police(police_id),

CONSTRAINT fk_reporter_id FOREIGN KEY (reporter_id) REFERENCES reporter(reporter_id),

CONSTRAINT fk_victim_id FOREIGN KEY (victim_id) REFERENCES victim(victim_id)

);

Part C:

The schema is populated as shown below. This is the test data

—district

insert into district

values(1,’Kota Kinabalu’);

insert into district

values(2,’Tawau’);

—city

insert into city

values(1,’Kota Kinabalu’,1);

insert into city

values(2,’ Kuantan’,1);

—address

insert into address

values (1, ‘post 345’,267,1);

insert into address

values (2, ‘post 25′,267,1);

—detective

insert into detective

values(1,’DCR1234′,’Denis’,’Dennis’);

insert into detective

values(2,’DCR1235′,’erick’,’erick’);

—witness

insert into witness

values (1,123456789012,’Wang’,’Wang’,0123456765,1);

—victim

insert into victim

values(1,098765432123,’Paul’,’Paul’,’012345676′,’male’,28,1);

insert into victim

values(2,0987654321549,’Lewis’,’Lewis’,’012345678′,’female’,19,1);

insert into victim

values(3,098755432154,’Wang’,’Wang’,’012345678′,’female’,19,1);

insert into victim

values(4,0987654321540,’Eliud’,’Wang’,’012345678′,’female’,19,1);

insert into victim

values(5,098765435154,’John’,’Close’,’012345678′,’female’,19,1);

insert into victim

values(6,0987654321541,’Scwaz’,’Nigger’,’012345678′,’female’,19,1);

insert into victim

values(7,098765432154,’Samson’,’Bible’,’012345678′,’female’,19,1);

insert into victim

values(8,098565432154,’Achahi’,’Dan’,’012345678′,’female’,19,1);

insert into victim

values(9,098705432154,’Van’,’Gaal’,’012345678′,’female’,19,1);

insert into victim

values(10,098761432154,’Jane’,’Marson’,’012345678′,’female’,19,1);

—police

insert into police

values(1,’PODEW345′,’Brian’,’Brian’);

—offender

insert into offender

values(1,465432123456,’Peter’,’peter’,’0123234567′,’male’,45,1);

insert into offender

values(2,565432123456,’Mercy’,’Victoria’,’0123234567′,’female’,45,1);

insert into offender

values(3,865432123456,’Peter’,’samuel’,’0123234567′,’male’,23,2);

insert into offender

values(4,065432123456,’Peter’,’Eliud’,’01267434567′,’male’,19,1);

insert into offender

values(5,165432123456,’Peter’,’peter’,’0123234567′,’male’,40,2);

insert into offender

values(6,665432123456,’Mercy’,’clear’,’0123834567′,’female’,23,1);

insert into offender

values(7,265432123456,’Peter’,’Steve’,’0123234567′,’male’,51,1);

insert into offender

values(8,765432123456,’Sos’,’John’,’0123214567′,’male’,15,2);

—repoter

insert into reporter

values (1,123456789012,’Derdus’,’Pros’,0123456765,1);

—crime

insert into crime

values(1,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’Knife’,’The accused stubbed him with a knife’,1,1,1,1,1,1,1,to_date(‘2014-10-30’, ‘yyyy-mm-dd’),’closed’,’Murder’);

insert into crime

values(2,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’gun’,’The accused stubbed him with a knife’,1,1,1,1,1,1,1,”,’open’,’robbery’);

insert into crime

values(3,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’Knife’,’The accused stubbed him with a knife’,1,1,1,1,1,1,1,”,’open’,’Murder’);

insert into crime values(4,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’Knife’,’The accused stubbed him with a knife’,1,1,2,1,5,1,1,to_date(‘2014-10-30’, ‘yyyy-mm-dd’),’closed’,’Murder’);

insert into crime

values(5,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’gun’,’The accused stubbed him with a knife’,1,1,1,1,6,1,1,to_date(‘2014-10-30’, ‘yyyy-mm-dd’),’closed’,’robbery’);

insert into crime

values(6,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’Knife’,’The accused stubbed him with a knife’,1,1,1,1,4,1,1,to_date(‘2014-10-30’, ‘yyyy-mm-dd’),’closed’,’Murder’);

insert into crime

values(7,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’Knife’,’The accused stubbed him with a knife’,1,1,2,1,2,1,1,”,’open’,’Murder’);

insert into crime

values(8,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’Knife’,’The accused stubbed him with a knife’,1,1,1,1,3,1,1,to_date(‘2014-10-30’, ‘yyyy-mm-dd’),’closed’,’Murder’);

Part D:

  1. The crimes would be related by type or location of occurrence
  2. based on type of crime , the sql query is

SELECT o_date, type_of_crime COUNT(DISTINCT type_of_crime) AS “type_of_crime”

FROM crime

GROUP BY type_of_crime;

  1. based on city of occurrence

SELECT o_date, type_of_crime COUNT(DISTINCT city_id) AS “city of occurrence ”

FROM crime

GROUP BY type_of_crime, o_date;

  1. select distinct crime.witness_id,crime.o_date,

offender.f_name,offender.l_name,police.f_name,police.l_name,

detective.f_name,detective.l_name,count(crime.witness_id)

from crime,offender,police,witness,detective

where (select distinct count(witness_id) from crime)>5 and

status=’crime’ and crime.offender_id=offender.offender_id and

crime.police_id=police.police_id and

crime.detective_id = detective.detective_id

group by crime.witness_id,crime.o_date, offender.f_name,offender.l_name,police.f_name,police.l_name,detective.f_name,detective.l_name;

  1. select distinct crime.witness_id,crime.o_date, offender.f_name,offender.l_name,police.f_name,police.l_name,detective.f_name,detective.l_name,count(crime.witness_id) from crime,offender,police,witness,detective where crime.o_date > to_date( ’04-Oct-2014′, ‘DD-Mon-YYYY’ ) and status=’crime’ and crime.offender_id=offender.offender_id and crime.police_id=police.police_id and crime.detective_id = detective.detective_id group by crime.witness_id,crime.o_date,offender.f_name,offender.l_name,police.f_name,police.l_name,detective.f_name,detective.l_name;

Part E

This section shows sample, queries, their english equivalent and related screenshots. Sqlfiddle online tool was used to generate the database schema and run queries (SQL Fiddle, 2014).

  • Close a crime case; the ID of the case is always provided

UPDATE crime SET status = ‘closed’ WHERE crime_id = 2;

Before query

After query run

  • Sort victims using their first name ,last name and gender

Select victin_id,nric_no,f_name,l_name,phone,gender,age from victim order by f_name,l_name,gender;

  • Joining tables

List crimes with all its details such assigned detective, reporter, witness, type of crime and reporting police from cloded crimes

select crime.o_date,crime.object_used,crime.crime_notes,crime.date_resolved,

crime.type_of_crime,police.f_name,detective.l_name

from crime left join police

on crime.police_id=police.police_id left join detective on crime.detective_id=detective.detective_id where status=’closed’;

  • Grouping using group by

List the crimes that are related by the fact that they occure in the the same city

SELECT DISTINCT o_date,type_of_crime,COUNT(city_id) AS city_Count

FROM crime GROUP BY type_of_crime, o_date;

  • Use of where clause

List all robbery crimes

select o_date,object_used,crime_notes,date_resolved from crime where type_of_crime=’robbery’;

  • Logical comparison

select distinct crime.witness_id,crime.o_date,

offender.f_name as offender ,police.f_name as police,

detective.f_name as detective

from crime,offender,police,witness,detective

where (select count(witness_id) from crime)>5 and

status=’open’ and crime.offender_id=offender.offender_id and crime.police_id=police.police_id and crime.detective_id = detective.detective_id

group by crime.witness_id,crime.o_date,offender.f_name, police.f_name,detective.f_name;

  • Sub-queries (sub-query is in the where clause)

List of crimes that are still in progres with a witness occuring more than five times

select distinct crime.witness_id,crime.o_date,

offender.f_name as offender ,police.f_name as police,

detective.f_name as detective

from crime,offender,police,witness,detective

where (select count(witness_id) from crime)>5 and

status=’open’ and crime.offender_id=offender.offender_id and crime.police_id=police.police_id and crime.detective_id = detective.detective_id

group by crime.witness_id,crime.o_date,offender.f_name, police.f_name,detective.f_name;

  • COUNT functions

List distinct types of crimes and their number

select distinct type_of_crime,count(witness_id) from crime group by type_of_crime;

References

CHEN, P. (2002). Data Modeling with Entity-Relationship Diagrams.

Class and Entity-Relationship Diagrams . (n.d.). Retrieved October 31, 2014, from toronto.edu: http://www.cs.toronto.edu/~sme/CSC340F/2005/slides/tutorial-classes_ERDs.pdf

Daisy, S. (2004). Developing a crime Analysis information system for police servoce in a developing country: The case of zambia police service.

Greenwald, R. (2001). Oracle Database (Third ed.). O’Reily.

Oracle. (2014). Oracle Database Installation Guide 11g Release 2 (11.2) for Microsoft Windows. Oracle .

Saber, M., & Khan, R. S. (2010). Design of a Hospital-Based Database System (A Case Study of BIRDEM). International Journal on Computer Science and Engineering , 2(8), 2616-2621 .

Smith, J. (2014). TECHNOLOGY: SQL Developer. Retrieved October 31, 2014, from Oracle : http://www.oracle.com/technetwork/issue-archive/2014/14-may/o34sqldev-2193423.html

Song, Y., Evans, M., & Park, E. (1195). A Comparative Analysis of Entity-Relationship Diagrams. Journal of Computer and Software Engineering,, 3(4).

SQL Fiddle. (2014). SQL Fiddle. Retrieved October 31, 2014, from SQL Fiddle: http://sqlfiddle.com/#!4/0a232/34

Techonthenet. (2014). Oracle/PLSQL: CREATE TABLE Statement. Retrieved October 31, 2014, from Techonthenet: http://www.techonthenet.com/oracle/tables/create_table.php

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: