Oracle Database Assignment help

13-01-17 Rochio Varela 0 comment

The CREATE TABLE statements

Criminal:

create table criminals

( criminal_id number(10) not null,

    criminal_name varchar2(40) not null,

    criminal_add varchar2(240),

    criminal_status char(1) not null,

    criminal_probation char(1) not null,

    constraint criminal_pk primary key (criminal_id)

);

Criminal_phone_details:

create table criminal_phone_details

( criminal_id number(10) not null,

    criminal_phone_no number(12) not null,

     constraint criminal_phone_pk primary key (criminal_id,criminal_phone_no),

    constraint criminal_phone_fk foreign key (criminal_id) references criminals(criminal_id)

);

Criminal_alias:

create table alias

( criminal_id number(10) not null,

    criminal_alias varchar2(24) not null,

    constraint criminal_alias_pk primary key (criminal_id,criminal_alias),

    constraint criminal_alias_fk foreign key (criminal_id) references criminals(criminal_id)

);

Crime_arresting_officer:

create table crime_arresting_officer

( criminal_id number(10) not null,

    crime_id number(8) not null,

    officer_id number(8) not null,

    constraint crime_arresting_officer_pk primary key (criminal_id,crime_id,officer_id),

    constraint criminal_fk foreign key (criminal_id) references criminals(criminal_id),

    constraint crime_arresting_fk foreign key (crime_id) references crimes(crime_id),

    constraint officer_fk foreign key (officer_id) references officer(officer_id)

);

Read more about Database Management Assignment help

Delete:

drop table criminal_phone_details;

drop table criminal_alias;

Crimes:

create table crimes

( crime_id number(8) not null,

    criminal_id number(10) not null,

    classification varchar2(40) not null,

    date_charged Date,

    appeal_status varchar2(40) not null,

    hearing_date Date,

    appeal_date Date,

    crime_codes varchar2(240) not null,

    amount number(12) not null,

    court_fee number(12) not null,

    amount_paid char(1) not null,

    payment_due_date Date,

    charge_status varchar2(20),

     constraint crime_pk primary key (crime_id),

    constraint crime_fk foreign key (criminal_id) references criminals(criminal_id)

);

Officer:

create table officer

( officer_id number(8) not null,

    officer_name varchar2(40) not null,

    officer_precient varchar2(40) not null,

    officer_badgeno varchar2(40) not null,

    officer_status char(1) not null,

     constraint officer_pk primary key (officer_id)

);

Officer_phone:

create table officer_phone

( officer_id number(8) not null,

    officer_phone_no number(12) not null,

     constraint officer_phone_pk primary key (officer_id,officer_phone_no),

    constraint officer_phone_fk foreign key (officer_id) references officer(officer_id)

);

Sentence:

create table sentence

( criminal_id number(10) not null,

    crime_id number(8) not null,

    start_date Date,

    end_date Date,

    no_of_violations number(3) not null,

    sentences varchar2(40) not null,

    constraint sentence_pk primary key (criminal_id,crime_id),

    constraint sentence_fk foreign key (criminal_id) references criminals(criminal_id),

    constraint sentence_crime_fk foreign key (crime_id) references crimes(crime_id)

);

Appeal:

create table appeal

( appeal_id number(8) not null,

    criminal_id number(10) not null,

    crime_id number(8) not null,

    appeal_filling_date Date,

    appeal_hearing_date Date,

    appeal_status varchar2(40) not null,

    constraint appeal_pk primary key (appeal_id),

    constraint appeal_criminal_fk foreign key (criminal_id) references criminals(criminal_id),

    constraint appeal_crime_fk foreign key (crime_id) references crimes(crime_id)

);  See about : Database Design Assignment Help

INSERT Statements

Criminals:

insert into criminals(criminal_id,criminal_name,criminal_add,criminal_status,criminal_probation) values(1,’Aleisha May’,’386 Murdoch Avenue’,’Y’,’N’);

insert into criminals(criminal_id,criminal_name,criminal_add,criminal_status,criminal_probation) values(2,’Zachary Ray’,’43 Paterson Close’,’Y’,’N’);

insert into criminals(criminal_id,criminal_name,criminal_add,criminal_status,criminal_probation) values(3,’Nicole Lee’,’6 Beare Circuit’,’Y’,’Y’);

insert into criminals(criminal_id,criminal_name,criminal_add,criminal_status,criminal_probation) values(4,’Julia’,’562 Macmanus Drive’,’Y’,’N’);

insert into criminals(criminal_id,criminal_name,criminal_add,criminal_status,criminal_probation) values(5,’Malang’,’38 Drimly Boulevard’,’N’,’N’);

criminal_phone_details:

insert into criminal_phone_details(criminal_id,criminal_phone_no) values(1,0428375770);

insert into criminal_phone_details(criminal_id,criminal_phone_no) values(2,0438730524);

insert into criminal_phone_details(criminal_id,criminal_phone_no) values(2,0420313992);

insert into criminal_phone_details(criminal_id,criminal_phone_no) values(3,0428899575);

insert into criminal_phone_details(criminal_id,criminal_phone_no) values(4,0411665692);

insert into criminal_phone_details(criminal_id,criminal_phone_no) values(5,0429120440);

Alias:

insert into alias(criminal_id,criminal_alias) values(1,’Jessica Denise’);

insert into alias(criminal_id,criminal_alias) values(2,’Amanda Jane’);

insert into alias(criminal_id,criminal_alias) values(3,’Jessica Kate’);

insert into alias(criminal_id,criminal_alias) values(4,’Troy Adam’);

insert into alias(criminal_id,criminal_alias) values(5,’Lisa Wayne’);

Officer:

insert into officer(officer_id,officer_name,officer_precient,officer_badgeno,officer_status) values(1,’Karen’,’Iluka’,’CJ1742′,’A’);

insert into officer(officer_id,officer_name,officer_precient,officer_badgeno,officer_status) values(2,’Amy Renee’,’Ashford’,’CJ1994′,’I’);

insert into officer(officer_id,officer_name,officer_precient,officer_badgeno,officer_status) values(3,’Zack Thomas’,’Bray Park’,’CJ1983′,’A’);

insert into officer(officer_id,officer_name,officer_precient,officer_badgeno,officer_status) values(4,’Joel Ethan’,’Breakwater’,’CJ1001′,’I’);

insert into officer(officer_id,officer_name,officer_precient,officer_badgeno,officer_status) values(5,’Jasmine Lee’,’Airly’,’CJ1155′,’A’);

Officer_phone

insert into officer_phone(officer_id,officer_phone_no) values(1,0420220860);

insert into officer_phone(officer_id,officer_phone_no) values(2,0421100457);

insert into officer_phone(officer_id,officer_phone_no) values(3,0458631589);

insert into officer_phone(officer_id,officer_phone_no) values(4,0434838541);

insert into officer_phone(officer_id,officer_phone_no) values(5,0432979615);

Crimes:

insert into crimes(crime_id,criminal_id,classification,date_charged,appeal_status,hearing_date,appeal_date,crime_codes,amount,court_fee,amount_paid,payment_due_date,charge_status) values(11,1,’felony’,(TO_DATE(‘2007/07/25′,’yyyy/mm/dd’)),’can appeal’,(TO_DATE(‘2007/08/15′,’yyyy/mm/dd’)),(TO_DATE(‘2007/10/15′,’yyyy/mm/dd’)),’burglary’,9900,3000,’Y’,(TO_DATE(‘2007/08/30′,’yyyy/mm/dd’)),’guilty’);

insert into crimes(crime_id,criminal_id,classification,date_charged,appeal_status,hearing_date,appeal_date,crime_codes,amount,court_fee,amount_paid,payment_due_date,charge_status) values(12,2,’other’,(TO_DATE(‘2012/04/10′,’yyyy/mm/dd’)),’in appeal’, (TO_DATE(‘2012/03/25′,’yyyy/mm/dd’)), (TO_DATE(‘2012/05/25′,’yyyy/mm/dd’)),’forgery’,10900,2200,’N’, (TO_DATE(‘2012/04/10′,’yyyy/mm/dd’)),’not guilty’);

insert into crimes(crime_id,criminal_id,classification,date_charged,appeal_status,hearing_date,appeal_date,crime_codes,amount,court_fee,amount_paid,payment_due_date,charge_status) values(13,3,’misdemeanour’, (TO_DATE(‘2014/05/24′,’yyyy/mm/dd’)),’closed’, (TO_DATE(‘2014/06/05′,’yyyy/mm/dd’)), (TO_DATE(‘2014/08/05′,’yyyy/mm/dd’)),’assault’,6900,1500,’Y’, (TO_DATE(‘2014/06/25′,’yyyy/mm/dd’)),’guilty’);

insert into crimes(crime_id,criminal_id,classification,date_charged,appeal_status,hearing_date,appeal_date,crime_codes,amount,court_fee,amount_paid,payment_due_date,charge_status) values(14,4,’felony’,(TO_DATE(‘2013/09/05′,’yyyy/mm/dd’)),’can appeal’, (TO_DATE(‘2013/08/15′,’yyyy/mm/dd’)), (TO_DATE(‘2013/10/15′,’yyyy/mm/dd’)),’burglary’,5500,3500,’Y’, (TO_DATE(‘2013/08/25′,’yyyy/mm/dd’)),’not guilty’);

insert into crimes(crime_id,criminal_id,classification,date_charged,appeal_status,hearing_date,appeal_date,crime_codes,amount,court_fee,amount_paid,payment_due_date,charge_status) values(15,5,’other’,(TO_DATE(‘2015/09/10′,’yyyy/mm/dd’)),’in appeal’, (TO_DATE(‘2015/09/20′,’yyyy/mm/dd’)), (TO_DATE(‘2015/11/20′,’yyyy/mm/dd’)),’forgery’,7900,5000,’N’, (TO_DATE(‘2015/10/05′,’yyyy/mm/dd’)),’pending’);

Crime_arresting_officer:

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(1,11,3);

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(1,11,2);

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(2,12,1);

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(3,13,3);

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(3,13,4);

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(4,14,2);

insert into crime_arresting_officer(criminal_id,crime_id,officer_id) values(5,15,1);

Sentence:

insert into sentence(criminal_id,crime_id,start_date,end_date,no_of_violations,sentences) values(1,11,(TO_DATE(‘2007/08/16′,’yyyy/mm/dd’)),(TO_DATE(‘2022/08/16′,’yyyy/mm/dd’)),15,’jail period’);

insert into sentence(criminal_id,crime_id,start_date,end_date,no_of_violations,sentences) values(2,12,(TO_DATE(‘2012/04/25′,’yyyy/mm/dd’)),(TO_DATE(‘2037/04/25′,’yyyy/mm/dd’)),25,’house arrest’);

insert into sentence(criminal_id,crime_id,start_date,end_date,no_of_violations,sentences) values(3,13,(TO_DATE(‘2014/06/07′,’yyyy/mm/dd’)),(TO_DATE(‘2054/06/07′,’yyyy/mm/dd’)),40,’probation’);

insert into sentence(criminal_id,crime_id,start_date,end_date,no_of_violations,sentences) values(4,14,(TO_DATE(‘2013/08/16′,’yyyy/mm/dd’)),(TO_DATE(‘2043/08/16′,’yyyy/mm/dd’)),30,’house arrest’);

insert into sentence(criminal_id,crime_id,start_date,end_date,no_of_violations,sentences) values(5,15,(TO_DATE(‘2015/11/20′,’yyyy/mm/dd’)),(TO_DATE(‘2060/11/20′,’yyyy/mm/dd’)),45,’jail period’);

Read more about Database Security Assignment Help

Appeal:

insert into appeal(criminal_id,crime_id,appeal_id,appeal_filling_date,appeal_hearing_date,appeal_status) values(1,11,1,(TO_DATE(‘2007/09/16′,’yyyy/mm/dd’)),(TO_DATE(‘2007/10/16′,’yyyy/mm/dd’)),’approved’);

insert into appeal(criminal_id,crime_id,appeal_id,appeal_filling_date,appeal_hearing_date,appeal_status) values(1,11,2,(TO_DATE(‘2007/11/16′,’yyyy/mm/dd’)),(TO_DATE(‘2008/01/16′,’yyyy/mm/dd’)),’pending’);

insert into appeal(criminal_id,crime_id,appeal_id,appeal_filling_date,appeal_hearing_date,appeal_status) values(3,13,3,(TO_DATE(‘2014/07/10′,’yyyy/mm/dd’)),(TO_DATE(‘2014/08/20′,’yyyy/mm/dd’)),’disapproved’);

insert into appeal(criminal_id,crime_id,appeal_id,appeal_filling_date,appeal_hearing_date,appeal_status) values(5,15,4,(TO_DATE(‘2015/12/05′,’yyyy/mm/dd’)),(TO_DATE(‘2015/12/27′,’yyyy/mm/dd’)),’approved’);

insert into appeal(criminal_id,crime_id,appeal_id,appeal_filling_date,appeal_hearing_date,appeal_status) values(5,15,5,(TO_DATE(‘2016/02/16′,’yyyy/mm/dd’)),(TO_DATE(‘2016/04/18′,’yyyy/mm/dd’)),’pending’);

Execute the following queries

Group member one

Insert 5 different entries in each table. You can create fictitious data to create those entries

Tables:

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Oracle Database Assignment help, programming assignment help, database assignment help

 Add a column named Mail_flag to the CRIMINALS table. The column should be assigned a data type CHAR (1).

alter table criminals

add (mail_flag char(1));

 Set the Mail_flag column to ‘N’ for all criminals who don’t have a street address recorded in the database

update criminals set mail_flag=’N’ where criminal_add is null;

 Change the phone number for a criminal to 7225659032

update criminal_phone_details set criminal_phone_no=7225659032 where criminal_id=2;

 Oracle Database Assignment help, programming assignment help, database assignment help

Remove a specific criminal from the database. Make sure you remove all the associated information relevant to that criminal

delete from appeal where criminal_id=1;

delete from sentence where criminal_id=1;

delete from crimes where criminal_id=1;

delete from crime_arresting_officer where criminal_id=1;

delete from alias where criminal_id=1;

delete from criminal_phone_details where criminal_id=1;

delete from criminals where criminal_id=1;

 Oracle Database Assignment help, programming assignment help, database assignment help

Figure 1 output

Group member two

List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing date: crime ID, classification, date changed, hearing date, and number of days between the date charged and the hearing date.

SELECT crimes.crime_id, crimes.classification, crimes.date_charged,crimes.hearing_date,crimes.hearing_date – crimes.date_charged

  FROM crimes

 WHERE  (crimes.hearing_date- crimes.date_charged) > 14;

 Produce a list showing each active police officer and his or her community assignment, indicated by the second letter of the precent code. Display the community description listed the following chart, based on the second letter of the precent code

select officer_id,officer_name,upper(substr(officer_precient,2,1))

from officer

where officer_status=’A’;

Read more about Database Development Assignment Help

Display the criminal name and probation start date for all criminals who have a probation period greater than two months. Also, display the date that’s two months from the beginning the probation period, which will serve as a review date

select criminal_name,start_date,end_date,months_between(sentence.end_date,sentence.start_date)

from sentence,criminals

where sentence.criminal_id=criminals.criminal_id

and months_between(sentence.end_date,sentence.start_date)>2;

Group member three

List all criminals along with crime status and appeal status (if applicable). The report need to include the criminal ID, name, crime classification, date change, appeal filling date, and appeal status. Show all criminals, regardless of whether they have filled an appeal.

select criminals.criminal_id,criminals.criminal_name,crimes.classification,crimes.date_charged,appeal.appeal_filling_date,appeal.appeal_status

from criminals,crimes,appeal

where criminals.criminal_id=crimes.criminal_id

and crimes.crime_id=appeal.crime_id

and criminals.criminal_id=appeal.criminal_id;

 List all criminals along with crime information. The reports need to include the criminal ID, name, crime classification, date charged, crime code, and fine amount. Include only crimes classified as “Other”. Sort the list by criminal ID and date changed.

select criminals.criminal_id,criminals.criminal_name,crimes.classification,crimes.date_charged,crimes.crime_codes,crimes.amount

from criminals,crimes

where criminals.criminal_id=crimes.criminal_id

and crimes.classification=’other’

order by criminals.criminal_id,crimes.date_charged;

 Create an alphabetic list of all criminals, including criminal ID, name, violent offender, status, parole status, and any known aliases.

select criminals.criminal_id,criminals.criminal_name,criminals.criminal_add,criminals.criminal_status,criminals.criminal_probation,criminal_phone_details.criminal_phone_no,alias.criminal_alias

from criminals,criminal_phone_details,alias

where criminals.criminal_id=criminal_phone_details.criminal_id

and criminals.criminal_id=alias.criminal_id

order by criminals.criminal_name;

Read more about Computer Programming Assignment Help

References

(DBMS), A. (2012). Excellence: Advantages and Disadvantages of Database Management System (DBMS). [online] Navdeep19.blogspot.in. Available at: http://navdeep19.blogspot.in/2012/04/advantages-and-disadvantages-of.html [Accessed 18 Mar. 2016].

Quackit.com. (2016). Querying a Database. [online] Available at:     http://www.quackit.com/database/tutorial/querying_a_database.cfm [Accessed 18 Mar. 2016].

Oracle.com. (2016). Oracle Database Express Edition 11g Release 2 Downloads. [online] Available at: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html [Accessed 6 Oct. 2016].

Docs.oracle.com. (2016). Installing Oracle SQL Developer. [online] Available at: https://docs.oracle.com/cd/E39885_01/doc.40/e38928/install.htm#RPTIG100 [Accessed 6 Oct. 2016].

Programming Assignment Help is the best assignment help provider in the United Kingdom. Our online assignment writing help UK is especially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.