University : Monash University UniLearnO is not sponsored or endorsed by this college or university.
Subject Code : FIT2094
Country : Australia
Assignment Task:

Assignment Tasks 

Using the supplied schema file (tds_ass2_schm_insert.sql) create the tables for the Traffic Demerit System and insert the supplied values. This provides you with a starting point for the following tasks. 

TASK 1: Data Manipulation : 

(a) Load selected tables with your own additional test data using the supplied Q1a-tds-insert.sql script file, and SQL commands which will insert, as a minimum, the following sample data - 

? 20 OFFENCES 

? 3 SUSPENSIONS 

Please note, these are the minimum number of entries you must insert ; you are encouraged to insert more to provide a richer data set to draw from. 

For this task only , data that you add in the database should follow the rules mentioned below:

1. The primary key values for this data should be hardcoded values (i.e., NOT make use of sequences) and must consist of values below 100.

2. Offences that you add must involve at least 12 different drivers.

3. You must not add any offences for the following driver: 

? Lion Lawless of 72 Aberg Avenue Richmond South 3121 (Licence no.: 100389)

4. Dates used must be chosen between the 1st January 2016 and 30th June 2019.

5. For each year from 2016 to 2019, you should add at least 4 offences.

6. The suspensions you add in the database should not be in just one year, 

e.g., you should not add all the suspensions in 2018.

7. The suspensions you add in the database should not belong to just one driver, e.g., you should not add all the suspensions for the driver with licence number 100100. 8. A suspended driver cannot incur extra offences under this system while suspended. If a driver commits a driving offence while suspended their vehicle is impounded and they are sent before the court (this means you must not add an offence to a suspended driver until their suspension period has expired). 

9. The supplied trigger shows the current total demerit points for a driver when you insert a new offence . You will need to run your inserts one by one in your offence date order, taking appropriate action if the drivers' accumulated points pass the 12 demerit point threshold. Any offence that you add, and any appropriate action that you may have to take as a result of the addition of the new offence, must be managed as a single transaction. 

TASK 2: SQL Queries: 

Your answers for these tasks must be placed in the supplied SQL Script Q2-tds-queries.sql 

ANSI joins must be used where two or more tables are to be joined , under no circumstances can "implicit join notation" be used - see the week 7 workshop slide 22 and tutorial 

(i) Show the demerit points and demerit description for all the demerits that either contains the word “heavy” or “Heavy” or start with the word “Exceed” in the description. The column headings in your output should be renamed as Demerit Points and Demerit Description. The output must be sorted in ascending format by demerit points and where two demerits have the same points sort them in ascending format of demerit description. Your output must have the form shown below. 

(ii) For all “Range Rover” and “Range Rover Sport” models, show the main colour, VIN and manufacture year for all the vehicles that were manufactured from 2012 to 2014. The column headings in your output should be renamed as Main Colour, VIN and Year Manufactured. The output must be sorted by manufacture year in descending format and where more than one vehicle was manufactured in the same year sort them by colour in ascending format. Your output must have the form shown below. 

(iii) Show the driver licence number, full name (firstname and lastname together), date of birth, full address (street, town and postcode together), suspension start date and suspension end date for all the drivers who have had their licence suspended in the last 30 months. You need SQL to calculate 30 months from the day this query would be executed by the user. The column headings in your output should be renamed as Licence No., Driver Fullname, DOB, Driver Address, Suspended On and Suspended Till. The output must be sorted by licence number in ascending format and where there is one licence number suspended more than once sort them by suspended date in descending format. Your output must have the form shown below. Your output can clearly be different from the following output.  

(iv) TDS would like to find out if there is any correlation between different months of a year and demerit codes so you have been assigned to generate a report that shows for ALL the demerits, the code, description, total number of offences committed for the demerit code so far in any month (of any year) and then the total of offences committed for the demerit code in each month (of any year) . The column headings in your output should be renamed as Demerit Code, Demerit Description, Total Offences (All Months), and then the first three letters of each month (with first letter in uppercase). The output must be sorted by Total Offences (All Months) column in descending format and where there is more than one demerit code with the same total, sort them by demerit code in ascending format. Your output must have the form shown below. Your output can clearly be different from the following output. 

(v) Find out which manufacturer's vehicles are involved in the highest number of offences which incur 2 or more demerit points. Show the manufacturer name and the total number of offences that the manufacturer’s vehicles are involved in. The column headings in your output should be renamed as Manufacturer Name and Total No. of Offences. The output must be sorted by Total No. of Offences column in descending format and where there is more than one manufacturer with the same total, sort them by manufacturer name in ascending format. Your output can clearly be different from the following output. 

(vi) Find out the drivers who have been booked more than once for the same offence by an officer with the last name as that of their last name. Show the driver licence number, driver full name (firstname and lastname together, officer number, officer full name (firstname and lastname together). The column headings in your output should be renamed as Licence No., Driver Name, Officer ID, Officer Name. The output must be sorted by driver licence number column in ascending format.  

(vii) For each demerit code for which an offence has been recorded, find out the driver/s who has/have been booked the most number of times. Show the demerit code, demerit description, driver licence number, driver full name (firstname and lastname together) and the total number of times the driver has been booked in the output. The column headings in your output should be renamed as Demerit Code, Demerit Description, Licence No., Driver Fullname and Total Times Booked. The output must be sorted by demerit code in ascending format and where for one demerit there is more than one driver booked the most number of times sort them by licence number in ascending format. Your output must have the form shown below. Your output can clearly be different from the following output. 

 

This Computer Science Assignment has been solved by our Computer Science Experts at onlineassignmentbank. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.

  • Uploaded By : admin
  • Posted on : June 03rd, 2019
  • Downloads : 6

Whatsapp Tap to ChatGet instant assistance