Rockstar Junkies

Rockstar Junkies Nick Willis Peresi Zigiriza Raj Rehill Stewart Scott Kirandeep Dhillon

896829 267260 475548 309664

[email protected] [email protected] [email protected] [email protected] [email protected]

Table of Contents Entity Relationship Diagram..........................................................................................3 Normalised Rational Model...........................................................................................4 Test Data........................................................................................................................5 TblDriver...................................................................................................................5 TblRace......................................................................................................................5 TblRaceResults..........................................................................................................5 TblRaceType.............................................................................................................5 TblLaps......................................................................................................................6 Sample Queries..............................................................................................................7 Average Lap Times...................................................................................................7 Average Position........................................................................................................7

Entity Relationship Diagram

Normalised Rational Model

Test Data TblDriver DriverID 1 2 3 4 5 6 7

Forename Stewart David Rolf Matt Edd Alex Tom

Surname Scott Moss Darwin Thornton Bauer Harris Blackshire

DateofBirth 11/03/89 4/13/1989 11/11/1989 12/12/1989 12/12/1989 12/12/1989 12/12/1979

StartDate 01/11/08 02/11/08 03/11/08 04/11/08

StartTime 13:00 14:00 09:00 18:00

RaceType 2 4 5 1

TblRace RaceID 1 2 3 4

TblRaceResults DriverID 1 1 2 2 2 3 4 5 5 5 6 7 7

RaceID 1 3 1 2 3 1 2 1 2 3 1 1 2

Position 1 3 2 2 1 6 1 4 3 2 3 5 4

TblRaceType RaceTypeID 1 2 3 4 5

RaceTypeTag Blood Gultch Canyon Kalidmor Western Kingdoms Outland

1st 10 1 6 20

2nd 5 2 5 5

3rd 2 3 4 1

4th 1 4 3 0

9

8

7

6

TblLaps RaceID 1 1 1 1 1 1 1 1 2 2 2 2

DriverID 1 1 1 1 2 2 2 2 1 1 1 1

Lap 1 2 3 4 1 2 3 4 1 2 3 4

Time 03:00 02:37 02:00 03:20 03:01 02:40 02:01 03:19 04:00 03:10 03:25 01:10

Sample Queries Average Lap Times //*assume x as variable defined in code to represent search perameter in the form of a forename, and y as a surname, z is later assigned to be the driver ID required, the query will be applied to a recordset, initialised as rst. SQL will be run from inside the VBA IDE, using ADODB recordsets for selection, via Rst.RunSQL, and for DML operations, the DoCmd.RunSql method will be used, DDL operations will be handled using the design view of MS-Access itself*// SQL injection:

SELECT DriverID FROM TblDriver WHERE Forename = x AND Surname = y;

Post-Query Validation: If(Rst.Length == 1) { Z = Rst.Fields(0); //assigns ID number to buffer variable } Else { If(Rst.Length > 1) { MessageBox.Show(“There are multiple selection, please supply ID number); //Initialise Textbox control and command button, set up events { If(Rst.Length == 0) { MessageBox.Show(“No matches found, please supply ID number”); //Initialise Textbox control and command button, set up events } } After this, then follows a query based on z SQL injection

SELECT DriverID, AVG(TblLaps.Lap) AS AverageTime FROM TblLaps WHERE TblLaps.DriverID = z;

Average Position //*assume x as variable defined in code to represent search parameter in the form of a forename, and y as a surname, z is later assigned to be the driver ID required, the query will be applied to a recordset, initialised as rst. SQL will be run from inside the VBA IDE, using ADODB recordsets for selection, via Rst.RunSQL, and for DML

operations, the DoCmd.RunSql method will be used. DDL operations will be handled using the design view of MS-Access itself*// SQL injection:

SELECT DriverID FROM TblDriver WHERE Forename = x AND Surname = y;

Post-Query Validation: If(Rst.Length == 1) { Z = Rst.Fields(0); //assigns ID number to buffer variable } Else { If(Rst.Length > 1) { MessageBox.Show(“There are multiple selection, please supply ID number); //Initialise Textbox control and command button, set up events { If(Rst.Length == 0) { MessageBox.Show(“No matches found, please supply ID number”); //Initialise Textbox control and command button, set up events } } After this, then follows a query based on z SQL injection

SELECT DriverID, AVG(TblRaceResult.Position) AS AveragePosition FROM TblRaceResult WHERE TblLaps.DriverID = z;