The problem TDS might face with the current database is if an offence is revoked for some valid reason, it is impossible for them to keep the offence information for the revoked offence in the database. TDS would like to fix this problem such that they are able to keep information about a revoked offence along with the other required information such as when it was revoked, who revoked the offence and the reason for revocation. For quick access, it has been decided that a column is also required to indicate Yes or No if an offence has been revoked or not.?

There will always be only one reason that can be associated with a revocation. TDS will not be able to add all the reasons for a revocation of the offences initially and so your solution should allow them to add new reasons as and when there is a need. At this stage, the only reasons for revocation of an offence TDS is interested in recording are First offence exceeding the speed limit by less than 10km/h (FOS), Faulty equipment used (FEU), Driver objection upheld (DOU), Court hearing (COH), and Error in proceedings (EIP). Each reason code will have exactly 3 letters.

When providing the solution, you must consider the following:

a. only around 1% of offences recorded are revoked and there can be hundreds of thousands of offences in the offence table,

b. all existing offences, after implementing this requirement, must automatically be recorded as not revoked,

c. appeals are made by completing a paper form and sending the completed form to TDS either by fax or mail and, at this stage, there is no need to record unsuccessful appeals in the database.

Transcribed Image Text:

VEHICLE veh_vin veh manuf veh mainceler veh manufname veh modname CHAR (17) Date VARCHAR (20) VARCHAR (30) VARCHAR (20) CHAR (2) HI involved in U OFFENCE off no off_datetime off_location UF dem code UF officer id UF lie_no UF veh_vin U incurs licno lic_frame lic_name lie_mobile lic_street lie_town lic_postcode lie dob lic_expiry DRIVER NUMERIC (8) Date VARCHAR (100) DO- NUMERIC (3) NUMERIC (8) CHAR (10) CHAR (17) CHAR (10) VARCHAR (30) VARCHAR (30) CHAR (10) VARCHAR (50) VARCHAR (20) P U CHAR (4) Date Date dem_code dem_description dem_points applies to DEMERIT charges NUMERIC (3) VARCHAR (75) NUMERIC (2) requires OFFICER officer id officer_fname officer Iname NUMERIC (8) VARCHAR (30) VARCHAR (30) SUSPENSION PF lie_no sus date sus enddate CHAR (10) Date Date VEHICLE veh_vin veh manuf veh mainceler veh manufname veh modname CHAR (17) Date VARCHAR (20) VARCHAR (30) VARCHAR (20) CHAR (2) HI involved in U OFFENCE off no off_datetime off_location UF dem code UF officer id UF lie_no UF veh_vin U incurs licno lic_frame lic_name lie_mobile lic_street lie_town lic_postcode lie dob lic_expiry DRIVER NUMERIC (8) Date VARCHAR (100) DO- NUMERIC (3) NUMERIC (8) CHAR (10) CHAR (17) CHAR (10) VARCHAR (30) VARCHAR (30) CHAR (10) VARCHAR (50) VARCHAR (20) P U CHAR (4) Date Date dem_code dem_description dem_points applies to DEMERIT charges NUMERIC (3) VARCHAR (75) NUMERIC (2) requires OFFICER officer id officer_fname officer Iname NUMERIC (8) VARCHAR (30) VARCHAR (30) SUSPENSION PF lie_no sus date sus enddate CHAR (10) Date Date

Answer rating: 100% (QA)

To address the problem faced by TDS regarding the revocation of offences and the need to keep track of the revoked offences along with relevant inform... View the full answer