CIS 3010: Assignment 2

Due date: 21 October 2013 Value: 30% Please submit the CODE and OUTPUT for all programming questions. You will submit this assignment electronically in the ASSESSMENT area of the Study Desk.

For the output, a screen dump is highly encouraged.

IMPORTANT NOTES – PLEASE READ BEFORE YOU BEGIN WORK

This assignment prepares you for the examination and you should consider each hour devoted to the assignment as an hour devoted to exam preparation.
I urge you not to give up if you are unable to do all the questions. Do as many as you can and submit what you have done.

In this assignment, you will use the CAR HIRE database. The CAR HIRE database including appropriate data will be made available on the USQ Oracle server. You can query these tables as if they are in your own schema but you are not permitted to insert or update data in these tables. The specification for the CAR HIRE database is as follows:
The table descriptions appear below, including the column names, datatypes and the meanings for values in the columns. Familiarise yourself with the tables and the data.
I_CAR

I_CARGROUP

I_MODEL

I_CUSTOMER

I_BOOKING

Question 1 (25 marks)

Write a stored procedure that accepts the model name of a car as the input parameter and provides the number of cars belonging to that model as the output parameter. The procedure should also display the description of the car model.
Procedure:
Create the procedure that should have one IN parameter (p_model) and one OUT parameter (p_noofcars)
Use an implicit cursor and a SQL function to calculate the number of cars belonging to the particular car model and assign that value to the OUT parameter.
Use another implicit cursor to display the description belonging to the particular car model.

Submit the code to create the procedure and the code to call the procedure in a PL/SQL block. Value for the input parameter for the procedure…