Database Systems: Procedures

Problem: Black list (Stored Procedure)

Create a store procedure to add clients to the Black list. The input parameters are C_ID and a string of characters for the reason. Insert all client information, the reason , and the USER NAME (STU.) and date/time. The procedure should also delete the client from the clients table

Problem: Archiving data (Stored Procedure)

Create a stored procedure to archive the reservation data. The archived procedure is executed by the DBA on the first day of a new year. This procedure has no input parameters and displays message total # reservations archived. The archiving procedure adds the old (previous year) reservations to the ARCHIVED_RESERVATION table and deletes the old reservations from the RESERVATION table.

VEHICLE V ID VIN V MAKE COST_PER_DAY RESERVATION R ID START_DATE END_DATE C ID V ID TOTAL_COST CLIENT C ID Name1 Name Z F NAME L NAME DOB CITY GENDER ARCHIVED RESERVATION R ID START_DATE END DATE C ID V ID TOTAL_COST ADDED BY ADDED DATE BLACK LIST C ID F NAME L_NAME DOB CITY GENDER REASON ADDED BY ADDED_DATE STATS YEAR C ID AGE GENDER TOT RESERVATIONS TOT_DAYS_OF_RESERVATIONS TOT COST_OF_RESERVATIONS VEHICLE V ID VIN V MAKE COST_PER_DAY RESERVATION R ID START_DATE END_DATE C ID V ID TOTAL_COST CLIENT C ID Name1 Name Z F NAME L NAME DOB CITY GENDER ARCHIVED RESERVATION R ID START_DATE END DATE C ID V ID TOTAL_COST ADDED BY ADDED DATE BLACK LIST C ID F NAME L_NAME DOB CITY GENDER REASON ADDED BY ADDED_DATE STATS YEAR C ID AGE GENDER TOT RESERVATIONS TOT_DAYS_OF_RESERVATIONS TOT COST_OF_RESERVATIONS