Reference no: EM132312927

Databases Assignment - SQL - 'RDBMS'

Introduction - A new startup company, Rental for Drivers - Bikes, Motorcars, and Sportcars (RDBMS) combines the business models of existing car rental companies, car-share systems, bike share systems, as well as sports car hire firms. RDBMS maintains several garages for vehicles across Australia.

For each garage, RDBMS assigns a garage code (an incremental number for each Garage with the first garage using a code of 10). The garage name, address, contact phone number, and email are also recorded. Each garage is assigned a manager and the garage's email address is uniquely company-issued.

Due to the size of some of the garages - some garages might just be a carpark with a small office which fits two vehicles, for example - hence a particular manager may manage several garages. Each manager is assigned a manager id. RDBMS record a manager's name and contact phone number.

RDBMS maintain records of current loans of vehicles to renters. Each renter is identified by a unique renter number. When a renter first registers or signs up at a garage in person (for document checks etc) - the garage where they register is recorded as their home garage. The name, address, email and mobile of each renter is held so that communications, such as overdue reminders and paperwork can be sent when necessary.

The details held about a type of vehicle (i.e. Vehicle Detail - these terms are used interchangeably) includes its insurance identifier used to identify a specific type of vehicle by RDBMS's insurer. The title (e.g. 'Mazda MX-5 Miata Convertible Roadster' or 'Santa Cruz V10 Carbon CC X01 Racing Bicycle'), vendor's name(s) -- more on this later, manufacturer's name, year manufactured, original purchase price, classification (Bike, regular Motorcar, or Sportscar), fixed weekly rental price, and engine capacity, if applicable, are recorded. Each manufacturer can be assumed to have a unique name.

A given type of vehicle (i.e. per Vehicle Detail) may be serviced by different vendors from 3 potential categories (Dealership who sold the vehicle, Mechanics who service/repair the vehicle, and Tuners who modify/tune the vehicle including any additions). This is required information for all vendors - a flag will specify what category the vendor is. However, RDBMS regards a vehicle as only being made by a single manufacturer. RDBMS assigns its own unique in-house numerical codes to identify Vendors and Manufacturers.

Details about a type of vehicle may include a number of different features (e.g. paint colour, spoilers, decals, sound system) which RDBMS wishes to record so that renters can use an online catalogue system to select vehicles by design as well as title and vendor name (e.g. if they only want cars with sound systems installed by a particular tuner).

Garages hold vehicle units - each vehicle unit is the property of a particular garage and is identified by the garage number and a garage-assigned local id number (these id numbers are repeated at each garage). Each individual vehicle unit will obviously have their own registration (rego, e.g. "XYZ123") issued by the government.

Some vehicle units are actually for exhibition and are not available for rent. They may only be used for a local test drive or to take selfies with. A flag is added to a vehicle unit to indicate if it is for exhibition or not. There may also be other vehicle units of the same title which are available for normal renting.

When a vehicle unit is rented (i.e. goes out on loan), the return date is recorded. A record of all rentals which take place is maintained. When a vehicle unit is returned from a loan its actual return date is recorded. Each vehicle can be loaned for exactly 1 week and must then be renewed to avoid a fine.

Renters may reserve vehicle units currently out on loan. The date and time on which the reserve was placed are recorded. A given vehicle unit may be reserved by several renters, it is made available based on the order in which the reserve was placed by the renter.

When a renter returns a vehicle unit, they may if they wish renew their rental and take the vehicle unit out for a further loan period provided it has not been reserved by another renter. To simplify things, the renewal is simply treated as a new rental for that renter. Vehicles must be returned to the garage from which they were rented-out (the garage owning the particular unit).

You have been supplied with

• a SCHEMA file FIT2094_FIT3171_A2_Schema_Start.sql which partially implements the model; and
• an INSERT file FIT2094_FIT3171_A2_Insert_Start.sql which inserts initial sample data.

TASK 1: Data Definition

For this task you are required to complete the following:

1.1 Add to your solutions script, the CREATE TABLE and CONSTRAINT definitions which are missing from the FIT2094_FIT3171_A2_Schema_Start.sql. You MUST use the relation and attribute names shown in the data model above to name tables and attributes which you add.

Remember: only modify YOUR SOLUTIONS, do not modify the SCHEMA START file.

1.2 Add the full set of DROP TABLE statements to your solutions script. In completing this section you must not use the CASCADE CONSTRAINTS clause as part of your DROP TABLE statement (you should include the PURGE clause).

Before proceeding with Task 2, you must run the file FIT2094_FIT3171_A2_Schema_Start.sql (which must not be altered in any way) followed by the extra definitions that you added in 1.1 Above.

In a script you can run a section of the script by highlighting the lines you wish to run and selecting the run button. If at any stage your tables are corrupted during working on this assignment you simply need to run your drop commands from 1.2 above and then rerun FIT2094_FIT3171_A2_Schema_Start.sql and your extra definitions that you added in 1.1 above.

TASK 2: Data Manipulation

Run the script FIT2094_FIT3171_A2_Insert_Start.sql to add some initial data into the tables you have created so far.

For this task you are required to complete the following sub-tasks in the same order they have mentioned:

2.1 RDBMS has just purchased its first 3 units of a recently released edition of a car. Potential renters will be interested in the 'metallic silver' paint and 'aluminium tray'.

You may make up any other reasonable data values you need to be able to add this Vehicle detail.

You may make up the other details of the garages with sensible values.

Your are required to treat this ADD of the vehicle details and the three units as a single transaction.

2.2 An Oracle sequence is to be implemented in the database for the subsequent insertion of records into the database for RENTER table.

Provide the CREATE SEQUENCE statement to create a sequence which could be used to provide primary key values for the RENTER table. The sequence should start at 10 and increment by 1.

2.3 Provide the DROP SEQUENCE statement for the sequence object you have created in question 2.2 above.

TASK 3 -

The sequence created in task 2 must be used to insert data into the database for the task 3 questions. For these questions you may only use the data supplied in this task.

You must correctly manage transactions with these tasks.

For this task you are required to complete the following sub-tasks in the same order they have been mentioned:

3.1 Assume today is MAY 4 2019.

Add a new renter in the database. Some of the details of the new renter are:

Name: Van DIESEL

Home Garage: Caulfield VIC

You may make up any other reasonable biodata values you need to be able to add this renter.

3.2 Immediately after becoming a member, at 4PM, Van places a reservation on a vehicle at the Melbourne Central VIC garage. Some of the details of the vehicle that he has placed a reservation on are:

Insurer ID: sports-ute-449-12b

Title: Toyota Hilux SR Manual 4x2 MY14

Manufacture Year: 2018

You may assume:

• RDBMS has not purchased any further vehicle units of the same model, beyond those which you inserted in Task 2.1
• that nobody has become a member of the garage between the time Van was added, and this reservation.

3.3 After 7 days from reserving the vehicle, Van receives a notification from the Melbourne Central VIC garage that the vehicle he had placed reservation on is available. Van is very excited about the vehicle being available as he needs to help a mate move house. Van goes to the garage and rents the vehicle at 2 PM on the same day of receiving the notification. You may assume that there is no other renter named Van DIESEL.

3.4 At 2 PM on the day the vehicle is due, Van goes to the garage and renews the rental as his friend still needs to buy materials to complete the new house which is half-finished. You may assume that there is no other renter named Van DIESEL.

TASK 4 -

For this task you are required to complete the following sub-tasks.

After using the system for some time, RDBMS has realised that it is necessary to

4.1 Record whether a vehicle unit needs maintenance (M) or written-off (W). If the vehicle unit is not damaged or written-off, then it is good (G) which means, it can be rented. The value cannot be left empty for this. Change the "live" database and add this required information for all the vehicle units currently in the database. You may assume that condition of all existing vehicle units will be recorded as being good. The information can be updated later, if need be.

4.2 Allow renters to be able to return the vehicle units they have loaned to any garage as RDBMS has several garages which are underutilised and/or are more convenient for renters. As part of this process RDBMS wishes to record which garage a particular rental is returned to. Change the "live" database and add this required information for all the rentals currently in the database. For all completed rentals, to this time, vehicle units were returned at the same garage from where those were loaned.

4.3 Some of the garages have become very large and it is difficult for a single manager to look after all aspects of the garage. For this reason RDBMS are intending to appoint three managers for the larger garages starting in the new year, with each specialising in a given collection - one specialising in Bikes, one in Motorcars, one in Sportscars.

The garages which continue to have one manager will ask this manager to manage the garages' Full collection. The number of garages which will require three managers is quite small (around 10% of the total company). Change the "live" database to allow RDBMS the option of appointing three managers to a garage and record, for all managers, which collection(s) they are managing.

In the new year, since the Melbourne Central VIC garage has a huge central collection of vehicles in comparison to Caulfield and South Yarra:

• Robert (Manager id: 1) who is currently managing the Caulfield garage has been asked to manage the Sportscar collection of Melbourne Central VIC garage, as well as the full collection at their own Caulfield garage.
• Cat (Manager id: 2) who is currently managing the South Yarra garage has been asked to manage the Bike and Motorcar collection of Melbourne Central VIC garage, as well as the full collection at their own South Yarra garage.

Write the code to implement these changes.

Attachment:- Assignment Files.rar