Computer Science Homework Help

Computer Science Homework Help. assignment in database

IS 620 Assignment 4

Suppose you have created tables in assignment 1. Please write PL/SQL program for the following problems. You can use the attached SQL statements to create the tables.

Problem 1: Please create a PL/SQL function that computes the total price of a reservation given a reservation ID. [50 points]

The input parameter also includes a tax rate. The total price is computed by adding up price of each leg of flight of the reservation, and then multiply

the sum by (1+tax).

The function also needs to update the flight_reservation table to reflect the computed total. Please handle the case the reservation does not exist.
Please write an anonymous PL/SQL program to call this function.

Problem 2: Please create a PL/SQL procedure that given a reservation ID, print out detail itinerary information. [50 points]

The information includes: name of passenger, total price of the reservation (assume it has been updated by function in problem 1), origin city and destination city.
Please print out information for outbound trip and inbound trip.
For each trip, includes information for each leg, including flight number, departure airport code, departure time, arrival airport time, arrival time, duration of flight. If there are multiple legs in the trip, also print out connection time between two legs.

Sample code to create the tables.

drop table Trip_Detail;

drop table Flight_Reservation;

drop table passenger;

drop table schedule;

drop table flight;

drop table airport;

drop table airline;

create table airline

(

alcode char(2),

alname varchar(50),

primary key (alcode)

);

insert into airline values

(‘UA’, ‘United’);

insert into airline values

(‘DL’, ‘Delta’);

insert into airline values

(‘NK’, ‘Spirit’);

create table airport

(

apcode char(3),

apname varchar(50),

city varchar(50),

state varchar(50),

country varchar(50),

primary key(apcode));

insert into airport values

(‘BWI’, ‘Baltimore Washington Airport’, ‘Baltimore’, ‘MD’, ‘USA’);

insert into airport values

(‘DTW’, ‘Detroit Airport’,’Detroit’, ‘MI’, ‘USA’);

insert into airport values

(‘LAX’, ‘Los Angeles Airport’,’Los Angeles’,’CA’,’USA’);

insert into airport values

(‘PVG’, ‘Shanghai Pudong Airport’,’Shanghai’,’Shanghai’,’China’);

insert into airport values

(‘EWR’, ‘Newark Airport’,’Newark’,’NJ’,’USA’);

create table flight

(

fnumber varchar(20),

alcode char(2),

departure_apcode char(3),

arrival_apcode char(3),

primary key (fnumber),

foreign key(alcode) references airline,

foreign key(departure_apcode) references airport,

foreign key(arrival_apcode) references airport

);

insert into flight values

(‘UA 3938′,’UA’,’BWI’,’EWR’);

insert into flight values

(‘UA 86′,’UA’,’EWR’,’PVG’);

insert into flight values

(‘UA 87′,’UA’,’PVG’,’EWR’);

insert into flight values

(‘UA 4144′,’UA’,’EWR’,’BWI’);

insert into flight values

(‘DL 2429′,’DL’,’BWI’,’DTW’);

insert into flight values

(‘DL 583′,’DL’,’DTW’,’PVG’);

insert into flight values

(‘DL 582′,’DL’,’PVG’,’DTW’);

insert into flight values

(‘DL 1906′,’DL’,’DTW’,’BWI’);

insert into flight values

(‘NK 141′,’NK’,’BWI’,’LAX’);

insert into flight values

(‘NK 128′,’NK’,’LAX’,’BWI’);

insert into flight values

(‘UA 1623′,’UA’,’BWI’,’LAX’);

insert into flight values

(‘UA 2020′,’UA’,’LAX’,’BWI’);

create table schedule

(

sid int,

fnumber varchar(20),

departure_time timestamp with time zone,

arrival_time timestamp with time zone,

price number,

primary key(sid),

foreign key(fnumber) references flight

);

insert into schedule values

(1, ‘UA 3938’, timestamp ‘2017-9-12 06:41:00.00 -05:00’,timestamp ‘2017-9-12 07:50:00.00 -05:00’,120);

insert into schedule values

(2, ‘UA 86’, timestamp ‘2017-9-12 10:45:00.00 -05:00’,timestamp ‘2017-9-13 13:40:00.00 +08:00’,400);

insert into schedule values

(3, ‘UA 87’, timestamp ‘2017-10-12 15:40:00.00 +08:00’,timestamp ‘2017-10-12 18:00:00.00 -05:00’,300);

insert into schedule values

(4, ‘UA 4144’, timestamp ‘2017-10-12 22:00:00.00 -05:00’,timestamp ‘2017-10-12 23:20:00.00 -05:00’,200);

insert into schedule values

(5, ‘DL 2429’, timestamp ‘2017-9-12 13:00:00.00 -05:00’,timestamp ‘2017-9-12 14:30:00.00 -05:00’,140);

insert into schedule values

(6, ‘DL 583’, timestamp ‘2017-9-12 16:30:00.00 -05:00’,timestamp ‘2017-9-13 18:50:00.00 +08:00’,500);

insert into schedule values

(7, ‘DL 582’, timestamp ‘2017-10-12 11:40:00.00 +08:00’,timestamp ‘2017-10-12 13:40:00.00 -05:00’,400);

insert into schedule values

(8, ‘DL 1906’, timestamp ‘2017-10-12 15:20:00.00 -05:00’,timestamp ‘2017-10-12 17:00:00.00 -05:00’,150);

insert into schedule values

(9, ‘NK 141’, timestamp ‘2017-11-12 08:40:00.00 -05:00’,timestamp ‘2017-11-12 11:30:00.00 -08:00’,120);

insert into schedule values

(10, ‘NK 128’, timestamp ‘2017-11-16 21:30:00.00 -08:00’,timestamp ‘2017-11-17 05:30:00.00 -05:00’,120);

insert into schedule values

(11, ‘UA 1623’, timestamp ‘2017-11-12 06:30:00.00 -05:00’,timestamp ‘2017-11-12 09:00:00.00 -08:00’,150);

insert into schedule values

(12, ‘UA 2020’, timestamp ‘2017-11-16 21:30:00.00 -08:00’,timestamp ‘2017-11-17 05:30:00.00 -05:00’,150);

insert into schedule values

(13, ‘UA 3938’, timestamp ‘2017-11-12 06:41:00.00 -05:00’,timestamp ‘2017-11-12 07:50:00.00 -05:00’,120);

insert into schedule values

(14, ‘UA 4144’, timestamp ‘2017-11-12 22:00:00.00 -05:00’,timestamp ‘2017-11-12 23:20:00.00 -05:00’,200);

insert into schedule values

(15, ‘DL 2429’, timestamp ‘2017-11-12 13:00:00.00 -05:00’,timestamp ‘2017-11-12 14:30:00.00 -05:00’,140);

insert into schedule values

(16, ‘NK 128’, timestamp ‘2017-11-11 21:30:00.00 -08:00’,timestamp ‘2017-11-12 05:30:00.00 -05:00’,120);

insert into schedule values

(17, ‘UA 2020’, timestamp ‘2017-11-11 21:30:00.00 -08:00’,timestamp ‘2017-11-12 05:30:00.00 -05:00’,150);

insert into schedule values

(18, ‘DL 1906’, timestamp ‘2017-11-12 15:20:00.00 -05:00’,timestamp ‘2017-11-12 17:00:00.00 -05:00’,150);

create table passenger

(pid int,

pname varchar(50),

phone varchar(20),

primary key(pid));

insert into passenger values

(1, ‘Jeff’, ‘410-465-1928’);

insert into passenger values

(2, ‘Erin’, ‘410-465-2234’);

create table flight_reservation

(

rid int,

pid int,

num_passengers int,

origin_apcode char(3),

destination_apcode char(3),

departure_date date,

return_date date,

total number, — total price, need to be computed from each flight leg price

primary key (rid),

foreign key(pid) references passenger,

foreign key (origin_apcode) references airport,

foreign key (destination_apcode) references airport

);

insert into flight_reservation values

(1, 1, 4, ‘BWI’,’PVG’,date ‘2017-09-12’, date ‘2017-10-12’,0);

insert into flight_reservation values

(2, 1, 1, ‘BWI’,’LAX’,date ‘2017-11-12’, date ‘2017-11-16’,0);

insert into flight_reservation values

(3, 2, 4, ‘BWI’,’LAX’,date ‘2017-11-12’, date ‘2017-11-16’,0);

create table trip_detail

(

rid int,

sid int,

flag int,

leg int,

primary key(rid,sid),

foreign key (rid) references flight_reservation,

foreign key (sid) references schedule

);