FIT3171留学生代做、Databases代写、代做SQL语言、SQL编程设计调试

日期：2019-05-24 11:16

FIT2094 - FIT3171 Databases

S1 / 2019

Assignment 2 - SQL - ‘RDBMS’

WARNINGS

Severe penalties will apply If the important instructions marked with (!!!) through the

document is not observed.

(!!!) Emails must be addressed to the correct role account (Clayton) and the correct

lecturer account (Malaysia). Emails not compliant with this policy, mentioned since Week

1 of semester, will be DELETED.

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

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 1

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.

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 2

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).

A model to represent this system has been developed:

This model is available on Moodle as a PDF document.

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.

Neither of these files may be altered in any way.

(!!!) We will assess your assignment based on the UNMODIFIED SCHEMA and INSERT

files. Hence, if your assignment does not work with the original unmodified files, you will

be marked down.

You have also been supplied with a document FIT2094_FIT3171_A2_SOLUTIONS.sql.

(!!!) You MUST rename this script by prepending your id and authcate username to the

start of the filename, and use the correct unit code. e.g.,

20123345_abc123_FIT2094_A2_SOLUTIONS.sql.

The wrong filename format will result in deductions.

This script file will be referred to as YOUR SOLUTIONS script. Within this script there are

marked points where each of your solutions must be added.

All of the work for assignment 2 will take place in this document so please take great care to

keep regular backups, including off your computer on Google Drive, so you do not lose

Work.

(!!!) For version control, you need to have at least FIVE VERSIONS of YOUR SOLUTIONS

script, each reflecting a different stage of your development of the assignment, or

SEVERE PENALTIES will apply.

(!!!) Before starting work on the task you MUST complete the header by adding your

name etc., in YOUR SOLUTIONS script.

(!!!) In completing this assignment you are not permitted to manually:

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 3

● lookup a value in the database, obtain its primary key or highest or lowest value in

a column, or

● calculate values external to the database eg. on a calculator

and use such values in your answers.

You may ONLY use the data provided in the text of the questions.

As an example, say you were asked to find the name of the manufacturer for the vehicle (type)

titled "Gallardo Superleggera" - you cannot look in the VEHICLE_DETAIL table and find the

manufacturer_id for the manufacturer of this vehicle (say 100), and then use the value 100

directly to find the name (say “Automobili Lamborghini S.p.A.”) from the MANUFACTURER

table. Here you can only use the text you were given "Gallardo Superleggera".

(!!!) You MUST USE ONLY the values listed for the particular task you are working on in

this document. You must ensure that no unnecessary SQL conditions are used in the

WHERE clause of your SQL statements.

Your answers must recognise the fact that you have been given, with the supplied insert file,

only a small sample snapshot of a multiuser database, as such you must operate on the basis

that there will be more data in all of the tables of the database than you have been given.

Your answers must work regardless of the extra quantity of this extra "real" data and the

fact that multiple users will be operating in the tables at the same time. You must take

this aspect into consideration when writing SQL statements.

TASK 1: Data Definition [15 + 5 = 20 mks]

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

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 4

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 [9 + 1 + 1 = 11 mks]

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’ .

Some of the vehicle details, common to all three, are:

Insurer ID: sports-ute-449-12b

Title: Toyota Hilux SR Manual 4x2 MY14

Manufacturing Year: 2018

Fixed Rental Price: $200

Manufacturer: Toyota

Vendors: Toyota Chadstone (vendor_id = 1) and

4x4 Mechanical Experts (vendor_id = 2)

Purchase Price: $50000

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

Vehicle detail.

Each of the 3 RDBMS garages listed below will get a single unit of the vehicle, the vehicle will

be available for renting (i.e. not on ‘exhibition’ status) at each garage:

Caulfield VIC (the unit with rego RD3161)

- contact detail caulfield@rdbms.example.com

South Yarra VIC (the unit with rego RD3141)

- contact detail southy@rdbms.example.com

Melbourne Central VIC (the unit with rego RD3000)

- contact detail melbournec@rdbms.example.com

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 5

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: [6 + 6 + 6 + 6 = 24 mks]

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:

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 6

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: [10 + 15 + 20 = 45 mks]

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

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 7

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 (melbournec@rdbms.example.com)

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

(caulfield@rdbms.example.com) 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

(southy@rdbms.example.com) 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.

SUBMISSION REQUIREMENTS

Due Date: Friday Week 12 5PM.

For this assignment there is only one file to submit. You are required to submit only your

solutions script file to Moodle before the assignment due date/time.

If you need to make any comments, assumptions, etc your marker/tutor should be aware of

please place them at the head of your solutions script in the "Comments for your marker:"

section.

All working version history for must be tracked in the Google Drive folder shared with your tutor

(since Assignment 1). Remember the requirement for version control and the potential penalties

if it’s not followed.

(!!!) Late submission will incur penalties as outlined in the unit guide.

Severe penalties will apply If the important instructions marked with (!!!) through the

document is not observed.

EOF.

FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University 2019. 8