Title: Database Systems

Title: Database Systems

<Name>

<Institution>

<Course>

<Date>

  1. ER-Diagram (ERD)

The entity relationship diagram is explicitly drawn using the entities identified from the case. Using the process of normalization, the entities, weak and strong, identified from the case Sabah and Sarawak crime database include suspect, crime, witness, investigation, city, district, detective, victim and officer. The process of normalization is shown in part b of this assignment. The entities identifies have relationship. The assumption made in this case is that after a crime has been recorder by police officer, labeled as officer, it is then assigned to a detective for investigation. The relation that exists between the entities is summarized as follows;

  • Crime is the offence committed by the accused. It affects the victim and it occurs in a city
  • Officer is the police officer who records a crime. He records a crime
  • Victim is that who is affected by a crime. He may be a primary or secondary victim
  • Suspect offends the victim by committing a crime. He commits a crime
  • Witness is that person who witnesses a crime.
  • A detective is the person who handles a case. He investigates a crime
  • An investigation results from the process, which leads to discovering the criminal
  • A district is a larger administrative region. A district has a city
  • City is an urban area. For this case’s scenario, a city belongs to a district

The relationships have been underlined. The entities shown below, with their attributes, including foreign keys is use to generate the ER-Diagram

Crime (id, occurrenceDate,objectUsed,crimeDetails,name,officerId,victimId,suspectId, cityId)

Officer (id,jobRegNo,fName,lName)

Victim (id,fName,lName,type,gender,age)

Suspect (id,nationalld,fName,lName,gender,age)

Witness (id,fName,lName)

Detective (id,jobRegNo,fName,lName)

City (id,name,district_id)

District (id,name)

Investigation (id,dateResolved,criminal,crime_id,detective_id)

ER1

  1. Normalisation process

The relational schemer is normalised as shown below

Before normalization

We would have a single table, crime with many fields

Crime(id, officerNames,detective_names,witnessNames,location,occuranceDate,objectUsed,crimeNotes,officerNames,name,victim_names,victim_type,victim_gender,victim_age,suspect_names,suspect_gender,suspect_age,location)

For example location would be repeating as shown below

Id name Location
1 Dennis Sarawak
2 Dennis sabah

As seen from the table above, many columns will be used to extract details of a single row. To solve this, a new table called location and others is extracted to form new table

Normalization rules

1NF

First normal form ensures that there are no repeating fields

Crime (id, officerNames,detective_names,witnessNames,,occuranceDate,objectUsed,crimeNotes,officerNames,name)

Victim (id,names,type,gender,age)

Suspect (id,names,gender,age)

Location (id,city,district)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
officerNames not null Varchar2(20)
witnessNames not null Varchar2(20)
occuranceDate not null Date
objectUsed not null Varchar2(20)
crimeNotes not null Varchar2(255)
officerNames not null Varchar2(20)
name not null Varchar2(20)

 

 

 

 

Victim (id,names,type,gender,age)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
Names not null Varchar2(20)
Type not null Varchar2(20)
Age Integer

 

Suspect (id,names,gender,age)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
Names not null Varchar2(20)
gender not null Varchar2(20)
Age Integer

Location (id,city,district)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
City not null Varchar2(20)
district not null Varchar2(20)

 

2NF

For any relation, there must be no partial dependence in a concatenated key. From the relations in the witnessName, detective_name,officerNames do not depend on the crime primary key. They are therefore isolated to form new relations with primary keys as shown below.

Crime (id,occurrenceDate,objectUsed,crimeDetails,name)

Officer (jobRegNo,fName,lName)

Victim (id,fNamelName,,type,gender,age)

Suspect (id,nationalld,fName,lName,gender,age)

Witness (id,fName,lName)

Detective (jobRegNo,fName,lName)

Location (id,city,district)

Investigation(id,date,criminal)

Crime (id,occurrenceDate,objectUsed,crimeDetails,name)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
occurrenceDate not null Date
objectUsed not null Varchar2(20)
crimeDetails not null Varchar2(256)
name not null Varchar2(20)

Officer (Id,jobRegNo,fName,lName)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
jobRegNo not null Integer
fName not null Varchar2(20)
lName not null Varchar2(256)

 

Victim (id,fName,lName,type,gender,age)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
fName not null Varchar2(20)
lName not null Varchar2(20)
type not null Varchar2(20)
age Not null Integer

Suspect (id,nationalld,fName,lName,gender,age)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
nationalld not null Integer
fName not null Varchar2(20)
lName not null Varchar2(20)
gender Not null Varchar2(20)
Age Not null Integer

Witness (id,fName,lName)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
fName not null Varchar2(20)
lName not null Varchar2(256)

Detective (id,jobRegNo,fName,lName)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
jobRegNo Not null Integer
fName not null Varchar2(20)
lName not null Varchar2(256)

Location (id,city,district)

Attribute Constraint (null/not null) Oracle DataType
id not null Integer
city Not null Varchar2(20)
district not null Varchar2(20)

 

3NF

All non-key attributes must depend on the primary keys of relations except for that for location attribute.City depends on district, which then depend on the primary key, id. This is true from the relations in the 2NF and therefore no changes occur. The relations are already in the 3NF.

Crime (id,occurrenceDate,objectUsed,crimeDetails,name)

Officer (jobRegNo,fName,lName)

Victim (id,fNamelName,type,gender,age)

Suspect (id,nationalld,fName,lName,gender,age)

Witness (id,fName,lName)

Detective (jobRegNo,fName,lName)

City(id,name)

District (id,name)

Investigation(id,dateResolved,criminal)

The Third Normal Form (3NF) entities shown above have been modified to include foreign keys as shown below. Primary keys have been underlined. Two primary keys for a single entity make a composite key.

3NF WITH FOREIGN KEYS (fk)

Crime (id, occurrenceDate,objectUsed,crimeDetails,name,officerId,victimId,suspectId, cityId)

Officer (id,jobRegNo,fName,lName)

Victim (id,fName,lName,type,gender,age)

Suspect (id,nationalld,fName,lName,gender,age)

Witness (id,fName,lName)

Detective (id,jobRegNo,fName,lName)

City (id,name,district_id)

District (id,name)

Investigation (id,dateResolved,criminal,crime_id,detective_id)

From the ER-Diagram above, the primary and foreign keys are shown. This section elaborates the entity’s attributes by indicating their data types and status of the NULL or NOT NULL constraint. This is shown in the following tables

  1. Crime (id, occurrenceDate,objectUsed,crimeDetails,name,officerId,victimId,suspectId, cityId)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
occurrenceDate not null Date
objectUsed not null Varchar2(20)
crimeDetails not null Varchar2(256)
name not null Varchar2(20)
officerId not null Integer
victimId not null Integer
victimId not null Integer
suspectId not null Integer
cityId not null Integer
  1. Officer (id,jobRegNo,fName,lName)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
jobRegNo not null integer
fName not null Varchar2(20)
lName not null Varchar2(20)
  1. Victim (id,fName,lName,type,gender,age)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
fName not null Varchar2(20)
lName not null Varchar2(20)
type not null Varchar2(10)
gender not null Varchar2(7)
age not null Integer
  1. Suspect (id,fName,lName,gender,age)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
nationalld Not null Integer
fName not null Varchar2(20)
lName not null Varchar2(20)
gender not null Varchar2(7)
age not null Integer
  1. Witness (id,fName,lName)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
fName not null Varchar2(20)
lName not null Varchar2(20)
  1. Detective (id,jobRegNo,fName,lName)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
jobRegNo Not null Integer
fName not null Varchar2(20)
lName not null Varchar2(20)
  1. Investigation (id, dateResolved,criminal,crimeIid,detectiveId)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
dateResolved null date
Criminal null Varchar2(20)
crimeIid not null Integer
detectiveId Not null Integer
  1. District (id,name)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
name not null Varchar2(20)
  1. city (id,name,district_id)
Attribute Constraint (null/not null) Oracle DataType
id not null Integer
name not null Varchar2(20)
districtId not null Integer

 

  1. Implementing database in oracle

The oracle version 11g R2 SQL script for the above database is shown below. SQL fiddle online tool was used as SQL editor and schema builder. All the SQL queries were also run on this tool (sqlfiddle.com, 2014).

—district table

create table district(

id integer not null,

name varchar2(20) not null,

primary key(id)

);

—city table

create table city(

id integer not null,

name varchar2(20) not null,

districtId integer not null,

primary key(id),

CONSTRAINT fk_district FOREIGN KEY(districtId) REFERENCES district(id)

);

—officer table

create table officer(

id integer not null,

jobRegNo integer not null,

fName varchar2(20) not null,

lName varchar2(20) not null,

primary key(id),

CONSTRAINT officer_uni UNIQUE (jobRegNo)

);

—victim table

create table victim(

id integer not null,

fName varchar2(20) not null,

lName varchar2(20) not null,

type varchar2(10) not null,

gender varchar2(7) not null,

age integer not null,

primary key(id)

);

—Suspect table

create table suspect(

id integer not null,

nationalld integer not null,

fName varchar2(20) not null,

lName varchar2(20) not null,

gender varchar2(7) not null,

age integer not null,

primary key(id),

CONSTRAINT suspect_uni UNIQUE (nationalld)

);

—witness table

create table witness(

id integer not null,

fName varchar2(20) not null,

lName varchar2(20) not null,

primary key(id)

);

—detective table

create table detective(

id integer not null,

jobRegNo integer not null,

fName varchar2(20) not null,

lName varchar2(20) not null,

primary key(id),

CONSTRAINT detective_uni UNIQUE (jobRegNo)

);

—crime table

create table crime(

id integer not null,

occurrenceDate DATE not null,

objectUsed varchar2(32),

crimeDetails varchar2(256) not null,

name varchar2(20) not null,

officerId integer not null,

victimeId integer not null,

suspectId integer not null,

cityId integer not null,

status varchar2(10) default ‘open’ not null,

primary key(id),

CONSTRAINT fk_officer FOREIGN KEY(officerId) REFERENCES officer(id),

CONSTRAINT fk_victim FOREIGN KEY(victimeId) REFERENCES victim(id),

CONSTRAINT fk_suspect FOREIGN KEY(suspectId) REFERENCES suspect(id),

CONSTRAINT fk_city FOREIGN KEY(cityId) REFERENCES city(id)

);

—investigation table

create table investigation(

id integer not null,

dateResolved DATE,

criminal varchar2(20),

crimeId integer not null,

detectiveId integer not null,

primary key(id),

CONSTRAINT fk_crime FOREIGN KEY(crimeId) REFERENCES crime(id),

CONSTRAINT fk_detective FOREIGN KEY(detectiveId) REFERENCES detective(id)

);

 

 

Inserting test data

—populating district

insert into district

values(1,’sabah’);

insert into district

values(2,’Sarawak’);

—populating city

insert into city

values(1,’Putatan’,1);

insert into city

values(2,’ Kunak’,2);

—populating detective

insert into detective

values(1,’1234′,’Andrew’,’Yong’);

insert into detective

values(2,’2235′,’Jeremy’,’amir’);

—populating witness

insert into witness

values (1,’Jack’,’Syahmi’);

insert into witness

values (2,’luqman’,’Adam’);

—populating victim

insert into victim

values(1,’Paul’,’Paul’,’primary’,’male’,28);

insert into victim

values(2,’Yong’,’akmal’,’primary’,’male’,42);

insert into victim

values(3,’Nurul’,’michelle’,’primary’,’female’,39);

insert into victim

values(4,’Ahmad’,’Shamil’,’secondary’,’male’,38);

insert into victim

values(5,’Yusri’,’Nabil’,’primary’,’male’,28);

insert into victim

values(6,’sofea’,’mira’,’secondary’,’female’,28);

insert into victim

values(7,’Najim’,’Fawzul’,’primary’,’male’,18);

insert into victim

values(8,’Celine’,’nur’,’secondary’,’female’,34);

insert into victim

values(9,’Aiman’,’Jared’,’primary’,’male’,17);

insert into victim

values(10,’Alan’,’raymond’,’primary’,’male’,23);

—populating officer

insert into officer

values(1,123456789,’Gohwin’,’Abdul’);

insert into officer

values(2,987654321,’Rahman’,’Wazir’);

—suspect

insert into suspect

values(1,465432456,’Peter’,’peter’,’male’,45);

insert into suspect

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

insert into suspect

values(3,86543456,’Ahmad’,’Mustahfa’,’male’,23);

insert into suspect

values(4,06543212,’Peter’,’Eliud’,’male’,19);

insert into suspect

values(5,165432123456,’Muhammad’,’Muhaimin’,’male’,40);

insert into suspect

values(6,6432456,’Mercy’,’Nancy’,’female’,23);

insert into suspect

values(7,26543256,’Peter’,’Mor’,’male’,51);

insert into suspect

values(8,7654356,’Sos’,’John’,’male’,15);

—populating crime

insert into crime

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

insert into crime

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

insert into crime

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

insert into crime

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

insert into crime

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

insert into crime

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

insert into crime

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

insert into crime

values(8,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’mechete’,’The accused stubbed him with a knife’,’injury’,1,5,1,1,2,’closed’);

insert into crime

values(9,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’mechete’,’The accused stubbed him with a knife’,’injury’,1,5,1,1,2,’closed’);

insert into crime

values(10,to_date(‘2014-1-10’, ‘yyyy-mm-dd’),’mechete’,’The accused stubbed him with a knife’,’injury’,1,5,1,1,2,’closed’);

–populating investigation

insert into investigation

values(1,”,”,1,1);

insert into investigation

values(2,”,”,2,1);

insert into investigation

values(3,”,”,3,1);

insert into investigation

values(4,”,”,4,1);

insert into investigation

values(5,”,”,5,1);

insert into investigation

values(6,”,”,6,1);

insert into investigation

values(7,”,”,7,1);

insert into investigation

values(8,to_date(‘2014-05-11′,’yyyy-mm-dd’),’MUHAMMAD SYAFIQ’,8,1);

  1. SLQ listing from database
  2. Related crimes

This query shows crimes related in a way that they wererecorded by the same police officer. Result is shown below.

select distinct crime.occurrenceDate,crime.objectUsed,crime.name,crime.status,officer.fName as officer_f_name,officer.lName as officer_l_name

from crime join officeron crime.officerId=officer.id;

  1. Crimes where witness is a witness over five times

Result shown below

select distinct crime.witnessId,crime.occurrenceDate,

suspect.fName as suspect_f_name,suspect.lName as suspect_l_name,

officer.fName as police_f_name,officer.lName as police_l_name,

count(crime.witnessId)

from crime,suspect,officer,witness

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

status=’open’ and crime.suspectId=suspect.id and

crime.officerId=officer.idgroup by crime.witnessId,crime.occurrenceDate,suspect.fName,suspect.lName,officer.fName,officer.lName;

  1. Latest crimes with officers who reported it

These are crimes which occurred after ‘2014-10-4’

select witness.fName as witness_f_name,crime.occurrenceDate,

officer.fName,officer.lName

from crime left join officer on crime.officerId = officer.id left join witness

on crime.witnessId = witness.id where crime.occurrenceDate > to_date(‘2014-10-04’, ‘yyyy-mm-dd’)group by witness.fName,officer.fName,officer.lName,crime.occurrenceDate; The result is shown below. Compare the reuslts before and after update

 

 

 

 

 

 

 

 

 

 

 

 

After update

 

 

  1. Understanding of queries
  • Updating query

Opening a case that was closed

select id,status from crime;

UPDATE  crime

set status=’open’

where id=9;

UPDATE  investigation

set dateResolved=”,criminal=”

where crimeId=9;

select id,status from crime;

The screenshots below shows results for crime and investigation before and after update

After update

  • Sorting suspects

Sort by first name then last name in ascending order.

select * from suspect

order by fName,lName asc;

See diagram below

  • Table joining

List crimes recorded by the same officer. see diagram below

select distinct crime.occurrenceDate,crime.objectUsed,crime.name,crime.status,officer.fName as officer_f_name,officer.lName as officer_l_name

from crime join officeron crime.officerId=officer.id;

  • Grouping

Group victims by type. See diagram below

select type,fName,lName,gender,age

from victim group by type,fName,lName,gender,age;

  • Using where clause

List of primary victims. See diagram below

select * from victim where type=’primary’;

  • Logical comparison

List victims above the age of 30 years. See diagram below

select * from victim

where age>30;

  • Sub-queries

Crimes where witness is a witness over five times

Result shown in diagram below

select distinct crime.witnessId,crime.occurrenceDate,

suspect.fName as suspect_f_name,suspect.lName as suspect_l_name,

officer.fName as police_f_name,officer.lName as police_l_name,

count(crime.witnessId)

from crime,suspect,officer,witness

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

status=’open’ and crime.suspectId=suspect.id and

crime.officerId=officer.idgroup by crime.witnessId,crime.occurrenceDate,suspect.fName,suspect.lName,officer.fName,officer.lName;

  • AVERAGE SUM and COUNT functions

Average age of victims. See diagram below

select avg(age) as average_age from victim;

Refesrences

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

Edraw. (2014). Database Model Diagrams – Draw Entity-Relationship Model Diagrams Easily with Edraw! Retrieved November 5, 2014, from Edraw: http://www.edrawsoft.com/datamodel.php

Kaula, R. (2007). Normalizing with Entity Relationship Diagramming. Retrieved November 5, 2014, from Tdan: http://www.tdan.com/view-articles/4583

LucidChart . (2014). Diagrams done right . Retrieved November 5, 2014, from LucidChart : https://www.lucidchart.com/

Modernanalyst. (2013). Data Modeling: Entity-Relationship Diagram (ER Diagram) – See more at: http://www.modernanalyst.com/Resources/Articles/tabid/115/ID/2008/Data-Modeling-Entity-Relationship-Diagram-ER-Diagram.aspx#sthash.QICjF9Wk.dpuf. Retrieved November 5, 2014, from modernanalyst: http://www.modernanalyst.com/Resources/Articles/tabid/115/ID/2008/Data-Modeling-Entity-Relationship-Diagram-ER-Diagram.aspx

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

Oracle. (2-05). Datatypes . Retrieved November 5, 2014, from Oracle.com: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm

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

Toman, D. (2013). Conceptual Modeling and the Entity-Relationship Model. University of Waterloo.

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: