Need the work on Oracle VM
Direct message if interested
chm_db.sql
— Drop all 7 tables
DROP TABLE promotion CASCADE CONSTRAINTS PURGE;
DROP TABLE location CASCADE CONSTRAINTS PURGE;
DROP TABLE customer CASCADE CONSTRAINTS PURGE;
DROP TABLE car_type CASCADE CONSTRAINTS PURGE;
DROP TABLE car CASCADE CONSTRAINTS PURGE;
DROP TABLE car_hired CASCADE CONSTRAINTS PURGE;
DROP TABLE invoice CASCADE CONSTRAINTS PURGE;
COMMIT;
— Create 7 tables
CREATE TABLE car (
car_id NUMBER(4) NOT NULL,
license_plate VARCHAR2(10) NOT NULL,
reg_expiry_date DATE NOT NULL,
color VARCHAR2(20) NOT NULL,
car_image VARCHAR2(50),
car_type_id NUMBER(4) NOT NULL,
car_name VARCHAR2(20) NOT NULL,
manufacturer VARCHAR2(20) NOT NULL,
usb_port VARCHAR2(1) NOT NULL,
bluetooth VARCHAR2(1) NOT NULL,
num_of_cylinders NUMBER(1) NOT NULL,
location_id NUMBER(4) NOT NULL,
extra_info VARCHAR2(100)
);
ALTER TABLE car ADD CONSTRAINT car_pk PRIMARY KEY ( car_id );
CREATE TABLE car_hired (
car_hired_id NUMBER(4) NOT NULL,
date_hired DATE NOT NULL,
date_returned DATE,
car_id NUMBER(4) NOT NULL,
promo_id NUMBER(4),
cust_id NUMBER(8) NOT NULL,
returned_location_id NUMBER(4),
date_expected_returned DATE NOT NULL,
is_insurred VARCHAR2(1) NOT NULL
);
ALTER TABLE car_hired ADD CONSTRAINT car_hired_pk PRIMARY KEY ( car_hired_id );
CREATE TABLE car_type (
car_type_id NUMBER(4) NOT NULL,
car_type VARCHAR2(1) NOT NULL,
is_luxury VARCHAR2(1) NOT NULL,
is_automatic VARCHAR2(1) NOT NULL,
num_of_doors NUMBER(1) NOT NULL,
num_of_seats NUMBER(1) NOT NULL,
num_of_airbags NUMBER(1) NOT NULL,
car_cost NUMBER(5,2) NOT NULL,
insurance_cost NUMBER(5,2) NOT NULL
);
ALTER TABLE car_type ADD CONSTRAINT car_type_pk PRIMARY KEY ( car_type_id );
CREATE TABLE customer (
cust_id NUMBER(8) NOT NULL,
cust_fname VARCHAR2(30) NOT NULL,
cust_lname VARCHAR2(30) NOT NULL,
cust_email VARCHAR2(50) NOT NULL,
cust_address VARCHAR2(50) NOT NULL,
cust_suburb VARCHAR2(20) NOT NULL,
cust_postcode VARCHAR2(4) NOT NULL,
cust_phone VARCHAR2(15) NOT NULL,
cust_dob DATE NOT NULL,
license_no VARCHAR2(10) NOT NULL,
license_expiry_date DATE NOT NULL
);
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( cust_id );
CREATE TABLE invoice (
invoice_id NUMBER(6) NOT NULL,
car_hired_id NUMBER(4) NOT NULL,
invoice_date DATE NOT NULL,
is_additional_payment VARCHAR2(1) NOT NULL,
total_car_cost NUMBER(7,2) NOT NULL,
total_insurrance_cost NUMBER(6,2) NOT NULL,
total_less_than_25_cost NUMBER(6,2) NOT NULL,
total_cost NUMBER(7,2) NOT NULL,
gst NUMBER(6,2) NOT NULL
);
ALTER TABLE invoice ADD CONSTRAINT invoice_pk PRIMARY KEY ( invoice_id );
CREATE TABLE location (
location_id NUMBER(4) NOT NULL,
address VARCHAR2(50) NOT NULL,
suburb VARCHAR2(20) NOT NULL,
postcode VARCHAR2(4) NOT NULL,
city VARCHAR2(20),
state VARCHAR2(3) NOT NULL
);
ALTER TABLE location ADD CONSTRAINT location_pk PRIMARY KEY ( location_id );
CREATE TABLE promotion (
promo_id NUMBER(4) NOT NULL,
promo_code VARCHAR2(10) NOT NULL,
promo_value NUMBER(2,2) NOT NULL,
promo_start_date DATE NOT NULL,
promo_end_date DATE NOT NULL
);
ALTER TABLE promotion ADD CONSTRAINT promotion_pk PRIMARY KEY ( promo_id );
ALTER TABLE car
ADD CONSTRAINT car_car_type_fk FOREIGN KEY ( car_type_id )
REFERENCES car_type ( car_type_id );
ALTER TABLE car_hired
ADD CONSTRAINT car_hired_car_fk FOREIGN KEY ( car_id )
REFERENCES car ( car_id );
ALTER TABLE car_hired
ADD CONSTRAINT car_hired_customer_fk FOREIGN KEY ( cust_id )
REFERENCES customer ( cust_id );
ALTER TABLE car_hired
ADD CONSTRAINT car_hired_location_fk FOREIGN KEY ( returned_location_id )
REFERENCES location ( location_id );
ALTER TABLE car_hired
ADD CONSTRAINT car_hired_promotion_fk FOREIGN KEY ( promo_id )
REFERENCES promotion ( promo_id );
ALTER TABLE car
ADD CONSTRAINT car_location_fk FOREIGN KEY ( location_id )
REFERENCES location ( location_id );
ALTER TABLE invoice
ADD CONSTRAINT invoice_car_hired_fk FOREIGN KEY ( car_hired_id )
REFERENCES car_hired ( car_hired_id );
ALTER TABLE car_hired DISABLE ALL TRIGGERS;
commit;
— Drop 7 sequences and delete data
DROP SEQUENCE invoice_seq;
DELETE FROM Invoice;
DROP SEQUENCE car_hired_seq;
DELETE FROM Car_Hired;
DROP SEQUENCE car_seq;
DELETE FROM Car;
DROP SEQUENCE car_type_seq;
DELETE FROM Car_type;
DROP SEQUENCE location_seq;
DELETE FROM Location;
DROP SEQUENCE promotion_seq;
DELETE FROM Promotion;
DROP SEQUENCE customer_seq;
DELETE FROM Customer;
commit;
— create 6 sequences
CREATE SEQUENCE location_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE promotion_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE car_type_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE car_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE car_hired_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
commit;
— insert records
— insert location
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘349 Queen st’, ‘CBD’, ‘4000’, ‘Brisbane’, ‘QLD’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘Shop 34B/3-15 Orchid Ave’, ‘Surfers Paradise’, ‘4217’, null, ‘QLD’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘2A Airport Ave’, ‘Cairns Airport’, ‘4870’, null, ‘QLD’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘1 Henry Wrigley Dr’, ‘Darwin Airport’, ‘0820’,null, ‘NT’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘Sydney Airport’, ‘Sydney’, ‘2020’, ‘Sydney’, ‘NSW’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘Departure Dr’, ‘Melbourne Airport’, ‘3045’, null, ‘VIC’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘Perth Airport’, ‘Perth Airport’, ‘6105’, null, ‘WA’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘1 James Schofield Dr’, ‘Adelaide Airport’, ‘5950’, null, ‘SA’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ‘Terminal Cct’, ‘Canberra Airport’, ‘2609’, ‘CBR’, ‘ACT’);
— insert promotion
INSERT INTO Promotion(promo_id, promo_code, promo_value, promo_start_date, promo_end_date)
VALUES (promotion_seq.nextval, ‘MIDYEAR19’, 0.15, TO_DATE(‘1/6/2019 00:00:00’, ‘DD/MM/YYYY HH24:MI:SS’), TO_DATE(’31/7/2019 23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’));
INSERT INTO Promotion(promo_id, promo_code, promo_value, promo_start_date, promo_end_date)
VALUES (promotion_seq.nextval, ‘CHRIST19’, 0.10, TO_DATE(‘1/12/2019 00:00:00’, ‘DD/MM/YYYY HH24:MI:SS’), TO_DATE(’31/1/2020 23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’));
INSERT INTO Promotion(promo_id, promo_code, promo_value, promo_start_date, promo_end_date)
VALUES (promotion_seq.nextval, ‘COVID19’, 0.20, TO_DATE(‘1/10/2020 00:00:00’, ‘DD/MM/YYYY HH24:MI:SS’), TO_DATE(’28/2/2021 23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’));
— insert customer
INSERT INTO Customer(cust_id, cust_fname, cust_lname, cust_email, cust_address, cust_suburb, cust_postcode, cust_phone, cust_dob, license_no, license_expiry_date)
VALUES (customer_seq.nextval, ‘John’,’Smith’,’j.smith@uni.edu’,’12 Smith St’,’Dakota’,’4623′,’0401401401′,TO_DATE(’12/01/1975′, ‘DD/MM/YYYY’), ‘012435640’, TO_DATE(’19/12/2022′, ‘DD/MM/YYYY’));
INSERT INTO Customer(cust_id, cust_fname, cust_lname, cust_email, cust_address, cust_suburb, cust_postcode, cust_phone, cust_dob, license_no, license_expiry_date)
VALUES (customer_seq.nextval,’Jane’,’Rooster’,’j.rooster@uni.edu’,’665 Angelside Mwy’,’Poolamatta’,’4246′,’0402402402′,TO_DATE(’25/06/2000′, ‘DD/MM/YYYY’), ‘425186321’, TO_DATE(’20/5/2025′, ‘DD/MM/YYYY’));
INSERT INTO Customer(cust_id, cust_fname, cust_lname, cust_email, cust_address, cust_suburb, cust_postcode, cust_phone, cust_dob, license_no, license_expiry_date)
VALUES (customer_seq.nextval,’Percy Bisshe’,’Shelley’,’pb.shelley@uni.edu’,’3 Cigar Smoke Lane’,’Dakota’,’4623′,’0403403403′,TO_DATE(’30/08/1970′, ‘DD/MM/YYYY’), ‘562001459’, TO_DATE(’19/2/2021′, ‘DD/MM/YYYY’));
INSERT INTO Customer(cust_id, cust_fname, cust_lname, cust_email, cust_address, cust_suburb, cust_postcode, cust_phone, cust_dob, license_no, license_expiry_date)
VALUES (customer_seq.nextval,’Mary’,’Brown’,’m.brown@uni.edu’,’17 Exam Way’,’Dakota’,’4623′,’0404404404′,TO_DATE(’22/04/1999′, ‘DD/MM/YYYY’), ‘045175236’, TO_DATE(’21/10/2024′, ‘DD/MM/YYYY’));
INSERT INTO Customer(cust_id, cust_fname, cust_lname, cust_email, cust_address, cust_suburb, cust_postcode, cust_phone, cust_dob, license_no, license_expiry_date)
VALUES (customer_seq.nextval,’Sarah’,’Heatwood’,’s.heatwood@gmail.com’,’32 Pass Road’,’Sunny Bank’,’4100′,’0404404404′,TO_DATE(’27/01/1986′, ‘DD/MM/YYYY’), ‘267825101’, TO_DATE(‘1/8/2022’, ‘DD/MM/YYYY’));
— insert car_type
INSERT INTO Car_type(car_type_id, car_type, is_luxury, is_automatic, num_of_doors, num_of_seats, num_of_airbags, car_cost, insurance_cost)
VALUES (car_type_seq.nextval, ‘A’, ‘N’, ‘N’,3, 4, 2, 85.45, 17.09);
INSERT INTO Car_type(car_type_id, car_type, is_luxury, is_automatic, num_of_doors, num_of_seats, num_of_airbags, car_cost, insurance_cost)
VALUES (car_type_seq.nextval, ‘B’, ‘N’, ‘Y’, 5, 5, 2, 99.95, 19.99);
INSERT INTO Car_type(car_type_id, car_type, is_luxury, is_automatic, num_of_doors, num_of_seats, num_of_airbags, car_cost, insurance_cost)
VALUES (car_type_seq.nextval, ‘C’, ‘N’, ‘Y’, 5, 5, 3, 105, 21);
INSERT INTO Car_type(car_type_id, car_type, is_luxury, is_automatic, num_of_doors, num_of_seats, num_of_airbags, car_cost, insurance_cost)
VALUES (car_type_seq.nextval, ‘D’, ‘N’, ‘Y’, 4, 5, 4, 115, 23);
INSERT INTO Car_type(car_type_id, car_type, is_luxury, is_automatic, num_of_doors, num_of_seats, num_of_airbags, car_cost, insurance_cost)
VALUES (car_type_seq.nextval, ‘V’, ‘N’, ‘Y’, 5, 8, 6, 179, 35.8);
INSERT INTO Car_type(car_type_id, car_type, is_luxury, is_automatic, num_of_doors, num_of_seats, num_of_airbags, car_cost, insurance_cost)
VALUES (car_type_seq.nextval, ‘W’, ‘N’, ‘Y’, 5, 5, 6, 185, 37);
— insert car
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘012-WAA’, TO_DATE(’24/05/2020′, ‘DD/MM/YYYY’), ‘Summit White’,null, 1, ‘Spark’, ‘Holden’, ‘Y’, ‘Y’, 3, 2, ‘Cruise control, CD-Player’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘195-WAB’, TO_DATE(’30/06/2020′, ‘DD/MM/YYYY’), ‘Black’, null, 1, ‘Barina’, ‘Holden’, ‘Y’, ‘Y’, 3, 2, ‘Cruise control, CD-Player’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘246-WAA’, TO_DATE(’15/06/2020′, ‘DD/MM/YYYY’), ‘Dark Blue’, null, 2, ‘Swift’, ‘Suzuki’, ‘Y’, ‘Y’, 4, 3, ‘Smoke free, Cruise control, CD-Player’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘075-WAD’, TO_DATE(’30/05/2020′, ‘DD/MM/YYYY’), ‘Fire Brick’, null, 2, ‘Rio’, ‘Kia’, ‘Y’, ‘Y’, 4, 4, ‘Cruise control, CD-Player’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘007-WZE’, TO_DATE(’20/04/2020′, ‘DD/MM/YYYY’), ‘Royal Blue’, null, 3, ‘Corolla Ascent Sedan’, ‘Toyota’, ‘Y’, ‘Y’, 4, 5, ‘Smoke Free, Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘445-WKK’, TO_DATE(’15/07/2020′, ‘DD/MM/YYYY’), ‘Black’, null, 3, ‘i30 Active Hatch’, ‘Hyundai’, ‘Y’, ‘Y’, 4, 6, ‘Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘730-WKK’, TO_DATE(’30/06/2020′, ‘DD/MM/YYYY’), ‘Dark Blue’, null, 4, ‘Camry’, ‘Toyota’, ‘Y’, ‘Y’, 4, 7, ‘Smoke Free, Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘336-WCD’, TO_DATE(’28/06/2020′, ‘DD/MM/YYYY’), ‘Orange Red’, null, 4, ‘Civic Sedan’, ‘Honda’, ‘Y’, ‘Y’, 4, 8, ‘Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘724-WXA’, TO_DATE(’18/05/2020′, ‘DD/MM/YYYY’), ‘Slate Gray’, null, 5, ‘Carnival’, ‘Kia’, ‘Y’, ‘Y’, 6, 1, ‘Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘657-WXA’, TO_DATE(’30/05/2020′, ‘DD/MM/YYYY’), ‘White’, null, 5, ‘Hiace’, ‘Toyota’, ‘Y’, ‘Y’, 4, 2, ‘Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘450-WKX’, TO_DATE(’30/04/2020′, ‘DD/MM/YYYY’), ‘Light Steel Blue’, null, 6, ‘Pajero’, ‘Mitsubishi’, ‘Y’, ‘Y’, 6, 3, ‘Cruise control, CD-Player, Reversing Camera/Sensors, 4 Wheel Drive’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘816-WAA’, TO_DATE(’30/07/2020′, ‘DD/MM/YYYY’), ‘Silver’, null, 6, ‘Pajero’, ‘Mitsubishi’, ‘Y’, ‘Y’, 6, 4, ‘Cruise control, CD-Player, Reversing Camera/Sensors, 4 Wheel Drive’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘600-WNN’, TO_DATE(’18/05/2020′, ‘DD/MM/YYYY’), ‘Olive Drab’, null, 1, ‘Barina’, ‘Holden’, ‘Y’, ‘Y’, 3, 5, ‘Cruise control, CD-Player’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘944-WBB’, TO_DATE(’15/06/2020′, ‘DD/MM/YYYY’), ‘Slate Blue’, null, 2, ‘Swift’, ‘Suzuki’, ‘Y’, ‘Y’, 4, 6, ‘Smoke free, Cruise control, CD-Player’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘550-WKA’, TO_DATE(’15/08/2020′, ‘DD/MM/YYYY’), ‘Purple’, null, 3, ‘i30 Active Hatch’, ‘Hyundai’, ‘Y’, ‘Y’, 4, 7, ‘Cruise control, CD-Player, Reversing Camera/Sensors’);
INSERT INTO Car(car_id, license_plate, reg_expiry_date, color, car_image, car_type_id, car_name, manufacturer, usb_port, bluetooth, num_of_cylinders, location_id, extra_info)
VALUES (car_seq.nextval, ‘620-WAK’, TO_DATE(’25/05/2020′, ‘DD/MM/YYYY’), ‘White’, null, 5, ‘Carnival’, ‘Kia’, ‘Y’, ‘Y’, 6, 8, ‘Cruise control, CD-Player, Reversing Camera/Sensors’);
— insert car_hired car_id = 1, no promotion, cus_id = 2 (less than 25)
INSERT INTO Car_Hired (car_hired_id, date_hired, date_returned,
car_id, promo_id, cust_id, returned_location_id, date_expected_returned, is_insurred)
VALUES (car_hired_seq.nextval, TO_DATE(’02/12/2019 09:30:00′, ‘DD/MM/YYYY HH24:MI:SS’), TO_DATE(’06/12/2019 09:00:00′, ‘DD/MM/YYYY HH24:MI:SS’),
1, null, 2, 2, TO_DATE(’04/12/2019 09:30:00′, ‘DD/MM/YYYY HH24:MI:SS’), ‘Y’);
— insert invoice car_id = 1
INSERT INTO invoice (invoice_id, car_hired_id, invoice_date, is_additional_payment,
total_car_cost, total_insurrance_cost, total_less_than_25_cost, total_cost, gst)
VALUES (invoice_seq.nextval, car_hired_seq.currval, TO_DATE(’01/12/2019 09:30:00′, ‘DD/MM/YYYY HH24:MI:SS’), ‘N’,
2 * 85.45, 2 * 17.09, 2 * 17.09, 2 * 85.45 + 2 * 17.09 + 2 * 17.09, 0.10 * (2 * 85.45 + 2 * 17.09 + 2 * 17.09));
— insert invoice car_id = 1, an additional payment of 2 days
INSERT INTO invoice (invoice_id, car_hired_id, invoice_date, is_additional_payment,
total_car_cost, total_insurrance_cost, total_less_than_25_cost, total_cost, gst)
VALUES (invoice_seq.nextval, car_hired_seq.currval, TO_DATE(’06/12/2019 09:00:00′, ‘DD/MM/YYYY HH24:MI:SS’), ‘Y’,
2 * 85.45, 2 * 17.09, 2 * 17.09, 2 * 85.45 + 2 * 17.09 + 2 * 17.09, 0.10 * (2 * 85.45 + 2 * 17.09 + 2 * 17.09));
— insert car_hired car_id = 1, promo_id = 3, cust_id = 1
INSERT INTO Car_Hired (car_hired_id, date_hired, date_returned,
car_id, promo_id, cust_id, returned_location_id, date_expected_returned, is_insurred)
VALUES (car_hired_seq.nextval, trunc(sysdate-2)+10/24, null,
1, 3, 1, null, trunc(sysdate+5)+10/24, ‘Y’);
— insert invoice car_id = 1
INSERT INTO invoice (invoice_id, car_hired_id, invoice_date, is_additional_payment,
total_car_cost, total_insurrance_cost, total_less_than_25_cost, total_cost, gst)
VALUES (invoice_seq.nextval, car_hired_seq.currval, trunc(sysdate-4)+10/24, ‘N’,
0.8 * 7 * 85.45, 0.8 * 7 * 17.09, 0, 0.8 * 7 * (85.45 + 17.09), 0.10 * 0.8 * 7 * (85.45 + 17.09));
ALTER TABLE car_hired enable ALL TRIGGERS;
commit;
__MACOSX/._chm_db.sql
create_user.sql
— Important: replace jc000000 by your jcxxxxxx username
DROP USER chm_jc000000 CASCADE;
DROP TABLESPACE chmts_jc000000 INCLUDING CONTENTS AND DATAFILES;
— TABLESPACE
CREATE SMALLFILE TABLESPACE chmts_jc000000
DATAFILE ‘chmts_jc000000.dbf’ SIZE 100M AUTOEXTEND ON NEXT 10M;
— USER SQL
CREATE USER chm_jc000000 IDENTIFIED BY oracle
DEFAULT TABLESPACE chmts_jc000000
TEMPORARY TABLESPACE temp;
— QUOTAS
ALTER USER chm_jc000000 QUOTA 100M ON chmts_jc000000;
— ROLES
GRANT “CONNECT” TO chm_jc000000 WITH ADMIN OPTION;
— SYSTEM PRIVILEGES
GRANT CREATE TRIGGER TO chm_jc000000 ;
GRANT CREATE VIEW TO chm_jc000000 ;
GRANT CREATE TABLE TO chm_jc000000 ;
GRANT CREATE SYNONYM TO chm_jc000000 ;
GRANT CREATE SEQUENCE TO chm_jc000000 ;
GRANT CREATE USER TO chm_jc000000 ;
GRANT CREATE PROCEDURE TO chm_jc000000 ;
__MACOSX/._create_user.sql
CP5503Enterprise Database Systems Oracle
Assignment Part2 – Trigger (10 % due by 5 pm Friday Week 8)
Firstly, you have to setup the user CHM_JCXXXXXX account and create the required tables. To do so, you
have to use 2 given files in ass2_sql_file.zip. You should perform the following steps:
1. Run Oracle and SQL Developer
2. Connect ORCL by the user system
3. Open the script create_user.sql and change jc000000 to your jc username, for example, jc123456
4. Run the script create_user.sql
5. You can now disconnect the system connection
6. Connect ORCL by the user chm_jcxxxxxx (jcxxxxxx is your username)
7. Open the script chm_db.sql and run it
Note: chm_tables.sql creates tables with basic constraints, no additional constraints
If there is anything wrong, you can always re-run the 2 files again. You simply disconnect the chm_jcxxxxxx
connection and then re-run the above 7 steps.
You have to login as a CHM_JCXXXXXX user to write SQL and PL/SQL to define triggers, stored
procedures/functions as specified in the following subsections. You should also include SQL/PLSQL
statements to test the triggers and procedures/functions that you have to implement. Make sure that you
test single row as well as multiple row actions. Include comments for each testing statement to indicate
the trigger or the procedure/function in which it applies and the expected results of the trigger firing or
procedure/function executing.
Task 1 (5 points): TR_change_state_upper trigger (Hint: using upper/lower functions)
To change the cases of state to upper case before inserting or updating a row in the Location table.
You should produce your answer in Microsoft Word with a heading Task1 containing:
– the required trigger (PL/SQL statements)
– testing: write SQL statements to insert 2 records to test the trigger as follows:
address suburb postcode city state
81 Canoona Rd Rockhampton Airport 4700 null qld
204 Oxford Street Bondi Junction 2022 Sydney Nsw
– showing the testing results
Hint:
— the required trigger
CREATE OR REPLACE TRIGGER TR_change_state_upper
…
…
BEGIN
:new.state := upper(:new.state);
END;
— testing statement(s)
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES (location_seq.nextval, ’81 Canoona Rd’, Rockhampton Airport’, ‘4700’, null, ‘qld’);
INSERT INTO Location(location_id, address, suburb, postcode, city, state)
VALUES …;
— test result(s)
SELECT * FROM Location;
Task 2 (15 points): FUNC_is_car_available function
Create a function called FUNC_is_car_available to check whether a given car is available or not. It should
have three input parameters car_id_p, date_hired_p, and date_expected_returned_p. It returns 1 if the
car is available and returns 0 if it is unavailable.
Hint: a car is unavailable if there is a record in Car_Hired with date_returned being null and [date_hired_p,
date_expected_returned_p] is outside [date_hired, date_expected_returned]. Use CURSOR or
SELECT…INTO , your choice. CURSOR does not need to raise an exception, SELECT…INTO does need to raise
an exception.
You should produce your answer in Microsoft Word with a heading Task2 containing:
– the required function (PL/SQL statements)
– testing: test the function with 2 values: car_id = 1 and car_id = 16. You can either write an
anonymous block to test or run the following 2 select statements:
— not available
select FUNC_IS_CAR_AVAILABLE(1, trunc(sysdate +1)+10/24, trunc(sysdate +2)+10/24) from dual;
— available
select FUNC_IS_CAR_AVAILABLE(1, trunc(sysdate +5)+11/24, trunc(sysdate +6)+11/24) from dual;
— available
select FUNC_IS_CAR_AVAILABLE(16, trunc(sysdate +1)+10/24, trunc(sysdate +2)+10/24) from dual;
– showing the testing results
Task 3 (20 points): TR_car_hired_before_insert trigger
Create a trigger called TR_car_hired_before_insert. This trigger fires before inserting a row in the
Car_Hired table. The trigger should raise an application error with a meaningful message to each of the
following cases:
– the car is not available (Hint: call the function FUNC_is_car_available) (3 points)
– date_hired is less than sysdate (3 points)
– date_expected_returned is less than date_hired (3 points)
– date_returned is not null (3 points)
– returned_location_id is not null (3 points)
– the time of date_hired must be between 8:00 and 17:00 (5 points)
You should produce your answer in Microsoft Word with a heading Task3 containing:
– the required trigger (PL/SQL statements)
– testing: write SQL statements to insert 7 records to test the trigger as follows:
1. Invalid record – car not available:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +1)+10/24
date_returned: null
car_id: 1
promo_id: null
cust_id: 5
returned_location_id: null
date_expected_returned: trunc(sysdate +2)+10/24
is_insurred: N
2. Invalid record – date_hired is less than sysdate:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate -1)+10/24
date_returned: null
car_id: 4
promo_id: null
cust_id: 5
returned_location_id: null
date_expected_returned: trunc(sysdate +1)+10/24
is_insurred: N
3. Invalid record – date_expected_returned is less than date_hired:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +1)+10/24
date_returned: null
car_id: 4
promo_id: null
cust_id: 5
returned_location_id: null
date_expected_returned: trunc(sysdate)+10/24
is_insurred: N
4. Invalid record – date_returned is not null:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +1)+10/24
date_returned: trunc(sysdate +2)+10/24
car_id: 4
promo_id: null
cust_id: 5
returned_location_id: null
date_expected_returned: trunc(sysdate +2)+10/24
is_insurred: N
5. Invalid record – returned_location_id is not null:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +1)+10/24
date_returned: null
car_id: 4
promo_id: null
cust_id: 5
returned_location_id: 1
date_expected_returned: trunc(sysdate +2)+10/24
is_insurred: N
6. Invalid record – date_hired is not between 8 am and 5 pm:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +1)+7/24+50/1440 — 7:50
date_returned: null
car_id: 4
promo_id: null
cust_id: 5
returned_location_id: null
date_expected_returned: trunc(sysdate+2)+8/24+30/1440 – 8:30
is_insurred: N
7. Invalid record – date_hired is not between 8 am and 5 pm:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +1)+17/24+10/1440 — 17:10
date_returned: null
car_id: 4
promo_id: null
cust_id: 5
returned_location_id: null
date_expected_returned: trunc(sysdate+2)+8/24+30/1440 – 8:30
is_insurred: N
Task 4 (10 points): PR_insert_invoice
Create a procedure called PR_insert_invoice. This procedure should contain the following input
parameters: car_hired_id_p, cust_id_p, car_id_p, is_insurred_p, promo_id_p, is_additional_payment_p,
from_DATE_p, to_DATE_p. Based on the given input parameters, the procedure should insert a new
record in the Invoice table. Note: please consider promotion, insurance and young drivers if exists. A valid
promotion is the promo_value in the Promotion table. The total cost = the total car cost + the total
insurance cost + the total less-than-25 cost. The total less-than-25 cost is equal to the total insurance cost
if the age of the driver is less than 25 years old, otherwise, the total less-than-25 cost is 0. GST is 10% of the
total cost.
You should produce your answer in Microsoft Word with a heading Task4 containing:
– the required procedure (PL/SQL statements)
– testing: no testing in this Task. The procedure will be tested in the Task 5.
Hint:
— get the next invoice sequence value:
select invoice_seq.nextval into invoice_id_var from dual;
— number of days between date1 and date2:
date2 – date1
— compute age:
months_between(sysdate, cust_dob)/12
— use the function to_date to insert date&time value:
to_date(’27/01/2020 09:30:00′,’DD/MM/YYYY HH24:MI:SS’
)
—- Here is the skeleton of the procedure —
create or replace PROCEDURE PR_INSERT_INVOICE
(
car_hired_id_p in number,
cust_id_p in number,
car_id_p in number,
is_insurred_p in varchar2,
promo_id_p in number,
is_additional_payment_p in varchar2,
from_DATE_p IN DATE,
to_DATE_p IN DATE
)
AS
— define your variables to use
— …
BEGIN
— your statements
null; — for testing
END PR_INSERT_INVOICE;
— end of the skeleton code —
Task 5 (10 points): TR_car_hired_after_insert trigger
Create a trigger called TR_car_hired_after_insert. This trigger fires after inserting a row in the Car_Hired
table. The trigger should insert a new record in the Invoice table. This trigger must call the procedure in
Task 4.
You should produce your answer in Microsoft Word with a heading Task5 containing:
– the required trigger (PL/SQL statements)
– testing: write SQL statements to insert 2 car_hired records to test the trigger as follows:
1. first car_hired record:
car_hired_id: car_hired.nextval,
date_hired: trunc(sysdate +1)+12/24,
date_returned: null,
car_id: 2,
promo_id: null,
cust_id: 3,
returned_location_id: null,
date_expected_returned: trunc(sysdate +2)+12/24,
is_insurred: N
2. second car_hired record:
car_hired_id: car_hired.nextval
date_hired: trunc(sysdate +2)+12/24,
date_returned: null,
car_id: 3,
promo_id: 3,
cust_id: 4,
returned_location_id: null,
date_expected_returned: trunc(sysdate +6)+12/24,
is_insurred: Y
– showing the testing results
SUBMISSION:
Submission date: By 5pm Friday Week 8.
Submit a single word file as jcnumber x (e.g. jc222333 x) on LearnJCU containing all of your
answers.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Achiever Papers is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Dissertation Writing Service Works
First, you will need to complete an order form. It's not difficult but, if anything is unclear, you may always chat with us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download