Data Base Systems Oracle
— 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 ;
