FIT9132: Introduction to Databases - Traffic Demerit System - IT Computer Science Assignment Help

Assignment Task :

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.  

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. 

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. 

This IT Computer Science Assignment has been solved by our IT Computer Science Experts at My Uni Paper. 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.