For these questions you must use the tables created in your own schema, because you DO NOT have privileges to INSERT, UPDATE or DELETE values in SI schema.

1. Write a stored procedure that can be used to find the number of cars and total amount of money a customer has spent on all cars they have bought. This procedure will be used in question 2, but it can be used in any circumstance to find the number of cars and the total amount spent by one or more customers. Only use parameters to pass/receive data. Donot use DBMS_OUTPUT.PUT_LINE to display data. Use a copy of the SI schema in your own schema (5 marks)
2. Write an anonymous block that would prompt the user to enter a city and uses the procedure created previously (in #1) to display the number of cars and the total amount spent on cars from all the customers from that city. You can use a cursor or a collection to store all the customers from the city and loop through and call the procedure within to display the number of cars and the money spent by the customers. Keep in mind that each city may have been stored in the database in different case spellings (for example OAKVILLE, oakville or Oakville are to be treated as identical). (10 marks)
3. Create a trigger for car table that will reject inserting or updating values for carlistprice that are either negative or larger than 250000. (5 marks)
4. Write a script with three commands.
   • An INSERT statement inserting a new record in car table. You will have to deal with referential integrity constraints caused by purchinvno, custname etc. Insert values for all columns in the car table and make sure that the value for carlistprice is between 1 and 249999.99.
   • Write an UPDATE statement and update the carlistprice to a value that is either negative or greater than 250000.
   • Follow by a COMMIT.
   Execute the script and show the response from the server.(5 marks)

/*----------------------------------------------- | create_si_tables.sql | | KI. | | s11 | | Create Specialty Imports Database Tables. | -----------------------------------------------*/

-- drop tables and sequences in the revoerse orders of creating DROP SEQUENCE saleinv_seq;

DROP SEQUENCE servinv_seq;

DROP TABLE prospect cascade constraints PURGE;

DROP TABLE servwork cascade constraints PURGE;

DROP TABLE servinv cascade constraints PURGE;

DROP TABLE invoption cascade constraints PURGE;

DROP TABLE tradeinv cascade constraints PURGE;

DROP TABLE saleinv cascade constraints PURGE;

DROP TABLE baseoption cascade constraints PURGE;

DROP TABLE purchinv cascade constraints PURGE;

DROP TABLE car cascade constraints PURGE;

DROP TABLE employee cascade constraints PURGE;

DROP TABLE options cascade constraints PURGE;

DROP TABLE customer cascade constraints PURGE;

PURGE RECYCLEBIN;

-- create tables in an order that does not violate referential integrity

CREATE TABLE customer ( custname VARCHAR2(20) CONSTRAINT customer_cname_pk PRIMARY KEY ,custstreet VARCHAR2(20) NOT NULL ,custcity VARCHAR2(20) NOT NULL ,custprovince VARCHAR2(2) DEFAULT 'ON' NOT NULL ,custpostal CHAR(7) ,custhphone CHAR(13) ,custbphone CHAR(13) );

CREATE TABLE options ( optioncode CHAR(4) CONSTRAINT options_ocode_pk PRIMARY KEY ,optiondesc VARCHAR2(30) ,optioncost NUMBER(7,2) ,optionlistprice NUMBER(7,2) );

CREATE TABLE employee ( empname VARCHAR2(20) CONSTRAINT employee_empname_pk PRIMARY KEY ,startdate DATE NOT NULL ,commissionrate NUMBER(3,1) ,title VARCHAR2(26) ,manager VARCHAR2(20) CONSTRAINT employee_fk REFERENCES employee ( empname ) );

CREATE TABLE purchinv ( purchinvno CHAR(6) CONSTRAINT purchinv_purchinvno_pk PRIMARY KEY ,purchfrom VARCHAR2(15) ,purchdate DATE );

CREATE TABLE car ( carserial CHAR(8) CONSTRAINT car_serial_pk PRIMARY KEY ,custname VARCHAR2(20) CONSTRAINT car_cname_fk REFERENCES customer ( custname ) ,carmake VARCHAR2(10) NOT NULL ,carmodel VARCHAR2(10) NOT NULL ,caryear CHAR(4) NOT NULL ,extcolor VARCHAR2(12) NOT NULL ,cartrim VARCHAR(16) NOT NULL ,enginetype VARCHAR2(10) NOT NULL ,purchinvno CHAR(6) ,purchcost NUMBER(9,2) ,freightcost NUMBER(9,2) ,carlistprice NUMBER(9,2) );

CREATE TABLE baseoption ( optioncode CHAR(4) CONSTRAINT baseoption_ocode_fk REFERENCES options ( optioncode ) ,carserial CHAR(8) CONSTRAINT baseoption_serial_fk REFERENCES car ( carserial ) ,CONSTRAINT baseoption_pk PRIMARY KEY ( carserial ,optioncode ) );

CREATE TABLE saleinv ( saleinvno CHAR(6) CONSTRAINT saleinv_pk PRIMARY KEY ,saledate DATE NOT NULL CONSTRAINT saleinv_saledate_ck CHECK ( saledate > TO_DATE('01-JAN-1990','DD-MON-YYYY') ) ,salesman VARCHAR2(20) NOT NULL ,custname VARCHAR2(20) NOT NULL ,carserial CHAR(8) NOT NULL ,insfire CHAR(1) CONSTRAINT saleinv_fire_ck CHECK ( insfire IN ( 'Y' ,'N' ) ) ,inscollision CHAR(1) CONSTRAINT saleinv_collision_ck CHECK ( inscollision IN ( 'Y' ,'N' ) ) ,insliability CHAR(1) CONSTRAINT saleinv_liability_ck CHECK ( insliability IN ( 'Y' ,'N' ) ) ,insproperty CHAR(1) CONSTRAINT saleinv_property_ck CHECK ( insproperty IN ( 'Y' ,'N' ) ) ,taxrate NUMBER(4,2) DEFAULT 13 ,licfee NUMBER(6,2) ,discount NUMBER(8,2) ,commission NUMBER(8,2) ,carsaleprice NUMBER(9,2) ,CONSTRAINT saleinv_customer_fk FOREIGN KEY ( custname ) REFERENCES customer ( custname ) ,CONSTRAINT saleinv_car_fk FOREIGN KEY ( carserial ) REFERENCES car ( carserial ) ,CONSTRAINT saleinv_employee_fk FOREIGN KEY ( salesman ) REFERENCES employee ( empname ) );

CREATE TABLE tradeinv ( saleinvno CHAR(6) CONSTRAINT tradeinv_saleinv_fk REFERENCES saleinv ( saleinvno ) ,carserial CHAR(8) CONSTRAINT tradeinv_car_fk REFERENCES car ( carserial ) ,tradeallow NUMBER(9,2) ,CONSTRAINT tradeinv_pk PRIMARY KEY ( saleinvno ,carserial ) );

CREATE TABLE invoption ( saleinvno CHAR(6) CONSTRAINT invoption_saleinv_fk REFERENCES saleinv ( saleinvno ) ,optioncode CHAR(4) CONSTRAINT invoption_options_fk REFERENCES options ( optioncode ) ,optionprice NUMBER(7,2) ,CONSTRAINT invoption_pk PRIMARY KEY ( saleinvno ,optioncode ) );

CREATE TABLE servinv ( servinvno CHAR(5) CONSTRAINT servinv_pk PRIMARY KEY ,servdate DATE NOT NULL ,custname VARCHAR2(20) NOT NULL CONSTRAINT servinv_cname_fk REFERENCES customer ( custname ) ,carserial CHAR(8) NOT NULL CONSTRAINT servinv_car_fk REFERENCES car ( carserial ) ,partscost NUMBER(7,2) ,laborcost NUMBER(7,2) ,taxrate NUMBER(4,2) DEFAULT 13.0 );

CREATE TABLE servwork ( servinvno CHAR(5) CONSTRAINT servwork_servinv_fk REFERENCES servinv ( servinvno ) ,workdesc VARCHAR2(80) ,CONSTRAINT servwork_pk PRIMARY KEY ( servinvno ,workdesc ) );

CREATE TABLE prospect ( custname VARCHAR2(20) NOT NULL CONSTRAINT prospect_customer_fk REFERENCES customer ( custname ) ,carmake CHAR(10) NOT NULL CONSTRAINT prospect_make_ck CHECK ( carmake IN ( 'ACURA' ,'MERCEDES' ,'LAND ROVER' ,'JAGUAR' ) ) ,carmodel VARCHAR2(10) ,caryear CHAR(4) ,carcolor VARCHAR2(12) ,cartrim VARCHAR2(16) ,optioncode CHAR(4) CONSTRAINT porspect_options_fk REFERENCES options ( optioncode ) ,CONSTRAINT prospect_un UNIQUE ( custname ,carmake ,carmodel ,caryear ,carcolor ,cartrim ,optioncode ) );

-- create sequences that are going to be used for creating primary keys for saleinv and servinv

CREATE SEQUENCE saleinv_seq;

CREATE SEQUENCE servinv_seq;

INSERT INTO customer SELECT * FROM si.customer;

INSERT INTO options SELECT * FROM si.options;

INSERT INTO employee SELECT * FROM si.employee;

INSERT INTO purchinv SELECT * FROM si.purchinv;

INSERT INTO car SELECT * FROM si.car;

INSERT INTO baseoption SELECT * FROM si.baseoption;

INSERT INTO saleinv SELECT * FROM si.saleinv;

INSERT INTO invoption SELECT * FROM si.invoption;

INSERT INTO tradeinv SELECT * FROM si.tradeinv;

INSERT INTO servinv SELECT * FROM si.servinv;

INSERT INTO servwork SELECT * FROM si.servwork;

INSERT INTO prospect SELECT * FROM si.prospect; COMMIT;