Car hire database – assignment 2

Assignment 2

 

 

Due date:

07 October 2014

Value:

100%

 

 

Please submit the CODE and OUTPUT for all programming questions.

 

For the output, a screen dump is highly encouraged.

 

IMPORTANT NOTES – PLEASE READ BEFORE YOU BEGIN WORK

 

  1. This assignment prepares you for the examination and you should consider each hour devoted to the assignment as an hour devoted to exam preparation.
  2. 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 (35 marks)

Write a function (called most_recent_rental) that returns the most recent date of rental for a particular car (registration) for all completed bookings made for that car.  If a rental does not exist for that car registration, the function should return ‘NO RENTALS’ (5 marks)

Write a second function (called maximum_rental_period) that returns the maximum rental period for a particular car (registration) for all completed bookings made for that car.  If the car has only one booking but has not been returned it should return a ‘-999’ for the rental period. (5 Marks)

For both the functions, you should check for completed bookings, i.e. miles_in is not null.

Write a procedure that takes a car registration and returns the most recent date of rental and the maximum rental period for that car.  The procedure had the following formal parameters (10 Marks)

PROCEDURE CAR_DETAIL( REGISTRATION IN VARCHAR2, RECENT_DATE VARCHAR2, RENTAL_PERIOD OUT NUMBER)

Write an anonymous block to call the procedure using an explicit cursor to display the details (registration, most_recent_rental, maximum_rental_period) for all the cars in the i_car table by calling the procedure above.  The output should only include cars that have a booking and have been returned. (10 Marks)

The CAR_REGSTRATION was last rented on MOST_RECENT_RENTAL and has had the longest rental period of MAXIMUM_RENTAL_PERIOD.

Write ONE SQL statement that displays the registration, model name and cost for each car and calls the two functions to display the most recent date of rental and the maximum rental period for each of the cars.  (5 marks)    

 

Display the output of the SQL statement.

 

Question 2 (20 marks)

The car hire company is concerned about possible unauthorised changes to customer details that may compromise the company’s security and privacy policies. You will create a database trigger to monitor such changes to some important columns in the I_CUSTOMER table.

  1. Create a copy of the I_CUSTOMER table in your own schema by using the following command:
    CREATE TABLE MY_CUSTOMER AS SELECT * FROM I_CUSTOMER;
  2. Also create a table to store change logs (CUSTOMER_CHANGELOG) by using the following command:

CREATE TABLE CUSTOMER_CHANGELOG

(

cust_no NUMBER(5),

cust_name VARCHAR2(20),

contact VARCHAR2(20),

log_date DATE);

  1. Create a database trigger with the following specification:
  • fires AFTER an UPDATE on MY_CUSTOMER table.
  • fires if there is any change to  cust_name or contact columns of the N_CUSTOMER table.
  • fires for each row. 
  • inserts a first row: the affected cust_no column, the OLD values of the cust_name and contact columns and the current date into the table CUSTOMER_CHANGELOG.
  • inserts a second row: the affected cust_no column, the NEW values of the cust_name and contact columns and the current date into the table CUSTOMER_CHANGELOG.

 

  1. Test the trigger with the following specification:
  • Go ahead and make a change to MY_CUSTOMER table by issuing the following command:

UPDATE MY_CUSTOMER

SET cust_name=”Sam Spade”, contact=”Kaptain Krunch”

WHERE cust_no = 8981;

  1. 5.     Now, select all rows from the CUSTOMER_CHANGELOG table. This should display the old and new values of the changes you made due to firing of the trigger. Output all the rows of CUSTOMER_CHANGELOG table.

 

Question 3 (40 marks)

 

Create a PL/SQL procedure called report_rental_month which has the needed IN parameters for year and month.  The procedure will display to the screen the following format for each car rented during that year/month in the i_booking table.  The information needs to be retrieved using an explicit cursor for all rentals in that year for that month.

 

Car REGISTRATION was rented TIMES for a total of DAYS

 

You will need to write extra functions to calculate the number of times and total number of days the car was booked for that month for that year to be called from within the procedure. Again only where the rental has finished and the car has been returned.

 

Write a procedure called report_booking_totals that will produce the following output in the format specified bellow.  The procedure needs to use explicit cursor/s for each year starting with the oldest year produces a summary of the cars rented for that month.  The years and month output needs to be in ascending order.

 

Year 2011

   January

Car REGISTRATION was rented TIMES for a total of DAYS

Car REGISTRATION was rented TIMES for a total of DAYS

   March

Car REGISTRATION was rented TIMES for a total of DAYS

Car REGISTRATION was rented TIMES for a total of DAYS

Year 2012

   February

Car REGISTRATION was rented TIMES for a total of DAYS

   July

Car REGISTRATION was rented TIMES for a total of DAYS

 

Total number of booking is COUNT for a total of DAYS with income of $AMOUNT.XX.

 

Currently there are COUNT numbers of rentals not finalized

 

The AMOUNT is to be calculated with a function call.  Rentals not finalized are rentals that have not been returned to the rental place.  The numbers not finalized is to be calculated either with a function call or during the processing of the procedure.

 

Important Notes:

 

  • You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) to create the PL/SQL function or the PL/SQL anonymous block.
  • The PL/SQL anonymous block must be ONE block only. Do NOT write a block to perform each task of the specification above.

 

MARKING CRITERIA

  1. The code executes without error messages.
  2. The code produces the required output.
  3. The code addresses the specification and provides a solution to every element in the specification.
  4. The code is well structured and, where applicable, adopts an optimal and sophisticated approach to PL/SQL.
  5. The programing units have been separated into necessary functions and procedures

 

Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.

Professional Academic Writers

Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.

Affordable Prices

Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.

On-Time delivery

We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.

100% Originality

At Papers Owl, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.

Customer Support 24/7

Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email: support@acmehomework.com.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Papers Owl has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Papers Owl, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.

Admissions

Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.

Editing

Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.

Coursework

Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.

error: Content is protected !!