FIT3171留学生讲解、Databases辅导、讲解SQL语言、SQL编程设计调试
辅导留学生 Statistics统计、回归、迭代|辅导R语
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. 1document 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. 2When 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. 4Above.
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. 5You 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. 6Insurer 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. 7for 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