FIT9132 Introduction to Databases
2020 Semester 1
Assignment 2 - SQL - Traffic Demerit System (TDS)
Assignment weighting 15% - Lecturer in Charge: Manoj Kathpalia
The local state government wishes to develop a database to record the details of
"on-the-spot" traffic offences incurred by drivers. When a driver commits an offence they
are stopped and booked "on the spot" by a police officer and provided with an offence
notice. Offences cause drivers to incur demerit points which accumulate against the
drivers' licence.
The system records the details of all registered vehicles driven in the local government
area. A vehicle is identified by a Vehicle Identification Number (VIN). Background reading
on a VIN is available from several sites such as AutoCheck. Other attributes recorded for a
vehicle are the vehicle's year of manufacture, main colour, model name, manufacturer
name and vehicle type.
Drivers are identified by their licence number. Other attributes recorded for a driver are the
driver's mobile number, first name, last name, residential address, date of birth and expiry
date for the licence. It is assumed for this task that each driver has a mobile number and
that number will be unique.
Each demerit (potential driving offence) is identified by a demerit code. A description of the
demerit and the number of points incurred for that demerit are also recorded. For example,
the demerit with code 108, has a description of "Failing to give way, or stop, or remain
stopped" and results in 3 demerit points being incurred by the driver.
Each traffic offence by a driver is identified by an offence number. The location of the
offence and the date and time at which the offence occurred is also stored. In some
circumstances when a driver is stopped by a police officer, for example, a speeding
offence, the police officer may also identify other offences such as "Driving contrary to a
major defect notice". Each offence recorded has a unique offence number and is for only
one particular demerit. The police officer who issued the offence is also recorded.
Page 1 of 15
Once a driver has accumulated 12 demerit points over a period of three years the driver’s
licence is suspended for a period of 6 months. When an offence committed by a driver is
added to the system by the police officer, the system at that time will determine
automatically if the licence should be suspended or not. When this occurs the driver’s
licence is suspended from the date recorded for the offence which caused the points to
equal or exceed the permitted maximum points. The date at which the suspension began
and the date at which it ends are to be stored in the system. The driver, if suspended, is
not allowed to drive and driving while suspended will lead to a criminal charge and may
cause the driver to be jailed.
After a driver’s suspension ends, their total demerit points are reset to zero and they begin
accumulating points again. For any previously suspended driver, to determine the new
suspension date, the system will calculate the accumulated demerit points from the date
their last suspension ended.
Police officers are identified by an officer id. The system only records the officer's first
name and last name.
A data model has been created for Traffic Demerit System and is shown below (it is also
available from Moodle):
TDS Data Model
For this assignment, you will populate these tables with appropriate test data and write the
SQL queries specified below. You must ensure that any activities you need to carry out in the
database to complete the assignment conform to the requirements of the data model
displayed above.
Page 2 of 15
The schema/insert file for creating this model is available in the archive ass2-student.zip -
this file creates the Traffic Demerit System tables and populates several of the tables - you
should read this schema carefully and be sure you understand the various data
requirements. You must not alter the schema file in any manner, it must be used as
supplied. This schema file contains a single commit after the inserts have completed since
this is setting up an initial state of the database for you to work with, you should not use this
as your method of approach.
IMPORTANT points for you to observe, when completing this assignment, are:
1. The ass2-student.zip archive also contains four SQL scripts for you to code your
answers in, you should ensure these files are regularly pushed to GitLab server
so a clear development history is available for the marker to verify (a minimum
of four pushes are required). In each file, you must fill in the header details with
your name and student ID before beginning any work. Your script files must not
include any SPOOL or ECHO commands. Although you might include such
commands when testing your work they must be removed before submission (a 5
mark grade penalty will be applied if your documents contain spool or echo
commands)
2. You are free to make assumptions if needed. However, your assumptions must
align with the details here and in the assignment 2 forum and must be clearly
documented (see the required submission files).
REMEMBER you must keep up to date with the Moodle assignment 2 forum where
further clarifications may be posted (this forum is to be treated as your client).
Please be careful to ensure you do not post anything which includes your
reasoning, logic or any part of your work to this assignment forum as doing so
violates Monash plagiarism/collusion rules.
3. Queries that use subqueries and SQL conditions unnecessarily to get required data
will be penalised. Views must not be used in arriving at any solutions for the tasks
you are required to complete as part of this assessment.
4. In handling dates, the default date format must not be assumed; you must make
use of the TO_DATE and TO_CHAR functions in all date situations. Failure to
do so will incur a 50% grade penalty for questions involving dates.
5. In completing the following tasks, you should design your test data so that you
always get output for the SQL scripts/queries specified below - this may require
you to add further data as you move through completing the required tasks. Queries
that are correct but do not produce any output (“no rows selected” message)
using your test data will lose 50% of the marks allocated, so you should carefully
check your test data and ensure it thoroughly validates your SQL queries.
You may need to rerun the schema, especially when you have been experimenting with
your solutions and may have corrupted the database unintentionally. If you suspect that
there might be such problems, simply rerun the schema. The schema includes the
appropriate drop commands at the head of the file.
Page 3 of 15
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 (23 marks):
(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).
Page 4 of 15
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.
For this task ONLY , you can look up and include values for the loaded
tables/data directly where required. However, if you wish, you can still use
SQL to get any non-key values.
You are reminded again that in carrying out this task you must not modify any
data or add any further data to the tables which were previously populated by
the supplied schema file.
[14 marks]
For all subsequent questions (Q1b onwards) you are NOT permitted to manually:
● lookup a value in the database, obtain its primary key or the highest/lowest value in
a column, or
● calculate values external to the database, e.g., on a calculator and then use such
values in your answers.
You must ONLY use the data as provided in the text of the questions. Where a
particular case (upper case, lower case, etc.) for a word is provided you must only use
that case. You may divide names such as Zora Mandrey into the first name of Zora and
a last name of Mandrey if required. Failure to adhere to this requirement will result in
a mark of 0 for the relevant question.
At any point in your solution, when inserting an offence you must ensure that
SET SERVEROUTPUT ON;
has been run in your SQL session so that you receive visual feedback from the
checkPointsAccumulated trigger.
[6 marks]
Page 12 of 15
(iii) TDS has found that having just the vehicle's main colour in the database, in some cases, is
inadequate in helping identify a vehicle that has been involved in a traffic offence. As a
consequence, they have decided to now also record if any outer part other than the body of the
vehicle is of a different colour. At this stage, the only other parts TDS is interested in recording the
colour of are the Spoiler (SP), Bumper (BM) and Grilles (GR) but this may change if the need
arises and so it should be able to be changed easily. Each outer part code will have exactly 2
letters. Where the colour of grilles, spoiler (if any) or Bumper is the same as that of the body
colour, there is no need to record anything in the database.
TDS is also intending to find a solution such that new colours can be easily introduced when a
manufacturer releases a new colour. Each colour must be identified by a unique colour number
(which is auto-generated by the system) and has its colour description recorded as shown below
(sample data only shown).
This data must be collected from the current state of the vehicle table via SQL only (you cannot
assume the presence of any particular colour). Subsequent colours will be added directly to this
collection via INSERT statements. You are reminded again that you must not make use of
any PL/SQL in solving this task.
At this stage, the vehicles that have different colours for some of the above-mentioned outer parts
are as follows:
Vehicle 1:
VIN: ZHWEF4ZF2LLA13803
Manufacturer Name: Lamborghini
Model Name: Huracan EVO
Vehicle main colour: Grey
Spoiler colour: Black
Bumper colour: Grey
Grilles colour: Magenta
Page 13 of 15
Vehicle 2:
VIN: ZHWES4ZF8KLA12259
Manufacturer Name: Lamborghini
Model Name: Huracan Performante
Vehicle main colour: Black
Spoiler colour: Yellow
Bumper colour: Blue
Grilles colour: Black
Change the database to meet these requirements. Note: You should only use the necessary DDL
and DML statements to achieve these requirements.
[12 marks]
SUBMISSION REQUIREMENTS
Due Date: Friday 12th June 2020 at 5 PM (Week 12)
Please note, if you need to resubmit, you cannot depend on your tutors' availability, for this reason,
please be VERY CAREFUL with your submission. It is strongly recommended that you submit
several hours before this time to avoid such issues.
For this assignment there are four files you are required to submit:
● Q1a-tds-insert.sql
● Q1b-tds-dm.sql
● Q2-tds-queries.sql
● Q3-tds-mods.sql
If you need to make any comments to your marker/tutor please place them at the head of each of
your solution scripts in the "Comments for your marker:" section.
Do not zip these files into one zip archive, submit four independent SQL scripts. The individual files
must also have been pushed to the FIT GitLab server with an appropriate history as you developed
your solutions.
Late submission will incur penalties as outlined in the unit guide.
Page 14 of 15
Please note we cannot mark any work on the GitLab Server, you need to ensure that you
submit correctly via Moodle since it is only in this process that you complete the required student
declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the files you submit are the correct files - we
strongly recommend after uploading a submission, and prior to actually submitting, that you
download the submission and double-check its contents.
Your assignment MUST show a status of "Submitted for grading" before it will be marked.
If your submission shows a status of "Draft (not submitted)" it will not be assessed and will incur
late penalties after the due date/time.
Please carefully read the documentation under the "Assignment Submission" on the Moodle
Assessments page which covers things such as extensions and resubmission.
Criteria for marking:
Submissions will be graded on:
● the correct application of relational database principles,
● the correct handling of transactions and the setting of appropriate transaction boundaries
i.e. correct placement of commits, and
● the correct application of SQL statements and constructs to:
○ populate tables,
○ modify existing data in tables,
○ prepare reports by retrieving the required data in the required format, and
○ modify the "live" database structure to meet the expressed requirements (including
appropriate use of constraints). In making these modifications there must be no loss
of existing data or data integrity within the database.
Submissions will be grade penalised if they:
● contain SET ECHO … or SPOOL commands
● make use of views
● use subqueries and SQL conditions unnecessarily,
● do not use to_char/to_date where appropriate in handling dates,
● do not have an appropriate development history on the FIT GitLab server for all source files (at
least four pushes required).
Page 15 of 15