Data base systems oracle
CP5503 Enterprise 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.docx (e.g. jc222333.docx) on LearnJCU containing all of your answers.