Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

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.

23_I_car.jpg

511_I_car group.jpg

987_I_model.jpg

341_I_Boking.jpg

2100_I customer.jpg

problem 1:

prepare a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive details about each customer, the number of bookings that customer has made and the total cost of all completed rentals for that customer.

Procedure:

A) Use a fully declared explicit cursor to find and display comprehensive details for each customer.
B) Use an implicit cursor and a SQL function to find out the number of bookings the customer has made. Display the number of bookings.
C) find out the total cost. In order to find out the total cost, you may not use any SQL functions (e.g. SUM) and you must find a way of using local variables to do the arithmetic. Only completed rentals are used in this calculation. When miles_in has a value, the rental is complete. Display the total cost when the computation is complete.

Submit the code and a screen dump of the output when the program is run.

problem 2:

prepare a function that find outs the average miles travelled for a particular car (registration) for all completed bookings made for that car.

prepare a second function that displays the maximum amount paid (amount due) for a particular car (registration) for all completed bookings made for that car.

prepare ONE SQL statement that displays the registration, model_name and date bought for each car and calls the two functions to display the average miles travelled and the maximum amount paid for that car.

Display the output of the SQL statement.

Submit the code and a screen dump of the output.

problem 3:

The company is concerned about hackers making changes to financial details for bookings, in particular the paid flag and the amount due. You will create a database trigger to monitor changes.

Create a copy of the I_BOOKING table in your own schema by using the following command: CREATE TABLE GBOOKING AS SELECT * FROM I_BOOKING. Now, create a database trigger on the GBOOKING table that fires after an UPDATE. The trigger should fire for each row. The trigger should fire if there is any change to  amount_due or paid and should insert the booking number, and the OLD and NEW values of each of these columns into a the table BOOKING_CHANGE. This table may be created using the command: CREATE TABLE BOOKING_CHANGE AS SELECT BOOKING_NO, AMOUNT_DUE, PAID FROM I_BOOKING WHERE BOOKING_NO = 0;

problem 4:

prepare a anonymous block that finds the car that has generated the most revenue (use amount_due here). Only include rentals that are complete. A completed rental involves a situation where the car has been returned and payment may (or may not) have been made. Note that the top car in terms of revenue may not be the same as the top car in terms of rental volume. Also note that, depending upon the date range selected, no car may be selected. Therefore, test with the date range 1 to 31 October, 2012 or a date range of several years. Exclude from your calculations any booking when the miles travelled is less than 100, the rental period is less than two days or the registration begins with a G.  This exclusion should not be done in the select statement i.e. it should be done after the BEGIN using IF.
The anonymous block should display the following: registration and revenue generated.

You may not use any function in this program, for ex SUM, COUNT, AVG, MIN, MAX. Therefore, you will need to use local variables to hold values and perhaps re-use explicit cursors.

Run the anonymous block from ISQLPLUS and include a screen dump of the output.

problem 5:

The table CUSTOMER_MASTER is a mirror of I_CUSTOMER,  BOOKING_MASTER is a mirror of I_BOOKING and CAR_MASTER is a mirror of I_CAR.  None of these tables belong to you but you have read and prepare access to them. prepare PL/SQL stored procedures that insert two rows into the tables CUSTOMER_MASTER AND BOOKING_MASTER and two rows into the table CAR_MASTER. Also, prepare an anonymous block that calls the procedures.

Examine the tables I_CUSTOMER, I_CAR and I_BOOKING carefully first, paying particular attention to the data. Your procedures and anonymous block should meet the following criteria:

A) The anonymous block should display the number of rows currently in each the three tables CUSTOMER_MASTER, CAR_MASTER AND BOOKING_MASTER. Then, the anonymous block should call the procedures and pass all the data to be entered into the tables to the procedures. It is assumed that three procedures will be required (i.e. one per table) but, if you are able to prepare a hybrid procedure to accomplish all the tasks, that is acceptable. Next, the anonymous block should display the total number of rows that the procedures have inserted into the database. Finally, the number of rows in each of the three tables CUSTOMER_MASTER, CAR_MASTER AND BOOKING_MASTER after the inserts should be displayed.

B) The procedures should use parameters to process the data passed from the anonymous block and insert the data into the appropriate table. The procedures should keep a count of each row inserted and pass the count back to the calling anonymous block.

C) For the CAR_MASTER table, you should create a primary key using your own initiative but, for the other two tables, your procedure should find the current highest value of the primary key and allocate the next value.

D) It is expected that you do your best to create data at least of the same standard as the data in the base tables. You will be penalised if you create poor quality data.

E) For the CUSTOMER_MASTER table, your new customers should use one of the town, county and post code combinations used by an existing customer.

F) For the CAR_MASTER table, make sure that the model_name and car_group_name match primary key values in the appropriate tables and that the statistical information (e.g. date bought) is consistent. One car should reflect a date bought somewhere in 2011, the other 2012.

G) For the BOOKING_MASTER table, use the two cars you have created. This will be the most complex insert. Make sure that registration number and model name match rows that already exist in the applicable tables. The person who does the reservation should reflect a name already in the table. The miles in/out columns should be consistent with the mileage values in the CAR_MASTER table and the amount due should be find outd using the rates in the CAR_MASTER table. One row should reflect a completed booking while the other should reflect a booking that is still in progress, i.e. the car has not yet been returned.

H) OUTPUT: Provide a screen dump of your anonymous block and the output created when this is run.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M91252

Have any Question? 


Related Questions in DBMS

1 what is the difference between a column constraint and a

1. What is the difference between a column constraint and a table constraint? 2. What is a recursive join? 3. Explain the difference between an ORDER BY clause and a GROUP BY clause. 4. What is a CROSS JOIN? Give an exam ...

Database design continueduse the attached database to

Database Design Continued Use the attached database to complete the following assignment: You will be modifying the frmMainMenu to call the caption of each button from the table MainMenu and form or the report. 1) Create ...

Realtor database data redundancy and anomaliesbullconduct

Realtor Database: Data Redundancy and Anomalies • Conduct an in-depth examination and analysis of the database, Realtor.accdb. Apply the concepts of database systems that you have learned this week. For example, you may ...

Itech 1006 - database management systems

ITECH 1006 - Database Management Systems Assignment Overview For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create t ...

Database systemretail store database Database system Retail Store Database: Relationships

Database system Retail Store Database: Relationships • Conduct an in-depth examination and analysis of the database, Week2_RetailStore.accdb . Apply the concepts of database systems that you have learned this week. For e ...

Your assignment should be submitted in a single word doc

Your assignment should be submitted in a single Word doc (.doc or .docx). It should include your scenario diagram for Problem 1, and your Ruby source code for Problems 2 and 3. Contact me or post to our discussion board ...

Database design and use group assignmentassignment

Database Design and Use Group Assignment Assignment Description - In this assignment you will use your normalized "City Jail" database you have created in your assignment one. You will form a group (2-3 students) and imp ...

1 what is the difference between database and data

1. What is the difference between database and data warehouse? 2. What is OLAP? 3. What is a data cube? How do DSS take advantage of the structure?

Overviewfor this assignment you will implement and query a

Overview For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with da ...

In general my report talks about the database management

In general, my report talks about the Database management system. But I want to a specific topics of it in 3 pages. The topics are: 1. Comparison between File approach and Database approach. 2. Advantages and disadvantag ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate