• Design and implement PL/SQL function, trigger, and stored procedures

Problem 2 Character-based Primary Key including a sequence number (TRIGGER)

Client ids C_ID are defined as a string of 10 characters. The first character is a letter C followed by 9 characters, which are created from a sequence left padded by zeros. Create a trigger to add automatically a new primary key for an INSERT on the Client table. Similar trigger must run on vehicle table to define vehicle Id as well as on the transaction table to have transaction id. Vehicle has the character V and transaction table do not have any code.

VEHICLE TABLE

V_ID

V_MODEL

V_MAKE

COST_PER_DAY

RESERVATION TABLE

R ID

START_DATE

END_DATE

C_ID

V_ID

TOTAL_COST

CLIENT TABLE

C_ID

F_NAME

L_NAME

DOB

CITY

GENDER

Following is the codes to create 3 tables and insert data.

CREATE TABLE VEHICLE_19 (V_ID CHAR(5) PRIMARY KEY , V_MODEL VARCHAR2(15), V_MAKE CHAR(4), COST_PER_DAY NUMBER(7,2));

INSERT INTO VEHICLE_19 VALUES ('V1001', 'Tata Sierra', '2011',37.50); INSERT INTO VEHICLE_19 VALUES ('V1002', 'Honda CRV', '2016',47.50); INSERT INTO VEHICLE_19 VALUES ('V1003', 'Mercedez Benz', '2011',117.50); INSERT INTO VEHICLE_19 VALUES ('V1004', 'Ford Focus', '2011',87.50); INSERT INTO VEHICLE_19 VALUES ('V1005', 'Dodge Dakota', '2013',87.00);

Create table CLIENT_19 (C_ID CHAR(10) PRIMARY KEY, F_NAME VARCHAR2(25), L_NAME VARCHAR2(25), DOB DATE, CITY VARCHAR2(15), GENDER CHAR(1) CHECK (GENDER IN (NULL, 'M','F')));

INSERT INTO CLIENT_19 VALUES ('C00000001','Mike','Thomas',to_date('1998-12-20','yyyy-mm-dd'), 'Kamloops','M'); INSERT INTO CLIENT_19 VALUES ('C00000002','Andrew','Thessen',to_date('1988-08-20','yyyy-mm-dd'), 'Williams Lake','M');

create table RESERVATION_19 (R_ID NUMBER PRIMARY KEY, START_DATE DATE, END_DATE DATE, C_ID CHAR(10) REFERENCES CLIENT_19(C_ID), V_ID CHAR(5) REFERENCES VEHICLE_19(V_ID) , TOTAL_COST NUMBER(9,2));

INSERT INTO RESERVATION_19 VALUES (1, TO_DATE('12-01-2019','DD-MM-YY'), TO_DATE('15-01-2019','DD-MM-YY'), 'C000000001', 'V1003',352.50); INSERT INTO RESERVATION_19 VALUES (2, TO_DATE('2-01-2019','DD-MM-YY'), TO_DATE('6-01-2019','DD-MM-YY'), 'C000000002', 'V1001',150.00); INSERT INTO RESERVATION_19 VALUES (3, TO_DATE('10-10-2018','DD-MM-YY'), TO_DATE('12-10-2018','DD-MM-YY'), 'C000000001', 'V1004',150.00);